Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

SQL Server 2008

How to calculate the covariance in T-SQL

DECLARE @Sample TABLE         (             x INT NOT NULL,             y INT NOT NULL         ) INSERT  @Sample VALUES  (3, 9),         (2, 7),         (4, 12),         (5, 15),         (6, 17) ;WITH cteSource(x, xAvg, y, yAvg, n) AS (         SELECT  1E * x,                 AVG(1E * x) OVER (PARTITION BY (SELECT NULL)),                 1E * y,                 AVG(1E * y) OVER (PARTITION BY (SELECT NULL)),                 COUNT(*) OVER (PARTITION BY (SELECT NULL))         FROM    @Sample ) SELECT  SUM((x - xAvg) *(y - yAvg)) / MAX(n) AS [COVAR(x,y)] FROM    cteSource

posted @ Wednesday, January 18, 2012 1:01 PM | Feedback (1) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 Denali ]

Avoid stupid mistakes

Today I had the opportunity to debug a system with a client. I have to confess it took a while to figure out the bug, but here it is SELECT COUNT(*) OfflineData Do you see the bug? Yes, there should be a FROM clause before the table name. Without the from clause, SQL Server treats the name as an alias for the count column. And what do the COUNT always return in this case? It returns 1. So the bug had a severe implication. Now I now it's easy to forget to write a FROM in your query. How can we avoid these stupid mistakes? An...

posted @ Thursday, September 22, 2011 8:38 AM | Feedback (3) | Filed Under [ SQL Server 2008 SQL Server 2005 SQL Server 2000 Miscellaneous Denali ]

Convert UTF-8 string to ANSI

CREATE FUNCTION dbo.fnConvertUtf8Ansi (     @Source VARCHAR(MAX) ) RETURNS VARCHAR(MAX) AS BEGIN     DECLARE @Value SMALLINT = 160,             @Utf8 CHAR(2),             @Ansi CHAR(1)     IF @Source NOT LIKE '%[ÂÃ]%'         RETURN  @Source     WHILE @Value <= 255         BEGIN             SELECT  @Utf8 = CASE                                 WHEN @Value BETWEEN 160 AND 191 THEN CHAR(194) + CHAR(@Value)                                 WHEN @Value BETWEEN 192 AND 255 THEN CHAR(195) + CHAR(@Value - 64)                                 ELSE NULL                             END,                     @Ansi = CHAR(@Value)             WHILE CHARINDEX(@Source, @Utf8) > 0                 SET    @Source = REPLACE(@Source, @Utf8, @Ansi)             SET    @Value += 1         END     RETURN  @Source END

posted @ Wednesday, September 14, 2011 6:30 AM | Feedback (2) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 Denali ]

Code Audit - The Beginning

For the next few months, I will be involved in an interesting project for a mission critical application that our company have outsourced to a well-known and respected consulting company here Sweden. My part will be the technical details of the forecasting application now when our former DBA has left our company. Today I took a brief look at the smallest building blocks; the Functions. No function is inline so I can assume some of the performance issues are derived from these. One function I stumled across is very simple. All it does is to add a timepart from current execution time to...

posted @ Thursday, July 21, 2011 8:44 AM | Feedback (2) | Filed Under [ Optimization SQL Server 2008 Algorithms Administration SQL Server 2005 ]

Simple function to get beginning or end of month

CREATE FUNCTION dbo.fnIsOnMonthEdge (     @theDate DATETIME ) RETURNS SMALLINT AS BEGIN     RETURN CASE @theDate                 WHEN '99991231' THEN 1                 ELSE DATEDIFF(MONTH, @theDate, DATEADD(DAY, 1, @theDate))             END +             CASE @theDate                 WHEN '17530101' THEN -1                 ELSE DATEDIFF(MONTH, @theDate, DATEADD(DAY, -1, @theDate))             END END  

posted @ Thursday, April 14, 2011 1:40 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Unified Relational Division algorithm

Finally! Today I finished my presentation about finding a unified algorithm for Relational Division, which should work for all types of division; singlerecord and multirecord, singlecolumn and multicolumn and both exact division and with remainder. Optionally it should work with single and multiple divisor sets. That's 16 permutations of relational division kinds. A bonus point is that the algorithm I've found work across multiple platforms with standard SQL language elements. Also, I have performance tested the algorithm with the sample data from Mr Celko here. For such small sample set, my algorithm is in the top queries, but the real performance kicks in when you...

posted @ Sunday, February 06, 2011 12:10 PM | Feedback (5) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Some datatypes doesn't honor localization

This bug has haunted me for a while, until today when I decided to not accept it anymore. So I filed a bug over at connect.microsoft.com, https://connect.microsoft.com/SQLServer/feedback/details/636074/some-datatypes-doesnt-honor-localization, and if you feel the way I do, please vote for this bug to be fixed. Here is a very simple repro of the problem DECLARE  @Sample TABLE          (              a DECIMAL(38, 19),              b FLOAT          ) INSERT   @Sample          (              a,              b          ) VALUES   (1E / 7E, 1E / 7E) SELECT   * FROM     @Sample Here is the actual output.                                       a                      b --------------------------------------- ----------------------                   0.1428571428571428400      0,142857142857143   I think that both columns should have the same decimal separator, don't you? //Peter

posted @ Friday, January 14, 2011 9:09 AM | Feedback (12) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 ]

The internal storage of a DATETIMEOFFSET value

Today I went for investigating the internal storage of DATETIME2 datatype. What I found out was that for a datetime2 value with precision 0 (seconds only), SQL Server need 6 bytes to represent the value, but stores 7 bytes. This is because SQL Server add one byte that holds the precision for the datetime2 value. Start with this very simple repro declare    @now datetimeoffset(7) = '2010-12-15 21:04:03.6934231 +03:30'   select     cast(cast(@now as datetimeoffset(0)) as binary(9)),            cast(cast(@now as datetimeoffset(1)) as binary(9)),            cast(cast(@now as datetimeoffset(2)) as binary(9)),            cast(cast(@now as datetimeoffset(3)) as binary(10)),            cast(cast(@now as datetimeoffset(4)) as binary(10)),            cast(cast(@now as datetimeoffset(5)) as binary(11)),            cast(cast(@now as datetimeoffset(6)) as binary(11)),           ...

posted @ Wednesday, December 15, 2010 10:44 PM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms ]

The internal storage of a DATETIME2 value

Today I went for investigating the internal storage of DATETIME2 datatype. What I found out was that for a datetime2 value with precision 0 (seconds only), SQL Server need 6 bytes to represent the value, but stores 7 bytes. This is because SQL Server add one byte that holds the precision for the datetime2 value. Start with this very simple repro declare @now datetime2(7) = '2010-12-15 21:04:03.6934231'   select  cast(cast(@now as datetime2(0)) as binary(7)),         cast(cast(@now as datetime2(1)) as binary(7)),         cast(cast(@now as datetime2(2)) as binary(7)),         cast(cast(@now as datetime2(3)) as binary(8)),         cast(cast(@now as datetime2(4)) as binary(8)),         cast(cast(@now as datetime2(5)) as binary(9)),         cast(cast(@now as datetime2(6)) as binary(9)),         cast(cast(@now...

posted @ Wednesday, December 15, 2010 10:05 PM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms ]

The internal storage of a SMALLDATETIME value

SELECT  [Now],         BinaryFormat,         SUBSTRING(BinaryFormat, 1, 2) AS DayPart,         SUBSTRING(BinaryFormat, 3, 2) AS TimePart,         CAST(SUBSTRING(BinaryFormat, 1, 2) AS INT) AS [Days],         DATEADD(DAY, CAST(SUBSTRING(BinaryFormat, 1, 2) AS INT), 0) AS [Today],         SUBSTRING(BinaryFormat, 3, 2) AS [Ticks],         DATEADD(MINUTE, CAST(SUBSTRING(BinaryFormat, 3, 2) AS SMALLINT), 0) AS Peso FROM    (             SELECT  CAST(GETDATE() AS SMALLDATETIME) AS [Now],                     CAST(CAST(GETDATE() AS SMALLDATETIME) AS BINARY(4)) AS BinaryFormat         ) AS d    

posted @ Friday, November 05, 2010 3:14 PM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

The internal storage of a DATETIME value

SELECT  [Now],         BinaryFormat,         SUBSTRING(BinaryFormat, 1, 4) AS DayPart,         SUBSTRING(BinaryFormat, 5, 4) AS TimePart,         CAST(SUBSTRING(BinaryFormat, 1, 4) AS INT) AS [Days],         DATEADD(DAY, CAST(SUBSTRING(BinaryFormat, 1, 4) AS INT), 0) AS [Today],         CAST(SUBSTRING(BinaryFormat, 5, 4) AS INT) AS [Ticks],         DATEADD(MILLISECOND, 1000.E / 300.E * CAST(SUBSTRING(BinaryFormat, 5, 4) AS INT), 0) AS Peso FROM    (             SELECT  GETDATE() AS [Now],                     CAST(GETDATE() AS BINARY(8)) AS BinaryFormat         ) AS d

posted @ Friday, November 05, 2010 3:10 PM | Feedback (3) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Sweet and simple "first and last" weekday calculation

DECLARE @Year SMALLINT = 2011,         @NumberOfYears TINYINT = 3   ;WITH cteCalendar(FirstOfMonth, LastOfMonth) AS (         SELECT  DATEADD(MONTH, 12 * @Year + Number - 22801, 6) AS FirstOfMonth,                 DATEADD(MONTH, 12 * @Year + Number - 22800, -1) AS LastOfMonth         FROM    master..spt_values         WHERE   TYPE = 'P'                 AND number BETWEEN 1 AND 12 * @NumberOfYears ) SELECT  DATEADD(DAY, DATEDIFF(DAY, 6, FirstOfMonth) / 7 * 7, 6) AS FirstSunday,         DATEADD(DAY, DATEDIFF(DAY, 5, LastOfMonth) / 7 * 7, 5) AS LastSaturday FROM    cteCalendar

posted @ Thursday, October 28, 2010 9:25 AM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

How to determine if you should use full or differential backup?

Or ask yourself, "How much of the database has changed since last backup?". Here is a simple script that will tell you how much (in percent) have changed in the database since last backup. -- Prepare staging table for all DBCC outputs DECLARE @Sample TABLE         (             Col1 VARCHAR(MAX) NOT NULL,             Col2 VARCHAR(MAX) NOT NULL,             Col3 VARCHAR(MAX) NOT NULL,             Col4 VARCHAR(MAX) NOT NULL,             Col5 VARCHAR(MAX)         )   -- Some intermediate variables for controlling loop DECLARE @FileNum BIGINT = 1,         @PageNum BIGINT = 6,         @SQL VARCHAR(100),         @Error INT,         @DatabaseName SYSNAME = 'Yoda'   -- Loop all files to the very end WHILE 1 = 1     BEGIN         BEGIN TRY             -- Build the...

posted @ Thursday, October 21, 2010 4:34 PM | Feedback (5) | Filed Under [ SQL Server 2008 Algorithms Administration ]

Fast easter day function

CREATE FUNCTION dbo.fnGetEasterDate (     @Year SMALLINT ) RETURNS DATE AS BEGIN     RETURN (                 SELECT DATEADD(DAY, DATEDIFF(DAY, 0, CONVERT(DATETIME, CAST(@Year AS CHAR(4)) + BaseDate, 112)) / 7 * 7, 6)                 FROM    (                             SELECT CASE @Year % 19                                         WHEN  0 THEN '0415'                                         WHEN  1 THEN '0404'                                         WHEN  2 THEN '0324'                                         WHEN  3 THEN '0412'                                         WHEN  4 THEN '0401'                                         WHEN  5 THEN '0419'                                         WHEN  6 THEN '0409'                                         WHEN  7 THEN '0329'                                         WHEN  8 THEN '0417'                                         WHEN  9 THEN '0406'                                         WHEN 10 THEN '0326'                                         WHEN 11 THEN '0414'                                         WHEN 12 THEN '0403'                                         WHEN 13 THEN '0323'                                         WHEN 14 THEN '0411'                                         WHEN 15 THEN '0331'                                         WHEN 16 THEN '0418'                                         WHEN...

posted @ Wednesday, September 08, 2010 3:30 PM | Feedback (2) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Excel XIRR function

First create a table type like this CREATE TYPE dbo.MyXirrTable AS TABLE ( theValue DECIMAL(19, 9) NOT NULL, theDate DATETIME NOT NULL ) GO And then you create a function like this CREATE FUNCTION dbo.XIRR ( @Sample MyXirrTable READONLY, @Rate DECIMAL(19, 9) = 0.1 ) RETURNS DECIMAL(38, 9) AS BEGIN DECLARE @LastRate DECIMAL(19, 9), @RateStep DECIMAL(19, 9) = 0.1, @Residual DECIMAL(19, 9) = 10, @LastResidual DECIMAL(19, 9) = 1, @i TINYINT = 0 IF @Rate IS NULL SET @Rate = 0.1 SET @LastRate = @Rate WHILE @i < 100 AND ABS((@LastResidual - @Residual) / @LastResidual) > 0.00000001 BEGIN SELECT @LastResidual = @Residual, @Residual = 0 SELECT @Residual = @Residual + theValue / POWER(1 + @Rate, theDelta / 365.0E) FROM ( SELECT theValue, DATEDIFF(DAY, MIN(theDate) OVER (), theDate) AS theDelta FROM @Sample ) AS d SET @LastRate = @Rate If @Residual >= 0 SET...

posted @ Thursday, August 19, 2010 9:46 PM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms ]

Joe Celko's Puzzles and Answers - The Restaurant seat assignment Problem

This problem is designed to come up with a solution that uses the smallest amount of storage possible for a 1,000 seat restaurant. I've come up with a solution that need only 125 bytes of storage. All other solutions covered in Mr Celko's book has at least 1,000 bytes of storage. Here is my solution, complete with all procedures to assign and release seats, together with views to display current status of each and one seat. -- Setup sample data CREATE TABLE    dbo.Restaurant                 (                     Seats BINARY(125) NOT NULL                 ) -- Initialize an empty restaurant INSERT  dbo.Restaurant          (             Seats         ) SELECT  0x GO -- Create procedure for handling seat assignment CREATE...

posted @ Saturday, July 31, 2010 1:20 AM | Feedback (5) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 Celko Puzzle ]

More on XML and encoding

Today, let's examine encoding with SQL Server and XML datatype. DECLARE @Inf XML SET     @Inf =  '<?xml version="1.0" encoding="utf-16"?>                  <root>                     <names>                          <name>test</name>                      </names>                      <names>                          <name>test1</name>                      </names>                  </root>                 ' SELECT  x.value('name[1]', 'VARCHAR(10)') AS Name FROM    @Inf.nodes('/root/names') AS t(x) If you try to run the code above, you will get an error message like this Msg 9402, Level 16, State 1, Line 3 XML parsing: line 1, character 39, unable to switch the encoding Why is that? If you change the encoding to UTF-8, the code works. The solution is to know that UTF-16 works like UNICODE, and how do we denote UNICODE strings in SQL Server? Yes, by prefixing the string with N. So this code works with UTF-16 encoding...

posted @ Tuesday, July 06, 2010 9:40 AM | Feedback (2) | Filed Under [ SQL Server 2008 SQL Server 2005 ]

String concatenation and entitization

This question has been asked over and over again, and instead of having to redirect to any of my previous answers, I will cover the solution here in my blog. Consider this sample data DECLARE @Sample TABLE         (             ID INT,             Data VARCHAR(100)         )   INSERT  @Sample VALUES  (1, 'Peso & Performance SQL'),         (1, 'MVP'),         (2, 'Need help <? /> -- '),         (2, 'With XML string concatenation ?') The "trick" is to use the TYPE directive (to deal with entitization), and then use ".value" function to get the correct value out. So here is the final query. SELECT      i.ID,             STUFF(f.Content.value('.', 'VARCHAR(MAX)'), 1, 1, '') FROM        (                 SELECT      ID                 FROM        @Sample                 GROUP...

posted @ Sunday, July 04, 2010 11:59 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

Proper Relational Division With Sets

I got an email from Mr Celko and he correctly stated that my previous solution was not truly working with sets, so he posted me some solutions and proper sample data. With this information at my hand, I started to investigate what really is needed to get this Relational Division to work properly with two sets; Dividend and Divisor. Some of you know me well, and know I am not satisfied with just solving the problem. There have to be some tweaks, and I did that too with this solution. Not only is it only touching the Dividend table once and...

posted @ Friday, July 02, 2010 12:48 AM | Feedback (36) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Relational division

I came across an interesting post on Microsoft SQL Server forum this afternoon. It was a question about Relational algebra and the poster wanted to have an efficient query to solve his problem. The problem could be solved with relational division, but there is no such operator in SQL Server. Maybe there will be some day. For a fully working solution, see http://weblogs.sqlteam.com/peterl/archive/2010/07/02/Proper-Relational-Division-With-Sets.aspx But for now there is no such operator, so we as developers have to find our own ways. First prepare and populate some sample data -- Prepare sample data DECLARE @Sample TABLE         (             ParentID INT NOT NULL,             Keyword VARCHAR(25) NOT NULL,             UNIQUE (ParentID, Keyword)         ) -- Populate sample data INSERT ...

posted @ Wednesday, June 30, 2010 9:41 PM | Feedback (1) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Another bin-packaging algorithm using recursion and XML

This time I will show you an algorithm to do the dreaded bin-packaging using recursion and XML. First, create some sample data like this -- Prepare sample data DECLARE @Sample TABLE         (             RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,             Expense SMALLMONEY NOT NULL         )   -- Populate sample data INSERT  @Sample         (             Expense         ) VALUES  (12.51),         (45.63),         (66.35),         (92.66),         (65.46),         (54.01),         (32.23),         (27.16),         (78.92),         (14.58)   Next, we need to create a variable to hold the user's wanted total sum. -- Prepare user supplied parameter DECLARE @WantedSUM SMALLMONEY = 111.09 And we also need to create a temporary staging table to hold the valid combinations   -- Prepare temporary staging table DECLARE @Temp TABLE         (             CombID INT...

posted @ Wednesday, June 30, 2010 1:44 PM | Feedback (1) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

Bug in SQL Server Management Studio

When I try to rename a node name such as a table or column in Management Studio, I cannot use DELETE key to remove previous characters. However, I can use BACKSPACE key. Please vote here https://connect.microsoft.com/SQLServer/feedback/details/570758/cannot-use-delete-key-in-ssms-and-object-explorer to fix this little, but annoying, issue. //Peso

posted @ Friday, June 25, 2010 10:13 AM | Feedback (0) | Filed Under [ SQL Server 2008 Administration ]

SQL Server 2008 Compression

Hi! Today I am going to talk about compression in SQL Server 2008. The data warehouse I currently design and develop holds historical data back to 1973. The data warehouse will have an other blog post laster due to it's complexity. However, the server has 60GB of memory (of which 48 is dedicated to SQL Server service), so all data didn't fit in memory and the SAN is not the fastest one around. So I decided to give compression a go, since we use Enterprise Edition anyway. This is the code I use to compress all tables with PAGE compression. DECLARE @SQL VARCHAR(MAX)   DECLARE curTables CURSOR FOR        ...

posted @ Thursday, June 17, 2010 2:16 PM | Feedback (4) | Filed Under [ Optimization SQL Server 2008 Administration ]

Simple script to get logins and default database names

SELECT     u.name,            l.dbname FROM       sys.sysusers AS u INNER JOIN sys.syslogins AS l ON l.sid = u.sid

posted @ Monday, June 14, 2010 1:27 PM | Feedback (0) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 ]

Simple script to get referenced table and their column names

-- Setup user supplied parameters DECLARE @WantedTable SYSNAME   SET     @WantedTable = 'Sales.factSalesDetail'   -- Wanted table is "parent table" SELECT      PARSENAME(@WantedTable, 2) AS ParentSchemaName,             PARSENAME(@WantedTable, 1) AS ParentTableName,             cp.Name AS ParentColumnName,             OBJECT_SCHEMA_NAME(parent_object_id) AS ChildSchemaName,             OBJECT_NAME(parent_object_id) AS ChildTableName,             cc.Name AS ChildColumnName FROM        sys.foreign_key_columns AS fkc INNER JOIN  sys.columns AS cc ON cc.column_id = fkc.parent_column_id                 AND cc.object_id = fkc.parent_object_id INNER JOIN  sys.columns AS cp ON cp.column_id = fkc.referenced_column_id                 AND cp.object_id = fkc.referenced_object_id WHERE       referenced_object_id = OBJECT_ID(@WantedTable)   -- Wanted table is "child table" SELECT      OBJECT_SCHEMA_NAME(referenced_object_id) AS ParentSchemaName,             OBJECT_NAME(referenced_object_id) AS ParentTableName,             cc.Name AS ParentColumnName,             PARSENAME(@WantedTable, 2) AS ChildSchemaName,             PARSENAME(@WantedTable, 1) AS ChildTableName,             cp.Name AS ChildColumnName FROM        sys.foreign_key_columns AS fkc INNER JOIN  sys.columns AS cp ON cp.column_id =...

posted @ Tuesday, June 08, 2010 4:02 PM | Feedback (0) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]

