Alex_Kuznetsov

Remove all non-numeric characters from a string.

2008-02-23T16:50:00Z

The problem came up on newsgroups. The set based solution is quite simple:

SELECT REPLICATE('0', COUNT(*) - MAX(CASE WHEN c<>'0' THEN n ELSE 0
END))
+
CAST((SUM(CAST((c + LEFT('00000000000000000',n)) AS INT)) /10) AS
VARCHAR(100))
FROM(
SELECT c, ROW_NUMBER()OVER(ORDER BY Number DESC) AS n FROM(SELECT
SUBSTRING('asdf004506õÎÉÏÃÄÅ8sd',Number, 1) AS c, Number FROM
Data.Numbers) t
WHERE c IN('0','1','2','3','4','5','6','7','8','9')
) t

-----------------
0045068

It assumes that there is an auxiliary table Data.Numbers.

Vote for TOP(@n) OVER(PARTITION BY ... ORDER BY ...) syntax

2008-02-23T16:46:00Z

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254390

To address a very common requirement, Itzik Ben-Gan is suggesting the following syntax:

TOP(@n) OVER(PARTITION BY ... ORDER BY ...)

For instance, the requirement "select three latest orders for every customer" would be implemented as

SELECT TOP(3) OVER(PARTITION BY CustomerID ORDER BY OrderDate DESC) ...

Advantages: all intentions are expressed in one place, in an intuitively clear way, similar to existing OLAP functions syntax. The alternative is to use ROW_NUMBER(), and the implementation of the requirement is scattered all over the query, nd requires an inline view.

Test Coverage: 100%

2008-02-12T23:27:00Z

In my C# projects I am using NCover (http://www.ncover.com/). It shows me which lines of code are executed by my unit tests (I am using NUnit). I added more unit tests in which my stored procedures blow up and raise exceptions. Now all my catch() branches are executed, and the coverage is 100%. Every line in my source code is executed at least once.

Using a Foreign key constraint to check validity of History Windows (Start - End Date Windows)

2008-02-07T16:07:00Z

CREATE TABLE dbo.TaskStatuses(TaskID INT NOT NULL,
Status VARCHAR(20),
StartedAt DATETIME NOT NULL,
FinishedAt DATETIME NOT NULL,
PreviousFinishedAt DATETIME NULL,
CONSTRAINT PK_TaskStatuses_TaskID_FinishedAt PRIMARY KEY(TaskID, FinishedAt),
CONSTRAINT UNQ_TaskStatuses_TaskID_PreviousFinishedAt UNIQUE(TaskID, PreviousFinishedAt),
CONSTRAINT FK_TaskStatuses_TaskID_PreviousFinishedAt
   FOREIGN KEY(TaskID, PreviousFinishedAt)
   REFERENCES dbo.TaskStatuses(TaskID, FinishedAt),
CONSTRAINT CHK_TaskStatuses_PreviousFinishedAt_Before_StartedAt CHECK(PreviousFinishedAt <= StartedAt)
)
go

INSERT INTO dbo.TaskStatuses(TaskID, Status, StartedAt, FinishedAt, PreviousFinishedAt)
VALUES(1, 'Pending', '20070101', '20070103', NULL)

-- you cannot have more than one beginning of history chain per task:
INSERT INTO dbo.TaskStatuses(TaskID, Status, StartedAt, FinishedAt, PreviousFinishedAt)
VALUES(1, 'Pending', '20070104', '20070105', NULL)
/*
Server: Msg 2627, Level 14, State 2, Line 1
Violation of UNIQUE KEY constraint 'UNQ_TaskStatuses_TaskID_PreviousFinishedAt'. Cannot insert duplicate key in object 'TaskStatuses'.
The statement has been terminated.
*/
INSERT INTO dbo.TaskStatuses(TaskID, Status, StartedAt, FinishedAt, PreviousFinishedAt)
VALUES(1, 'Pending', '20070104', '20070105', '20070103')

-- history windows cannot overlap:
INSERT INTO dbo.TaskStatuses(TaskID, Status, StartedAt, FinishedAt, PreviousFinishedAt)
VALUES(1, 'Opened', '20070104', '20070109', '20070105')
/*
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with TABLE CHECK constraint 'CHK_TaskStatuses_PreviousFinishedAt_Before_StartedAt'. The conflict occurred in database 'RiskCenter', table 'TaskStatuses'.
The statement has been terminated.
*/
INSERT INTO dbo.TaskStatuses(TaskID, Status, StartedAt, FinishedAt, PreviousFinishedAt)
VALUES(1, 'Opened', '20070114', '20070119', '20070105')

-- you cannot fill a gap in one insert
-- Gap between Jan 5 and Jan 14

