HoneyHoney

IF EXISTS (select 1 from sys.objects WHERE type = 'FN' AND name = 'removeXmlNode')
DROP FUNCTION dbo.removeXmlNode
GO
CREATE FUNCTION dbo.removeXmlNode(@XMLString NVARCHAR(MAX), @NodeName NVARCHAR(100))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @xml XML = CAST(@xmlString AS XML);
SET @xml.modify('delete //*[local-name() = sql:variable("@NodeName")]');
    RETURN CAST(@xml AS NVARCHAR(MAX));
END;
GO
IF EXISTS (select 1 from sys.objects WHERE type = 'FN' AND name = 'insertXmlNode')
DROP FUNCTION dbo.insertXmlNode
GO
CREATE FUNCTION dbo.insertXmlNode(@XMLString NVARCHAR(MAX), @BaseNodeName NVARCHAR(100), @NodeName NVARCHAR(100), @NodeValue NVARCHAR(100))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @xml XML = CAST(@xmlString AS XML);
DECLARE @node XML = CAST(CONCAT('<', @NodeName, '>', @NodeValue, '</', @NodeName, '>') AS XML);
SET @xml.modify('insert(sql:variable("@node")) after(//*[local-name() = sql:variable("@BaseNodeName")])[1]');
    RETURN CAST(@xml AS NVARCHAR(MAX));
END;
GO
IF EXISTS (select 1 from sys.objects WHERE type = 'FN' AND name = 'updateXmlNode')
DROP FUNCTION dbo.updateXmlNode
GO
CREATE FUNCTION dbo.updateXmlNode(@XMLString NVARCHAR(MAX), @NodeName NVARCHAR(100), @NodeValue NVARCHAR(100))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @xml XML = CAST(@xmlString AS XML);
SET @xml.modify('replace value of ((//*[local-name() = sql:variable("@NodeName")]/text())[1]) with sql:variable("@NodeValue")');
    RETURN CAST(@xml AS NVARCHAR(MAX));
END;
GO

CREATE TABLE [dbo].[test1](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [a] [int] NULL,
    [b] [int] NULL,
    [c] [int] NULL,
    [d] [int] NULL,
    [e] [int] NULL,
    [f] [int] NULL,
PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

insert into test1 (a, b, c, d, e, f)
VALUES (
ABS(Checksum(NewID()) % 99),
ABS(Checksum(NewID()) % 99),
ABS(Checksum(NewID()) % 99),
ABS(Checksum(NewID()) % 99),
ABS(Checksum(NewID()) % 99),
ABS(Checksum(NewID()) % 99)
)
go 900000 

我希望找出每行中的最大数，有几个方法：

1. 用Values
   

SELECT  id,         (         SELECT  MAX(x)         FROM    ( VALUES ( a), ( b), ( c), ( d), ( e), ( f) ) t (x)         ) FROM    test1;

2. 用unpivot

SELECT id, MAX(list.value) AS greatest FROM test1 UNPIVOT(value FOR ColumName IN(a,b,c,d,e,f)) list GROUP BY id

3. 类似的Values

select id, (SELECT MAX(value) FROM (select t.a value UNION ALL SELECT t.b UNION ALL select t.c UNION ALL select t.d UNION ALL select t.e UNION ALL select t.f ) data ) maxvalue from test1 t

4. 用CASE
冗长，容易出错，但是性能最好的一个方法。

对于上面四种方式，性能依次是4>1=3>2。用第一种方法比较容易方便，不容易出错。

当然，还有另外一种方法就是用其他的开发语言，比如Ruby：

require 'tiny_tds'
tdate = Time.now
client = TinyTds::Client.new(:username => 'username', :password => 'password', :dataserver => 'localhost',:port => 1433, :database => 'event_service', :timeout => 300)
data = client.execute("select * from test1;");
tmp = []
data.each do |t|
tmp << t['a'] << t['b'] << t['c'] << t['e'] << t['f']
max = tmp.max
# puts t['id'].to_s + "\t" + max.to_s
tmp = []
end
delta = Time.now - tdate
puts "Elapsed time: --#{delta}"

还是第一次遇到column LIKE column的情况。

背景是这样。需要找到当前数据库里面所有column，但想排除某些特定的column，而且希望这个是能够通过配置即可自动实现而不需要改query。关键就在创建这个配置表，并且LIKE的使用。

首先创建一个#tmp的临时表，写入配置。此后将临时表的value写到LIKE之后。

想了一下，用到这种方法的情况挺少的，蛮有意思。

做数据修复的时候，有时候需要动态生成rollback script。下面就是用AdventureWorks2012做个例子自动生成rollback script。

USE AdventureWorks2012;

DECLARE @personId int = 1

CREATE TABLE #tmp_person(
 BusinessEntityID int,
 PersonType nchar(2),
 NameStyle bit,
 Title nvarchar(8),
 FirstName nvarchar(50),
 MiddleName nvarchar(50),
 LastName nvarchar(50),
 Suffix nvarchar(10),
 EmailPromotion int,
 AdditionalContactInfo xml,
 Demographics xml,
 rowguid uniqueidentifier,
 ModifiedDate datetime);

