0

Print VARCHAR(MAX) more than 8000

Posted on Wednesday, March 18, 2015 by 醉·醉·鱼 and labeled under 


--http://stackoverflow.com/questions/7850477/how-to-print-varcharmax-using-print-statement
--Kelsey
DECLARE @Counter INT
SET @Counter = 0
DECLARE @TotalPrints INT
SET @TotalPrints = (LEN(@sql) / 4000) + 1
WHILE @Counter < @TotalPrints 
BEGIN
    PRINT SUBSTRING(@sql, @Counter * 4000, 4000)
    SET @Counter = @Counter + 1
END
PRINT LEN(@sql)
0

3 Functions for XML

Posted on Wednesday, March 11, 2015 by 醉·醉·鱼 and labeled under ,
写了3个简单的function去处理XML。


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
0

get maximum value across columns

Posted on Monday, March 02, 2015 by 醉·醉·鱼 and labeled under ,
假如有一张表
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
    2. SELECT  id,         (         SELECT  MAX(x)         FROM    ( VALUES ( a), ( b), ( c), ( d), ( e), ( f) ) t (x)         ) FROM    test1;
  2. 用unpivot
    3. 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
    4. 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
    5. 冗长，容易出错，但是性能最好的一个方法。

对于上面四种方式，性能依次是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}"