A while ago i presented my approach to generate a nums table here.
DECLARE @count INT = 1000;
WITH
[Nums1] AS ( SELECT 1 AS [Value] UNION SELECT 2 AS [Value] )
, [Nums2] AS ( SELECT A.* FROM [Nums1] AS A, [Nums1] AS B, [Nums1] AS C)
, [Nums3] AS ( SELECT A.* FROM [Nums2] AS A, [Nums2] AS B, [Nums2] AS C)
, [Nums4] AS ( SELECT A.* FROM [Nums3] AS A, [Nums3] AS B )
, [Numbers] AS ( SELECT TOP(@count) -1 + ROW_NUMBER() OVER(ORDER BY [Value]) AS [Value] FROM[Nums4] )
SELECT * FROM [Numbers];
Because we only use this code once to fill the table we don’t really care that it is not very fast. Today i discovered there is a way to speed it up in this wonderful book: Inside Microsoft® SQL Server® 2008: T-SQL Querying:
WITH
[Nums1] AS ( SELECT 1 AS [Value] UNION SELECT 2 AS [Value] )
, [Nums2] AS ( SELECT A.* FROM [Nums1] AS A, [Nums1] AS B, [Nums1] AS C)
, [Nums3] AS ( SELECT A.* FROM [Nums2] AS A, [Nums2] AS B, [Nums2] AS C)
, [Nums4] AS ( SELECT A.* FROM [Nums3] AS A, [Nums3] AS B )
, [Numbers] AS ( SELECT TOP(@count) -1 + ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS [Value] FROM[Nums4] )
SELECT * FROM [Numbers];
Yay for features like ORDER BY (SELECT <Constant>).
Here is a quick snippet that demonstrates how you can invoke a parametrized stored procedure with PowerShell:
$CreateTraceCommand = $SqlConnection.CreateCommand();
$CreateTraceCommand.CommandType = [System.Data.CommandType] "StoredProcedure";
$CreateTraceCommand.CommandText = "sp_trace_create";
$TraceIdParameter = $CreateTraceCommand.Parameters.Add("@traceid", [System.Data.SqlDbType] "Int");
$TraceIdParameter.Direction = [System.Data.ParameterDirection] "Output";
$OptionParameter = $CreateTraceCommand.Parameters.Add("@options", [System.Data.SqlDbType] "Int");
$OptionParameter.Value = [int] 2;
$TraceFileParameter = $CreateTraceCommand.Parameters.Add("@tracefile", [System.Data.SqlDbType] "NVarChar");
$TraceFileParameter.Value = [string] "c:\temp\test";
[void] $CreateTraceCommand.ExecuteNonQuery();
$TraceId = $TraceIdParameter.Value;
Write-Host "You created a trace with id: $TraceId";
I have been programming in c-based languages for more than 10 years now. Lately i have spent quite a bit of time in SQLtopia and learned a couple of techniques that are quite different from what i was used to.
Let me explain with an example: The boss comes in and asks for a report that contains all the days on which i have worked this year. As a typical c# programmer i come up with the following solution: Iterate over all the days in the year and skip the days i was out on holidays:
IEnumerable<DateTime> FindWorkingDays()
{
var begin = new DateTime(2010, 01, 01);
var end = new DateTime(2010, 12, 31);
var holidays = new[]
{
new Holiday { Begin = new DateTime(2010, 07, 01), End = new DateTime(2010, 07, 31) },
new Holiday { Begin = new DateTime(2010, 09, 01), End = new DateTime(2010, 09, 15) }
};
for (var date = begin; date <= end; date = date.AddDays(1))
{
if(holidays.Any(holiday => holiday.Begin <= date && date <= holiday.End)) continue;
yield return date;
}
}
In SQLtopia it is recommended to use set-based solutions instead of loops so we need a radically different solution.
Here is how we generate rows:
WITH
[Nums1] AS ( SELECT 1 AS [Value] UNION SELECT 2 AS [Value] )
, [Nums2] AS ( SELECT A.* FROM [Nums1] AS A, [Nums1] AS B, [Nums1] AS C)
, [Nums3] AS ( SELECT A.* FROM [Nums2] AS A, [Nums2] AS B, [Nums2] AS C)
, [Nums4] AS ( SELECT A.* FROM [Nums3] AS A, [Nums3] AS B )
SELECT * FROM [Nums4];
With rows we can generate numbers:
DECLARE @count INT = 1000;
WITH
[Nums1] AS ( SELECT 1 AS [Value] UNION SELECT 2 AS [Value] )
, [Nums2] AS ( SELECT A.* FROM [Nums1] AS A, [Nums1] AS B, [Nums1] AS C)
, [Nums3] AS ( SELECT A.* FROM [Nums2] AS A, [Nums2] AS B, [Nums2] AS C)
, [Nums4] AS ( SELECT A.* FROM [Nums3] AS A, [Nums3] AS B )
, [Numbers] AS ( SELECT TOP(@count) -1 + ROW_NUMBER() OVER(ORDER BY [Value]) AS [Value] FROM[Nums4] )
SELECT * FROM [Numbers];
And with numbers we can generate dates:
DECLARE @begin DATETIME = '2010-01-01';
DECLARE @end DATETIME = '2010-12-31';
DECLARE @count INT = DATEDIFF(DAY, @begin, @end) + 1;
WITH
[Nums1] AS ( SELECT 1 AS [Value] UNION SELECT 2 AS [Value] )
, [Nums2] AS ( SELECT A.* FROM [Nums1] AS A, [Nums1] AS B, [Nums1] AS C)
, [Nums3] AS ( SELECT A.* FROM [Nums2] AS A, [Nums2] AS B, [Nums2] AS C)
, [Nums4] AS ( SELECT A.* FROM [Nums3] AS A, [Nums3] AS B )
, [Numbers] AS ( SELECT TOP(@count) -1 + ROW_NUMBER() OVER(ORDER BY [Value]) AS [Value] FROM[Nums4] )
, [Dates] AS ( SELECT DATEADD(DAY, [Value], @begin) AS [Date] FROM [Numbers] )
SELECT * FROM [Dates];
With dates we can build the dates on which we were out on holidays:
DECLARE @begin DATETIME = '2010-01-01';
DECLARE @end DATETIME = '2010-12-31';
DECLARE @count INT = DATEDIFF(DAY, @begin, @end) + 1;
WITH
[Nums1] AS ( SELECT 1 AS [Value] UNION SELECT 2 AS [Value] )
, [Nums2] AS ( SELECT A.* FROM [Nums1] AS A, [Nums1] AS B, [Nums1] AS C)
, [Nums3] AS ( SELECT A.* FROM [Nums2] AS A, [Nums2] AS B, [Nums2] AS C)
, [Nums4] AS ( SELECT A.* FROM [Nums3] AS A, [Nums3] AS B )
, [Numbers] AS ( SELECT TOP(@count) -1 + ROW_NUMBER() OVER(ORDER BY [Value]) AS [Value] FROM[Nums4] )
, [Dates] AS ( SELECT DATEADD(DAY, [Value], @begin) AS [Date] FROM [Numbers] )
, [Holidays] AS ( SELECT '2010-07-01' AS [Begin], '2010-07-31' AS [End]
UNION
SELECT '2010-09-01' AS [Begin], '2010-09-15' AS [End]
)
SELECT [Date] FROM [Holidays],[Dates] WHERE [Date] BETWEEN [Begin] AND [End] ORDER BY [Date];
And now we can easily select the dates that are not holiday dates:
DECLARE @begin DATETIME = '2010-01-01';
DECLARE @end DATETIME = '2010-12-31';
DECLARE @count INT = DATEDIFF(DAY, @begin, @end) + 1;
WITH
[Nums1] AS ( SELECT 1 AS [Value] UNION SELECT 2 AS [Value] )
, [Nums2] AS ( SELECT A.* FROM [Nums1] AS A, [Nums1] AS B, [Nums1] AS C)
, [Nums3] AS ( SELECT A.* FROM [Nums2] AS A, [Nums2] AS B, [Nums2] AS C)
, [Nums4] AS ( SELECT A.* FROM [Nums3] AS A, [Nums3] AS B )
, [Numbers] AS ( SELECT TOP(@count) -1 + ROW_NUMBER() OVER(ORDER BY [Value]) AS [Value] FROM[Nums4] )
, [Dates] AS ( SELECT DATEADD(DAY, [Value], @begin) AS [Date] FROM [Numbers] WHERE [Value] <= DATEDIFF(DAY, @begin, @end))
, [Holidays] AS ( SELECT '2010-07-01' AS [Begin], '2010-07-31' AS [End]
UNION
SELECT '2010-09-01' AS [Begin], '2010-09-15' AS [End]
)
, [HolidayDates] AS (SELECT [Date] FROM [Holidays],[Dates] WHERE [Date] BETWEEN [Begin] AND [End] )
SELECT [Dates].[Date] FROM [Dates]
LEFT OUTER JOIN [HolidayDates] ON [Dates].[Date] = [HolidayDates].[Date]
WHERE [HolidayDates].[Date] IS NULL;
Look Ma, no loops!
Here is a sample query that allows you to explore the behavior of various aggregate window functions:
WITH
[Nums1] AS ( SELECT 1 AS [Value] UNION SELECT 2 AS [Value] )
, [Nums2] AS ( SELECT A.* FROM [Nums1] AS A, [Nums1] AS B, [Nums1] AS C)
, [Nums3] AS ( SELECT A.* FROM [Nums2] AS A, [Nums2] AS B, [Nums2] AS C)
, [Nums4] AS ( SELECT A.* FROM [Nums3] AS A, [Nums3] AS B )
-- Build numbers from 1 to 1000
, [Numbers] AS ( SELECT TOP(1000) ROW_NUMBER() OVER(ORDER BY [Value]) AS [Value] FROM[Nums4] )
SELECT
[Value]
, ROW_NUMBER() OVER(PARTITION BY [Value] / 100 ORDER BY [Value] % 10) AS [RowNumber]
, RANK() OVER(PARTITION BY [Value] / 100 ORDER BY [Value] % 10) AS [Rank]
, DENSE_RANK() OVER (PARTITION BY [Value] / 100 ORDER BY [Value] % 10) AS [DenseRank]
, NTILE(4) OVER (PARTITION BY [Value] / 100 ORDER BY [Value] % 10) AS [Tile100]
FROM
[Numbers]
ORDER BY
[Value];
Last week we noticed that the artefacts for a custom schema in our Database Project were not created (or updated). It took us a while to figure out what was wrong. When you add a script via the ‘Add Existing item’ menu in VS2010 the file’s Build Action is set to Not in Build for some unknown reason. As soon as we changed the value to Build and Deployed again our artefacts were created.
Sometimes i want to quickly clean up a database and start from fresh. Here is a small script that does exactly that.. (I just run the script a couple of times, untill no affected rows remain… Far more efficient than figuring out which constraints exist, building up a dependency tree, and cleanly deleting all rows):
-- Remove all rows from all tables
DECLARE @tableName VARCHAR(255)
DECLARE tableNames CURSOR FOR SELECT name FROM sys.Tables;
OPEN tableNames
FETCH NEXT FROM tableNames INTO @tableName
WHILE @@FETCH_STATUS = 0 BEGIN
EXEC('DELETE FROM [' + @tableName + ']')
FETCH NEXT FROM tableNames INTO @tableName
END
CLOSE tableNames
DEALLOCATE tableNames
Imagine that you have a table with a column of the type DATETIME. You’ve been storing data as localtime and after a while you need to convert these datetimes to UTC. Here’s a possible approach:
UPDATE events SET start = SYS_EXTRACT_UTC(FROM_TZ(start, 'Europe/Brussels'));
You get a more generic variant using the AT TIME ZONE clause:
UPDATE events SET start = FROM_TZ(start, 'Europe/Brussels') AT TIME ZONE 'America/Denver';
Earlier today someone asked the following:
I’m trying to move selected data from one table to another. The following works apart from the destination table is not incrementing the ID (I’m not using auto increment for that field).
How can I increase the value of field_1_id for each select > insert? I’m guessing SQL doesn’t loop through each SELECT match an insert correspondingly?
Here is a possible answer: (Don’t forget to wrap these queries in a transaction if your MySQL Engine supports it)
-- Some demo tables (and data) -- CREATE TABLE table1 (id INT(10), name VARCHAR(20), PRIMARY KEY(id)); -- CREATE TABLE table2 (id INT(10), name VARCHAR(20), PRIMARY KEY(id)); -- INSERT INTO table1 VALUES (1, "tim"), (2, "mike"); SET @id := (SELECT MAX(id) + 1 FROM table2); INSERT INTO table2 SELECT @id := @id + 1, table1.name FROM (SELECT @id) AS id, table1 AS table1;
Sometimes you want to select rows where a value is in a specific collection. Here’s an example that show how you can select all the rows in the TEST table with an id of 1, 2 or 3. First we create an SQL type to contain a list of numbers:
CREATE TYPE LIST_NUMBER AS TABLE OF NUMBER(10); /
Next thing to do is add a custom type and function header to the package specification:
PACKAGE TIMVW.TESTPACKAGE AS TYPE CRSR_REF IS REF CURSOR; TYPE ARR_IDS IS TABLE OF TEST.TEST_ID%Type INDEX BY BINARY_INTEGER; PROCEDURE GET_TESTS ( P_IDS IN ARR_IDS, P_CURSOR OUT CRSR_REF ); END;
And offcourse we have to implement the function in the body:
PROCEDURE GET_TESTS
(
P_IDS IN ARR_IDS,
P_CURSOR OUT CRSR_REF
)
AS
V_IDS LIST_NUMBER := LIST_NUMBER();
BEGIN
V_IDS.EXTEND(P_IDS.COUNT);
FOR i IN P_IDS.FIRST .. P_IDS.LAST LOOP
V_IDS(i) := P_IDS(i);
END LOOP;
OPEN P_CURSOR FOR
SELECT
TEST.TEST_ID,
TEST.NAME,
TEST.TYPE_CODE
FROM
TEST
WHERE
TEST_ID IN (SELECT * FROM TABLE(V_IDS))
ORDER BY
TEST.TEST_ID ASC;
END;
Now that we have done all this we can consume the function from our client code:
using (OracleConnection conn = new OracleConnection("User Id=u;password=p;Data Source=ORCL"))
{
conn.Open();
OracleCommand command = conn.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "TIMVW.TESTPACKAGE.GET_TESTS";
command.Parameters.Add("P_IDS", OracleDbType.Int32, new int[] { 1, 2, 3 }, ParameterDirection.Input);
command.Parameters["P_IDS"].CollectionType = OracleCollectionType.PLSQLAssociativeArray;
command.Parameters.Add("P_CURSOR", OracleDbType.RefCursor, ParameterDirection.Output);
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("test id: " + reader.GetDecimal(0));
}
Console.Write("{0}Press any key to continue...", Environment.NewLine);
Console.ReadKey();
}