在查找问题的时候，无意搜索到这个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
create function F_TBL_VALS_FROM_STRING
(@p_str varchar(1000))
returns @tbl table (VAL int)
as
begin
set @p_str = @p_str + ','
declare @p_counter int
declare @p_len int
declare @p_curr_char varchar(1)
declare @p_char varchar(6)
declare @p_num int
set @p_len = len(@p_str)
set @p_counter = 1
set @p_curr_char = ''
set @p_char = ''
-- loop
while @p_counter <= @p_len
begin
set @p_curr_char = substring(@p_str, @p_counter, 1)
if (@p_curr_char <> ',')
begin
set @p_char = @p_char + @p_curr_char
end
else
begin
set @p_num = convert(int, @p_char)
insert into @tbl values(@p_num)
set @p_char = ''
end
set @p_counter = @p_counter + 1
end
return
end
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
/*
Recompile Procs, Views, UDF
In The Database
*/
DECLARE proccurs CURSOR
FOR
SELECT [name]
FROM sysobjects
WHERE xtype in ('p', 'v', 'fn')
OPEN proccurs
DECLARE @pname VARCHAR(60)
FETCH NEXT FROM proccurs INTO @pname
WHILE @@fetch_status = 0
BEGIN
EXEC sp_recompile @pname
FETCH NEXT FROM proccurs INTO @pname
END
CLOSE proccurs
DEALLOCATE proccurs
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
DECLARE @i INT
EXEC sp_executesql N'SELECT @i = 1', N'@i INT OUTPUT', @i OUTPUT
SELECT @i
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
declare tabcurs cursor
for
select name
from sysobjects
where xtype = 'u'
open tabcurs
declare @tname varchar(30)
fetch next from tabcurs into @tname
while @@fetch_status = 0
begin
dbcc dbreindex(@tname)
fetch next from tabcurs into @tname
end
close tabcurs
deallocate tabcurs
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
SELECT [name]
FROM syscolumns
WHERE [id] IN (SELECT [id]
FROM sysobjects
WHERE [name] = @table_name)
AND colid IN (SELECT SIK.colid
FROM sysindexkeys SIK
JOIN sysobjects SO ON SIK.[id] = SO.[id]
WHERE SIK.indid = 1
AND SO.[name] = @table_name)
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
SELECT TOP 1 UserId
FROM (SELECT TOP 50 PERCENT UserId
FROM T_USERS WITH (NOLOCK)
ORDER BY UserId ASC) AS T1
ORDER BY 1 DESC
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
SELECT SOP.[name]
FROM sysforeignkeys SFK
JOIN sysobjects SOF ON (SFK.fkeyid = SOF.[id])
JOIN sysobjects SOP ON (SFK.rkeyid = SOP.[id])
JOIN syscolumns C ON (C.[id] = SOF.[id] AND C.colid = SFK.fkey)
WHERE SOF.[name] = @foreign_table_name
AND C.[name] = @foreign_col_name
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
SELECT (SELECT SUM(1)
FROM employee t1
WHERE t1.emp_id <= t2.emp_id) AS rownum, t2.*
FROM employee t2
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
SELECT [name]
FROM syscolumns
WHERE [id] IN (SELECT [id]
FROM sysobjects
WHERE [name] = @foreign_table_name)
AND colid IN (SELECT fkey
FROM sysforeignkeys SFK
JOIN sysobjects SOF ON SFK.fkeyid = SOF.[id]
JOIN sysobjects SOP ON SFK.rkeyid = SOP.[id]
WHERE SOF.[name] = @foreign_table_name
AND SOP.[name] = @primary_table_name)
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
DECLARE tabcurs CURSOR
FOR
SELECT 'SOMEOWNER.' + [name]
FROM sysobjects
WHERE xtype = 'u'
OPEN tabcurs
DECLARE @tname NVARCHAR(517)
FETCH NEXT FROM tabcurs INTO @tname
WHILE @@fetch_status = 0
BEGIN
EXEC sp_changeobjectowner @tname, 'dbo'
FETCH NEXT FROM tabcurs INTO @tname
END
CLOSE tabcurs
DEALLOCATE tabcurs
11、俺自己整理的
Code
/*
应用：每个类别下的数据，各取3条
注意：此语句是用title来判断大小的，像实例中有2条数据是一样的，所以还存在点问题
*/
DECLARE @daTable TABLE(
title nvarchar(100),
category int
)
INSERT INTO @daTable
SELECT N'ABCDEF', 1
UNION ALL SELECT N'XYZ', 1
UNION ALL SELECT N'WXYS', 1
UNION ALL SELECT N'KDSADA', 1
UNION ALL SELECT N'DLSADJK', 1
UNION ALL SELECT N'ANMMAF', 1
UNION ALL SELECT N'BBBBBB', 1
UNION ALL SELECT N'BBBBBB', 1
UNION ALL SELECT N'ZZZZZZZ', 1
UNION ALL SELECT N'CCC', 2
UNION ALL SELECT N'PWKKKASDF', 2
UNION ALL SELECT N'WIOASF', 2
UNION ALL SELECT N'IOASFHSFA', 2
UNION ALL SELECT N'PPPPPPPPPP', 3
UNION ALL SELECT N'QQQQQQQQ', 3
UNION ALL SELECT N'OOOOOOOOO', 3
UNION ALL SELECT N'HHHH', 3
UNION ALL SELECT N'LLLLLL', 3
UNION ALL SELECT N'LLsisl', 4
SELECT title
, category
FROM @daTable AS t
WHERE ( SELECT COUNT(*)
FROM @daTable
WHERE category = t.category
AND title < t.title ) < 3
ORDER BY t.category
DECLARE @gencalendar TABLE (cal_date DATETIME PRIMARY KEY)
DECLARE @p_date SMALLDATETIME
DECLARE @date smalldatetime
SET @p_date = '20040101'
SET @date = '20040504'
WHILE @date <= '20041231'
BEGIN
INSERT INTO @gencalendar(cal_date)
VALUES(@p_date)
SET @p_date = dateadd(d, 1, @p_date)
END
SELECT *
FROM @gencalendar
根据帮助，使用WITH写的一个无限级类别的列表(可以显示层次关系，理论上无限级，实际会有实际情况不同有限制)
Code
CREATE TABLE [dbo].[tb_WEB_ArticleType] (
[At_Id] [int] IDENTITY (1, 1) Not Null,
[At_ActualId] [int] NOT NULL,
[At_ParentId] [int] NOT NULL CONSTRAINT DF_tb_WEB_ArticleType_ParentId DEFAULT(0),
[At_TypeClass] [int] NOT NULL CONSTRAINT DF_tb_WEB_ArticleType_TypeClass DEFAULT(0),
[At_TypeShowName] [nvarchar](100) NOT NULL CONSTRAINT DF_tb_WEB_ArticleType_TypeShowName DEFAULT(N''),
[At_TypeName] [nvarchar](100) NOT NULL CONSTRAINT DF_tb_WEB_ArticleType_TypeName DEFAULT(N''),
[At_OrderBy] [int] NOT NULL CONSTRAINT DF_tb_WEB_ArticleType_OrderBy DEFAULT(0),
CONSTRAINT [PK_tb_WEB_ArticleType] PRIMARY KEY CLUSTERED
(
[At_ActualId],
[At_TypeShowName]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tb_WEB_ArticleType_1] ON [dbo].[tb_WEB_ArticleType] ([At_Id])
CREATE NONCLUSTERED INDEX [IX_tb_WEB_ArticleType_2] ON [dbo].[tb_WEB_ArticleType] ([At_ActualId], [At_Id])
CREATE NONCLUSTERED INDEX [IX_tb_WEB_ArticleType_3] ON [dbo].[tb_WEB_ArticleType] ([At_CreatedDate] Desc)
CREATE NONCLUSTERED INDEX [IX_tb_WEB_ArticleType_4] ON [dbo].[tb_WEB_ArticleType] ([At_ActualId], [At_TypeShowName])
GO
exec sp_addextendedproperty N'MS_Description', N'文章分类表', N'user', N'dbo', N'table', N'tb_WEB_ArticleType'
exec sp_addextendedproperty N'MS_Description', N'自增标识', N'user', N'dbo', N'table', N'tb_WEB_ArticleType', N'column', N'At_Id'
exec sp_addextendedproperty N'MS_Description', N'实名ID', N'user', N'dbo', N'table', N'tb_WEB_ArticleType', N'column', N'At_ActualId'
exec sp_addextendedproperty N'MS_Description', N'上级分类标识', N'user', N'dbo', N'table', N'tb_WEB_ArticleType', N'column', N'At_ParentId'
exec sp_addextendedproperty N'MS_Description', N'分类类型(0为系统类别,1为实名新增的)', N'user', N'dbo', N'table', N'tb_WEB_ArticleType', N'column', N'At_TypeClass'
exec sp_addextendedproperty N'MS_Description', N'显示分类名称', N'user', N'dbo', N'table', N'tb_WEB_ArticleType', N'column', N'At_TypeShowName'
exec sp_addextendedproperty N'MS_Description', N'内置分类名称(用于页面标签)', N'user', N'dbo', N'table', N'tb_WEB_ArticleType', N'column', N'At_TypeName'
exec sp_addextendedproperty N'MS_Description', N'排序号', N'user', N'dbo', N'table', N'tb_WEB_ArticleType', N'column', N'At_OrderBy'
GO
WITH RecursionArticleType(At_Id, At_ActualId, At_ParentId, At_TypeClass, At_TypeShowName, At_TypeName, At_OrderBy, At_sLevel, At_iLevel, At_Ladder)
AS
(
SELECT At_Id, At_ActualId, At_ParentId, At_TypeClass, At_TypeShowName, At_TypeName, At_OrderBy, At_sLevel=CONVERT(nvarchar(1000),''), At_iLevel=1, At_Ladder=CONVERT(nvarchar(3000), At_Id)
FROM tb_WEB_ArticleType
WHERE
At_ActualId = 173764 AND At_ParentId = 0
UNION ALL SELECT
R1.At_Id,
R1.At_ActualId,
R1.At_ParentId,
R1.At_TypeClass,
R1.At_TypeShowName,
R1.At_TypeName,
R1.At_OrderBy,
CONVERT(nvarchar(1000), REPLICATE('*', At_iLevel)),
At_iLevel + 1,
At_Ladder = CONVERT(nvarchar(3000), RTRIM(At_Ladder)+N'|'+cast(R1.At_Id AS nvarchar(100)))
FROM tb_WEB_ArticleType as R1
JOIN RecursionArticleType as R2 ON R1.At_ParentId = R2.At_Id
)
SELECT * FROM RecursionArticleType ORDER BY At_Ladder, At_OrderBy, At_Id
结果（可以把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
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_ArticleTypeSubIds]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_ArticleTypeSubIds]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/*
递归列出指定类别下的子类别，@OperateType为预留参数，用于以后扩展
Written By Net205
Date:2009-01-08
*/
CREATE FUNCTION [dbo].[fn_ArticleTypeSubIds] (@PId int, @ActualId int, @iLevel int, @OperateType int)
RETURNS @ReturnTable TABLE(
RtIdent INT IDENTITY(1,1) NOT NULL,
RtId int,
RtLevel int
)
AS
BEGIN
DECLARE @TempTable TABLE(
TtIdent INT IDENTITY(1,1) NOT NULL,
TtId int,
TtLevel int
)
--取子类别的下所有类别写入表变量
SET @iLevel = isNull(@iLevel, 0) + 1
INSERT INTO @TempTable(TtId, TtLevel) SELECT At_Id, @iLevel FROM tb_WEB_ArticleType WHERE At_ActualId=@ActualId AND At_ParentId=@PId ORDER BY At_OrderBy, At_Id
--循环表变量
DECLARE @getId int, @getLevel int, @ident int
SELECT TOP 1 @getId = TtId, @getLevel = TtLevel FROM @TempTable ORDER BY TtIdent
Set @ident = @@RowCount
WHILE @ident=1
BEGIN
DELETE FROM @TempTable WHERE TtId=@getId
INSERT INTO @ReturnTable(RtId, RtLevel) SELECT @getId, @getLevel
INSERT INTO @ReturnTable(RtId, RtLevel) SELECT RtId, RtLevel FROM dbo.fn_ArticleTypeSubIds(@getId, @ActualId, @iLevel, @OperateType) ORDER BY RtIdent
SELECT TOP 1 @getId = TtId, @getLevel = TtLevel FROM @TempTable ORDER BY TtIdent
Set @ident = @@RowCount
END
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
使用：
Code
SELECT b.*, a.RtLevel AS At_iLevel, REPLICATE(' ', a.RtLevel) AS At_sLevel
FROM dbo.fn_ArticleTypeSubIds(0,173764,0,0) a
INNER JOIN tb_WEB_ArticleType b ON a.RtId=b.At_Id
ORDER BY a.RtIdent
扫一扫
1913
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