The Excel Column Name assigment problem

Here is a generic algorithm to get the Excel column name according to it's position. By changing the @Base parameter, you can do this for any sequence according to same style as Excel. DECLARE @Value BIGINT = 8839,         @Base TINYINT = 26   ;WITH cteSequence(Position, Value, Chr) AS (     SELECT  CAST(LOG(@Value - @Value / @Base) / LOG(@Base) AS INT),             CAST(@Value - 1 AS BIGINT),             CAST(CHAR(65 +(@Value - 1) % @Base) AS VARCHAR(MAX))       UNION ALL       SELECT  Position - 1,             Value / @Base - 1,             CHAR(65 +(Value / @Base - 1) % @Base) + Chr     FROM    cteSequence     WHERE   Position > 0 ) SELECT  Chr FROM    cteSequence WHERE   Position = 0

posted @ Wednesday, April 28, 2010 3:45 PM | Feedback (3) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

How to get current connection settings

SELECT  name AS Setting,         CASE             WHEN @@OPTIONS & number = number THEN 'ON'             ELSE 'OFF'         END AS Value FROM    master..spt_values WHERE   type = 'SOP'         AND number > 0 Or this SELECT  * FROM    sys.dm_exec_sessions WHERE   session_id = @@SPID Or this SELECT  * FROM    sys.dm_exec_request WHERE   session_id = @@SPID

posted @ Saturday, March 20, 2010 9:53 PM | Feedback (6) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]

Get client IP address

Up until now, I have used convuluted approaches to get the current user client IP-address. This weekend I browsed Books Online for SQL Server 2008 R2 (November CTP) and found this new cool function! SELECT  CONNECTIONPROPERTY('net_transport') AS net_transport,         CONNECTIONPROPERTY('protocol_type') AS protocol_type,         CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,         CONNECTIONPROPERTY('local_net_address') AS local_net_address,         CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,         CONNECTIONPROPERTY('client_net_address') AS client_net_address Or this   SELECT  * FROM    sys.dm_exec_connections WHERE   session_id = @@SPID

posted @ Saturday, March 20, 2010 9:48 PM | Feedback (6) | Filed Under [ SQL Server 2008 Administration ]

Is it possible to have multiple sets of key columns in a table?

Filtered indexes is one of my new favorite things with SQL Server 2008. I am currently working on designing a new datawarehouse. There are two restrictions doing this It has to be fed from the old legacy system with both historical data and new data It has to be fed from the new business system with new data When we incorporate the new business system, we are going to do that for one market only. It means the old legacy business system still will produce new data for other markets (together with historical data for all markets)...

posted @ Tuesday, February 16, 2010 2:26 PM | Feedback (0) | Filed Under [ SQL Server 2008 Administration ]

Unsupported and/or undocumented features

In my past competition for Phil Factor 'Subscription List' SQL Problem, I presented a technique of Ordered CTE Update which is explained more in detail by Mladen Prajdic. In the comments to the in-depth analysis follow-up article by MVP Kathi Kellenberger, there is now some sort of debate whether or not if it's responsible to present a technique like that. The nay-sayers argument are valid Ordered updates are not supported by Microsoft It is an undocumented and unsupported feature which may break in next service pack or version of SQL Server Most of the nay-sayers reference to the traditional "Quirky Update",...

posted @ Wednesday, February 10, 2010 3:55 PM | Feedback (11) | Filed Under [ SQL Server 2008 SQL Server 2005 Miscellaneous ]

Convert FILETIME to SYSTEM time using T-SQL

Today I answered a question where OP wanted to convert a 64-bit filetime value to a system time. After a little reading about FILETIME, I learnt that the number is a value for 100 ns passed since January 1st 1601. This gets little tricky since the normal date zero for SQL Server is January 1st 1900. But with a little arithmetic things worked out. The most cumbersome part was the INT limit for passing parameters to DATEADD function, but that was easy to overcome too. However, I am not convinced the actual number for the nanosecond value is returned "reversed" from...

posted @ Monday, February 08, 2010 5:55 PM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

Convert binary value to string value

With SQL Server 2008, we can easily use DECLARE @bin VARBINARY(MAX) SET     @bin = 0x5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8 SELECT  CONVERT(VARCHAR(MAX), @bin, 2) But how can we do this in SQL Server 2005? You can't use a simple CAST or CONVERT, because you get the ASCII representation of the binary values. So, here is how you can do the conversion with SQL Server 2005 by using XML. -- Prepare value DECLARE @bin VARBINARY(MAX) SET     @bin = 0x5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8 -- Display the results SELECT @bin AS OriginalValue,         CAST('' AS XML).value('xs:hexBinary(sql:variable("@bin"))', 'VARCHAR(MAX)') AS ConvertedString

posted @ Wednesday, January 27, 2010 9:38 AM | Feedback (8) | Filed Under [ SQL Server 2008 SQL Server 2005 ]

Get date from ISO week number

This function is just the opposite of this one, http://weblogs.sqlteam.com/peterl/archive/2009/05/27/Extended-ISO-week-function.aspx. The function in the link returns the ISO week number from a given date, and the function below returns the monday's date from an ISO week. Or you can use the function blogged here http://weblogs.sqlteam.com/peterl/archive/2009/12/01/How-to-get-a-date-from-Year-week-and-weekday.aspx. CREATE FUNCTION dbo.fnISOMonday (     @theYear SMALLINT,     @theWeek TINYINT ) RETURNS DATETIME AS BEGIN     RETURN  (                 SELECT  DATEADD(DAY, 7 * @theWeek - 7, CurrentYear)                 FROM    (                             SELECT  DATEADD(DAY,(DATEDIFF(DAY, '17530101', Jan4) / 7) * 7, '17530101') AS CurrentYear,                                     DATEADD(DAY,(DATEDIFF(DAY, '17530101', DATEADD(YEAR, 1, Jan4)) / 7) * 7, '17530101') AS NextYear                             FROM    (                                         SELECT  DATEADD(YEAR, @theYear - 1900, 3) AS Jan4                                         WHERE   @theYear BETWEEN 1900 AND 9999                                                ...

posted @ Tuesday, January 26, 2010 10:57 AM | Feedback (6) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Celko Stumper - The Class Scheduling Problem

Joe Celko has posted a new Stumper - The Class Scheduling Problem here http://www.simple-talk.com/sql/t-sql-programming/celkos-sql-stumper-the-class-scheduling-problem/ Here is one suggestion to solve the problem. It's linear in time so it should be very fast. It's based on the "Descending order" approach, and the "paths" columns are used to store valid room and classes. -- Initialize and find the valid combinations DECLARE  @HowManySeatsFree INT = 0 -- Set to zero for maximum seating, and to 1 for letting in late pupils. DECLARE  @Source TABLE         (             room_nbr CHAR(2),             class_nbr CHAR(2),             recID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED         ) INSERT      @Source             (                 room_nbr,                 class_nbr             ) SELECT      r.room_nbr,             c.class_nbr FROM        dbo.Rooms AS r INNER JOIN  dbo.Classes AS...

posted @ Saturday, January 23, 2010 1:31 PM | Feedback (6) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Collapse date range, with safety date range

This example is based on AdventureWorks database. More details about the task is found here http://sqlblog.com/blogs/adam_machanic/archive/2009/12/08/t-sql-tuesday-001-exploring-fuzzy-interval-islands-using-sqlclr.aspx. The task is to produce a set of dateranges where a product has been sold, with a "latency" of 7 days. I wanted do display that there are other way to write a query to make it more efficient. Adam's SQLCLR version runs in 0.5 seconds. DECLARE @Interval INT = 7 ;WITH cteSingle(ProductID, TransactionDate, recID) AS (     SELECT  ProductID,             TransactionDate,             ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY TransactionDate) AS recID     FROM    Production.TransactionHistory ), cteLower(ProductID, StartDate, recID) AS (     SELECT      s.ProductID,                 s.TransactionDate AS EndDate,                 ROW_NUMBER() OVER (PARTITION BY s.ProductID ORDER BY s.TransactionDate) AS recID     FROM       ...

posted @ Friday, January 08, 2010 1:07 AM | Feedback (4) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

Create permutations, a simple way

This is one way to create permutations of a word. It is certainly not the fastest way (there are hardwired and specialized approaches, and there are other algorithms such as Fike) but this is very simple. DECLARE @Word VARCHAR(10) = 'Peter' ;WITH cteYak(Word, Letters) AS (     SELECT  CAST(SUBSTRING(@Word, Number, 1) AS VARCHAR(10)) AS Word,             STUFF(@Word, Number, 1, '') AS Letters     FROM    dbo.TallyNumbers     WHERE   Number BETWEEN 1 AND LEN(@Word)     UNION ALL     SELECT      CAST(Word + SUBSTRING(y.Letters, n.Number, 1) AS VARCHAR(10)) AS Word,                 STUFF(y.Letters, n.Number, 1, '') AS Letters     FROM        cteYak AS y     INNER JOIN  dbo.TallyNumbers AS n ON n.Number BETWEEN 1 AND LEN(y.Letters) ) SELECT  DISTINCT         Word FROM    cteYak WHERE   LEN(Word) = LEN(@Word)  

posted @ Saturday, January 02, 2010 8:15 AM | Feedback (11) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

First take on Azure

I created a small table with about 150,000 records in order to do some aggregations. There are some prerequisites to comply for, but that is easily done with replace function. With Azure, it took almost 4 times as long time as SQL Server Express on my old development machine to complete the query. With this simple test, I find the use for Azure very limited, as of today. It is excellent for small companies who is not hosting their own environment themself. For companies on a web hosting company which doesn't support Microsoft SQL Server it will do just fine. But today, almost...

posted @ Saturday, December 12, 2009 12:26 AM | Feedback (3) | Filed Under [ SQL Server 2008 Administration Miscellaneous ]

Problem with SQL Server service restart

The few last days, our hosting company have  updated their VMware environment a number of times and thus have forced equal number of restarts for our database servers. The problem with this is that one of the databases, Yoda, needed 1.5-2.0 hours to start up due to "In Recovery" status. I asked around what could be the cause of this and also read some excellent posts by Kimberley Tripp http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx I saw the undocumented DBCC LogInfo command and decided to give it a go. To my surprise, there were 33,636 records returned. As a general rule of thumb, you should have about 8-16 VLF...

posted @ Wednesday, December 09, 2009 12:05 PM | Feedback (4) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]

How to get a date from Year, week and weekday

