|
|
SQL Server 2000
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
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...
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
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
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...
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
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
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...
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...
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 ...
-- 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 =...
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
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
...
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...
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...
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...
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 %...
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...
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) +...
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...
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 >...
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
(
...
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...
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
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,...
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
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)
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'...
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...
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...
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)...
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
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
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 ...
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
)...
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
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 %...
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
...
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...
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...
DECLARE @Stats TABLE ( SomeDate DATETIME ) INSERT @Stats SELECT 20000 + ABS(CHECKSUM(NEWID())) % 30000 FROM master..spt_values DECLARE @Style INT ...
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...
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 *...
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
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,...
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
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)) + '.'
...
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
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 ...
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?
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...
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...
-- 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)
...
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
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...
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
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
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
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
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...
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'
)...
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
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...
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
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,
...
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,...
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...
-- 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 ...
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] %', ' '...
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',...
-- 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
...
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 , ...
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
Here are all the 36 numbers between 0 and 9000 that have a different LEN than DATALENGTH.
Number
Binary digits
...
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
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
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...
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)...
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,
...
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
(
...
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
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.
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
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...
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
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...
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',
...
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 + ',')
|