Thinking outside the box

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

My Links

Advertisement

News

Archives

Post Categories

SQL Server 2000

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 ]

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 ]

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 ]

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 ]

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 ]

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 ]

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

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 ]

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 ]

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 ]

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 ]

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 ]

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 get a batch of identity values without OUTPUT

Late this evening I stumbled across a post where OP wanted to get all IDENTITY values for the latest INSERT statement, and correctly OP stated that SCOPE_IDENTITY() only returns last IDENTITY value of the batch. Well, since OUTPUT is not an option for SQL Server 2000, and if you don't want to include temp tables or other means, this is one way how to solve this issue. The algorithm relies in implicit transaction; if SPID 60 inserts 5000 records exactly the same time as SPID 61 inserts another 7500 records, the identity values for each spid doesn't get interleaved. CREATE TABLE    #Sample                 (                    ...

posted @ Thursday, July 16, 2009 11:37 PM | Feedback (4) | Filed Under [ Algorithms SQL Server 2000 ]

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 ]

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

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 ]

@@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 ]

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 ]

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 ]

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 ]

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 ]

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 ]

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 ]

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 ]

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 ]

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 ]

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 ]

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 ]

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 ]

Bug found in SQL Server 2005 sp2

For this to work, disconnect and reconnect the current query window in SSMS and then copy, paste and run the code below. Books Online says IDENT_CURRENT "Returns the last identity value generated for a specified table or view in any session and any scope.". So how can the IDENT_CURRENT() return 1 for a newly created table with no inserted records? @@IDENTITY and SCOPE_IDENTITY works as expected. CREATE TABLE      #Temp                   (                         RowID INT IDENTITY(1, 1),                         theValue INT                   )   SELECT      * FROM       #Temp   SELECT      @@IDENTITY,             IDENT_CURRENT('#Temp'),             SCOPE_IDENTITY()   INSERT      #Temp SELECT      99   SELECT      * FROM       #Temp   SELECT      @@IDENTITY,             IDENT_CURRENT('#Temp'),             SCOPE_IDENTITY()   INSERT      #Temp SELECT      123456   SELECT      * FROM       #Temp   SELECT      @@IDENTITY,      ...

posted @ Thursday, June 05, 2008 10:30 AM | Feedback (1) | Filed Under [ Administration SQL Server 2005 SQL Server 2000 ]

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 ]

Index pages

SQL Server 2005 introduced the new DMV views. They are great and a real improvement to debug and optimize queries. I find sys.dm_db_index_physical_stats very useful and often write this type of code  SELECT  index_id,         page_count FROM    sys.dm_db_index_physical_stats(DB_ID('MyDB'), OBJECT_ID('MyTable'), NULL, NULL, NULL)   to find out if the query optimizer has choosen the "right" index for the query.   This can be done in SQL Server 2000 too!   Use the DBCC SHOWCONTIG command. Maybe most of you have only used this with tables too see table fragmentation? Well, you can use it for indexes too.   Use   DBCC SHOWCONTIG ('MyDB..MyTable') WITH ALL_INDEXES, TABLERESULTS

posted @ Wednesday, April 30, 2008 9:38 AM | Feedback (0) | Filed Under [ Administration SQL Server 2000 ]

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 ]

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 ]

Simulating cross apply with CSV-records in SQL Server 2000

DECLARE @Sample TABLE (Col1 VARCHAR(6), Col3 VARCHAR(200)) INSERT @Sample SELECT '123', '125,124,126' UNION ALL SELECT '124', '127,21,245' --SELECT         Col1, --                Data --FROM            @Sample --CROSS APPLY     fnParseList(',', Col3) SELECT          a.Col1,                 SUBSTRING(',' + a.Col3 + ',', n.Number + 1, CHARINDEX(',', ',' + a.Col3 + ',', n.Number + 1) - n.Number - 1) AS [Value] FROM            @Sample AS a INNER JOIN      master..spt_values AS n ON SUBSTRING(',' + a.Col3 + ',', n.Number, 1) = ',' WHERE           n.Type = 'p'                 AND n.Number > 0                  AND n.Number < LEN(',' + a.Col3 + ',')

posted @ Monday, October 15, 2007 8:25 AM | Feedback (7) | Filed Under [ Algorithms SQL Server 2000 ]

Powered by:
Powered By Subtext Powered By ASP.NET