Subscribe: Alex_Kuznetsov
http://sqlblogcasts.com/blogs/alex_kuznetsov/rss.aspx
Added By: Feedage Forager Feedage Grade B rated
Language: English
Tags:
dbo taskstatuses  dbo  finishedat  insert  int  previousfinishedat  select  startedat  sum  taskid  taskstatuses taskid  taskstatuses 
Rate this Feed
Rate this feedRate this feedRate this feedRate this feedRate this feed
Rate this feed 1 starRate this feed 2 starRate this feed 3 starRate this feed 4 starRate this feed 5 star

Comments (0)

Feed Details and Statistics Feed Statistics
Preview: Alex_Kuznetsov

Alex_Kuznetsov





 



Remove all non-numeric characters from a string.

Sat, 23 Feb 2008 16:50:00 GMT

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.

(image)



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

Sat, 23 Feb 2008 16:46:00 GMT

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

Description
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.


(image)



Test Coverage: 100%

Tue, 12 Feb 2008 23:27:00 GMT

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.

 

(image)



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

Thu, 07 Feb 2008 16:07:00 GMT

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))goINSERT 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 1Violation 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 1INSERT 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 14INSERT INTO dbo.TaskStatuses(TaskID, Status, StartedAt, FinishedAt, PreviousFinishedAt)  VALUES(1, 'Reviewed', '20070105', '20070114', '20070105')/*Server: Msg 2627, Level 14, State 2, Line 1Violation 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   NULL1           Pending              20070104  20070105   200701031           Opened               20070114  20070119   200701051           Reviewed             20070125  20070129   20070119(4 row(s) affected)*/-- then move the last period to fill the gapUPDATE 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' ENDWHERE TaskID = 1 AND Fin[...]



List of tables without primary keys

Wed, 06 Feb 2008 20:56:00 GMT

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

(image)



When SUM of Six Floats Depends on Order of Adding

Fri, 26 Oct 2007 21:07:00 GMT

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.

(image)



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

Fri, 19 Oct 2007 15:54:00 GMT

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.

(image)



Mimicking a table variable parameter with an image.

Sun, 23 Sep 2007 21:18:00 GMT

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))

 

(image)



Yet another index covering tip

Fri, 14 Sep 2007 13:30:00 GMT

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.(image)



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

Fri, 14 Sep 2007 13:22:00 GMT

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)

*/

(image)