This is currently for ISO weeks only, but if you have the week number, the weekday and year and you want that date in return, you can use this function below. And according to ISO, Monday is 1 and Sunday is 7. CREATE FUNCTION dbo.fnGetDateFromYearWeekWeekday (     @Year INT,     @Week INT,     @Weekday INT ) RETURNS DATETIME AS BEGIN     RETURN CASE                 WHEN @Year < 1900 OR @Year > 9999 THEN NULL                 WHEN @Week < 1 OR @Week > 53 THEN NULL                 WHEN @Weekday < 1 OR @Weekday > 7 THEN NULL                 WHEN @Year = 9999 AND @Week = 52 And @Weekday > 5 THEN NULL                 WHEN DATEPART(YEAR, DATEADD(DAY, 7 * @Week + DATEDIFF(DAY, 4, DATEADD(YEAR, @Year - 1900, 7)) / 7...

posted @ Tuesday, December 01, 2009 12:21 PM | Feedback (6) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Lesson learned by Trial and Error

Yesterday, I decided to install Microsoft SQL Server 2008 R2 (November CTP) on my local machine. I already had SQL Server Express 2008 installed so the first step was to uninstall Express, since there seemed to be no upgrade path. Uninstalling went great. I then installed 2008 R2 and rebooted my machine. I attached my sample databases, and my test database. To my surprise, the Test database was now corrupted! File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Test.ldf" may be incorrect. The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred...

posted @ Wednesday, November 18, 2009 11:46 AM | Feedback (4) | Filed Under [ SQL Server 2008 Administration ]

How to tell if you are running on a virtual environment

This is a piece of code I often use to see if the database server is running in a virtual environment. That is not always obvious or known by the developers. DECLARE @Result TABLE         (             LogDate DATETIME,             ProcessInfo NVARCHAR(MAX),             Text NVARCHAR(MAX)         )   INSERT  @Result EXEC    sys.xp_readerrorlog 0, 1, 'System Manufacturer', 'VMware'   IF EXISTS (SELECT * FROM @Result)     SELECT 'It seems you are running on VMware.' AS Msg ELSE     SELECT 'It seems you are not running on VMware.' AS Msg If you test this in your environment and found other virtual manufacturers, please let me know so I can add them in the code above.

posted @ Thursday, November 12, 2009 3:27 PM | Feedback (10) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 ]

Get the Nth weekday of any arbitrary period

I have previously posted some algorithm how to find the Nth weekday for a fixed period such as month, quarter or year. See http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-in-a-month.aspx http://weblogs.sqlteam.com/peterl/archive/2009/06/18/How-to-get-the-Weekday-and-Nth-from-a-date.aspx http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-for-any-given.aspx http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-number-of-weekdays-in-a-year.aspx This algorithm in the function allows you to get a weekday for any arbitrary period, both forward and backwards. If you pass a positive number for @Nth you will return the Nth weekday within the specified period, starting forwards from @FromDate. If you pass a negative number for @Nth, you will return the Nth weekday starting backwards from @ToDate. CREATE FUNCTION    dbo.fnWeekdayOfPeriod (     @FromDate AS DATETIME,     @ToDate AS DATETIME,     @WeekDay TINYINT,     @Nth INT ) RETURNS DATETIME AS BEGIN     RETURN  (             SELECT  DATEADD(DAY,                     CASE                         WHEN @Weekday < theFrom %...

posted @ Tuesday, November 03, 2009 4:11 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Superfast sequence generators!

Based on Itzik's suggestion for sequence generators, I rewrote them and adapted them for my needs. Itzik showed how to work around a big issue for some cases where the query optimizer actually tried to produce all possible combinations before returning the wanted records. My rewrite is two-part 1) The functions accepts a Starting point and an Ending point. 2) The functions accepts a Stepping point. You may want only odd or even numbers? Or only every 7th day? Here are the functions. Have fun with them! I haven't checked performance compared to MVJ's F_NUMBER_TABLE function, but they should be at least equally fast. CREATE FUNCTION dbo.fnGetNumbers (     @FromNum INT,     @ToNum...

posted @ Tuesday, November 03, 2009 11:38 AM | Feedback (4) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

New article series going on by Itzik Ben-Gan

I recommend you read it. Next part will contain a brilliant solution for calculating concurrent sessions in a linear algorithm. The math involved is very good indeed. First part is found here http://www.sqlmag.com/articles/index.cfm?articleid=102734 //Peso

posted @ Monday, October 26, 2009 11:34 AM | Feedback (4) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

Performance consideration when using a Table Variable

This is nothing new to me (I come across this autumn 2007) and probably not to you either but I forgot about it and yesterday it came alive again. I often choose a table variable for performance reasons (if data is less than 1 page anyway) due to the benefits of no logging etc etc. But yesterday I wrote a query for Phil Factor's "Subscription List" competition where I had choosen a table variable for the same reasons as before. It took a while to realize why (in this case) the solution with a temporary table was 30% faster than the solution...

posted @ Thursday, October 15, 2009 3:24 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 SQL Server 2005 ]

Median and weighted median

Yesterday Joe Celko posted on microsoft.public.sqlserver.programming about how to write an elegant query for Weighted Median. He managed to get the correct results but always ended up with ugly code. Joe had a feeling an elegant query existed but was not seeing it. Anyway, since Celko's books have helped me in the past, I thought I should help him now. Consider this sample data DECLARE @Foo TABLE         (             x INT NOT NULL         ) INSERT  @Foo VALUES  (1),         (2),         (2),         (3),         (3),         (3) The most common approach to calculate the median value I have seen is SELECT  AVG(1.0E * x) FROM    (             SELECT  x,                     ROW_NUMBER() OVER (ORDER BY x DESC) AS a,                     ROW_NUMBER() OVER (ORDER BY x) AS...

posted @ Wednesday, September 16, 2009 9:56 PM | Feedback (7) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

Statistical SQLCLR for prediction analysis now in RC1

For more information about my SQLCLR, see http://www.developerworkshop.net/software.html The biggest difference from Beta2 stage is that I now only target SQL Server 2008 and later. //Peso

posted @ Thursday, September 03, 2009 9:45 AM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms ]

NOT IN not equal to <>

Hi! I am between session of 24 hour of PASS right now, so I just post this piece of code I helped out with on a forum. The original poster was amazed that Col1 <> 'Some value' didn't return same records as Col1 NOT IN ('Some value') See this example code to understand the implications of fiddling with SET ANSI_NULLS option setting. declare @sample table         (             d varchar(200)         ) Insert  @sample select  'cancelled' union all select  null -- Try 1 set ansi_nulls off select  d as [Try 1, <>, ANSI_NULLS off] from    @sample where   d <> 'cancelled' select  d as [Try 1, NOT IN, ANSI_NULLS off] from    @sample where   d not in ('cancelled') -- Try 2 set ansi_nulls on select  d as [Try...

posted @ Wednesday, September 02, 2009 4:07 PM | Feedback (4) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Recursive Fibonacci number calculation

The answer to your question is "Yes, I am having a slow day today." ;WITH Fibonacci(n, f, f1) AS (     SELECT  CAST(1 AS BIGINT),             CAST(0 AS BIGINT),             CAST(1 AS BIGINT)       UNION ALL       SELECT  n + 1,             f + f1,             f     FROM    Fibonacci     WHERE   n < 93 ) SELECT  n,         f AS Number FROM    Fibonacci

posted @ Wednesday, August 26, 2009 12:33 PM | Feedback (5) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

CHAR(0) is not that innocent you may think

