SQL备忘
修改基础表后，刷新关联视图的两种方法：http://www.cnblogs.com/Sabre/archive/2012/04/23/2467144.html
更新ntext字段的方法：http://www.cnblogs.com/miaomiaoga/archive/2006/07/31/463858.html
断开指定用户对某个数据库的连接：
1.在指定的数据库中使用sp_who存储过程获得正在连接此数据库的用户
例如 sp_who sa
2.使用KILL 终止 SPID
例如 kill 10
分组编号（参考）：
---2000 ---------------------------------------------------------------- -- Author :fredrickhu(小F，向高手学习) -- Date :2010-04-06 20:38:41 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据：[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([YM] int,[Name] varchar(1),[OrderNo] varchar(6)) insert [tb] select 200901,'a','014123' union all select 200901,'b','014723' union all select 200901,'c','015145' union all select 200902,'a','015146' union all select 200902,'b','015178' union all select 200902,'c','015100' union all select 200903,'a','014174' union all select 200901,'a','015197' union all select 200903,'a','016127' union all select 200901,'b','014567' union all select 200901,'a','016200' --------------开始查询-------------------------- select *,item=(select count(1)+1 from tb where ym=t.ym and name=t.name and OrderNo<t.OrderNo) from [tb] t order by ym,name ----------------结果---------------------------- /* YM Name OrderNo item ----------- ---- ------- -------------------- 200901 a 014123 1 200901 a 015197 2 200901 a 016200 3 200901 b 014567 1 200901 b 014723 2 200901 c 015145 1 200902 a 015146 1 200902 b 015178 1 200902 c 015100 1 200903 a 014174 1 200903 a 016127 2 (11 行受影响) */
---------------------------------------------------------------- -- Author :fredrickhu(小F，向高手学习) -- Date :2010-04-06 20:38:41 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据：[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([YM] int,[Name] varchar(1),[OrderNo] varchar(6)) insert [tb] select 200901,'a','014123' union all select 200901,'b','014723' union all select 200901,'c','015145' union all select 200902,'a','015146' union all select 200902,'b','015178' union all select 200902,'c','015100' union all select 200903,'a','014174' union all select 200901,'a','015197' union all select 200903,'a','016127' union all select 200901,'b','014567' union all select 200901,'a','016200' --------------开始查询-------------------------- select *,item=row_number()over(partition by ym,name order by OrderNo) from [tb] ----------------结果---------------------------- /* YM Name OrderNo item ----------- ---- ------- -------------------- 200901 a 014123 1 200901 a 015197 2 200901 a 016200 3 200901 b 014567 1 200901 b 014723 2 200901 c 015145 1 200902 a 015146 1 200902 b 015178 1 200902 c 015100 1 200903 a 014174 1 200903 a 016127 2 (11 行受影响) */
分组取最大/最新值（借用上面分组编号的测试数据 参考）：
select YM,[Name],OrderNo from ( select *,item=(select count(1)+1 from tb where YM=t.YM and Name=t.Name and OrderNo>t.OrderNo) from [tb] t ) t where item=1 order by YM,[Name] select a.* from [tb] a where not exists ( select 1 from [tb] where YM=a.YM and [Name]= a.[Name] and OrderNo> a.OrderNo ) order by YM,[Name] select a.* from [tb] a where exists ( select count(1) from [tb] where YM=a.YM and [Name]= a.[Name] and OrderNo> a.OrderNo having count(1)<1 ) order by YM,[Name] select a.* from [tb] a inner join ( select YM,[Name],max(OrderNo) as OrderNo from [tb] group by YM,[Name] ) b on a.YM=b.YM and a.[Name]=b.[Name] and a.OrderNo=b.OrderNo order by YM,[Name] select a.* from [tb] a where OrderNo = ( select top 1 OrderNo from [tb] where YM=a.YM and [Name]= a.[Name] order by OrderNo desc ) order by YM,[Name]
在SQL Server中区分大小写的几种方法（参考）
从数据库Collate到存储过程到函数，各种方法都有，选择适合你的。
第一种：
ALTER TABLE tb ALTER COLUMN colname nvarchar(100) COLLATE Chinese_PRC_CI_AS --不区分大小写 ALTER TABLE tb ALTER COLUMN colname nvarchar(100) COLLATE Chinese_PRC_CS_AS --区分大小写 alter database 数据库 COLLATE Chinese_PRC_CS_AS
第二种：--创建如下用户自定义函数(UDF)
CREATE FUNCTION StrComp(@Str1 VARCHAR(50),@Str2 VARCHAR(50)) --ALTER FUNCTION StrComp(@Str1 VARCHAR(50),@Str2 VARCHAR(50)) RETURNS INTEGER AS BEGIN DECLARE @i INTEGER --DECLARE @Str1 VARCHAR(50) --DECLARE @Str2 VARCHAR(50) DECLARE @y INT --SET @Str1='a' --SET @Str2='A' SET @i=0 --SELECT ASCII(SUBSTRING(@Str1,@i+1,1)) SET @y=1 DECLARE @iLen INT SET @iLen = LEN(LTRIM(RTRIM(@Str1))) IF LEN(LTRIM(RTRIM(@Str1))) < LEN(LTRIM(RTRIM(@Str2))) --THEN SET @iLen = LEN(LTRIM(RTRIM(@Str2))) WHILE (@i < @iLen) BEGIN IF (ASCII(SUBSTRING(@Str1,@i+1,1))=ASCII(SUBSTRING(@Str2,@i+1,1))) --THEN SET @i = @i +1 ELSE BEGIN SET @y=0 BREAK END END RETURN @y END
测试：
select * from Table1 Where dbo.StrComp(Field1,'aAbB') =1
第三种：
SQL Server 数据库中的文本信息可以用大写字母、小写字母或二者的组合进行存储。例如，姓氏可"SMITH"、"Smith"或"smith"等形式出现。
数据库是否区分大小写取决于 SQL Server 的安装方式。如果数据库区分大小写，当搜索文本数据时，必须用正确的大小写字母组合构造搜索条件。例如，如果搜索名字"Smith"，则不能使用搜索条件"=smith"或"=SMITH"。
另外，如果服务器被安装成区分大小写，则必须用正确的大小写字母组合提供数据库、所有者、表和列的名称。如果提供的名称大小写不匹配，则 SQL Server 返回错误，报告"无效的对象名"。
当使用关系图窗格和网格窗格创建查询时，查询设计器始终正确地反映出服务器是否区分大小写。但是，如果在 SQL 窗格中输入查询，则必须注意使名称与服务器解释名称的方式相匹配。
如果服务器是用不区分大小写的选项安装的，则提示若要确定服务器是否区分大小写，请执行存储过程 sp_server_info，然后检查第18 行的内容。如果服务器是用不区分大小写的设置安装的，则 sort_order 选项将设置为"不区分大小写"。可以从查询分析器运行存储过程。
第四种：
select * from servers where convert(varbinary, name)=convert(varbinary, N'RoCKEY')
第五种：
如ascii('a')再配合Substring()一起用。
-- 生成纯数字密码 SELECT LEFT(abs(checksum(newid()))+'0000000000',10) -- 生成数字字母混合密码 SELECT LEFT(LOWER(NEWID()),8)
--From URL http://www.sqlservercentral.com/articles/SQL+Puzzles/2878/ CREATE PROCEDURE dbo.uspCreatePassword( @UpperCaseItems SMALLINT--指定含有的大写个数 , @LowerCaseItems SMALLINT--指定含有的小写个数 , @NumberItems SMALLINT--指定含有的数字个数 , @SpecialItems SMALLINT)--指定含有的特殊字符个数 AS SET NOCOUNT ON DECLARE @UpperCase VARCHAR(26) , @LowerCase VARCHAR(26) , @Numbers VARCHAR(10) , @Special VARCHAR(13) , @Temp VARCHAR(8000) , @Password VARCHAR(8000) , @i SMALLINT , @c VARCHAR(1) , @v TINYINT -- Set the default items in each group of characters SELECT @UpperCase = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' , @LowerCase = 'abcdefghijklmnopqrstuvwxyz' , @Numbers = '0123456789' , @Special = '!@#$%&*()_+-=' , @Temp = '' , @Password = '' -- Enforce some limits on the length of the password IF @UpperCaseItems > 20 SET @UpperCaseItems = 20 IF @LowerCaseItems > 20 SET @LowerCaseItems = 20 IF @NumberItems > 20 SET @NumberItems = 20 IF @SpecialItems > 20 SET @SpecialItems = 20 -- Get the Upper Case Items SET @i = ABS(@UpperCaseItems) WHILE @i > 0 AND LEN(@UpperCase) > 0 SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@UpperCase) + 1 , @c = SUBSTRING(@UpperCase, @v, 1) , @UpperCase = CASE WHEN @UpperCaseItems < 0 THEN STUFF(@UpperCase, @v, 1, '') ELSE @UpperCase END , @Temp = @Temp + @c , @i = @i - 1 -- Get the Lower Case Items SET @i = ABS(@LowerCaseItems) WHILE @i > 0 AND LEN(@LowerCase) > 0 SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@LowerCase) + 1 , @c = SUBSTRING(@LowerCase, @v, 1) , @LowerCase = CASE WHEN @LowerCaseItems < 0 THEN STUFF(@LowerCase, @v, 1, '') ELSE @LowerCase END , @Temp = @Temp + @c , @i = @i - 1 -- Get the Number Items SET @i = ABS(@NumberItems) WHILE @i > 0 AND LEN(@Numbers) > 0 SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Numbers) + 1 , @c = SUBSTRING(@Numbers, @v, 1) , @Numbers = CASE WHEN @NumberItems < 0 THEN STUFF(@Numbers, @v, 1, '') ELSE @Numbers END , @Temp = @Temp + @c , @i = @i - 1 -- Get the Special Items SET @i = ABS(@SpecialItems) WHILE @i > 0 AND LEN(@Special) > 0 SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Special) + 1 , @c = SUBSTRING(@Special, @v, 1) , @Special = CASE WHEN @SpecialItems < 0 THEN STUFF(@Special, @v, 1, '') ELSE @Special END , @Temp = @Temp + @c , @i = @i - 1 -- Scramble the order of the selected items WHILE LEN(@Temp) > 0 SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Temp) + 1 , @Password = @Password + SUBSTRING(@Temp, @v, 1) , @Temp = STUFF(@Temp, @v, 1, '') SELECT @Password /* -- exec uspCreatePassword 2,2,2,2 ---------- MT&4g(5j exec uspCreatePassword 2,2,4,0 ------------ 38Io84Vw */
/** use master SELECT name, dbo.fn_RandomPassword(8,8) as UserPassword FROM sysusers dbo.fn_RandomPassword(最小长度,最大长度) **/ --- Start vwRand --- SET quoted_identifier ON GO SET ansi_nulls ON GO IF EXISTS (SELECT * FROM sys.sysobjects WHERE id = Object_id(N'[dbo].[vwRand]') and OBJECTPROPERTY(id, N'IsView') = 1) DROP VIEW [dbo].[vwrand] GO --created by Rick Toner on 03/16/2007 --updated by Rick Toner on 03/16/2007 CREATE VIEW dbo.vwrand AS SELECT Rand() AS R GO SET quoted_identifier off GO SET ansi_nulls ON GO --Uncomment the below line if you need to implement security --GRANT SELECT ON [vwRand] TO [UserAccountOrDataseRole] --- End vwRand --- --- Start fn_Rand --- SET quoted_identifier ON GO SET ansi_nulls ON GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = Object_id(N'[dbo].[fn_Rand]') AND xtype IN (N'FN',N'IF',N'TF')) DROP FUNCTION [dbo].[fn_rand] GO --created by Rick Toner on 03/16/2007 --updated by Rick Toner on 03/16/2007 CREATE FUNCTION fn_rand( ) RETURNS FLOAT AS BEGIN RETURN (SELECT r FROM vwrand) END GO SET quoted_identifier off GO SET ansi_nulls ON GO --Uncomment the below line if you need to implement security --GRANT EXECUTE ON [fn_Rand] TO [UserAccountOrDataseRole] --- End fn_Rand --- --- Start fn_RandomPassword --- SET quoted_identifier ON GO SET ansi_nulls ON GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = Object_id(N'[dbo].[fn_RandomPassword]') AND xtype IN (N'FN',N'IF',N'TF')) DROP FUNCTION [dbo].[fn_randompassword] GO --created by Rick Toner on 03/16/2007 --updated by Rick Toner on 03/16/2007 CREATE FUNCTION fn_randompassword (@MinLength SMALLINT = 5, @MaxLength SMALLINT = 8) RETURNS VARCHAR(100) AS BEGIN DECLARE @Password VARCHAR(30) DECLARE @Length SMALLINT DECLARE @Position SMALLINT DECLARE @Characters VARCHAR(55) DECLARE @LetterPosition INT DECLARE @Letter CHAR(1) SET @Characters = 'aeubcdfghjklmnpqrstvwxyzBCDFGHJKLMNPQRSTVWXYZ23456789' SET @Password = '' -- determine length SET @Length = @MinLength + Round(dbo.Fn_rand() * (@MaxLength - @MinLength),0,0) SET @Position = 1 WHILE @Position <= @Length BEGIN BEGIN BEGIN SET @LetterPosition = CONVERT(INT,Round((dbo.Fn_rand() * (Len(@Characters) - 1)),0,0),1) + 1 SET @Letter = Substring(@Characters,@LetterPosition,1) SET @Password = @Password + @Letter END END SET @Position = @Position + 1 -- incriment counter END -- return password RETURN @password END GO SET quoted_identifier off GO SET ansi_nulls ON GO --Uncomment the below line if you need to implement security --GRANT EXECUTE ON [fn_RandomPassword] TO [UserAccountOrDataseRole] --- End fn_RandomPassword ---