INSERT INTO dbo.TaskStatuses(TaskID, Status, StartedAt, FinishedAt, PreviousFinishedAt)
VALUES(1, 'Reviewed', '20070105', '20070114', '20070105')
/*
Server: Msg 2627, Level 14, State 2, Line 1
Violation of UNIQUE KEY constraint 'UNQ_TaskStatuses_TaskID_PreviousFinishedAt'. Cannot insert duplicate key in object 'TaskStatuses'.
The statement has been terminated.
*/

-- to fill a gap, add to the end of the chain:
INSERT INTO dbo.TaskStatuses(TaskID, Status, StartedAt, FinishedAt, PreviousFinishedAt)
VALUES(1, 'Reviewed', '20070125', '20070129', '20070119')

/*
TaskID      Status               StartedAt FinishedAt PreviousFinishedAt
----------- -------------------- --------- ---------- ------------------
1           Pending              20070101 20070103   NULL
1           Pending              20070104 20070105   20070103
1           Opened               20070114 20070119   20070105
1           Reviewed             20070125 20070129   20070119

(4 row(s) affected)
*/

-- then move the last period to fill the gap
UPDATE dbo.TaskStatuses SET StartedAt = CASE WHEN FinishedAt = '20070129' THEN '20070105' ELSE StartedAt END,
FinishedAt = CASE WHEN FinishedAt = '20070129' THEN '20070114' ELSE FinishedAt END,
PreviousFinishedAt = CASE WHEN FinishedAt = '20070129' THEN '20070105' ELSE '20070114' END
WHERE TaskID = 1 AND FinishedAt IN('20070129', '20070119')

SELECT TaskID, Status, CONVERT(CHAR(8), StartedAt, 112) StartedAt,
   CONVERT(CHAR(8), FinishedAt, 112) FinishedAt,
   CONVERT(CHAR(8), PreviousFinishedAt, 112) PreviousFinishedAt
   FROM dbo.TaskStatuses ORDER BY FinishedAt

/*
TaskID      Status               StartedAt FinishedAt PreviousFinishedAt
----------- -------------------- --------- ---------- ------------------
1           Pending              20070101 20070103   NULL
1           Pending              20070104 20070105   20070103
1           Reviewed             20070105 20070114   20070105
1           Opened               20070114 20070119   20070114

(4 row(s) affected)
*/

List of tables without primary keys

2008-02-06T20:56:00Z

SELECT
t.TABLE_SCHEMA, t.TABLE_NAME, tc.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLES t
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
ON t.TABLE_SCHEMA = tc.TABLE_SCHEMA
AND t.TABLE_NAME = tc.TABLE_NAME
AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
WHERE t.TABLE_TYPE = 'BASE TABLE'
AND tc.CONSTRAINT_NAME IS NULL
ORDER BY t.TABLE_NAME

When SUM of Six Floats Depends on Order of Adding

2007-10-26T21:07:00Z

Following the discussion started by Hugo Kornelis and Old Grumpy DBA, an example of inexact calculations with float datatype:

declare @big float, @small float, @sum1 float, @sum2 float, @sum3 float, @i INT
SELECT @big = 12345678901234.50, @small = 0.01, @i = 0
SELECT @sum1 = @big, @sum2 = 0, @sum3 = 0
WHILE @i < 5 BEGIN
SELECT @sum1 = @sum1 + @small, @sum2 = @sum2 + @small, @sum3 = @sum3 + @small
SET @i = @i + 1
END
SELECT @sum2 = @sum2 + @big
SELECT @sum1, @sum2, @sum3

---------------------- ---------------------- ----------------------
12345678901234.5 12345678901234.6 0.05

(1 row(s) affected)

This is why you cannot have sums of floats in indexed views - they are not deterministic, they may depend on order of adding.

When It Is OK to Use SELECT * in Production Code.

2007-10-19T15:54:00Z

If you google up 'never use SELECT * in production code' you will get many many hits.
Clearly in many cases using SELECT * makes your code vulnerable to changes in underlying table(s) and as such should be avoided.
Yet I don't think the common rule of thumb 'never use SELECT * in production code' should be used
without thinking, I don't think it should be blindly applied in all the situations.
For instanse, consider a request to display top five sales for every region and a simple query that satisfies it

SELECT SalesAmount, RegionName, LongListOfOtherColumns
FROM(
SELECT SalesAmount, RegionName, LongListOfOtherColumns,
ROW_NUMBER() OVER(PARTITION BY RegionName ORDER BY SalesAmount DESC) AS rn
FROM Sales.Sales
) t WHERE rn < 6

Does repeating SalesAmount, RegionName, LongListOfOtherColumns twice make your code nay better/safer/readable?
I don't think so. Consider the following alternative:

SELECT *
FROM(
SELECT SalesAmount, RegionName, LongListOfOtherColumns,
ROW_NUMBER() OVER(PARTITION BY RegionName ORDER BY SalesAmount DESC) AS rn
FROM Sales.Sales
) t WHERE rn < 6