Some days ago I posted a solution for a simple problem on a forum about to delete multiple spaces in one statement (not using loop). My suggestion was declare @s varchar(100) set @s = 'xxxx                                  yyyyy          zzzzzz' SELECT REPLACE(REPLACE(REPLACE(@s, ' ', ' ' + CHAR(2)), CHAR(2) + ' ', ''), CHAR(2), '') I used CHAR(2) because that is not commonly used in normal texts. I then thought I could use CHAR(0) to be on the "safe" side, and now strange things begun to happen. Run this on your own risk, as you will see soon. Select  q,         len(q) from    (             SELECT    REPLACE(REPLACE(REPLACE(@s, ' ', ' ' + CHAR(0)), CHAR(0) +...

posted @ Sunday, August 23, 2009 9:33 AM | Feedback (1) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 Miscellaneous ]

Third running streak

declare @t table (Id int, dt datetime, value int) set dateformat 'dmy' insert into @t   select 1, '10/12/2008', 10 union all select 1, '11/12/2008', 10 union all select 1, '12/12/2008', 10 union all select 1, '13/12/2008', 9 union all select 1, '14/12/2008', 10 union all select 1, '15/12/2008', 10 union all select 1, '16/12/2008', 10 union all select 1, '17/12/2008', 10 union all select 2, '05/03/2008', 8 union all select 2, '06/03/2008', 6 union all select 2, '07/03/2008', 8 union all select 2, '08/03/2008', 8 union all select 2, '09/03/2008', 8 union all select 2, '20/03/2008', 8   SELECT      Id,             MIN(dt) AS Startdt,             MAX(dt) AS Enddt,             MIN(value) AS Value FROM        (                 SELECT Id,                         dt,                         value,                         ROW_NUMBER() OVER (PARTITION BY Id,...

posted @ Friday, August 21, 2009 11:19 AM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

Change collation

Yesterday I hade the unfortenate task to change the database collation for a particular database. Not only the default database collation should be changed, but also all columns! After some investigating about how to do this, I noticed that check constraints and foreign key constraints must be removed before changing a collation. Also indexes needed to be removed for this operation to succeed. I did it manually since it was my first time, but I wrote down the steps and today I gathered the notes into a beta-script, just in case I need to do this again in the future Why a script? Because most...

posted @ Wednesday, August 19, 2009 2:07 PM | Feedback (3) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 ]

Complement of ISNUMERIC function

Today I was involved in an interesting topic about how to check if a text string really is integer or not. This is what I finally suggested. CREATE FUNCTION dbo.fnIsINT (     @Data NVARCHAR(11) ) RETURNS INT AS BEGIN     RETURN  CASE                 WHEN SUBSTRING(@Data, 2, LEN(@Data)) LIKE '%[^0-9]%' COLLATE LATIN1_GENERAL_BIN THEN NULL                 WHEN SUBSTRING(@Data, 1, 1) NOT LIKE '[-+0-9]' COLLATE LATIN1_GENERAL_BIN THEN NULL                 WHEN @Data IN('-', '+') THEN NULL                 WHEN CAST(@Data AS BIGINT) NOT BETWEEN -2147483648 AND 2147483647 THEN NULL                 ELSE CAST(@Data AS INT)             END END And the BIGINT alternative CREATE FUNCTION dbo.fnIsBIGINT (     @Data NVARCHAR(20) ) RETURNS BIGINT AS BEGIN     RETURN  CASE                 WHEN SUBSTRING(@Data, 2, LEN(@Data)) LIKE '%[^0-9]%' COLLATE LATIN1_GENERAL_BIN THEN NULL                 WHEN SUBSTRING(@Data, 1, 1) NOT LIKE...

posted @ Friday, August 14, 2009 12:18 AM | Feedback (6) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Calculating Running Streak over many records

It has come to my attention that sometimes there are more than 100,000 records for which a "running streak" should be calculated on, so my previous blog post http://weblogs.sqlteam.com/peterl/archive/2009/08/12/Another-running-streaks-algorithm.aspx will not do. But this will work, and work fast! DECLARE  @Sample TABLE         (             Col1 INT,             Col2 INT,             Col3 INT,             Col4 INT,             Col5 INT,             Col6 INT,             Col7 INT,             Col8 DATETIME         ) INSERT  @Sample SELECT  43, 12345, 99887, 0, 0, 0, 0, '8/10/2009 6:00' UNION ALL SELECT  43, 12345, 99887, 0, 0, 0, 0, '8/10/2009 6:05' UNION ALL SELECT  43, 12345, 99887, 0, 0, 0, 1, '8/10/2009 6:10' UNION ALL SELECT  43, 12345, 99887, 0, 0, 0, 1, '8/10/2009 6:15' UNION ALL SELECT  43, 12345, 99887, 0, 0, 0,...

posted @ Wednesday, August 12, 2009 7:24 PM | Feedback (5) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

Curiosity about SQL Server 2008 R2 installation

I installed SQL Server Enterprise 2008 R2 on my laptop as a new instance. I also have SQL Server Developer 2008 as default instance. For the default instance, I have a database named Test on a separate partition of my laptop harddrive. After installing R2, I tried to attach the Test database, and failed, because I didn't detach the database from the default instance. So now I detached the Test database from the default instance but didn't make it because it was marked as suspect, even if the icon displayed good. So I took the Test database Offline and then detached it (there...

posted @ Wednesday, August 12, 2009 9:04 AM | Feedback (1) | Filed Under [ SQL Server 2008 Administration ]

Another running streaks algorithm

It has been some years since this article was posted http://www.sqlteam.com/article/detecting-runs-or-streaks-in-your-data and things has evolved since then. So I thought about using XML to solve the case. If the number of records are large, maybe this approach http://weblogs.sqlteam.com/peterl/archive/2009/08/12/Calculating-Running-Streak-over-many-records.aspx will be better for you? Below, I am using the same sample data as the original. DECLARE @Sample TABLE         (             GameDate DATETIME,             Result CHAR(1)         ) INSERT @Sample SELECT '1/1/2000', 'W' UNION ALL SELECT '1/12/2000', 'L' UNION ALL SELECT '1/15/2000', 'W' UNION ALL SELECT '1/17/2000', 'W' UNION ALL SELECT '1/22/2000', 'W' UNION ALL SELECT '2/1/2000', 'L' UNION ALL SELECT '2/5/2000', 'W' UNION ALL SELECT '2/8/2000', 'L' UNION ALL SELECT '2/16/2000', 'W' UNION ALL SELECT '2/19/2000', 'L' UNION ALL SELECT '2/25/2000', 'L' UNION ALL SELECT '2/28/2000', 'L' UNION ALL SELECT '3/15/2000', 'L' UNION ALL SELECT '3/19/2000', 'W' UNION ALL SELECT '3/25/2000', 'W' For this to...

posted @ Wednesday, August 12, 2009 12:49 AM | Feedback (8) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

Script out your foreign keys

This is a simple query for creating a script for your foreign keys in your database. It may need tweaking for composite keys. If that's the case, see here how to concatenate http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254 ;WITH Yak AS (     SELECT      ROW_NUMBER() OVER (ORDER BY o.[schema_id]) AS RowID,                 QUOTENAME(o.name) AS CONSTRAINT_NAME,                 QUOTENAME(SCHEMA_NAME(po.[schema_id])) AS FOREIGN_TABLE_SCHEMA,                 QUOTENAME(po.name) AS FOREIGN_TABLE_NAME,                 QUOTENAME(rccu.COLUMN_NAME) AS FOREIGN_COLUMN_NAME,                 QUOTENAME(SCHEMA_NAME(ro.[schema_id])) AS PRIMARY_TABLE_SCHEMA,                 QUOTENAME(ro.name) AS PRIMARY_TABLE_NAME,                 QUOTENAME(rc.name) AS PRIMARY_COLUMN_NAME,                 CASE fk.is_disabled                     WHEN 0 THEN 'CHECK'                     ELSE 'NOCHECK'                 END AS [ENABLED]     FROM        sys.foreign_keys AS fk     INNER JOIN sys.objects AS o ON o.[object_id] = fk.[object_id]     INNER JOIN sys.objects AS po ON po.[object_id] = fk.parent_object_id     INNER JOIN sys.objects AS ro ON ro.[object_id]...

posted @ Friday, August 07, 2009 12:43 PM | Feedback (1) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 ]

Manipulate XML data, continued

In my previous blog post about how to manipulate data in XML columns, http://weblogs.sqlteam.com/peterl/archive/2009/07/03/Manipulate-XML-data-with-non-xml-columns-and-not-using-variable.aspx, I didn't have time to include how to delete elements. Now I have and here you can see how to delete elements from a XML column. I am using the same sample data for easier understanding. CREATE TABLE #Sample              (                  RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,                  MemberID INT,                  MemberData XML              )   INSERT  #Sample SELECT  1, '<meta><customergroup>F</customergroup><mosaic>Young educated man</mosaic></meta>' UNION ALL SELECT  2, '<meta><age>24</age></meta>'   CREATE PRIMARY XML INDEX IX_PrimaryXML ON #Sample(MemberData)   CREATE XML INDEX IX_Element ON #Sample(MemberData)  USING XML INDEX IX_PrimaryXML FOR PATH   SELECT  MemberID,         MemberData,         MemberData.value('/meta[1]/customergroup[1]', 'VARCHAR(8)') AS CustomerGroup,         MemberData.value('/meta[1]/mosaic[1]', 'VARCHAR(200)') AS Mosaic,         MemberData.value('/meta[1]/age[1]', 'TINYINT') AS Age,         MemberData.value('/meta[1]/zipcode[1]', 'VARCHAR(5)') AS ZipCode,         MemberData.value('/meta[1]/status[1]', 'VARCHAR(15)') AS [Status] FROM    #Sample   --...

posted @ Tuesday, August 04, 2009 9:30 AM | Feedback (0) | Filed Under [ SQL Server 2008 SQL Server 2005 ]

Fast and Simple Prime Number Factorization

This algorithm requires an existing Prime numbers table. You can easily create one of your own or importing the primes ranging from 2 to 3,037,000,493 from the Internet. If you only is interested in primes with INT range {2..2,147,483,647} you only need the first 4,792 primes {2..46,337} DECLARE @Number BIGINT   SET     @Number = 2020208534430421   SELECT  Prime AS Number,         CAST(1 AS TINYINT) AS Items INTO    #Temp FROM    Primes WHERE   Prime <= SQRT(@Number)         AND @Number % Prime = 0 SELECT  @Number = @Number / Number FROM    #Temp WHILE @@ROWCOUNT > 0     UPDATE  #Temp     SET     Items = Items + 1,             @Number = @Number / Number     WHERE   @Number % Number = 0 SELECT  Number,         Items FROM    #Temp UNION ALL SELECT  @Number,         1 WHERE   @Number >...

posted @ Sunday, August 02, 2009 2:12 AM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

How to insert into two tables in one statement

Today I am going to show a statement available with SQL Server 2008 and later, the MERGE command, and how it will be possible to insert into two tables within same statement. Right now, I can't see a practical use, but that might change in the future. As long as I remember this blog post exists... DECLARE @Source TABLE         (             a INT,             b INT         )   INSERT  @Source SELECT  11, 21 UNION ALL SELECT  12, 22   DECLARE @PrimaryTarget TABLE         (             a INT         )   DECLARE @SecondaryTarget TABLE         (             b INT         )   MERGE   @PrimaryTarget AS pt USING   @Source AS s ON 1 = 1 WHEN    NOT MATCHED BY TARGET         THEN    INSERT  (                             a                         )                 VALUES  (                             s.a                         ) OUTPUT  s.b INTO    @SecondaryTarget;   SELECT  'Source' AS TableName,         a,         b FROM   ...

posted @ Wednesday, July 29, 2009 6:16 PM | Feedback (7) | Filed Under [ SQL Server 2008 Algorithms ]

Simple base converter

Here is a simple base converter that manages [from and to] base 2 to 16. CREATE FUNCTION    dbo.fnBaseConvert (     @Value VARCHAR(8),     @FromBase TINYINT,     @ToBase TINYINT ) RETURNS VARCHAR(32) AS BEGIN     RETURN (                 SELECT     SUBSTRING('0123456789abcdef', 1 +(x.theValue % CAST(POWER(CAST(@ToBase AS FLOAT), v.Number + 1) AS BIGINT)) / CAST(POWER(CAST(@ToBase AS FLOAT), v.Number) AS BIGINT), 1)                 FROM        (                                 SELECT SUM((CHARINDEX(SUBSTRING(@Value, LEN(@Value) - Number, 1), '0123456789abcdef') - 1) * CAST(POWER(CAST(@FromBase AS FLOAT), Number) AS BIGINT)) AS theValue                                 FROM    master..spt_values                                 WHERE   Type = 'P'                                         AND Number < LEN(@Value)                                 HAVING MIN(CHARINDEX(SUBSTRING(@Value, LEN(@Value) - Number, 1), '0123456789abcdef')) > 0                                         AND MAX(CHARINDEX(SUBSTRING(@Value, LEN(@Value) - Number, 1), '0123456789abcdef')) <= @FromBase                             ) AS x                 INNER JOIN  master..spt_values AS v ON v.Type = 'P'                 WHERE       x.theValue >= 0                             AND v.Number < CEILING(0.00000005 + LOG(COALESCE(NULLIF(x.theValue,...

posted @ Tuesday, July 21, 2009 1:18 AM | Feedback (1) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

EXISTS is my new favorite tool

Since this blog post http://weblogs.sqlteam.com/peterl/archive/2007/09/24/Finding-group-of-records-with-a-certain-status.aspx I have kept the technique in similar situations and perhaps never bothered to rethink my prerogative. Well, today I have. Recently I blogged here http://weblogs.sqlteam.com/peterl/archive/2009/06/12/Timings-of-different-techniques-for-finding-missing-records.aspx about finding records in one table which are missing in another table. In that blog post, I noticed that NOT EXISTS were the better choice for all scenarios, and have learnt to adopt the NOT EXISTS approach. Why haven't I extended that knowledge to the "Certain status" scenario? I can't tell. And that is a good thing. Because being active on a good forum now and then makes you rethink your positions. As I did today. Here is the test case from which...

posted @ Monday, July 13, 2009 9:36 PM | Feedback (2) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

How to calculate the number of weekdays for any given period

Here http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-in-a-month.aspx is a specialized version for a month, and here http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-number-of-weekdays-in-a-year.aspx is a specialized version for a year. This code below calculates the number of weekdays for any given period. CREATE FUNCTION dbo.fnPeriodWeekdays (     @StartDate DATETIME,     @EndDate DATETIME ) RETURNS INT AS BEGIN     RETURN  (                 DATEDIFF(DAY, @StartDate, @EndDate) + 1                 + (DATEDIFF(DAY, '17530101', @StartDate) + 1) / 7 * 2                 - (DATEDIFF(DAY, '17530101', @EndDate) + 1) / 7 * 2                 - CASE DATEDIFF(DAY, '17530101', @StartDate) % 7                       WHEN 6 THEN 1                       ELSE 0                   END                 - CASE DATEDIFF(DAY, '17530101', @EndDate) % 7                       WHEN 5 THEN 1                       ELSE 0                   END         ) END

posted @ Sunday, July 12, 2009 2:29 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

How to calculate the number of weekdays in a month

Here http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-for-any-given.aspx you can find a specialized version that calculates number of weekdays for any given period. CREATE FUNCTION dbo.fnMonthWeekDays (     @Year SMALLINT,     @Month TINYINT ) RETURNS TINYINT AS BEGIN     RETURN (                 SELECT 20 + SUM(1 - DATEDIFF(DAY, '17530101', dt) % 7 / 5)                 FROM    (                             SELECT DATEADD(MONTH, 12 * @Year - 22800 + @Month, -1) AS dt                             WHERE   ISDATE(10000 * @Year + 100 * @Month + 31) = 1                               UNION ALL                               SELECT DATEADD(MONTH, 12 * @Year - 22800 + @Month, -2)                             WHERE   ISDATE(10000 * @Year + 100 * @Month + 30) = 1                               UNION ALL                               SELECT CASE ISDATE(10000 * @Year + 100 * @Month + 29)                                         WHEN 1 THEN DATEADD(MONTH,...

posted @ Sunday, July 12, 2009 1:50 PM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

How to calculate number of weekdays in a year

This function calculates the number of weeksdays in a year, and has error-checking for invalid years. It is also language independant. Here http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-for-any-given.aspx you can find a specialized version that calculates number of weekdays for any given period. CREATE FUNCTION dbo.fnYearWeekDays (     @Year SMALLINT ) RETURNS SMALLINT AS BEGIN         RETURN  (                     SELECT  260 + SUM(1 - DATEDIFF(DAY, '17530101', dt) % 7 / 5)                     FROM    (                                 SELECT  DATEADD(YEAR, @Year - 1899, -1) AS dt                                   UNION ALL                                                                                                     SELECT  DATEADD(YEAR, @Year - 1899, -2)                                 WHERE   ISDATE(10000 * @Year + 229) = 1                             ) AS d                     WHERE   @Year BETWEEN 1753 AND 9999                 ) END  

posted @ Sunday, July 12, 2009 12:31 PM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

JDEdwards date conversion

It seems JDEdwards dates are stored NUMERIC(6, 0) in this format 107299, where 107 is the number of years after 1900, and 299 is the 299th day of the year. So how to convert JDEdwards to DATETIME? DECLARE @jde NUMERIC(6, 0) SET     @jde = 107299 SELECT @jde,         DATEADD(DAY, @jde % 1000, DATEADD(YEAR, @jde / 1000, -1))   And how to go from DATETIME to JDEdwards? DECLARE @Date DATETIME SET     @Date = '20041117' SELECT @Date,         1000 * DATEDIFF(YEAR, 0, @Date) + DATEPART(DAYOFYEAR, @Date)  

posted @ Friday, July 10, 2009 4:23 PM | Feedback (3) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Manipulate XML data with non-xml columns and not using variable

Some time ago, I displayed how to work with XML data when searching for data stored in a XML column. Here Some XML search approaches and here Updated XML search (test case with variables). Today I thought I should demonstrate how to insert and update XML columns with data from other columns and not using variables. Well, I do have some examples of using variables in here because I wanted to display the core difference. And this is my first blog post (not counting the previous announcement) since I become a MVP, I thought this blog post should be about...

posted @ Friday, July 03, 2009 3:57 PM | Feedback (4) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 ]

Microsoft Connect - Enhanced Syntax For Insert Into Statement

I've posted a feedback on Microsoft Connect about how to enhance the INSERT INTO syntax. Especially for INSERT INTO ... EXEC ... Sometimes when you need the result from a stored procedure, the SP itself returns two (or more) resultsets. And it's only possibly to fetch and store the first resultset. What I have suggested is an enhanced syntax for INSERT INTO ... EXEC, like this INSERT INTO Table1 (Col1, Col2), Table2 (ColX, ColY, ColZ) EXEC usp_MyStoredProcedure @Param1, @Param2 In this example, usp_MyStoredProcedure returns three resultsets, of which I want to store the two first. First resultset has two columns, and second resultset has three columns. Let Microsoft know...

posted @ Saturday, June 27, 2009 10:06 PM | Feedback (6) | Filed Under [ Optimization SQL Server 2008 Miscellaneous ]

Microsoft Connect - SSMS Debugger Issue

I've found an issue with the Debugger for SQL Server 2008 Management Studio a while ago. This is my way to ask you to endorse a fix https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=374183 Please let Microsoft know what you think about this suggestion.

posted @ Saturday, June 27, 2009 10:01 PM | Feedback (0) | Filed Under [ SQL Server 2008 Miscellaneous ]

Extended Get Nth Weekday of period

Recently I posted a function which returned the Nth weekday of a month, either from the beginning of month or from the end of month. Function is found here http://weblogs.sqlteam.com/peterl/archive/2009/06/17/How-to-get-the-Nth-weekday-of-a-month.aspx I have fiddled around with it and have now extended the function to find the Nth weekday not only for a month, but also for a quarter or a year. Below is the function. CREATE FUNCTION dbo.fnGetNthWeekdayOfPeriod (     @theDate DATETIME,     @theWeekday TINYINT,     @theNth SMALLINT,     @theType CHAR(1) ) RETURNS DATETIME BEGIN     RETURN (                 SELECT theDate                 FROM    (                             SELECT DATEADD(DAY, theDelta +(@theWeekday + 6 - DATEDIFF(DAY, '17530101', theFirst) % 7) % 7, theFirst) AS theDate                             FROM    (                                         SELECT CASE UPPER(@theType)                                                     WHEN 'M' THEN DATEADD(MONTH, DATEDIFF(MONTH, @theNth, @theDate), '19000101')                                                     WHEN 'Q'...

posted @ Thursday, June 18, 2009 11:34 AM | Feedback (3) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

How to get the Weekday and Nth from a date

You call this function with a date. The function returns a table with one record and 3 columns. First column is Weekday; Monday = 1, Tuesday = 2, Wednesday = 3; Thursday = 4, Friday = 5, Saturday = 6 and Sunday = 7. Second column is number of occurencies of that date since beginning of selected period type. Third columns is number of occurencies left of that period type. CREATE FUNCTION dbo.fnGetWeekdayAndNths (     @theDate DATETIME,     @theType CHAR(1) ) RETURNS TABLE AS RETURN (   SELECT 1 + DATEDIFF(DAY, -53690, @theDate) % 7 AS theWeekday,                     1 +(theDelta - 1) / 7 AS Beginning,                     DATEDIFF(DAY, DATEADD(DAY, -1, thePeriod), @theDate) / 7 - 1...

posted @ Thursday, June 18, 2009 2:28 AM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

How to get the Nth weekday of a month

You call this function with three parameters: 1. Any date of the month in question 2. The weekday to calculate; Monday 1, Tuesday 2, Wednesday 3, Thursday 4, Friday 5, Saturday 6 and Sunday 7 3. The choice of weekday count; a positive number means from the beginning of month and a negative number means from the end of month If a valid date cannot be calculated, NULL is returned. For an extended version which, besides month, also handles quarter and year, see new blog post here http://weblogs.sqlteam.com/peterl/archive/2009/06/18/Extended-Get-Nth-Weekday-of-period.aspx CREATE FUNCTION dbo.fnGetNthWeekdayOfMonth (     @theDate DATETIME,     @theWeekday TINYINT,     @theNth SMALLINT ) RETURNS DATETIME BEGIN     RETURN  (                 SELECT  theDate                 FROM    (                             SELECT  DATEADD(DAY, 7 * @theNth...

posted @ Wednesday, June 17, 2009 9:05 PM | Feedback (9) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Timings of different techniques for finding missing records

I did some tests today to measure the different approaches for finding records present in one table but not in another. The results of CPU and Duration are presented below with some help from SQL Profiler. Number of reads are equal between methods but different depending how many record in #TableB. If there are other methods I haven't included, please let me know. Method      TableA   TableB   CPU  Duration ----------  -------  -------  ---  -------- GROUP BY    1000000  1000000  748       754 LEFT JOIN   1000000  1000000  328       321 NOT EXISTS  1000000  1000000  265       288 NOT IN      1000000  1000000  296       293 EXCEPT      1000000  1000000  312       288 GROUP BY    1000000   500000  577      2984 LEFT JOIN   1000000   500000...

posted @ Friday, June 12, 2009 3:22 PM | Feedback (11) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Extract XML structure automatically, part 2

For some time ago, I posted an algorithm how to get the XML structure automatically. Today I stumbled across another approach which seems to be faster. Reservations though I haven't tested this against large xml data yet. However, the previous algorithm relied on a WHILE loop here http://weblogs.sqlteam.com/peterl/archive/2009/03/05/Extract-XML-structure-automatically.aspx but this new algorithm doesn't. It's all xml internal thingies going on. DECLARE     @Nodes TABLE             (                          NodeID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,                          ParentNodeName NVARCHAR(64),                          NodeName NVARCHAR(64)             ) DECLARE @Data XML SET @Data = ' <root>         <elementGroup>                         <element>                                      <stuff>                                                  <comment>Stuff comment</comment>                                      </stuff>                                      <comment>Element comment</comment>                         </element>                         <comment>Element group comment</comment>             </elementGroup>             <comment>Root comment</comment> </root>' INSERT      @Nodes             (                          ParentNodeName,                          NodeName             ) SELECT      e.value('local-name(..)[1]', 'VARCHAR(MAX)') AS ParentNodeName,            ...

posted @ Thursday, June 04, 2009 1:30 PM | Feedback (2) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

How to check Danish personal number

CREATE FUNCTION dbo.fnCheckDanSSN (             @SSN CHAR(10) ) RETURNS BIT AS BEGIN     IF @SSN NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'         RETURN 0       DECLARE @x CHAR(6)         SET     @x = SUBSTRING(@SSN, 5, 2) + SUBSTRING(@SSN, 3, 2) + SUBSTRING(@SSN, 1, 2)       IF SUBSTRING(@SSN, 7, 1) IN('0', '1', '2', '3') AND ISDATE('19' + @x) = 0         RETURN 0       IF SUBSTRING(@SSN, 7, 1) IN('4', '9') SUBSTRING(@SSN, 5, 2) BETWEEN '00' AND '36' AND ISDATE('20' + @x) = 0         RETURN 0       IF SUBSTRING(@SSN, 7, 1) IN('4', '9') SUBSTRING(@SSN, 5, 2) BETWEEN '37' AND '99' AND ISDATE('19' + @x) = 0         RETURN 0       IF SUBSTRING(@SSN, 7, 1) IN('5', '6', '7', '8') SUBSTRING(@SSN, 5, 2)...

posted @ Friday, May 29, 2009 2:26 PM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

How to calculate Finnish personal number

CREATE FUNCTION dbo.fnCalculateFinSSN (             @SSN CHAR(10) ) RETURNS CHAR(11) AS BEGIN     IF LOWER(@SSN) NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][-+a][0-9][0-9][0-9]'         RETURN NULL       IF SUBSTRING(@SSN, 7, 1) IN('-', '+') AND ISDATE('19' + SUBSTRING(@SSN, 1, 6)) = 0         RETURN NULL       IF LOWER(SUBSTRING(@SSN, 7, 1)) = 'a' AND ISDATE('20' + SUBSTRING(@SSN, 1, 6)) = 0         RETURN 0       DECLARE @Digits INT       SET     @Digits = CAST(SUBSTRING(@SSN, 1, 6) + SUBSTRING(@SSN, 8, 3) AS INT) % 31       RETURN @@SSN + SUBSTRING('0123456789abcdefghijklmnopqrstuvwxy', @Digits + 1, 1) END

posted @ Friday, May 29, 2009 2:07 PM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

How to check Finnish personal number

CREATE FUNCTION dbo.fnCheckFinSSN (             @SSN CHAR(11) ) RETURNS BIT AS BEGIN     IF LOWER(@SSN) NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][-+a][0-9][0-9][0-9][0-9a-y]'         RETURN 0       IF SUBSTRING(@SSN, 7, 1) IN('-', '+') AND ISDATE('19' + SUBSTRING(@SSN, 1, 6)) = 0         RETURN 0       IF LOWER(SUBSTRING(@SSN, 7, 1)) = 'a' AND ISDATE('20' + SUBSTRING(@SSN, 1, 6)) = 0         RETURN 0       DECLARE @Digits INT       SET     @Digits = CAST(SUBSTRING(@SSN, 1, 6) + SUBSTRING(@SSN, 8, 3) AS INT) % 31       IF SUBSTRING('0123456789abcdefghijklmnopqrstuvwxy', @Digits + 1, 1) <> LOWER(RIGHT(@SSN, 1))         SET @Digits = -1       RETURN @Digits + 1 END  

posted @ Friday, May 29, 2009 1:46 PM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Get this group but not that group

Wow! This seems to be blog post number 100. Today I stumbled across an interesting problem where OP had a set of records. Every record were "typed" and "grouped". Now OP wanted to get distinct group id's for those groups having at least one record typed 33, but not having a record typed 11. This can be done in a number of ways, but I wanted to write an efficiently running query. DECLARE     @Sample TABLE             (                         MeetingID INT,                          TicketID INT             )   INSERT      @Sample SELECT      1, 11 UNION ALL SELECT      1, 22 UNION ALL SELECT      1, 33 UNION ALL SELECT      2, 22 UNION ALL SELECT      2, 33 UNION ALL SELECT      3, 11 UNION ALL SELECT     ...

posted @ Friday, May 29, 2009 10:57 AM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Extended ISO week function

CREATE FUNCTION    dbo.fnISOWEEK (     @theDate DATETIME ) RETURNS TINYINT AS BEGIN     RETURN (                 SELECT CASE                             WHEN @theDate >= '99990104' THEN (DATEPART(DAYOFYEAR, @theDate) - 4) / 7                             WHEN @theDate >= '99990101' THEN 52                             WHEN NextYear <= @theDate THEN 0                             WHEN CurrentYear <= @theDate THEN DATEDIFF(DAY, CurrentYear, @theDate) / 7                             ELSE DATEDIFF(DAY, PreviousYear, @theDate) / 7                         END + 1                 FROM    (                             SELECT DATEADD(DAY,(DATEDIFF(DAY, '17530101', DATEADD(YEAR, -1, Jan4)) / 7) * 7, '17530101') AS PreviousYear,                                     DATEADD(DAY,(DATEDIFF(DAY, '17530101', Jan4) / 7) * 7, '17530101') AS CurrentYear,                                     DATEADD(DAY,(DATEDIFF(DAY, '17530101', DATEADD(YEAR, 1, Jan4)) / 7) * 7, '17530101') AS NextYear                             FROM    (                                         SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, @theDate), 3) AS Jan4                                     ) AS x                         )...

posted @ Wednesday, May 27, 2009 9:52 AM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Double duty

I was browsing some of my older solutions, and I stumbled across this interesting piece. The object was to mark any employees with Duty 'B' for those employees having both Duty 'O' and Duty 'D'. DECLARE     @Roles TABLE (Person VARCHAR(5), [Role] VARCHAR(1)) INSERT      @Roles SELECT      'Smith', 'O' UNION ALL SELECT      'Smith', 'D' UNION ALL SELECT      'Jones', 'O' UNION ALL SELECT      'White', 'D' UNION ALL SELECT      'Brown', 'X' SELECT      Person,             SUBSTRING('ODB', SUM(CHARINDEX(Role, 'OD')), 1) AS Duty FROM        @Roles WHERE       [Role] IN ('D', 'O') GROUP BY    Person

posted @ Tuesday, April 21, 2009 3:24 PM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Greatest Common Divisor function

This approach is not dependent on the 32-level recursion as most other algorithms are for this problem. CREATE FUNCTION dbo.fnGCD (             @a INT,             @b INT ) RETURNS INT AS BEGIN             DECLARE     @c INT               IF @a IS NULL OR @b IS NULL OR (@a = 0 AND @b = 0)                          RETURN NULL               IF @a = 0 OR @b = 0                          RETURN ABS(@a) + ABS(@b)               IF ABS(@a) < ABS(@b)                          SELECT      @c = ABS(@a),                                      @a = ABS(@b),                                      @b = @c             ELSE                          SELECT      @a = ABS(@a),                                      @b = ABS(@b)               SET         @c = @a % @b               WHILE @c > 0                          SELECT      @a = @b,                                      @b = @c,                                      @c = @a %...

posted @ Wednesday, April 15, 2009 10:17 AM | Feedback (3) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Composable DML

With the arrival of SQL Server 2005 the new OUTPUT operator was introduced. The OUTPUT operator works much like a "local trigger" on the current statement. The drawback is that there is no way to filter the returned resultset directly. You have to insert the resultset in a staging table and work from there. With SQL Server 2008 you now have a tool named Composable DML. What is then Composable DML? Well, with this tool you can have a statement of UPDATE, DELETE and even MERGE as a data source for your query! In this example I am showing you how to audit certain...

posted @ Wednesday, April 08, 2009 12:24 PM | Feedback (5) | Filed Under [ Optimization SQL Server 2008 Administration ]

@@ERROR, BEGIN TRY/CATCH and XACT_ABORT

Today I am showing you the difference between @@ERROR, BEGIN TRY/CATCH and XACT_ABORT.  The three alternatives to error handling works little different and let us intercept the error handling in different stages in the process. First I am going to show you the ordinary @@ERROR check which most of you are used to.   IF OBJECT_ID('uspTest_2000') IS NOT NULL ...

posted @ Tuesday, April 07, 2009 9:53 AM | Feedback (5) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]

One way to calculate decimal year

Today I am going to show you one way to calculate a decimal year. This is not a easy as it sounds because some years (leap years) include a leap day so the number of days in a year is not consistent. The longest defined consistent time period there is, is a week. A week is seven days or 168 hours. However, a week can be defined to have different start weekday in different regions of the world. Enough said about that. First, set up a test environment by declaring and setting two datetime variables like this declare @f datetime...

posted @ Wednesday, April 01, 2009 10:09 AM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

NULL NOT IN conundrum

The scenario is very simple. You want to get all ID’s from one table that does not exists in another table. This is how the two simple tables are set up. DECLARE     @Table1 TABLE             (                          ID INT             ) INSERT      @Table1 SELECT      1 UNION ALL SELECT      2 UNION ALL SELECT      3 UNION ALL SELECT      4 UNION ALL SELECT      5 UNION ALL SELECT      6 UNION ALL SELECT      7 DECLARE     @Table2 TABLE             (                          ID INT             ) INSERT      @Table2 SELECT      1 UNION ALL SELECT      3 UNION ALL SELECT      4 UNION ALL SELECT      5 UNION ALL SELECT      7 UNION...

posted @ Thursday, March 19, 2009 10:02 AM | Feedback (7) | Filed Under [ SQL Server 2008 SQL Server 2005 SQL Server 2000 ]

Human readable string truncation

In same cases, truncation of a long string is necessary. In most cases you just need to truncate it at the spot and have every section be exactly the same length, like this: DECLARE     @String VARCHAR(MAX),             @Size TINYINT SELECT      @String = 'Hello my name is Jeff. I need some help on a project because ',             @String = @String + 'right now this is how the application i am working ',             @String = @String + 'with displays data.',             @Size = 32 SELECT      1 + Number AS Part,              SUBSTRING(@String, Number * @Size, @Size) FROM        master..spt_values WHERE       Type = 'P'...

posted @ Wednesday, March 18, 2009 11:11 PM | Feedback (3) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

A general approach to sort different dateformats correctly

DECLARE @Stats TABLE         (             SomeDate DATETIME         ) INSERT  @Stats SELECT  20000 + ABS(CHECKSUM(NEWID())) % 30000 FROM    master..spt_values DECLARE @Style INT ...

posted @ Wednesday, March 18, 2009 2:31 PM | Feedback (4) | Filed Under [ SQL Server 2008 SQL Server 2005 SQL Server 2000 ]

Excel DAYS360 clone

CREATE FUNCTION dbo.DATEDIFF360 (     @source DATETIME,     @target DATETIME,     @style BIT = 0 ) RETURNS INT AS BEGIN         RETURN  CASE @style                     -- European style                     WHEN 1 THEN CASE                                     WHEN DATEPART(DAY, @target) = 31...

posted @ Sunday, March 15, 2009 4:12 PM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Excel ERF clone for two variables

I previously posted a solution for one variable here http://weblogs.sqlteam.com/peterl/archive/2009/03/11/Excel-ERF-clone.aspx and here is a solution for using two variables. CREATE FUNCTION dbo.fnErf (             @z1 FLOAT,             @z2 FLOAT,             @MaxIterations TINYINT = 10 ) RETURNS FLOAT AS BEGIN             IF @z1 IS NULL OR @z2 IS NULL                          RETURN      NULL               DECLARE     @n TINYINT,                          @s1 FLOAT,                          @s2 FLOAT,                          @p1 FLOAT,                          @p2 FLOAT,                          @a1 FLOAT,                          @a2 FLOAT               SELECT      @n = 1,                          @p1 = 1,                          @p2 = 1,                          @a1 = @z1,                          @a2 = @z2,                          @MaxIterations = COALESCE(ABS(@MaxIterations), 10)               WHILE @p1 <> 0.0E AND @p2 <> 0.0E AND @n <= @MaxIterations                          SELECT      @s1 = @z1 /(2.0E * @n + 1.0E),                                      @s2 = @z2 /(2.0E *...

posted @ Wednesday, March 11, 2009 2:55 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Excel ERF clone

This Excel ERF clone works for with SQL Server 2000 and later. I have found that 10 iterations will give enough acccuracy (maximum float accuracy) in most cases, so you can call the function with SELECT  dbo.fnErf(0.35, DEFAULT) However, if you feel the need to more accuracy, replace second parameter with a value of your choice. Here http://weblogs.sqlteam.com/peterl/archive/2009/03/11/Excel-ERF-clone-for-two-variables.aspx is also a solution for two variables. CREATE FUNCTION dbo.fnErf (         @z FLOAT,         @MaxIterations TINYINT = 10 ) RETURNS FLOAT AS BEGIN         IF @z IS NULL                 RETURN      NULL         DECLARE @n TINYINT,                 @s FLOAT,                 @p FLOAT,                 @a FLOAT         SELECT  @n = 1,                 @p = 1,                 @a = @z,                 @MaxIterations = COALESCE(ABS(@MaxIterations), 10)         WHILE @p <> 0.0E AND @n <= @MaxIterations                 SELECT  @s = @z /(2.0E * @n...

posted @ Wednesday, March 11, 2009 1:59 PM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

How to efficiently reuse gaps in identity column

Today I am going to talk about how to efficiently reuse identity values in a column, even if this is something that normally not should be bothered. The reason for this solution was a request for help from a member here on SQLTeam, who was near run out of identity values. I did some reasearch first to see which was the most common method to deal with this situation and not surprisingly the method of iterating all records from start to end was used. That method is not efficient. What if you have 1 million records and there is only 1 gap at...

posted @ Tuesday, March 10, 2009 9:06 AM | Feedback (7) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

Extract XML structure automatically

Today I am going to write about how to extract the XML structure from a file. The basic idea is to bulk read the file from disk, place the content in an XML variable and traverse elements in the variable and ultimately output a resultset showing the structure of xml file. I often use this to determine what kind of XML the file is by comparing the returned resultset with a lookup-table. See comment in code to understand what happens CREATE PROCEDURE dbo.uspGetFileStructureXML (     @FileName NVARCHAR(256) ) AS -- Prevent unwanted resultsets back to client SET NOCOUNT ON -- Initialize...

posted @ Thursday, March 05, 2009 3:06 PM | Feedback (9) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

Fastest LeapYear checker?

CREATE FUNCTION dbo.fnIsLeapYear (             @Year SMALLINT ) RETURNS BIT AS BEGIN             RETURN      CASE DATEPART(DAY, DATEADD(YEAR, @Year - 1904, '19040229'))                                      WHEN 29 THEN 1                                      ELSE 0                          END END

posted @ Wednesday, February 25, 2009 3:11 PM | Feedback (21) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Improved anniversary calculation (better datediff)

Some time ago, I wrote this article about how DATEDIFF works. http://www.sqlteam.com/article/datediff-function-demystified At the end I suggested two functions to calculate the number of months according to how human mind works. At the discussion later, a person notified me that it calculated the wrong number of months if you tried January 29th 2009 to February 28th 2009. The day is still greater, but you cannot have more days in february 2009 than 28. These are improved functions that also deals with those situations. CREATE FUNCTION [dbo].[fnMonthsApart] (     @FromDate DATETIME,     @ToDate DATETIME ) RETURNS INT AS BEGIN         RETURN  CASE                     WHEN @ToDate < DATEADD(MONTH, DATEDIFF(MONTH, @FromDate, @ToDate), @FromDate) THEN DATEDIFF(MONTH, @FromDate, @ToDate) - 1                     ELSE DATEDIFF(MONTH,...

posted @ Friday, February 13, 2009 10:50 AM | Feedback (5) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Get all your databases and their sizes

SELECT      @@SERVERNAME AS SqlServerInstance,             db.name AS DatabaseName,             SUM(CASE WHEN af.groupid = 0 THEN 0 ELSE f.size / 128.0E END) AS DatabaseSize,             SUM(CASE WHEN af.groupid = 0 THEN af.size / 128.0E ELSE 0 END) AS LogSize,             SUM(af.size / 128.0E) AS TotalSize FROM        master..sysdatabases AS db INNER JOIN  master..sysaltfiles AS af ON af.[dbid] = db.[dbid] WHERE       db.name NOT IN ('distribution', 'Resource', 'master', 'tempdb', 'model', 'msdb') -- System databases             AND db.name NOT IN ('Northwind', 'pubs', 'AdventureWorks', 'AdventureWorksDW')   -- Sample databases GROUP BY    db.name

posted @ Thursday, February 12, 2009 11:20 AM | Feedback (3) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]

How to tell who did a backup when

SELECT      db.name AS DatabaseName,             bf.logical_name AS LogicalName,             CASE bs.[type]                         WHEN 'D' THEN 'Database'                         WHEN 'I' THEN 'Differential database'                         WHEN 'L' THEN 'Log'                         WHEN 'F' THEN 'File or filegroup'                         WHEN 'G' THEN 'Differential file'                         WHEN 'P' THEN 'Partial'                         WHEN 'Q' THEN 'Differential partial'                         ELSE 'Unknown'             END AS BackupType,             CASE bf.file_type                         WHEN 'D' THEN 'SQL Server data file'                         WHEN 'L' THEN 'SQL Server log file'                         WHEN 'F' THEN 'Full text catalog'                         ELSE 'Unknown'             END AS FileType,             bs.user_name AS UserName,             bs.backup_start_date AS StartDate,             bs.backup_finish_date AS FinishDate,             CAST(bs.software_major_version AS VARCHAR(11)) + '.'            ...

posted @ Thursday, February 12, 2009 10:36 AM | Feedback (2) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]

Alternative approach to calculate most used time interval

In the past I have given the advice to break down all date intervals into the smallest part, most often minutes. Then OP should group by the minute. -- Prepare sample data DECLARE     @Data TABLE             (                          RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,                          CreateDate DATETIME,                          DeleteDate DATETIME             ) -- Populate sample data INSERT      @Data             (                          CreateDate,                          DeleteDate             ) SELECT      '2009-01-14 22:33', '2009-01-14 22:35' UNION ALL SELECT      '2009-01-14 22:33', '2009-01-14 22:33' UNION ALL SELECT      '2009-01-14 22:34', '2009-01-14 22:35' UNION ALL SELECT      '2009-01-14 22:35', '2009-01-14 22:35' UNION ALL SELECT      '2009-01-14 22:35', '2009-01-14 22:36' UNION ALL SELECT      '2009-01-14 22:37', '2009-01-14 22:37' UNION ALL SELECT      '2009-01-14 22:39', '2009-01-14 22:39' UNION ALL SELECT      '2009-01-14 22:38', '2009-01-14 22:38' UNION...

posted @ Friday, January 30, 2009 9:03 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

Create another nested XML hierarchy

-- Prepare sample data DECLARE     @Sample TABLE             (                          GalaxyID INT,                          ObjectID VARCHAR(16),                          ObjectType VARCHAR(5),                          ObjectTitle VARCHAR(200)             ) INSERT      @Sample SELECT      1, 'T022520001611242', 'Topic', 'Business Strategy' UNION ALL SELECT      1, 'T021320001145243', 'Topic', 'Decision Making' UNION ALL SELECT      1, 'T8150310322032', 'Topic', 'New Growth' UNION ALL SELECT      1, 'T97200019493829', 'Topic', 'Marketing Strategy' UNION ALL SELECT      2, 'T1210018575047', 'Topic', 'Strategic Relationships' UNION ALL SELECT      2, 'T1027001655860', 'Topic', 'Globalization' UNION ALL SELECT      2, 'T95200015582307', 'Topic', 'Strategic Sourcing' UNION ALL SELECT      2, 'T021120001714561', 'Topic', 'Business Processes & Architectures' UNION ALL SELECT      3, 'T011820001527219', 'Topic', 'Business Models' UNION ALL SELECT      3, 'T022520001622334', 'Topic', 'Venture Capital Processes' UNION ALL SELECT      3, 'T524200010114538', 'Topic', 'Entrepreneurial Thinking' UNION ALL SELECT      3, 'T011820001524538', 'Topic', 'Drivers...

posted @ Tuesday, January 20, 2009 9:50 PM | Feedback (4) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

Easy script for calculating weekday or weekend for a date

SELECT d.theDate,         DATEDIFF(DAY, '17530101', d.theDate) % 7 / 5 AS IsWeekend,         1 - DATEDIFF(DAY, '17530101', d.theDate) % 7 / 5 AS IsWeekday FROM    (                 SELECT CAST('20081124' AS SMALLDATETIME) AS theDate UNION ALL                 SELECT '20081125' UNION ALL                 SELECT '20081126' UNION ALL                 SELECT '20081127' UNION ALL                 SELECT '20081128' UNION ALL                 SELECT '20081129' UNION ALL                 SELECT '20081130'         ) AS d

posted @ Wednesday, January 14, 2009 2:03 PM | Feedback (5) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

One way to resolve and calculate fractional strings

CREATE FUNCTION dbo.fnResolveFractionals (             @data VARCHAR(20) ) RETURNS FLOAT AS BEGIN             RETURN      CASE                                      WHEN LEN(@data) - LEN(REPLACE(@data, ' ', '')) = 1 AND LEN(@data) - LEN(REPLACE(@data, '/', '')) = 1 THEN CAST(LEFT(@data, CHARINDEX(' ', @data) - 1) AS FLOAT) + 1.0E * SUBSTRING(@data, CHARINDEX(' ', @data) + 1, CHARINDEX('/', @data) - CHARINDEX(' ', @data) - 1) / NULLIF(RIGHT(@data, LEN(@data) - CHARINDEX('/', @data)), 0)                                      WHEN LEN(@data) - LEN(REPLACE(@data, ' ', '')) = 0 AND LEN(@data) - LEN(REPLACE(@data, '/', '')) = 0 THEN CAST(@data AS FLOAT)                                      ELSE NULL                          END END GO   DECLARE     @Sample TABLE             (                          data VARCHAR(20)             )   INSERT      @Sample SELECT      '5 3/16' UNION ALL SELECT      '7' UNION ALL SELECT      '2 /' UNION ALL SELECT     ...

posted @ Monday, December 15, 2008 3:01 PM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Find popular combos

DECLARE     @Sample TABLE             (                  StudentID INT,                  Class VARCHAR(20)             )   INSERT      @Sample SELECT      1, 'Maths' UNION ALL SELECT      1, 'English' UNION ALL SELECT      1, 'Science' UNION ALL SELECT      2, 'Maths' UNION ALL SELECT      2, 'English' UNION ALL SELECT      2, 'Science' UNION ALL SELECT      2, 'History' UNION ALL SELECT      3, 'English' UNION ALL SELECT      3, 'Maths' UNION ALL SELECT      3, 'Science' UNION ALL SELECT      3, 'RE'  UNION ALL SELECT      4, 'Science' UNION ALL SELECT      4, 'Maths' UNION ALL SELECT      4, 'English' UNION ALL SELECT      4, 'History' UNION ALL SELECT      4, 'French'   ;WITH Yak(ClassName, ClassPath, Combinations) AS (             SELECT      Class,                         CAST(Class AS VARCHAR(MAX)),                         CAST(1 AS INT)             FROM        @Sample             GROUP BY    Class               UNION ALL               SELECT     s.Class,                        y.ClassPath + '-' + s.Class,                        y.Combinations + 1             FROM       Yak AS y             INNER JOIN @Sample AS s ON s.Class > y.ClassName            ...

posted @ Tuesday, December 09, 2008 4:16 PM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

Strange happening

I had a strange scenario today and I can't reproduce it. I changed current database to adventureworks and ran following code DECLARE     @SQL NVARCHAR(200) SET         @SQL = 'SELECT  DB_NAME()' EXEC        sp_executesql @SQL EXEC        (@SQL) The sp_executesql statement returned "master" and exec statement returned "adventureworks". Anyone knows why?

posted @ Thursday, December 04, 2008 4:58 PM | Feedback (6) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]

How many logical and physical processors do you have?

SELECT cpu_count AS [Logical CPUs],         cpu_count / hyperthread_ratio AS [Physical CPUs] FROM   sys.dm_os_sys_info

posted @ Tuesday, December 02, 2008 5:08 PM | Feedback (0) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 ]

An alternative to IDENTITY column

Lately I have seen a number of questions regarding incremental update of some sort of counter placed in a central table. The original posters will for some reason not use an IDENTITY column which has a minimum overhead and use of system resources. And today I saw this type of question again (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=114970) and I started to think if there actually were an alternative to IDENTITY column for their cases. I found a solution. I can't tell if it's elegant or not, but it work 100%. If you are using SQL Server 2005 or later, you can stop reading here because Michael Valentine Jones has...

posted @ Friday, November 28, 2008 8:48 AM | Feedback (9) | Filed Under [ Optimization SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

Expand network using CTE without circular reference

Today I come across an interesting approach to a networking algorithm. The question was about how to use recursive to expand a network and still avoid circular reference. See topic here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=115290 You can’t do that in a recursive CTE because you can only reference the CTE once in the recursive part. Then I thought about a “recursive csv string”. And I gave it a try.   Here is the result.   DECLARE @Stations TABLE       (             stationID INT,             name VARCHAR(255)       )   INSERT      @Stations SELECT      1, 'Glasgow' UNION ALL SELECT      2, 'Edinburgh' UNION ALL SELECT      3, 'York' UNION ALL SELECT      4, 'London' UNION ALL SELECT      5, 'Aberdeen' UNION ALL SELECT      6, 'Bjuv'   DECLARE @Links TABLE      ...

posted @ Thursday, November 27, 2008 4:00 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

Run jobs synchronously

If you use sp_start_job you have noticed that the code will continue ro run and the job you started is run asynchrously. What if you want to wait for the job to finished? Try this CREATE PROCEDURE dbo.usp_Start_And_Wait_For_Job (        @jobName SYSNAME ) AS   SET NOCOUNT ON   DECLARE       @jobID UNIQUEIDENTIFIER,        @maxID INT,        @status INT,        @rc INT   IF @jobName IS NULL       BEGIN             RAISERROR('Parameter @jobName have no value.', 16, 1)             RETURN -100       END   SELECT @jobID = job_id FROM   msdb..sysjobs WHERE name = @jobName   IF @@ERROR <> 0       BEGIN             RAISERROR('Error when returning jobID for job %s.', 18, 1, @jobName)             RETURN -110       END   IF @jobID IS NULL       BEGIN             RAISERROR('Job %s does not exist.', 16, 1, @jobName)             RETURN -120       END   SELECT @maxID...

posted @ Thursday, November 27, 2008 2:48 PM | Feedback (5) | Filed Under [ SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

Half Hour Impacts

Yesterday I came across this question on another forum.   I am trying to come up with a way to identify the half hour impact from several exceptions across multiple days. I have access to SQL 2000 and SQL 2005. The fields that are used would be a start moment ( 9/3/2008 3:45:00 PM), stop moment (9/3/2008 4:30:00 PM), and total minutes (45). I have hundreds of exceptions with the above data and I need to identify the half hourly impact. When I have one exception with a start time of 7:15 and an end time of 8:20, I would like to see...

posted @ Thursday, November 27, 2008 10:50 AM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

Finding streaks in data

A good article about finding streaks in your data is this article by MVP Jeff Smith http://www.sqlteam.com/article/detecting-runs-or-streaks-in-your-data. Also see http://weblogs.sqlteam.com/peterl/archive/2008/05/13/Lightning-fast-collapsed-date-ranges-and-missing-date-ranges.aspx And this is an alternative way to find your streaks -- Prepare sample data SET NOCOUNT ON   DECLARE       @GameResults TABLE        (               gameID INT,               homeScore INT,               awayScore INT        )   INSERT @GameResults SELECT 1, 2, 1 UNION ALL SELECT 2, 4, 1 UNION ALL SELECT 3, 4, 3   DECLARE       @Program TABLE        (               gameID INT,               gameDate DATETIME,               homeID INT,               awayID INT        )   INSERT @Program SELECT 1, '2008-05-12', 101, 102 UNION ALL SELECT 2, '2008-05-20', 106, 101 UNION ALL SELECT 3, '2008-05-14', 107, 101   -- Prepare staging data DECLARE       @Stage TABLE        (               teamID INT,               gameDate DATETIME,               outcome CHAR(3),               streak INT,              ...

posted @ Wednesday, November 26, 2008 1:25 PM | Feedback (4) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

Easy sorted numeric pivoting with maximum three columns

-- Prepare sample data DECLARE       @Sample TABLE        (               ID INT,               col INT        )   INSERT @Sample SELECT 0, 1 UNION ALL SELECT 0, 1 UNION ALL SELECT 0, 2 UNION ALL SELECT 1, 1 UNION ALL SELECT 1, 2 UNION ALL SELECT 1, 3 UNION ALL SELECT 2, 5 UNION ALL SELECT 2, 5 UNION ALL SELECT 2, 5 UNION ALL SELECT 3, 6 UNION ALL SELECT 3, 6 UNION ALL SELECT 5, 8 UNION ALL SELECT 5, 9 UNION ALL SELECT 4, 7   -- Pivot the source data SELECT        ID,               MIN(col) AS col1,               CASE COUNT(*)                      WHEN 1 THEN NULL                      WHEN 2 THEN MAX(col)                      ELSE SUM(col) - MIN(col) - MAX(col)               END AS col2,               CASE COUNT(*)                      WHEN 3 THEN MAX(col)                     ...

posted @ Tuesday, November 25, 2008 3:58 PM | Feedback (1) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Datetime manipulation - Time only by Itzik Ben-Gan

See his blog entry here http://www.sqlmag.com/Article/ArticleID/100884/sql_server_100884.html  And this older http://www.sqlmag.com/Article/ArticleID/95734/sql_server_95734.html    

posted @ Monday, November 24, 2008 5:17 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

SQL Server 2008 with MERGE and triggers

I come across a question today if SQL Server 2008 MERGE command call table triggers one per hit, or one per statement as it normally does. The short conclusion is that the MERGE command splits the source data into three “streams” and internally executes INSERT, UPDATE and DELETE statements. According to Books Online, there is no sure way to guarantee the order of "streams" to execute, but it seems SQL Server favors INSERT / UPDATE / DELETE order.   CREATE TABLE tTemp               (                      i INT,                      j INT               )   INSERT tTemp        (               i,               j        ) SELECT 1, 0 UNION ALL SELECT 2, 0 UNION ALL SELECT 3, 0 UNION ALL SELECT...

posted @ Monday, November 24, 2008 2:38 PM | Feedback (3) | Filed Under [ SQL Server 2008 Administration ]

Bin packaging

With respect to my old algorithm found here http://weblogs.sqlteam.com/peterl/archive/2008/08/12/How-to-sum-up-an-unknown-number-of-records.aspx which is about how to sum up an unknown number of items, I have come up with a new algorithm.   This new algorithm is about finding all possible sums and how many combinations you have of each sum. Have fun!   DECLARE       @Data TABLE        (               faceValue MONEY,               maxItems INT,               permCount INT        ) INSERT        @Data               (                      faceValue,                      maxItems               ) SELECT        faceValue,               1 + COUNT(*) FROM          (                      SELECT 899 AS faceValue UNION ALL                      SELECT 100 UNION ALL                      SELECT 95 UNION ALL                      SELECT 50 UNION ALL                      SELECT 55 UNION ALL                      SELECT 40 UNION ALL                      SELECT 5 UNION ALL                      SELECT 100 UNION...

posted @ Sunday, November 23, 2008 3:32 PM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

How to get the productsum from a table

IF EXISTS(SELECT * FROM YourTable WHERE Number = 0)     SELECT 0.0E ELSE     SELECT CASE IsNegativeProduct                WHEN 1 THEN -EXP(theSum)                ELSE EXP(theSum)            END     FROM   (                SELECT SUM(LOG(ABS(Number))) AS theSum,                       SUM(CASE WHEN Number < 0 THEN 1 ELSE 0 END) % 2 AS IsNegativeProduct                FROM   YourTable            ) AS d

posted @ Wednesday, November 19, 2008 2:13 PM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Reading the transaction log

SELECT        * FROM          ::fn_dblog(DEFAULT, DEFAULT) AS l INNER JOIN    sysobjects AS so ON so.name = l.[transaction name] SELECT        so.name AS ObjectName,               so.type AS ObjectType,               MAX(CAST(l.[Begin Time] AS DATETIME)) AS LogTime FROM          ::fn_dblog(DEFAULT, DEFAULT) l inner join    sysobjects so on so.name = l.[transaction name] --where              so.type = 'u' GROUP BY      so.name,               so.type ORDER BY      so.name,               so.type

posted @ Thursday, November 13, 2008 9:49 AM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Stripping out all non-numeric characters from a string

DECLARE @Value NVARCHAR(200) SET     @Value = 'a+(6aaaa02.......()/)(/)=(#¤NIOU#H¤CÖIÖERFÖNKER)123456789/fffffffffffff' WHILE @Value LIKE '%[^0-9]%'         SET     @Value = REPLACE(@Value, SUBSTRING(@Value, PATINDEX('%[^0-9]%', @Value), 1), '') SELECT  @Value

posted @ Wednesday, November 12, 2008 8:48 AM | Feedback (9) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Getting date or time only from a Datetime value

SELECT GETDATE() AS theFullDateTime,        DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AS theDateOnly,        DATEADD(DAY, DATEDIFF(DAY, GETDATE(), 0), GETDATE()) AS theTimeOnly SELECT GETDATE() AS theFullDateTime,        DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101') AS theDateOnly,        DATEADD(DAY, DATEDIFF(DAY, GETDATE(), '19000101'), GETDATE()) AS theTimeOnly

posted @ Wednesday, November 12, 2008 8:38 AM | Feedback (3) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Create nested hierachy XML

-- Prepare sample data DECLARE @Master TABLE         (                 ID INT,                 Name VARCHAR(20)         ) INSERT  @Master SELECT  1, 'Peso' UNION ALL SELECT  2, 'SQLTeam' UNION ALL SELECT  3, 'SQL' DECLARE @Child TABLE         (                 MasterID INT,                 ID INT,                 Value VARCHAR(20)         ) INSERT  @Child SELECT  1, 1, 'Row 1 for Peso' UNION ALL SELECT  1, 2, 'Row 2 for Peso' UNION ALL SELECT  2, 3, 'Row 1 for SQLTeam' -- Display the XML SELECT          ePurchaseOrder.ID AS MasterID,                 ePurchaseOrder.Name AS MasterName,                 ePurchaseOrderLine.ID AS ChildID,                 ePurchaseOrderLine.Value AS ChildValue FROM            @Master AS ePurchaseOrder LEFT JOIN       @Child AS ePurchaseOrderLine ON ePurchaseOrderLine.MasterID = ePurchaseOrder.ID FOR XML         AUTO,                 ROOT('POSystem'),                 ELEMENTS SELECT      m.ID AS MasterID,             m.Name...

posted @ Thursday, November 06, 2008 4:57 PM | Feedback (0) | Filed Under [ SQL Server 2008 SQL Server 2005 ]

Using namespaces in XML queries

If your xml data contains a namespace, you also need to query the data using the same xml namespace. DECLARE @Sample TABLE         (                 rowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,                 [xml] XML         ) INSERT  @Sample SELECT  '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value1</value></stringList>' UNION ALL SELECT  NULL UNION ALL SELECT  '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value5</value><value>value2</value></stringList>' UNION ALL SELECT  '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value4</value><value>value1</value><value>value5</value><value>value2</value></stringList>' SELECT  * FROM    @Sample ;WITH XMLNAMESPACES('http://schemas.myCompany.com/myProject/[app]/stringList/1.0' AS theYak) SELECT DISTINCT t.c.value('.', 'VARCHAR(20)') FROM            @Sample AS s CROSS APPLY     s.xml.nodes('/theYak:stringList/theYak:value') AS t(c)

posted @ Thursday, November 06, 2008 10:36 AM | Feedback (0) | Filed Under [ SQL Server 2008 SQL Server 2005 ]

Bayesian Estimate and Root Mean Square

Sometimes you face the situation where you have some items ranked, and someone always tries to bump the ranking by voting a "perfect 100" to a particular item. And when you rank the items with average function or root mean square, that single "perfect 100" vote still bumps the item at top of ranking. Well, have you looked a Bayesian Estimate? DECLARE       @Sample TABLE        (               userID INT,               vote INT        ) INSERT @Sample SELECT 3, 40 UNION ALL SELECT 3, 60 UNION ALL SELECT 0, 100 UNION ALL SELECT 1, 100 UNION ALL SELECT 1, 100 UNION ALL SELECT 1, 90 UNION ALL SELECT 1, 100 UNION ALL SELECT 1, 90 UNION ALL SELECT 1, 100 UNION ALL SELECT...

posted @ Monday, October 27, 2008 11:14 AM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Sequencies in string

DECLARE @s VARCHAR(100)   SET    @s = 'aardddvaaaarrkkkk'   -- Highest frequency of same character SELECT TOP 1 WITH TIES               [char],               COUNT(*) AS cnt FROM          (                      SELECT SUBSTRING(@s, 1 + Number, 1) [char]                      FROM   master..spt_values                      WHERE Number < DATALENGTH(@s)                            AND type = 'P'               ) AS q GROUP BY      [char] ORDER BY      COUNT(*) DESC   -- Longest sequence of same character SELECT TOP 1 WITH TIES               [char],               CASE [seq]                      WHEN 0 THEN DATALENGTH(@s) - Number                      ELSE [seq]               END AS [seq] FROM          (                      SELECT SUBSTRING(@s, 1 + Number, 1) [char],                            Number,                            PATINDEX('%[^' + SUBSTRING(@s, 1 + Number, 1) + ']%', SUBSTRING(@s, 2 + Number, 8000)) AS [seq]                      FROM   master..spt_values                      WHERE Number < DATALENGTH(@s)                            AND type = 'P'               )...

posted @ Monday, October 27, 2008 8:32 AM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Ordinal your numbers

SELECT        number,               CASE                      WHEN number % 100 IN (11, 12, 13) THEN 'th'                      WHEN number % 10 = 1 THEN 'st'                      WHEN number % 10 = 2 THEN 'nd'                      WHEN number % 10 = 3 THEN 'rd'                      ELSE 'th'               END AS Ordinal FROM          master..spt_values WHERE         type = 'p' ORDER BY      number

posted @ Monday, October 27, 2008 8:29 AM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Delete all subset records

Today I helped a guy out with a special request. His goal was to remove all records that are subsets or parts of another record, a superset record. See this sample data DECLARE       @Sample TABLE        (               recID INT IDENTITY(1, 1),               col1 VARCHAR(1),               col2 VARCHAR(2),               col3 VARCHAR(3),               userID INT        ) INSERT @Sample SELECT 'A', 'B', 'C', 1 UNION ALL SELECT 'A', 'B', ' ', 1 UNION ALL SELECT 'A', ' ', 'C', 1 UNION ALL SELECT 'F', ' ', 'C', 1 UNION ALL SELECT ' ', 'M', ' ', 2 UNION ALL SELECT 'T', 'M', 'O', 2 UNION ALL SELECT ' ', 'M', 'O', 2 UNION ALL SELECT 'X', 'M', 'O', 2...

posted @ Wednesday, October 15, 2008 5:26 PM | Feedback (3) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Get the job name for current context

Today I had to write some code to dynamically get the job name currently running. DECLARE @SQL NVARCHAR(72),         @jobID UNIQUEIDENTIFIER,         @jobName SYSNAME SET     @SQL = 'SET @guid = CAST(' + SUBSTRING(APP_NAME(), 30, 34) + ' AS UNIQUEIDENTIFIER)' EXEC    sp_executesql @SQL, N'@guid UNIQUEIDENTIFIER OUT', @guid = @jobID OUT SELECT  @jobName = name FROM    msdb..sysjobs WHERE   job_id = @jobID

posted @ Monday, October 13, 2008 10:32 AM | Feedback (3) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]

Keep track of all your jobs schedules

This is a piece of code I use to create a resultset from and display in Outlook calendar. CREATE PROCEDURE dbo.uspGetScheduleTimes (        @startDate DATETIME,        @endDate DATETIME ) AS /*     This code is blogged here     http://weblogs.sqlteam.com/peterl/archive/2008/10/10/Keep-track-of-all-your-jobs-schedules.aspx */ SET NOCOUNT ON   -- Create a tally table. If you already have one of your own please use that instead. CREATE TABLE #tallyNumbers               (                      num SMALLINT PRIMARY KEY CLUSTERED               )   DECLARE       @index SMALLINT   SET    @index = 1   WHILE @index <= 8640        BEGIN               INSERT #tallyNumbers                      (                            num                      )               VALUES (                            @index                      )                 SET    @index = @index + 1        END   -- Create a staging table for jobschedules CREATE TABLE #jobSchedules               (                      rowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,                      serverName SYSNAME NOT NULL,                     ...

posted @ Friday, October 10, 2008 5:07 PM | Feedback (6) | Filed Under [ SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

Excerpt from The Compression Session

This tuesday I had the opportunity to meet Kalen Delaney and hear her talk about the new compression algorithms in SQL Server 2008. For those of you that never have met Kalen, I can tell she is a sweet lady, knowledgable and interesting to listen to. With SQL Server 2005 SP2, the new VARDECIMAL datatype arrived, with a few stored procedures to calculate eventual savings. This turned out to be a subset for the ROW and PAGE compressions available in SQL Server 2008, Enterprise Edition. You can have ROW compression only if you want, but if you choose PAGE compression you...

posted @ Thursday, October 09, 2008 8:39 AM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms Administration ]

SUM and JOIN together

DECLARE    @a TABLE (pk INT) DECLARE    @b TABLE (fk INT, i INT) DECLARE    @c TABLE (fk INT, j INT) INSERT    @a SELECT    1 UNION ALL SELECT    2 UNION ALL SELECT    3 INSERT    @b SELECT    1, 1 UNION ALL SELECT    1, 3 UNION ALL SELECT    2, 4 UNION ALL SELECT    2, 8 UNION ALL SELECT    2, 10 UNION ALL SELECT    3, 1 INSERT    @c SELECT    1, 11 UNION ALL SELECT    1, 13 UNION ALL SELECT    2, 14 UNION ALL SELECT    2, 18 UNION ALL SELECT    2, 60 UNION ALL SELECT    3, 11 -- Wrong way SELECT        a.pk,         SUM(b.i) AS SumAct,...

posted @ Wednesday, October 01, 2008 3:48 PM | Feedback (3) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Create a complex password

CREATE PROCEDURE dbo.uspCreatePassword (     @UpperCaseItems SMALLINT,     @LowerCaseItems SMALLINT,     @NumberItems SMALLINT,     @SpecialItems SMALLINT ) AS SET NOCOUNT ON -- Initialize some variables DECLARE    @UpperCase VARCHAR(26),     @LowerCase VARCHAR(26),     @Numbers VARCHAR(10),     @Special...

posted @ Monday, September 29, 2008 2:35 PM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Updated XML search (test case with variables)

This blog post is continued here http://weblogs.sqlteam.com/peterl/archive/2009/07/03/Manipulate-XML-data-with-non-xml-columns-and-not-using-variable.aspx  /*******************************************************************************   Prepare script with testcase *******************************************************************************/ DECLARE     @XMLString XML,             @ElementEqual VARCHAR(50),             @ElementLike VARCHAR(50),             @AttributeEqual VARCHAR(50),             @AttributeLike VARCHAR(50) SELECT      @XMLString = '                   <Customers>                         <Customer>                               <FirstName>Kevin</FirstName>                               <LastName>Goff</LastName>                               <City type="aca">Camp Hill</City>                         </Customer>                         <Customer>                               <FirstName>Steve</FirstName>                               <LastName>Goff</LastName>                               <City type="acb"> Philadelphia</City>                         </Customer>                   </Customers>',       @ElementEqual = 'Camp Hill',       @ElementLike = 'adel',       @AttributeEqual = 'acb',       @AttributeLike = 'c' /*******************************************************************************   Search for City-nodes *******************************************************************************/ -- Get all City where element is equal to "Camp Hill" SELECT      cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName,             ...

posted @ Monday, September 01, 2008 2:29 PM | Feedback (3) | Filed Under [ SQL Server 2008 SQL Server 2005 ]

Simple FAQ search algorithm

-- Prepare sample data DECLARE       @Keywords TABLE        (               FaqID INT,               Keyword VARCHAR(200)        ) INSERT @Keywords SELECT 1, 'help' UNION ALL SELECT 1, 'resolve' UNION ALL SELECT 1, 'issue' UNION ALL ...

posted @ Wednesday, August 13, 2008 4:27 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Extract UK postcode

CREATE FUNCTION dbo.fnExtractPostCodeUK (        @Data VARCHAR(8000) ) RETURNS VARCHAR(8) AS BEGIN         RETURN        COALESCE(                            -- AANN NAA                            SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @Data + ' '), 0), 8),                            -- AANA NAA                            SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][ABEHMNPRVWXY] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @Data + ' '), 0), 8),                            -- ANN NAA                            SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @Data + ' '), 0), 7),                            -- AAN NAA                            SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @Data + ' '), 0), 7),                            -- ANA NAA                            SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][0123456789][ABCDEFGHJKSTUW] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @Data + ' '), 0), 7),                            --   AN NAA                            SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' '...