CREATE TABLE #tmp_address(
 AddressID int,
 AddressLine1 nvarchar(60),
 AddressLine2 nvarchar(60),
 City nvarchar(30),
 StateProvinceID int,
 PostalCode nvarchar(15),
 SpatialLocation geography,
 rowguid uniqueidentifier,
 ModifiedDate datetime);


INSERT INTO #tmp_person
SELECT TOP 1 *
FROM Person.Person
WHERE BusinessEntityID = @personId

--delete data
--DELETE FROM Person.Person WHERE BusinessEntityID = @personId


PRINT 'ROLLBACK SCRIPT'
PRINT '----------------------------------------'

SET NOCOUNT ON

CREATE TABLE #tables (id int identity PRIMARY KEY, table_name varchar(50), tmp_table_name varchar(50))

--tables to insert the data back
INSERT INTO #tables(table_name, tmp_table_name) VALUES ('Person.Person', '#tmp_person')
INSERT INTO #tables(table_name, tmp_table_name) VALUES ('Person.Address', '#tmp_address')

DECLARE @columns NVARCHAR(MAX) = N'';
DECLARE @sql NVARCHAR(MAX) = N'';
DECLARE @tablename VARCHAR(50),
  @tmptablename VARCHAR(50),
  @rowcount int

------------------- AdventureWorks2012 -------------------
PRINT 'USE AdventureWorks2012;'

DECLARE table_cursor CURSOR FAST_FORWARD LOCAL
FOR
SELECT table_name, tmp_table_name
FROM #tables
ORDER BY id

OPEN table_cursor
FETCH NEXT FROM table_cursor
INTO @tablename, @tmptablename

WHILE @@FETCH_STATUS = 0
 BEGIN

 --Check if data exists in temp table. Skip if no data
 SET @sql = N'SELECT 1 FROM ' + @tmptablename
 EXEC(@sql)

 SET @rowcount = @@rowcount

 IF @rowcount <> 0
 BEGIN
  -- get columns names to be inserted
  SELECT @columns = STUFF(
  (SELECT '+'''''', '''''' + ISNULL(CAST(' + name + ' AS varchar(2000)), ''NULL'')' FROM sys.columns c
   WHERE object_id = object_id(@tablename)
   ORDER BY c.column_id
   FOR XML PATH('')
  ), 1, 12, '')


  -- format INSERT script
  SET @sql = N'SELECT @sqlout = STUFF(
          (SELECT N''INSERT INTO ' + @tablename + ' VALUES ('''''' + ' + @columns + N' + N''''''); ''
          FROM ' + @tmptablename +'
          FOR XML PATH('''')
          ), 1, 0, '''')'

  EXEC sys.sp_executesql @sql, N'@sqlout NVARCHAR(MAX) OUTPUT',  @sqlout = @sql OUTPUT

  SET @sql = REPLACE(@sql, '''NULL''', 'NULL')
  SET @sql = REPLACE(@sql, 'INSERT INTO', CHAR(10)+'INSERT INTO')

  PRINT @sql

 END

 FETCH NEXT FROM table_cursor
 INTO @tablename, @tmptablename

 END

CLOSE table_cursor
DEALLOCATE table_cursor

实例如下

在寻找一些关于SQL SERVER CURSOR FAST_FORWARD的文章，发现这么一个案例。都知道用游标可能会导致运行速度很慢，但又如何去处理呢？在这个案例里面，他需要处理近60000条记录，每条记录又会衍生更多的操作等等。在添加上FAST_FORWARD之后情况，情况就好了很多。

http://mitchelsellers.com/blogs/2011/09/23/sql-server-forward_only-cursor-for-performance.aspx

在这里，MSDN更加详细地解释了FAST_FORWARD.
http://blogs.msdn.com/b/sqlqueryprocessing/archive/2009/08/12/understanding-sql-server-fast-forward-server-cursors.aspx