Alex_Kuznetsov

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

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

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

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.

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

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.

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

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)

"Close These Loopholes - Testing Database Modifications" 

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

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.

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