posted @ Wednesday, August 13, 2008 2:32 PM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Validate UK postcode

CREATE FUNCTION dbo.fnValidatePostCodeUK (        @PostCode VARCHAR(8) ) RETURNS BIT AS BEGIN        RETURN CASE                      --   Special case GIR 0AA                      WHEN @PostCode LIKE 'GIR 0AA' THEN 1                      -- Current postcode prefixes                      WHEN   LEFT(@Postcode, 2) NOT IN ('AB', 'AL', 'BA', 'BB', 'BD', 'BH', 'BL', 'BN', 'BR', 'BS', 'BT', 'CA', 'CB', 'CF', 'CH', 'CM', 'CO', 'CR', 'CT', 'CV', 'CW', 'DA', 'DD', 'DE', 'DG', 'DH', 'DL', 'DN', 'DT', 'DY', 'EC', 'EH', 'EN', 'EX', 'FK', 'FY', 'GL', 'GU', 'GY', 'HA', 'HD', 'HG', 'HP', 'HR', 'HS', 'HU', 'HX', 'IG', 'IM', 'IP', 'IV', 'JE', 'KA', 'KT', 'KW', 'KY', 'L', 'LA', 'LD', 'LE', 'LL', 'LN', 'LS', 'LU', 'ME', 'MK', 'ML', 'NE', 'NG', 'NN', 'NP',...

posted @ Wednesday, August 13, 2008 1:46 PM | Feedback (7) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

How to sum up an unknown number of records

-- Initialize the search parameter DECLARE       @WantedValue INT   SET    @WantedValue = 221   -- Stage the source data DECLARE       @Data TABLE        (               RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,               MaxItems INT,               CurrentItems INT DEFAULT 0,               FaceValue INT,               BestUnder INT DEFAULT 0,               BestOver INT DEFAULT 1        )   -- Aggregate the source data INSERT        @Data               (                      MaxItems,                      FaceValue               ) SELECT        COUNT(*),               Qty FROM          (                      SELECT 899 AS Qty UNION ALL                      SELECT 100 UNION ALL                      SELECT 95 UNION ALL                      SELECT 50 UNION ALL                      SELECT 55 UNION ALL                      SELECT 40 UNION ALL                      SELECT 5 UNION ALL                      SELECT 100 UNION ALL                      SELECT 100 UNION ALL                      SELECT 100 UNION ALL                      SELECT 100 UNION ALL                     ...

posted @ Tuesday, August 12, 2008 5:06 PM | Feedback (4) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Another sequencing algorithm

This problem originated here http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=87&threadid=90916&enterthread=y and I post the solution here for two reasons. 1) The forum above does not support code tags 2) The common interest is high enough -- Prepare sample data DECLARE @Sample TABLE         (              HoleID CHAR(8),              mFrom SMALLMONEY,              mTo SMALLMONEY,              Result SMALLMONEY,              PRIMARY KEY CLUSTERED              (                  HoleID,                  mFrom              ),              Seq INT          ) INSERT  @Sample          (              HoleID,              mFrom,              mTo,              Result          ) SELECT  'TWDD0004',   1   ,   2   ,  0.86 UNION ALL SELECT  'TWDD0004',   3   ,   4   ,  8.93 UNION ALL SELECT  'TWDD0004',   4   ,   5   ,  2.78 UNION ALL SELECT  'TWDD0004',   8   ,  ...