It is shorter, easier to maintain, and just as robust as the original query.
You have explicitly listed the columns
in your subquery, so you are already insulated from any changes in Sales.Sales table.
You don't need an additional layer of protection.
Repeating the list of columns twice only makes your code more prone to errors.

Mimicking a table variable parameter with an image.

2007-09-23T21:18:00Z

Following a discussion with SQL Server MVP Joe Webb at PASS conference in Denver, I decided to re-post this approach including all the bells and whistles. Of course, the general idea of the approach was borrowed from Erland Sommarskog's article on Arrays and Lists in SQL.

1. Packing an array of numbers in an image. I have to change the order of bytes when I store an array of long integers in an array of bytes, as follows:

static byte[] UlongsToBytes(ulong[] ulongs) {
   int ifrom = ulongs.GetLowerBound(0);
   int ito   = ulongs.GetUpperBound(0);
   int l = (ito - ifrom + 1)*8;
   byte[] ret = new byte[l];
   int retind = 0;
   for(int i=ifrom; i<=ito; i++)
   {
           ulong v = ulongs[ i ];
           ret[retind++] = (byte) (v >> 0x38);
           ret[retind++] = (byte) (v >> 0x30);
           ret[retind++] = (byte) (v >> 40);
           ret[retind++] = (byte) (v >> 0x20);
           ret[retind++] = (byte) (v >> 0x18);
           ret[retind++] = (byte) (v >> 0x10);
           ret[retind++] = (byte) (v >> 8);
           ret[retind++] = (byte) v;
   }

   return ret;

This code snippet is also included in Erland's article at

http://www.sommarskog.se/arrays-in-sql-2005.html

2. Binding the array of bytes as an image parameter:

static void Main(string[] args)
{
DateTime d1, d2, d3;
d1 = DateTime.Now;
d2 = DateTime.Now;
string sddd = d1.ToString();
ulong[] ul = new ulong[10000];
for(uint i=0; i<10000; i++)
{
ul[ i ] = i;
}
string sss = UlongsToString(ul);
byte[] ba = UlongsToBytes(ul);
string directInsert = UlongsToDirectInsert(ul);
try
{
string source = @"packet size=4096;integrated security=SSPI;data source=MyPC\MyNamedInstance;persist security info=False;initial catalog=Sandbox";
SqlConnection conn = new SqlConnection(source);
conn.Open();
SqlCommand a = new SqlCommand("INSERT BigintsTarget(bi) SELECT * FROM dbo.ParseImageIntoBIGINTs(@BIGINTs)", conn);
a.CommandType = System.Data.CommandType.Text;
a.Parameters.Add(new SqlParameter("@BIGINTs", System.Data.SqlDbType.Image,2147483647));
for(int q=0; q<10; q++)
{
a.Parameters[0].Value = ba;
int res = a.ExecuteNonQuery();
}
d2 = DateTime.Now;
SqlCommand b = new SqlCommand("INSERT BigintsTarget1(bi) SELECT * FROM dbo.ParseVarcharMAXIntoBIGINTs(@BIGINTs)", conn);
b.CommandType = System.Data.CommandType.Text;
b.Parameters.Add(new SqlParameter("@BIGINTs", System.Data.SqlDbType.VarChar,2147483647));
for(int q=0; q<10; q++)
{
b.Parameters[0].Value = sss;
int res = b.ExecuteNonQuery();
}
//b.ExecuteNonQuery();
conn.Close();
}
catch(Exception ex)
{
string s = ex.Message;
int t=0;
t++;
}
d3 = DateTime.Now;
string sdiff1 = d1.ToString() + " - " + d2.ToString();
string sdiff2 = d2.ToString() + " - " + d3.ToString();
string tttttt = "sdfa";
}
}

3. Objects on SQL Server side:

-- Assuming that there already is an auxiliary Numbers table:

SELECT Number*8 + 1 AS StartFrom, Number*8 + 8 AS MaxLen INTO dbo.ParsingNumbers FROM dbo.Numbers

CREATE FUNCTION dbo.ParseImageIntoBIGINTs(@BIGINTs IMAGE)
RETURNS TABLE AS RETURN(
SELECT CAST(SUBSTRING(@BIGINTs, StartFrom, 8) AS BIGINT) Num FROM dbo.ParsingNumbers WHERE MaxLen <= DATALENGTH(@BIGINTs))

Yet another index covering tip

2007-09-14T13:30:00Z

Suppose you want you NCI index cover a query. Suppose you want to make sure that a column, let's say CustomerID, is stored in the index.
Even if your table is currently clustered on CustomerID, it is still a good practice to explicitly include CustomerID in your NCI. The reason is simple: sometimes we can drop the CI and build another CI on another column(s). Your index should still store CustomerID, so that it still covers your query.

