MSSQL相关应用收集

在查找问题的时候，无意搜索到这个Blog：http://blogs.x2line.com，发现有好一些内容不错，实际项目完全有可能用到，特收集起来

 1、SQL Tip: HowTo Convert Delimited String to Table

Many times we need to deal with lists in T-SQL, for instance we need to have a list of values to pass it to IN clause in SQL. This list is supposed to look like delimited string. However we can not pass it to T-SQL procedure as a string because T-SQL compiler can not interpret delimited string as a list of values.

For MSSQL - the list of values is of table type, so we need to have a tool to convert delimited string to table. The function below does just that. So we are able to pass delimited strings to the procedure, convert this string to table and use it later in SQL IN close or in joins.

 

Code

 

2、MSSQL: Recompile Stored Procedures, Views, Functions

Sometimes after massive MSSQL schema update to online scalable production environment there is a need to recompile all stored procedures, user-defined functions and views in the database in order to MSSQL will refresh stores procedures execution plans stored in memory in order to reflect recent schema changes. Below is a small MSSQL code snipped written solely for maintenance purposes. It goes through database objects and performs recompilation using sp_recompile system stored procedure.

Code


 This MSSQL code snipped can easily be implemented as a stored procedure.

 

3、T-SQL: Output Parameter in sp_executesql

 sp_executesql is MSSQL system procedure used to execute dynamic SQL (SQL string built dynamically). Sometimes there is a need to return output parameter from that dynamic SQL. Below is a small T-SQL code snippet that demonstrates how to do it.

 

Code

 

 

 4、MSSQL Tip: Rebuild Clustered Indexes

 Rebuild clustered index in the database tables. This code uses cursor to run through user tables. Also it uses dbcc dbreindex command to rebuild index. Use this with care since each rebuild hangs table up.

 

Code

 

 5、MSSQL Tip: Get Table Primary Key Column(s)

 Suppose the following MSSQL server problem. We know table name and need to get its Primary Key columns programmatically. In order to do it we will use the following system tables:

sysobjects - for all user objects.
sysindexkeys - for indexes and keys.
syscolumns - for tables columns.

The query will look like so:

 

Code

sysindexkeys.indid equals to 1 for clustered indexes.

 

6、T-SQL Query: Select Middle Record

 SELECT TOP 1 query in T-SQL helps to find the first or the last record of the table data sorted by some criteria. But what if we need to find exactly middle record entry in the table ? Below is a small T-SQL query snippet that demonstrates a technique how to get middle record in a single query (pay attention to the inline view used):

 

Code

 

7、MSSQL Tip: Get Primary Table in Relationship

How to get Primary Table, having the name of Foreign table in relation ? We can use the following MSSQL system tables:

sysobjects - for all user objects.
sysforeignkeys - for foreign keys.
syscolumns - for tables columns.

Build the following query:

 

Code

Pay attention how sysobjects is used twice in a query.


8、ROWNUM [ PL/SQL ] Analog for T-SQL

 There is no ROWNUM Analog in T-SQL. ROWNUM is used in PL/SQL to to get the number of rows the query returns in the same query. There is no easy way to perform the same trick in T-SQL query. Small snippet below demonstrates the easy way to achieve the same goal using inner view.

Code

 

 Use in small tables only, since it is not performant on large databases.


9、MSSQL Tip: Get Connected Columns in Related Tables

 How to get Foreign Key related columns, having the names of Primary and Foreign tables ? We can use the following MSSQL system tables:

sysobjects - for all user objects.
sysforeignkeys - for foreign keys.
syscolumns - for tables columns.

Build the following query:

 

Code

 

10、MSSQL: Change tables owner to dbo with sp_changeobjectowner

 Sometimes there is a need to change all tables in the database to be owned by dbo for maintenance or to fix up accidental errors. All tables owned by dbo schema is usually best practices in the database application development with MSSQL while we can meet different approaches in real life...

The following small SQL code snippet goes through all user tables in the database and changes their owner to dbo. It uses sp_changeobjectowner system stored procedure:

 

Code

 

 11、俺自己整理的

 

Code

 

根据帮助，使用WITH写的一个无限级类别的列表(可以显示层次关系，理论上无限级，实际会有实际情况不同有限制)

 

Code

 

结果（可以把At_sLevel 的值替换成空格就会呈现层次关系，At_iLevel 为当前是第几级分在）：

At_Id       At_ActualId At_ParentId At_TypeClass At_TypeShowName    At_TypeName   At_OrderBy   At_sLevel    At_iLevel   At_Ladder
--------- ----------- ----------- ------------ ----------------  -------------  ----------  -----------  ----------- ------------
4           173764       0           0            分类名称      分类名称                1                           1           4
5           173764       0           1            分类名称1      分类名称1             2                          1           5
11          173764      5           1            xxxxx          xxxxx                 1          *              2           5|11
12          173764      11         1            uuuuu              uuuuu             1          **             3           5|11|12
6           173764       0           1            分类名称xx                             3                            1           6

 

由于MSSQL2000不支持WITH,所以又整了一个递归函数(注：对于这样的无限级分类，递归在效率上是很低的，但却很好使，代码也明了)

 

Code

 

使用：

Code
posted @ 2009-01-13 12:13  Net205 Blog  阅读(480)  评论(0)  编辑  收藏  举报