posted @ Tuesday, August 12, 2008 2:24 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

How to tell if a number is a "POWER of 2"-number

SELECT  Number,         1 - SIGN(Number & (Number - 1)) FROM    master..spt_values WHERE   Type = 'P'         AND Number > 0   Other way is   CREATE FUNCTION dbo.isPowerOf2 (       @i INT ) RETURNS BIT AS BEGIN       DECLARE @x FLOAT          SET @x = LOG(Number) / LOG(2)       RETURN      CASE                   WHEN FLOOR(@x) = CEILING(@x) THEN 1                   ELSE 0             END END

posted @ Saturday, August 09, 2008 1:22 PM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Why LEN differs from DATALENGTH when using BINARY data

Here are all the 36 numbers between 0 and 9000 that have a different LEN than DATALENGTH. Number Binary digits ...

posted @ Tuesday, July 22, 2008 3:43 PM | Feedback (2) | Filed Under [ SQL Server 2008 SQL Server 2005 SQL Server 2000 ]

How to get IP address

DECLARE       @Interfaces TABLE        (               RowID INT IDENTITY(0, 1),               Interface CHAR(38),               IP VARCHAR(15)        )   INSERT @Interfaces        (               Interface        ) EXEC   master..xp_regenumkeys     N'HKEY_LOCAL_MACHINE',                            N'System\CurrentControlSet\Services\TcpIP\Parameters\Interfaces'   DECLARE       @RowID INT,        @IP VARCHAR(15),        @Key NVARCHAR(200)   SELECT @RowID = MAX(RowID) FROM   @Interfaces   WHILE @RowID >= 0        BEGIN               SELECT @Key = N'System\CurrentControlSet\Services\TcpIP\Parameters\Interfaces\' + Interface               FROM   @Interfaces               WHERE RowID = @RowID                 EXEC   master..xp_regread   N'HKEY_LOCAL_MACHINE',                                          @Key,                                          N'DhcpIPAddress',                                          @IP OUTPUT                 IF @IP <> '0.0.0.0'                      UPDATE @Interfaces                      SET    IP = @IP                      WHERE RowID = @RowID                 SET    @RowID = @RowID - 1        END                                   SELECT IP FROM   @Interfaces WHERE IP IS NOT NULL