Not qualifying column names with table aliases may lead to hard-to-find errors

2007-09-14T13:22:00Z

CREATE TABLE #t1(i INT)
INSERT #t1 VALUES(1)
INSERT #t1 VALUES(2)

CREATE TABLE #t2(i INT)
INSERT #t2 VALUES(1)
INSERT #t2 VALUES(3)

-- incorrect result
SELECT i FROM #t1
WHERE NOT EXISTS(SELECT 1 FROM #t2 WHERE #t2.i = i)
/*
i
-----------

(0 row(s) affected)
*/

-- correct result
SELECT i FROM #t1 t1
WHERE NOT EXISTS(SELECT 1 FROM #t2 WHERE #t2.i = t1.i)
/*
i
-----------
2

(1 row(s) affected)

*/

The price of not qualifying stored procedure's names with schema names

2007-09-03T17:19:00Z

Just a very brief example:

1. Create 10K stored procedures.

DECLARE @i INT, @SQL NVARCHAR(300)
SELECT @i = 0
WHILE @i < 10000 BEGIN
SET @i = @i + 1
SET @SQL = 'CREATE PROCEDURE dbo.DummyProc' + CAST(@i AS NVARCHAR(10)) + ' AS RETURN 0'
EXEC sp_executesql @SQL
END
GO

2. Run them.

DECLARE @i INT, @j INT, @d DATETIME, @SQL NVARCHAR(300), @SQL_to_run NVARCHAR(300)
SELECT @i = 0, @j = 0, @d = getdate()
WHILE @j < 3 BEGIN
SET @j = @j + 1
IF @j = 1 BEGIN
    SET @SQL = 'EXEC DummyProc'
END ELSE BEGIN
    SET @SQL = 'EXEC dbo.DummyProc'
END
WHILE @i < 10000 BEGIN
    SET @i = @i + 1
    SET @SQL_to_run = @SQL + CAST(@i AS NVARCHAR(10))
    --PRINT @SQL_to_run
    IF @j <3 BEGIN
      EXEC sp_executesql @SQL_to_run
    END
END
SELECT DATEDIFF(ms, @d, GEtdate()) as ms
SELECT @i = 0, @d = getdate()
END
GO

3. Clean up.

DECLARE @i INT, @SQL NVARCHAR(300)
SELECT @i = 0
WHILE @i < 10000 BEGIN
SET @i = @i + 1
SET @SQL = 'DROP PROCEDURE dbo.DummyProc' + CAST(@i AS NVARCHAR(10))
EXEC sp_executesql @SQL
END
GO

The results:

ms - no schema names
-----------
3860

(1 row(s) affected)

ms - with schema names
-----------
640

(1 row(s) affected)

ms - the overhead of running the loop without sp_executesql calls
-----------
46

However, if you repeatedly call one and the same procedure, you will not notice much difference one way or another:

CREATE PROCEDURE dbo.DummyProc
AS
RETURN 0
GO
CREATE PROCEDURE dbo.Run_SQL_100K_times
@SQL NVARCHAR(30)
AS
DECLARE @i INT, @d DATETIME
SELECT @i = 0, @d = getdate()
WHILE @i < 100000 BEGIN
SET @i = @i + 1
EXEC sp_executesql @SQL
END
SELECT DATEDIFF(ms, @d, GEtdate()) as ms, @SQL AS SQL
GO
EXEC dbo.Run_SQL_100K_times 'EXEC DummyProc'
EXEC dbo.Run_SQL_100K_times 'EXEC dbo.DummyProc'
GO
DROP PROCEDURE dbo.Run_SQL_100K_times
DROP PROCEDURE dbo.DummyProc
GO

ms          SQL
----------- ------------------------------
2330        EXEC DummyProc

(1 row(s) affected)

ms          SQL
----------- ------------------------------
2310        EXEC dbo.DummyProc

(1 row(s) affected)

Our next article went live on simple-talk.com

2007-09-03T00:02:00Z

"Close These Loopholes - Testing Database Modifications"

http://www.simple-talk.com/sql/t-sql-programming/close-these-loopholes---testing-database-modifications/

Using a placeholder

2007-08-30T20:54:00Z

I usually create a few files, let's say 5 GB each, on my hard drive. Whenever I run out of disk space, I can just drop a couple of those and keep working - that buys me some time. If I am in a hurry, dropping a placeholder file is a quick fix.

Of course, at some later time I need to clean up my hard drive, and re-create dropped placeholder files.

Selecting a random number for each row

2007-08-24T16:57:00Z

create view wrapped_rand_view
as
select rand( ) as random_value
go
create function wrapped_rand()
returns float
as
begin
declare @f float
set @f = (select random_value from wrapped_rand_view)
return @f
end

select --your columns--, dbo.wrapped_rand() wrapped_rand
from YourTable