|
|
Optimization
This week I was invited my Microsoft to give two presentations in Slovenia. My presentations went well and I had good energy and the audience was interacting with me.
When I had some time over from networking and partying, I attended a few other presentations. At least the ones who where held in English. One of these was "SQL Server Integration Services 2012 - All the News, and More", given by Davide Mauri, a fellow co-worker from SolidQ.
We started to talk and soon came into the details of the new things in SSIS 2012. All of the official things Davide talked...
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
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
For the next few months, I will be involved in an interesting project for a mission critical application that our company have outsourced to a well-known and respected consulting company here Sweden.
My part will be the technical details of the forecasting application now when our former DBA has left our company.
Today I took a brief look at the smallest building blocks; the Functions. No function is inline so I can assume some of the performance issues are derived from these.
One function I stumled across is very simple. All it does is to add a timepart from current execution time to...
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...
DECLARE @Year SMALLINT = 2011,
@NumberOfYears TINYINT = 3
;WITH cteCalendar(FirstOfMonth, LastOfMonth)
AS (
SELECT DATEADD(MONTH, 12 * @Year + Number - 22801, 6) AS FirstOfMonth,
DATEADD(MONTH, 12 * @Year + Number - 22800, -1) AS LastOfMonth
FROM master..spt_values
WHERE TYPE = 'P'
AND number BETWEEN 1 AND 12 * @NumberOfYears
)
SELECT DATEADD(DAY, DATEDIFF(DAY, 6, FirstOfMonth) / 7 * 7, 6) AS FirstSunday,
DATEADD(DAY, DATEDIFF(DAY, 5, LastOfMonth) / 7 * 7, 5) AS LastSaturday
FROM cteCalendar
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...
This problem is designed to come up with a solution that uses the smallest amount of storage possible for a 1,000 seat restaurant.
I've come up with a solution that need only 125 bytes of storage. All other solutions covered in Mr Celko's book has at least 1,000 bytes of storage.
Here is my solution, complete with all procedures to assign and release seats, together with views to display current status of each and one seat.
-- Setup sample data
CREATE TABLE dbo.Restaurant
(
Seats BINARY(125) NOT NULL
)
-- Initialize an empty restaurant
INSERT dbo.Restaurant
(
Seats
)
SELECT 0x
GO
-- Create procedure for handling seat assignment
CREATE...
This question has been asked over and over again, and instead of having to redirect to any of my previous answers, I will cover the solution here in my blog.
Consider this sample data
DECLARE @Sample TABLE
(
ID INT,
Data VARCHAR(100)
)
INSERT @Sample
VALUES (1, 'Peso & Performance SQL'),
(1, 'MVP'),
(2, 'Need help <? /> -- '),
(2, 'With XML string concatenation ?')
The "trick" is to use the TYPE directive (to deal with entitization), and then use ".value" function to get the correct value out.
So here is the final query.
SELECT i.ID,
STUFF(f.Content.value('.', 'VARCHAR(MAX)'), 1, 1, '')
FROM (
SELECT ID
FROM @Sample
GROUP...
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 ...
This time I will show you an algorithm to do the dreaded bin-packaging using recursion and XML.
First, create some sample data like this
-- Prepare sample data
DECLARE @Sample TABLE
(
RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
Expense SMALLMONEY NOT NULL
)
-- Populate sample data
INSERT @Sample
(
Expense
)
VALUES (12.51),
(45.63),
(66.35),
(92.66),
(65.46),
(54.01),
(32.23),
(27.16),
(78.92),
(14.58)
Next, we need to create a variable to hold the user's wanted total sum.
-- Prepare user supplied parameter
DECLARE @WantedSUM SMALLMONEY = 111.09
And we also need to create a temporary staging table to hold the valid combinations
-- Prepare temporary staging table
DECLARE @Temp TABLE
(
CombID INT...
Hi!
Today I am going to talk about compression in SQL Server 2008. The data warehouse I currently design and develop holds historical data back to 1973. The data warehouse will have an other blog post laster due to it's complexity.
However, the server has 60GB of memory (of which 48 is dedicated to SQL Server service), so all data didn't fit in memory and the SAN is not the fastest one around.
So I decided to give compression a go, since we use Enterprise Edition anyway.
This is the code I use to compress all tables with PAGE compression.
DECLARE @SQL VARCHAR(MAX)
DECLARE curTables CURSOR FOR
...
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...
This example is based on AdventureWorks database. More details about the task is found here http://sqlblog.com/blogs/adam_machanic/archive/2009/12/08/t-sql-tuesday-001-exploring-fuzzy-interval-islands-using-sqlclr.aspx.
The task is to produce a set of dateranges where a product has been sold, with a "latency" of 7 days. I wanted do display that there are other way to write a query to make it more efficient. Adam's SQLCLR version runs in 0.5 seconds.
DECLARE @Interval INT = 7
;WITH cteSingle(ProductID, TransactionDate, recID)
AS (
SELECT ProductID,
TransactionDate,
ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY TransactionDate) AS recID
FROM Production.TransactionHistory
), cteLower(ProductID, StartDate, recID)
AS (
SELECT s.ProductID,
s.TransactionDate AS EndDate,
ROW_NUMBER() OVER (PARTITION BY s.ProductID ORDER BY s.TransactionDate) AS recID
FROM ...
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 %...
Based on Itzik's suggestion for sequence generators, I rewrote them and adapted them for my needs.
Itzik showed how to work around a big issue for some cases where the query optimizer actually tried to produce all possible combinations before returning the wanted records.
My rewrite is two-part
1) The functions accepts a Starting point and an Ending point.
2) The functions accepts a Stepping point. You may want only odd or even numbers? Or only every 7th day?
Here are the functions. Have fun with them!
I haven't checked performance compared to MVJ's F_NUMBER_TABLE function, but they should be at least equally fast.
CREATE FUNCTION dbo.fnGetNumbers
(
@FromNum INT,
@ToNum...
I recommend you read it. Next part will contain a brilliant solution for calculating concurrent sessions in a linear algorithm. The math involved is very good indeed.
First part is found here http://www.sqlmag.com/articles/index.cfm?articleid=102734
//Peso
This is nothing new to me (I come across this autumn 2007) and probably not to you either but I forgot about it and yesterday it came alive again.
I often choose a table variable for performance reasons (if data is less than 1 page anyway) due to the benefits of no logging etc etc.
But yesterday I wrote a query for Phil Factor's "Subscription List" competition where I had choosen a table variable for the same reasons as before. It took a while to realize why (in this case) the solution with a temporary table was 30% faster than the solution...
Yesterday Joe Celko posted on microsoft.public.sqlserver.programming about how to write an elegant query for Weighted Median. He managed to get the correct results but always ended up with ugly code.
Joe had a feeling an elegant query existed but was not seeing it. Anyway, since Celko's books have helped me in the past, I thought I should help him now.
Consider this sample data
DECLARE @Foo TABLE
(
x INT NOT NULL
)
INSERT @Foo
VALUES (1),
(2),
(2),
(3),
(3),
(3)
The most common approach to calculate the median value I have seen is
SELECT AVG(1.0E * x)
FROM (
SELECT x,
ROW_NUMBER() OVER (ORDER BY x DESC) AS a,
ROW_NUMBER() OVER (ORDER BY x) AS...
For more information about my SQLCLR, see
http://www.developerworkshop.net/software.html
The biggest difference from Beta2 stage is that I now only target SQL Server 2008 and later.
//Peso
The answer to your question is "Yes, I am having a slow day today."
;WITH Fibonacci(n, f, f1)
AS (
SELECT CAST(1 AS BIGINT),
CAST(0 AS BIGINT),
CAST(1 AS BIGINT)
UNION ALL
SELECT n + 1,
f + f1,
f
FROM Fibonacci
WHERE n < 93
)
SELECT n,
f AS Number
FROM Fibonacci
Here is a simple base converter that manages [from and to] base 2 to 16.
CREATE FUNCTION dbo.fnBaseConvert
(
@Value VARCHAR(8),
@FromBase TINYINT,
@ToBase TINYINT
)
RETURNS VARCHAR(32)
AS
BEGIN
RETURN (
SELECT SUBSTRING('0123456789abcdef', 1 +(x.theValue % CAST(POWER(CAST(@ToBase AS FLOAT), v.Number + 1) AS BIGINT)) / CAST(POWER(CAST(@ToBase AS FLOAT), v.Number) AS BIGINT), 1)
FROM (
SELECT SUM((CHARINDEX(SUBSTRING(@Value, LEN(@Value) - Number, 1), '0123456789abcdef') - 1) * CAST(POWER(CAST(@FromBase AS FLOAT), Number) AS BIGINT)) AS theValue
FROM master..spt_values
WHERE Type = 'P'
AND Number < LEN(@Value)
HAVING MIN(CHARINDEX(SUBSTRING(@Value, LEN(@Value) - Number, 1), '0123456789abcdef')) > 0
AND MAX(CHARINDEX(SUBSTRING(@Value, LEN(@Value) - Number, 1), '0123456789abcdef')) <= @FromBase
) AS x
INNER JOIN master..spt_values AS v ON v.Type = 'P'
WHERE x.theValue >= 0
AND v.Number < CEILING(0.00000005 + LOG(COALESCE(NULLIF(x.theValue,...
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
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)
I've posted a feedback on Microsoft Connect about how to enhance the INSERT INTO syntax.
Especially for INSERT INTO ... EXEC ...
Sometimes when you need the result from a stored procedure, the SP itself returns two (or more) resultsets.
And it's only possibly to fetch and store the first resultset.
What I have suggested is an enhanced syntax for INSERT INTO ... EXEC, like this
INSERT INTO Table1 (Col1, Col2), Table2 (ColX, ColY, ColZ)
EXEC usp_MyStoredProcedure @Param1, @Param2
In this example, usp_MyStoredProcedure returns three resultsets, of which I want to store the two first.
First resultset has two columns, and second resultset has three columns.
Let Microsoft know...
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 a date. The function returns a table with one record and 3 columns.
First column is Weekday; Monday = 1, Tuesday = 2, Wednesday = 3; Thursday = 4, Friday = 5, Saturday = 6 and Sunday = 7.
Second column is number of occurencies of that date since beginning of selected period type.
Third columns is number of occurencies left of that period type.
CREATE FUNCTION dbo.fnGetWeekdayAndNths
(
@theDate DATETIME,
@theType CHAR(1)
)
RETURNS TABLE
AS
RETURN ( SELECT 1 + DATEDIFF(DAY, -53690, @theDate) % 7 AS theWeekday,
1 +(theDelta - 1) / 7 AS Beginning,
DATEDIFF(DAY, DATEADD(DAY, -1, thePeriod), @theDate) / 7 - 1...
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...
For some time ago, I posted an algorithm how to get the XML structure automatically. Today I stumbled across another approach which seems to be faster. Reservations though I haven't tested this against large xml data yet.
However, the previous algorithm relied on a WHILE loop here
http://weblogs.sqlteam.com/peterl/archive/2009/03/05/Extract-XML-structure-automatically.aspx
but this new algorithm doesn't. It's all xml internal thingies going on.
DECLARE @Nodes TABLE
(
NodeID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
ParentNodeName NVARCHAR(64),
NodeName NVARCHAR(64)
)
DECLARE @Data XML
SET @Data = '
<root>
<elementGroup>
<element>
<stuff>
<comment>Stuff comment</comment>
</stuff>
<comment>Element comment</comment>
</element>
<comment>Element group comment</comment>
</elementGroup>
<comment>Root comment</comment>
</root>'
INSERT @Nodes
(
ParentNodeName,
NodeName
)
SELECT e.value('local-name(..)[1]', 'VARCHAR(MAX)') AS ParentNodeName,
...
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 ...
With the arrival of SQL Server 2005 the new OUTPUT operator was introduced. The OUTPUT operator works much like a "local trigger" on the current statement.
The drawback is that there is no way to filter the returned resultset directly. You have to insert the resultset in a staging table and work from there.
With SQL Server 2008 you now have a tool named Composable DML. What is then Composable DML?
Well, with this tool you can have a statement of UPDATE, DELETE and even MERGE as a data source for your query!
In this example I am showing you how to audit certain...
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 *...
Today I am going to talk about how to efficiently reuse identity values in a column, even if this is something that normally not should be bothered. The reason for this solution was a request for help from a member here on SQLTeam, who was near run out of identity values.
I did some reasearch first to see which was the most common method to deal with this situation and not surprisingly the method of iterating all records from start to end was used.
That method is not efficient. What if you have 1 million records and there is only 1 gap at...
Adam Machanic has a nice competition going on here http://sqlblog.com/blogs/adam_machanic/archive/2009/02/27/t-sql-challenge-grouped-string-concatenation.aspx until the 16th of March 2009.
Please join in! The prize, for the best submission, is a full MSDN subscription, valued at around $10,000. How's that for inspiration!
My first attempt runs for 1.9 seconds and uses 418k reads.
My second attempt runs for 2.1 seconds and uses 334k reads.
Table 'Product'. Scan count 0, logical reads 158866, physical reads 0.
Table 'SalesOrderDetail'. Scan count 31466, logical reads 97140, physical reads 0.
Table 'SalesOrderHeader'. Scan count 38239, logical reads 77321, physical reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0.
Table 'Contact'. Scan count 1, logical reads 569,...
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,...
In the past I have given the advice to break down all date intervals into the smallest part, most often minutes.
Then OP should group by the minute.
-- Prepare sample data
DECLARE @Data TABLE
(
RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
CreateDate DATETIME,
DeleteDate DATETIME
)
-- Populate sample data
INSERT @Data
(
CreateDate,
DeleteDate
)
SELECT '2009-01-14 22:33', '2009-01-14 22:35' UNION ALL
SELECT '2009-01-14 22:33', '2009-01-14 22:33' UNION ALL
SELECT '2009-01-14 22:34', '2009-01-14 22:35' UNION ALL
SELECT '2009-01-14 22:35', '2009-01-14 22:35' UNION ALL
SELECT '2009-01-14 22:35', '2009-01-14 22:36' UNION ALL
SELECT '2009-01-14 22:37', '2009-01-14 22:37' UNION ALL
SELECT '2009-01-14 22:39', '2009-01-14 22:39' UNION ALL
SELECT '2009-01-14 22:38', '2009-01-14 22:38' UNION...
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...
Today I come across an interesting approach to a networking algorithm. The question was about how to use recursive to expand a network and still avoid circular reference.
See topic here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=115290
You can’t do that in a recursive CTE because you can only reference the CTE once in the recursive part. Then I thought about a “recursive csv string”. And I gave it a try.
Here is the result.
DECLARE @Stations TABLE
(
stationID INT,
name VARCHAR(255)
)
INSERT @Stations
SELECT 1, 'Glasgow' UNION ALL
SELECT 2, 'Edinburgh' UNION ALL
SELECT 3, 'York' UNION ALL
SELECT 4, 'London' UNION ALL
SELECT 5, 'Aberdeen' UNION ALL
SELECT 6, 'Bjuv'
DECLARE @Links TABLE
...
-- 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
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
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...
-- 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 ...
-- 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 , ...
I started out with typing
SELECT @@VERSION
and got the result as
Microsoft SQL Server 2005 - 9.00.3215.00 (Intel X86)
Dec 8 2007 18:51:32
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
So far so good. Now I created some sample data like this
-- Prepare sample data
DECLARE @Sample TABLE (RecNo TINYINT, ID TINYINT, EventDate SMALLDATETIME, OrderStatus VARCHAR(9))
INSERT @Sample
SELECT 1, 10, '7/5/2008', 'Opened' UNION ALL
SELECT 2, 11, '7/5/2008', 'Closed' UNION ALL
SELECT 3, 12, '7/5/2008', 'Closed' UNION ALL
SELECT 4, 13, '7/6/2008', 'Opened' UNION ALL
SELECT 4, 14, '7/6/2008', 'Opened' UNION ALL
SELECT 4, 15, '7/6/2008', 'Closed' UNION ALL
SELECT 1, 16, '7/7/2008',...
CREATE PARTITION FUNCTION pfOrderDate
(
DATETIME
)
AS RANGE RIGHT FOR VALUES (
'20000101',
'20010101',
'20020101'
)
GO
CREATE PARTITION SCHEME psYak
AS PARTITION pfOrderDate
ALL TO ([PRIMARY])
GO
CREATE TABLE Orders
(
OrderID INT NOT NULL,
CustomerID VARCHAR(15) NOT NULL,
OrderDate DATETIME NOT NULL
)
ON psYak(OrderDate)
GO
CREATE CLUSTERED INDEX IX_OrderID
ON Orders
(
OrderID
)
CREATE NONCLUSTERED INDEX IX_OrderDate
ON Orders
(
OrderDate
)
INCLUDE (
OrderID,
CustomerID
)
GO
INSERT Orders
(
OrderID,
CustomerID,
OrderDate
)
SELECT 1, 'Peso', '20011225' UNION ALL
SELECT 2, 'Jennie', '20020314'
SELECT OrderID,
OrderDate
FROM Orders
WHERE OrderDate = '20011225'
SELECT OrderID,
OrderDate
FROM Orders
WHERE OrderID = 1
DROP TABLE Orders
DROP PARTITION SCHEME psYak
DROP PARTITION FUNCTION pfOrderDate
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
(
...
This is what I pondered about today. Maybe I also will have some time to test it.
CREATE PROCEDURE dbo.uspPaginate
(
@PageNumber INT,
@RecordsPerPage TINYINT = 50
)
AS
SET NOCOUNT ON
DECLARE @MaxRows INT
SET @MaxRows = @PageNumber * @RecordsPerPage
SELECT SomeColumns
FROM (
SELECT TOP (@RecordsPerPage)
SomeColumns
FROM (
SELECT TOP (@MaxRows)
SomeColumns
FROM YourTable
ORDER BY SomeCase ASC/DESC
)
ORDER BY SomeCase DESC/ASC
)
ORDER BY SomeCase ASC/DESC
Topic is here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97550
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
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...
-- Prepare sample data
DECLARE @Accounts TABLE (AccountNumber CHAR(11), ParentAccountNumber CHAR(11))
INSERT @Accounts
SELECT '100-000-000', NULL UNION ALL
SELECT '100-001-000', '100-000-000' UNION ALL
SELECT '100-002-000', '100-000-000' UNION ALL
SELECT '100-002-001', '100-002-000' UNION ALL
SELECT '100-002-002', '100-002-000'
DECLARE @Transactions TABLE (AccountNumber CHAR(11), Amount MONEY)
INSERT @Transactions
SELECT '100-001-000', 1000.00 UNION ALL
SELECT '100-002-001', 500.00 UNION ALL
SELECT '100-002-002', 300.00
-- Setup staging expression
;WITH Yak (AccountNumber, Amount)
AS (
SELECT AccountNumber,
SUM(Amount) AS Amount
FROM @Transactions
GROUP BY AccountNumber
UNION ALL
SELECT a.ParentAccountNumber,
y.Amount
FROM @Accounts AS a
INNER JOIN Yak AS y ON y.AccountNumber = a.AccountNumber
)
-- Show the expected resultset
SELECT COALESCE(AccountNumber, 'All accounts') AS AccountNumber,
SUM(Amount) AS Amount
FROM Yak
WHERE AccountNumber IS NOT NULL
GROUP BY AccountNumber
ORDER BY CASE
WHEN AccountNumber...
Let’s play with the new OUTPUT operator!
-- Setup TableA & TableB
CREATE TABLE #TableA
(
i INT
)
CREATE TABLE #TableB
(
i INT
)
CREATE TABLE #TableC
(
iOld INT,
iNew INT
)
-- Check TableA and TableB
SELECT 'A' AS [Table], * FROM #TableA
UNION ALL
SELECT 'B' AS [Table], * FROM #TableB
-- Insert into TableA
INSERT #TableA
OUTPUT inserted.i
INTO #TableB
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3
-- Check TableA and TableB
SELECT 'A' AS [Table], * FROM #TableA
UNION ALL
SELECT 'B' AS [Table], * FROM #TableB
-- Delete from TableA
DELETE a
OUTPUT 10 * deleted.i + 49
INTO #TableB
FROM #TableA AS a
WHERE i = 2
-- Check TableA and TableB
SELECT 'A'...
Sometimes you have a denormalized table with several BIT columns used as flags.
Say you want to select every row that has at least one flag set. That's easy.
SELECT * FROM Table1 WHERE Flag1 = 1 OR Flag2 = 1 OR Flag3 = 1...
But how to easy select all records where all flags are not set?
SELECT * FROM Table1 WHERE Flag1 = 0 AND Flag2 = 0 AND Flag3 = 0...
That can be the way you normally write, and it can get very long!
But if you write like this instead to get all records where at least one flag is set
SELECT * FROM...
In previous topic here
http://weblogs.sqlteam.com/peterl/archive/2007/09/20/Finding-records-in-one-table-not-present-in-another-table.aspx
Michael Valentine Jones suggested an alternative route to get all records from one table not found in another.
Here are the results (including my suggestion with only MIN(t1) = 1 as MVJ2)
SQL 2000 CPU DURATION READS WRITESOriginal 1781 1783 30667 0Peso 1 0 0 29 0Peso 2 0 0 31 0Peso 3 ...
I got some response from same topic posted on September 20
http://weblogs.sqlteam.com/peterl/archive/2007/09/24/Finding-group-of-records-with-a-certain-status.aspx
Hugo Kornelis posted an alternative code to my improvement and I promised to test it.
Here is the new results (made on other machines so the absolute numbers do not match).
SQL 2000 Memory CPU DURATION READS WRITESOriginal 5304 670 920 13555 0Peso 8685 125 125 604 0Hugo 8685 110 110 604 0
SQL 2005 Memory CPU DURATION READS WRITESOriginal 8838 437 558 851 0Peso 8838 172 ...
Difference between NOT IN, LEFT JOIN and NOT EXISTS.
The objective is to fetch all records in one table that are not present in another table. The most common code I’ve seen at client sites includes the use of NOT IN, because this keyword is included in most programming languages and programmers tend to use this technique when writing stored procedures in the database too. The code example I have found in most cases is this simple and understandable
SELECT a.iFROM #a AS aWHERE a.i NOT IN (SELECT b.j FROM #b AS b) OR a.i IS NULL
The basic idea is to get all records...
I recently was given the task to optimize some code prior to a client's upgrade to SQL Server 2005.
The objective for the old code was to get all orders where status for all orderlines where set to 'DROP'.
SELECT t.OrderID, t.OrderDateFROM tblOrder AS tINNER JOIN ( SELECT a.OrderID, CASE WHEN SUM(b.Type) = COUNT(*) THEN 'DROP' ELSE NULL END AS [Type] FROM tblOrder AS a INNER JOIN ( SELECT OrderID, CASE WHEN [Status] = 'DROP' THEN 1 ELSE 0 END AS Type FROM tblOrder ) AS b ON b.OrderID = a.OrderID GROUP BY a.OrderID ) AS c ON c.OrderID = t.OrderID AND c.Type = 'DROP'
After some testing I...
|