posted @ Wednesday, July 16, 2008 1:18 PM | Feedback (6) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 ]

PIVOT conundrum

I started out with typing SELECT @@VERSION and got the result as Microsoft SQL Server 2005 - 9.00.3215.00 (Intel X86)         Dec 8 2007 18:51:32         Copyright (c) 1988-2005 Microsoft Corporation        Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) So far so good. Now I created some sample data like this -- Prepare sample data DECLARE     @Sample TABLE (RecNo TINYINT, ID TINYINT, EventDate SMALLDATETIME, OrderStatus VARCHAR(9)) INSERT      @Sample SELECT      1, 10, '7/5/2008', 'Opened' UNION ALL SELECT      2, 11, '7/5/2008', 'Closed' UNION ALL SELECT      3, 12, '7/5/2008', 'Closed' UNION ALL SELECT      4, 13, '7/6/2008', 'Opened' UNION ALL SELECT      4, 14, '7/6/2008', 'Opened' UNION ALL SELECT      4, 15, '7/6/2008', 'Closed' UNION ALL SELECT      1, 16, '7/7/2008',...

posted @ Tuesday, July 15, 2008 4:00 PM | Feedback (2) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

Firewall strategy

When Windows Server 2008 is more common, you will be surprised how the Firewall is blocking all versions of SQL Server when upgrading the OS. Here are some links to remedy the problems http://msdn.microsoft.com/en-us/library/cc646023(SQL.100).aspx http://technet.microsoft.com/en-us/network/bb545423.aspx

posted @ Monday, July 14, 2008 3:48 PM | Feedback (0) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]

How to get authentication mode in SQL Server

CREATE FUNCTION dbo.fnGetSQLServerAuthenticationMode ( ) RETURNS INT AS   BEGIN       DECLARE @InstanceName NVARCHAR(1000),             @Key NVARCHAR(4000),             @LoginMode INT         EXEC master..xp_regread N'HKEY_LOCAL_MACHINE',                         N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\',                         N'MSSQLSERVER',                         @InstanceName OUTPUT         IF @@ERROR <> 0 OR @InstanceName IS NULL             RETURN NULL         SET   @Key = N'Software\Microsoft\Microsoft SQL Server\' + @InstanceName + N'\MSSQLServer\'         EXEC master..xp_regread N'HKEY_LOCAL_MACHINE',                         @Key,                         N'LoginMode',                         @LoginMode OUTPUT         RETURN @LoginMode END

posted @ Thursday, June 19, 2008 5:10 PM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

How to change authentication mode in SQL Server

CREATE PROCEDURE dbo.uspSetSQLServerAuthenticationMode (        @MixedMode BIT ) AS   SET NOCOUNT ON   DECLARE @InstanceName NVARCHAR(1000),        @Key NVARCHAR(4000),        @NewLoginMode INT,        @OldLoginMode INT   EXEC master..xp_regread    N'HKEY_LOCAL_MACHINE',                      N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\',                      N'MSSQLSERVER',                      @InstanceName OUTPUT   IF @@ERROR <> 0 OR @InstanceName IS NULL        BEGIN               RAISERROR('Could not read SQL Server instance name.', 18, 1)               RETURN -100        END   SET    @Key = N'Software\Microsoft\Microsoft SQL Server\' + @InstanceName + N'\MSSQLServer\'   EXEC master..xp_regread    N'HKEY_LOCAL_MACHINE',                      @Key,                      N'LoginMode',                      @OldLoginMode OUTPUT   IF @@ERROR <> 0        BEGIN               RAISERROR('Could not read login mode for SQL Server instance %s.', 18, 1, @InstanceName)               RETURN -110        END   IF @MixedMode IS NULL        BEGIN               RAISERROR('No change to authentication mode was made. Login mode is %d.', 10, 1, @OldLoginMode)               RETURN -120        END   IF...

posted @ Thursday, June 19, 2008 5:04 PM | Feedback (8) | Filed Under [ SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

Some SQL Server network properties

DECLARE       @Stage TABLE               (                      RowID INT IDENTITY(0, 1) PRIMARY KEY CLUSTERED,                      Data VARCHAR(90),                      Section INT               )   INSERT @Stage               (                      Data               ) EXEC   xp_cmdshell 'ipconfig /all'   DECLARE       @Section INT   SET    @Section = 0   UPDATE @Stage SET    @Section = Section = CASE WHEN ASCII(LEFT(Data, 1)) > 32 THEN @Section + 1 ELSE @Section END   SELECT        MAX(CASE WHEN x.minRowID IS NULL THEN NULL ELSE s.Data END) AS Header,               MAX(CASE WHEN s.Data LIKE '%Host Name%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END) AS HostName,               MAX(CASE WHEN s.Data LIKE '%Media State%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END) AS MediaState,               MAX(CASE WHEN s.Data LIKE '%Description%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END)...

posted @ Thursday, June 19, 2008 3:57 PM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

Horizontal partitioning, Enterprise style

CREATE PARTITION FUNCTION   pfOrderDate ( DATETIME ) AS RANGE RIGHT FOR VALUES  ( '20000101', '20010101', '20020101' ) GO   CREATE PARTITION SCHEME    psYak AS PARTITION               pfOrderDate ALL TO                     ([PRIMARY]) GO   CREATE TABLE Orders               (                      OrderID INT NOT NULL,                      CustomerID VARCHAR(15) NOT NULL,                      OrderDate DATETIME NOT NULL               ) ON            psYak(OrderDate) GO   CREATE CLUSTERED INDEX      IX_OrderID ON                          Orders ( OrderID ) CREATE NONCLUSTERED INDEX  IX_OrderDate ON                         Orders ( OrderDate ) INCLUDE                    ( OrderID, CustomerID ) GO   INSERT Orders        (               OrderID,               CustomerID,               OrderDate        ) SELECT 1, 'Peso', '20011225' UNION ALL SELECT 2, 'Jennie', '20020314'   SELECT OrderID,        OrderDate FROM   Orders WHERE  OrderDate = '20011225'   SELECT OrderID,        OrderDate FROM   Orders WHERE  OrderID = 1   DROP TABLE                 Orders DROP PARTITION SCHEME      psYak DROP PARTITION FUNCTION    pfOrderDate  

posted @ Thursday, June 12, 2008 4:18 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Administration SQL Server 2005 ]

Lightning fast collapsed date ranges and missing date ranges

The last two days I have been involved in a rather interesting discussion. The original poster wanted a fast way to get missing date ranges in a series of date pairs. Naturally I posted the link to the Script Library topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88422 Traditional T-SQL proved to be very inefficient. Even when using the CTE approch which proved to be second fastest and still 100 to 1,600 times slower! I started out with creating 1,000 date pairs with following code -- Prepare sample data CREATE TABLE #ProcessCellAllocation               (                      AllocationID INT IDENTITY(1, 1) NOT NULL,                      ProcessCell VARCHAR(50) NOT NULL,                      DateFrom DATETIME NOT NULL,                      DateTo DATETIME,                      Seq INT               ) INSERT        #ProcessCellAllocation                 (                     ...

posted @ Tuesday, May 13, 2008 4:16 PM | Feedback (7) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Some XML search approaches

I just played around with some different techniques to fetch relevant data from XML content.   DECLARE       @XMLString XML,        @Search VARCHAR(50)   SELECT @XMLString = '                            <Customers>                                   <Customer>                                          <FirstName>Kevin</FirstName>                                          <LastName>Goff</LastName>                                          <City type="aca">Camp Hill</City>                                   </Customer>                                   <Customer>                                          <FirstName>Steve</FirstName>                                          <LastName>Goff</LastName>                                          <City type="acb">Philadelphia</City>                                   </Customer>                            </Customers>',               @Search = 'Camp Hill'   -- Get all customers living in Camp Hill SELECT cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName,        cust.coldef.value('LastName[1]','VARCHAR(20)') AS LastName,        cust.coldef.value('City[1]','VARCHAR(20)') AS City,        cust.coldef.value('City[1]/@type','VARCHAR(20)') AS Typ FROM   @XMLString.nodes('/Customers/Customer') AS cust(coldef) WHERE cust.coldef.exist('City/text()[.= sql:variable("@Search")]') = 1   -- Get all customers living in a City containing the text "adel" SELECT cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName,        cust.coldef.value('LastName[1]','VARCHAR(20)') AS LastName,        cust.coldef.value('City[1]','VARCHAR(20)') AS City,        cust.coldef.value('City[1]/@type','VARCHAR(20)') AS Typ FROM   @XMLString.nodes('/Customers/Customer') AS cust(coldef) WHERE cust.coldef.exist('City [contains(.,"adel")]') = 1   -- Get...

posted @ Wednesday, March 26, 2008 11:17 AM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

Search all code for specific keyword

This is an updated version for SQL 2005 and later to search all code for a specific keyword SELECT p.RoutineName, 'EXEC sp_helptext ' + QUOTENAME(p.RoutineName) AS [Exec] FROM ( SELECT OBJECT_NAME(so.ID) AS RoutineName, (SELECT TOP 100 PERCENT '' + sc.TEXT FROM SYSCOMMENTS AS sc WHERE sc.ID = so.ID ORDER BY sc.COLID FOR XML PATH('')) AS Body FROM SYSOBJECTS AS so WHERE so.TYPE IN ('C', 'D', 'FN', 'IF', 'P', 'R', 'RF', 'TF', 'TR', 'V', 'X') ) AS p WHERE p.Body LIKE '%YourKeyWordHere%' The types are C = CHECK constraint D = Default or DEFAULT constraint FN = Scalar function IF = In-lined table-function...

posted @ Friday, March 14, 2008 2:05 PM | Feedback (4) | Filed Under [ SQL Server 2008 Algorithms Administration SQL Server 2005 ]

Change schema for all tables

I just helped a guy here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98346 with schema and thought that someone could benefit from this code exec     sp_MSforeachtable "PRINT '? modify'; ALTER SCHEMA new_schema TRANSFER ?; IF @@ERROR = 0 PRINT '? modified'; PRINT ''"

posted @ Wednesday, March 05, 2008 1:31 AM | Feedback (1) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 ]

Efficient pagination for large set of data?

This is what I pondered about today. Maybe I also will have some time to test it.   CREATE PROCEDURE dbo.uspPaginate ( @PageNumber INT, @RecordsPerPage TINYINT = 50 ) AS SET NOCOUNT ON DECLARE @MaxRows INT SET @MaxRows = @PageNumber * @RecordsPerPage SELECT SomeColumns FROM ( SELECT TOP (@RecordsPerPage) SomeColumns FROM ( SELECT TOP (@MaxRows) SomeColumns FROM YourTable ORDER BY SomeCase ASC/DESC ) ORDER BY SomeCase DESC/ASC ) ORDER BY SomeCase ASC/DESC Topic is here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97550  

posted @ Tuesday, February 19, 2008 4:12 PM | Feedback (3) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

Finding table reference levels and simulating cascading deletes

I worked with this topic recent weekend and posted the final functions here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97454   The general idea is to have a generic purge functionality.

posted @ Saturday, February 16, 2008 7:32 PM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

Curiosity found, the wrap

After a good nights sleep when almost all pieces fit together here weblogs.sqlteam.com/peterl/archive/2008/02/06/Curiosity-found.aspx I realized this morning that this behaviour also explains why there are gaps in identity sequences when inserting a record that violates a contraint. It seems that identity values are assigned to complete insert-set before checking for constraints such us unique indexes. DECLARE @Items TABLE (i INT IDENTITY(1, 1), j INT PRIMARY KEY) INSERT  @Items SELECT  1 UNION ALL SELECT  2 SELECT  * FROM    @Items INSERT  @Items SELECT  1 INSERT  @Items SELECT  2 INSERT  @Items SELECT  3 SELECT  * FROM    @Items

posted @ Thursday, February 07, 2008 9:15 AM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

Algorithm to sort strings mixed with Alpha and Numeric values.

CREATE FUNCTION dbo.fnSplitType (         @Data VARCHAR(200),         @PartSize TINYINT ) RETURNS VARCHAR(8000) AS BEGIN         DECLARE @Result VARCHAR(8000),                 @Alpha TINYINT,                 @OldPosition SMALLINT,                 @NewPosition SMALLINT         SELECT  @Result = '',                 @Alpha = 1,                 @OldPosition = 1,                 @NewPosition = 1         IF @Data LIKE '[0-9]%'                 SELECT  @Result = REPLICATE(' ', @PartSize),                         @Alpha = 0         WHILE @NewPosition < LEN(@Data)                 SELECT  @NewPosition =  CASE @Alpha                                                 WHEN 1 THEN PATINDEX('%[0-9]%', SUBSTRING(@Data, @OldPosition, 8000))                                                 ELSE PATINDEX('%[a-z]%', SUBSTRING(@Data, @OldPosition, 8000))                                         END,                         @NewPosition =  CASE @NewPosition                                                 WHEN 0 THEN LEN(@Data)                                                 ELSE @OldPosition + @NewPosition - 2                                         END,                         @Result = @Result +     CASE @Alpha                                                         WHEN 1 THEN LEFT(LTRIM(SUBSTRING(@Data, @OldPosition, @NewPosition - @OldPosition + 1)) + REPLICATE(' ', @PartSize), @PartSize)                                                         ELSE RIGHT(REPLICATE(' ', @PartSize) + RTRIM(SUBSTRING(@Data, @OldPosition, @NewPosition - @OldPosition + 1)), @PartSize)                                                 END,                         @Alpha = 1 - @Alpha,                         @OldPosition = @NewPosition + 1         RETURN  RTRIM(@Result) END Here is...

posted @ Wednesday, December 19, 2007 2:20 PM | Feedback (4) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Thanks Jon!

I didn't read this until I noticed Mladens link. http://weblogs.sqlteam.com/jhermiz/archive/2007/12/17/What-If-The-Dream-Company.aspx  

posted @ Monday, December 17, 2007 9:05 PM | Feedback (1) | Filed Under [ SQL Server 2008 SQL Server 2005 SQL Server 2000 ]

Cursor is really faster than set-based solution for weighted moving average?

Today, I was involved in an interesting discussion. Someone asked for a moving average solution. I joined the discussion late. The previous solutions and mine were all set-based and very slow. Then Jezemine come up with a CURSOR solution that looked fine and fast. After some trial-and-errors I finally posted a set-based solution that seems to be the fastest solution yet. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93911 So it still seems that [proper implemented] set-based solutions are the fastest, compared to CURSORs.  Below is also an implementation of a SQL Server 2005 approach.   DECLARE     @Sample TABLE (dt DATETIME, Rate FLOAT)   INSERT      @Sample SELECT      CURRENT_TIMESTAMP - 10, 1 UNION ALL SELECT      CURRENT_TIMESTAMP - 9, 2 UNION ALL SELECT      CURRENT_TIMESTAMP - 8, 4...

posted @ Monday, December 10, 2007 8:20 PM | Feedback (3) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

First test with November CTP

declare @sample table (p geography) insert @sample select geography::STGeomFromText('POINT(12.65100 65.34900)', 4619) union all select geography::STGeomFromText('POINT(35.65100 23.34900)', 4619) union all select geography::STGeomFromText('POINT(64.65100 12.34900)', 4619) DECLARE @h geography SET @h = geography::STGeomFromText('POINT(47.65100 -122.34900)', 4619) SELECT p.STDistance(@h) / 1000 as [km] from @sample

posted @ Thursday, November 22, 2007 11:38 PM | Feedback (0) | Filed Under [ SQL Server 2008 ]

Getting errors when working with Excel and SQL Server

In this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926 I gave a suggestion how to get a more detailed error message when working with OPENROWSET. But the same thinking is applicable for LINKED SERVER and other "outer referenced" data retreival methods. The OPENROWSET syntax with Jet provider does not give you full error description when problem occurs, such as permission errors. -- Using this code for a file with no appropriate permissions throws a general error SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 5.0;HDR=No;IMEX=0;Database=\\datastorage\excel\book2.xls', 'select * from [Sheet1$a1:q50]') If you have some error and you don't understand why, try using MSDASQL provider temporarily. SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=\\datastorage\excel\book2.xls', ...

posted @ Wednesday, October 24, 2007 8:01 AM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

Sum up a tree hierachy in SQL Server 2005

  -- Prepare sample data DECLARE @Accounts TABLE (AccountNumber CHAR(11), ParentAccountNumber CHAR(11)) INSERT  @Accounts SELECT  '100-000-000', NULL          UNION ALL SELECT  '100-001-000', '100-000-000' UNION ALL SELECT  '100-002-000', '100-000-000' UNION ALL SELECT  '100-002-001', '100-002-000' UNION ALL SELECT  '100-002-002', '100-002-000' DECLARE @Transactions TABLE (AccountNumber CHAR(11), Amount MONEY) INSERT  @Transactions SELECT  '100-001-000', 1000.00 UNION ALL SELECT  '100-002-001',  500.00 UNION ALL SELECT  '100-002-002',  300.00 -- Setup staging expression ;WITH Yak (AccountNumber, Amount) AS (         SELECT          AccountNumber,                         SUM(Amount) AS Amount         FROM            @Transactions         GROUP BY        AccountNumber         UNION ALL         SELECT          a.ParentAccountNumber,                         y.Amount         FROM            @Accounts AS a         INNER JOIN      Yak AS y ON y.AccountNumber = a.AccountNumber ) -- Show the expected resultset SELECT          COALESCE(AccountNumber, 'All accounts') AS AccountNumber,                 SUM(Amount) AS Amount FROM            Yak WHERE           AccountNumber IS NOT NULL GROUP BY        AccountNumber ORDER BY        CASE                         WHEN AccountNumber...

posted @ Thursday, October 04, 2007 10:48 AM | Feedback (4) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

New SQL Server 2005 OUTPUT operator

  Let’s play with the new OUTPUT operator! -- Setup TableA & TableB CREATE TABLE          #TableA                       (                                  i INT                       )   CREATE TABLE          #TableB                       (                                  i INT                       )   CREATE TABLE          #TableC                       (                                  iOld INT,                                  iNew INT                       )   -- Check TableA and TableB SELECT 'A' AS [Table], * FROM #TableA UNION ALL SELECT 'B' AS [Table], * FROM #TableB   -- Insert into TableA INSERT     #TableA OUTPUT     inserted.i INTO       #TableB SELECT     1 UNION ALL SELECT     2 UNION ALL SELECT     3   -- Check TableA and TableB SELECT 'A' AS [Table], * FROM #TableA UNION ALL SELECT 'B' AS [Table], * FROM #TableB   -- Delete from TableA DELETE     a OUTPUT     10 * deleted.i + 49 INTO       #TableB FROM       #TableA AS a WHERE      i = 2   -- Check TableA and TableB SELECT 'A'...

posted @ Wednesday, October 03, 2007 6:44 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms Administration SQL Server 2005 ]

Save some time and key-typing

Sometimes you have a denormalized table with several BIT columns used as flags. Say you want to select every row that has at least one flag set. That's easy. SELECT * FROM Table1 WHERE Flag1 = 1 OR Flag2 = 1 OR Flag3 = 1... But how to easy select all records where all flags are not set? SELECT * FROM Table1 WHERE Flag1 = 0 AND Flag2 = 0 AND Flag3 = 0... That can be the way you normally write, and it can get very long! But if you write like this instead to get all records where at least one flag is set SELECT * FROM...

posted @ Friday, September 28, 2007 12:24 PM | Feedback (2) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

Example of MERGE in SQL Server 2008

MERGE Production.ProductInventory AS [pi]USING     (           SELECT ProductID,                           SUM(OrderQty) AS OrderQty               FROM       Sales.SalesOrderDetail AS sod                INNER JOIN Sales.SalesOrderHeader AS soh ON sod.SalesOrderID = soh.SalesOrderID                WHERE      soh.OrderDate = GETDATE()               GROUP BY   ProductID           ) AS src (ProductID, OrderQty) ON src.ProductID = [pi].ProductIDWHEN      MATCHED AND src.OrderQty = 0            THEN DELETE; WHEN      MATCHED                THEN UPDATE SET [pi].Quantity = src.OrderQtyWHEN      NOT MATCHED                THEN INSERT VALUES (src.ProductID, src.OrderQty)  

posted @ Thursday, September 20, 2007 10:31 AM | Feedback (0) | Filed Under [ SQL Server 2008 ]

Powered by:
Powered By Subtext Powered By ASP.NET