Dimant DataBase Solutions

Calendar table , again...

2020-07-13T01:13:00.002-07:00

Hello friends.
I am back to start writing posts about all things that related to SQL Server. There are lots of examples over a internet about how create a calendar table in T-SQL.
I would like to show you a little bit different technique to create a calendar table which based on the time interval. Let say we need to create a table with one hour interval, I have done it recently for our BI team to create Power BI reports.
Simple we are going to use a recursivequery to create such table.

with tmp(plant_date) as

(

select CAST('20200101' as datetime) --- Start date

union all

select DATEADD(hour, 1, plant_date)

from tmp

where DATEADD(hour, 1, plant_date) < '20281231'

)/*After we create a simple table you can extract an year , month, a day and etc.*/

select plant_date,cast(plant_date as date) date,YEAR(plant_date) Year,

CASE WHEN DATEPART(MONTH, plant_date)<10 THEN '0'

CAST(DATEPART(MONTH, plant_date) AS VARCHAR(2))

ELSE CAST(DATEPART(MONTH, plant_date) AS VARCHAR(2))

END Month,

DATENAME(MONTH,plant_date),

CASE WHEN DATEPART(day, plant_date)<10 THEN '0'

CAST(DATEPART(day, plant_date) AS VARCHAR(2))

ELSE CAST(DATEPART(day, plant_date) AS VARCHAR(2)) END day,

DATEPART(hour,plant_date) hour

from tmp

option (maxrecursion 0)

I hope the above script helps you.

Make sure that you write/create optimized SSIS Packages

2014-05-14T00:10:00.001-07:00

Recently I worked on the package that loops many text files and insert the data into a staging table and then from the staging into original table which has five columns only defined as VARCHAR(50) in the database. Pretty simple , not? Text files have many data and the destination table has many columns (90+) and very wide data tape client defines (VARCHAR(3000)). Actually he needs to get only few columns but who cares, right? We have observed that the memory during the package execution grow rapidly and very quickly MSSQL Server service was become unavailabe. You can imagine that during this spike nobody can work and a local DBA started getting complains from the end users. I would like to say that the server is very strong (4CPU+ 28RAM)...So what's problem?

At the source component , the estimated sized of a row is determined by the maximum columns sizes of all columns returned by the query (remember we have 90+ columns defined as VARCHAR(3000)). This is where the performance problem resides.

After sometime investigations we dropped the wide stage table and created a stage table with exactly same structure as original table has.
That means columns have VARCHAR(50) and we also changed the job schedule to run it frequently to deal with small data sets. So performance was drastically improved. We have not seen any more memory growing and not reducing, there is no more compliance from the end user.

Please read this article if you deal with packages especially if it works with large data sets.
http://technet.microsoft.com/en-us/library/cc966529.aspx

How to fix the fragmentation on heaps?

2014-01-01T00:53:00.002-08:00

We know that heaps are the tables without clustered index on. It may have many non clustered index (NCI) but still it is considered a heap. So consider a huge table (heap) that is fragmented. How would you fix it? Technically, you cannot defrag a heap table. But remember we have ALTER table REBUILD command which works very well on the heaps. But there is one big But. If you have many NCI on the table this command will rebuild all of them at once. What does it mean? It can produce transaction-log bloat which hits the over all performance. Think about the process the scan the log or perhaps Log Shipping job that needs to move the log file to the remote server. All this affects performance.

So, to answer the question ,we need to create a clustered index on that table..That is simple answer to the question.

Happy New Year to all!

One more method of using UNPIVOT command

2013-05-10T01:03:00.000-07:00

Hi friends
I had recently a client who has a table with more than 90's column and his requirement was to return the all columns that IS NOT NULL. So starting with sample T-SQL you will need to filter each column to check for NULLs, like WHERE col1 IS NOT NULL or col2 IS NOT NULL... or perhaps even using an aggregation to eliminate NULLs. But I found pretty nice solution, so take a look at below DDL. We have a table with number of columns (Dayn) to be checked for NULLs. I used simple UNPIVOT output of multiple rows into multiple columns in a single row.

DECLARE @Table TABLE
(
UserId INT,
Day1 INT NULL,
Day2 INT NULL,
Day3 INT NULL,
Day4 INt NULL,
Day5 INT NULL,
Day6 INT NULL,
Day7 INT NULL,
Day8 INt NULL,
Day9 INT NULL,
Day10 INt NULL
);

INSERT INTO @Table(UserId,
Day1, Day2, Day3, Day4, Day5, Day6,Day7, Day8,Day9, Day10)
VALUES(1,null,null,20,3,null,null,null,null,null,null);
INSERT INTO @Table(UserId,
Day1, Day2, Day3, Day4,Day5, Day6,Day7, Day8,Day9, Day10)
VALUES(2,50,25,15,5,null,null,null,null,null,null);

SELECT UserId, Col_NotNull FROM @Table
UNPIVOT (Col_NotNull FOR DayNumber
IN (Day1, Day2, Day3, Day4,Day5, Day6,Day7, Day8,Day9, Day10)) AS c

UserId Col_NotNull
1 20
1 3
2 50
2 25
2 15
2 5

SQL Server 2012 IIF function may replace CASE expression?

2012-10-31T06:01:00.001-07:00

Hi
Did you install already SQL Server 2012? Well, see a new T-SQL function named IIF you can use sometimes instead of CASE expression.
http://msdn.microsoft.com/en-us/library/hh213574.aspx

CREATE TABLE #t (id INT)

INSERT INTO #t VALUES (1),(2),(3)

DECLARE @a int = 3;
DECLARE @b int = 2;

SELECT * FROM #t WHERE id= IIF ( @a > @b, @a, @b )

Returns Id=3.

You can even using nested IIF commands.
SELECT * FROM #t WHERE id= IIF ( @a > @b, IIF(@a>0,@b,0), @b )

Identity property remains gaps

2012-06-18T02:21:00.001-07:00

Let say you have a table and use identity property. It is ok, but I really hope that you are aware of the bug that remains gaps in identity if you restart MS SQL Server service. Please vote http://connect.microsoft.com/SQLServer/feedback/details/739013/alwayson-fail

over-results-in-reseed-of-identity

Thanks

Successful upgrade to SQL Server 2012

2012-04-23T00:28:00.002-07:00

Hi everyone. Today I successfully upgraded our production database to the new version -SQL Server 2012. Actually everything went ok, and after running Upgrade Advisor and restored the database into a new server. The "challenge" was to upgrade existing SSRS reports and SSIS packages. What I would recommend is to open a new project in SQL Data Tools (yes BIDS is gone) and adding report by report to the project. SQL Server automatically upgraded them for first time I run them. Another thing is that now you can much easily to configure SSRS and even if you specified not to "configure" during the installation. I have not noticed any performance degradation since we moved from SQL Server 2005.So lets enjoy new features that were introduced and happy working with SQL Server 2012.

SQL Server 2012 has released to manufacturing (RTM)

2012-03-06T21:46:00.000-08:00

Microsoft announced that SQL Server 2012 has released to manufacturing (RTM). Customers and partners can download an evaluation of the product today (http://www.microsoft.com/sqlserver/en/us/default.aspx) and can expect general availability to begin on April 1.

LIKE operator "issue"

2012-02-01T04:20:00.001-08:00

Hi
Please consider the below script

create table #t ( col varchar(40))

insert into #t values ('[Untitled].pdf')
insert into #t values ('Untitled].pdf')
insert into #t values ('^Untitled].pdf')
insert into #t values ('|Untitled].pdf')

Now the client runs the below SELECT statement

select * from #t where col like col

Ok, you ask why col LIKE col and not col=col. Well I really simplified the the query because it takes a parameter and needs to search sub string within a col base on CASE Expression.So as you can see the above script does not return the first row [Untitled].pdf. After some investigation it turns out than we cannot get rid of left bracket. The only real solution is the below script (thanks to Peter Larsson)

select * from #t where col like replace(col, '[', '[[]')

Must read that blog

2012-01-10T20:57:00.000-08:00

Happy New Year to everyone!

Just found great web site about SQL Server maintained by Remus Rusanu (MS Employee)
It contains great info not only about published releases (SQL Server 2005/2008/2008R2) but also new features,command about SQL Server 2012

http://rusanu.com/2011/08/05/

Implicit conversions , sometimes it is hidden

2011-12-07T02:02:00.000-08:00

Consider simple table with one column defined as REAL datatype
CREATE TABLE #t (c REAL)
INSERT INTO #t VALUES (0)

SELECT COUNT(*) FROM #t WHERE c=''

In above statement you expect getting 0 rows to be returns as we filter out for all nonempty rows..But it returns 1 and the answer you find looking at execution plan.

SQL Server will implicitly convert '' to REAL datatype with 0 and a result is 1 row to be return.

Filter out characters---conversion error?

2011-11-06T06:13:00.000-08:00

Just help out may colleague to write a query where we needed to filter out all data that contains characters and because a column is defined as VARCHAR we CAST it to INTEGER in order to implement range searching. Please see simplified demo script.

CREATE TABLE #t (c varchar(50))
INSERT INTO #t VALUES ('122')
INSERT INTO #t VALUES ('4545')
INSERT INTO #t VALUES ('4545/454')
INSERT INTO #t VALUES ('4899')

----Failed
SELECT * FROM
(
SELECT c FROM #t WHERE c NOT LIKE '%[/]%'
) AS d WHERE CAST(c AS INT)>10

----Succeed
SELECT * FROM #t
WHERE CASE WHEN c LIKE '%[^0-9]%' THEN 0
WHEN CAST(c AS int) BETWEEN 1 AND 1000 THEN 1
ELSE 0 END = 1

The answer why the first attemp is failed we found looking at execution plan.
Predicate
CONVERT(int,[tempdb].[dbo].[#t].[c],0)>(10) AND NOT [tempdb].[dbo].[#t].[c] like '%[/]%'

Thinking that we filter out all "bad" rows and can CAST the rest is wrong because as we see above predicate is applied for the whole table.

As opposite the second query we used CASE expression to filter out "bad" rows CASE...=1 we see that SQL Server really filters out "bad" rows and now CAST is working.
Predicate
CASE WHEN [tempdb].[dbo].[#t].[c] like '%[^0-9]%' THEN (0) ELSE CASE WHEN CONVERT(int,[tempdb].[dbo].[#t].[c],0)>=(1) AND CONVERT(int,[tempdb].[dbo].[#t].[c],0)<=(1000) THEN (1) ELSE (0) END END=(1)

Why all my stored procedures are saved in master database under System stored procedure folder?

2011-11-03T01:00:00.000-07:00

Just having a discussion with a colleague , she made some changes in configuration and now when she creates a simple (not a system) stored procedure in master database it saves under Programmability --Stored Procedures--System Stored Procedures. Is it comfortable? No,right? After some investigation I found that we need to return 'allow updates' to 0 , see below script

EXEC sp_configure 'allow updates',0
reconfigure

Now everything got back to work in the 'right' place.

Default Schema for Windows Group

2011-09-05T02:04:00.000-07:00

Remember if you connect to SQL Server using Windows group, you cannot define a default schema. It is greyed out. This means that if you want everyone to be in a specific schema, you can't do it by default.

See reference
http://connect.microsoft.com/SQLServer/feedback/details/328585/default-schema-for-windows-group.

I hope finally MS will be addressing this issue in the next release of SQL Server named Denali (CTP 4)

/*
I'm happy to report that the next CTP for SQL Server Denali will allow default schemas to be assigned to Windows groups.

I'd like to thank everyone for their votes and attention to this issue. It made a different in being able to appropriately prioritize and make this improvement happen.

Il-Sung.
*/

SQL Server 2008 R2 Service Pack 1 has been released

2011-07-12T20:58:00.000-07:00

MS has just released SQL Server 2008 R2 Service Pack 1. Both the Service Pack and Feature Pack updates are available for download on the Microsoft Download Center. SQL Server 2008 R2 SP1 contains cumulative updates for SQL Server 2008 R2 and fixes to issues that have been reported through our customer feedback platforms.

To download the Service Pack packages please visit:

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=26727 ---SQL Server 2008 R2 SP1
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=26729 ---SQL Server 2008 R2 SP1 Express
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=26728 ---SQL Server 2008 R2 SP1 Feature Packs

To learn more about SQL Server 2008 R2 SP1 please visit Knowledge Base Article For Microsoft SQL Server 2008 R2 SP1.
http://support.microsoft.com/kb/2528583

Partitioning experience

2011-06-13T02:15:00.000-07:00

As you know since SQL Server 2005 MS introduced partitioning feature. I have been using it frequently with my clients but yesterday ,answering question on MSDN forum about partition I learned something new. Let say we have a partition table and staging table with the data and check constraint. See the below script.

create database db1
go
use db1
go

create partition function pf1(int) as range left for values (2,4)
create partition scheme ps1 as partition pf1 all to ([primary])

create table db1.dbo.t1 (id int ,descr varchar(2)) on ps1(id)
create table db1.dbo.t2 (id int,descr varchar(2))
alter table db1.dbo.t2 add check (id > 9)

create nonclustered index i1 on db1.dbo.t1(id)
create nonclustered index i2 on db1.dbo.t2(id)

insert into db1.dbo.t1 values (1,'n1')
insert into db1.dbo.t1 values(2,'n2')
insert into db1.dbo.t1 values(3,'N3')
insert into db1.dbo.t2 values (10,'n1')
insert into db1.dbo.t2 values(15,'n2')

---Add new range
alter partition function pf1() split range (9)

---Moving the data from t2 to t1 into a new added range
alter table db1.dbo.t2 switch to db1.dbo.t1 partition 4

Msg 4972, Level 16, State 1, Line 2
ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'db1.dbo.t2' allows values that are not allowed by check constraints or partition function on target table 'db1.dbo.t1'.

I got the error, but everything should be ok. Both tables have the same datatypes,indexes,lengths.. What is going on here. I spend some time and found the below explanation in the BOL

/*
We recommend that you specify NOT NULL on the partitioning column of partitioned tables, and also nonpartitioned tables that are sources or targets for ALTER TABLE...SWITCH operations. By doing this, you make sure that any CHECK constraints on partitioning columns do not have to check for null values. Null values are ordinarily placed in the leftmost partition of a partitioned table. When you are switching any partition other than the leftmost partition and when the ANSI_NULLS database option is set to ON, the absence of a NOT NULL constraint on the source and target tables might interfere with any CHECK constraints that are also defined on the partitioning column.
*/
So altering CHECK constraint with id IS NOT NULL solved the problem..

alter table db1.dbo.t2 add check (id > 9 AND id is not null)

SQL Server 2005 Maintenance Plan won't delete bakup files created in SQL Server 2008

2011-05-03T02:52:00.001-07:00

I have been asked recently to help to one of my friends with "strange behaviour" as he described...He has SQL Server 2008 where sql job copies .BAK files (database backup) into his local disk drive with requirement to keep those file only for one month. So as he has SQL Server 2005 (SP3) installed on his machine the first idea was to create Maintenance Clean Up task to delete the files. But as you imagine that did not work. Surely, when I came to help, I did not know that those files are created in SQL Server 2008 and has been copied to his local machine and after cheching all possibilities I launch up SQL Server Profiler to see what is going on... I saw the below command to be executed for each file to be deleted .
exec sp_executesql N'RESTORE LABELONLY FROM DISK=@P1',N'@P1 nchar(27)',N'X:\GBDB\Log\log080411PM.bak'
Well, I put that statement in Query window and obviously got the error which leads me to the root of the problem.
Msg 3241, Level 16, State 7, Line 1
The media family on device 'X:\GBDB\Log\log040411PM.bak' is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 1
RESTORE LABELONLY is terminating abnormally.

What do you think guys? Does SQL Server need to check out the version of the files need to be deleted or just delete files with .BAK extension regardless on version they were created ?

Name of CTE and referenced table should be different

2011-04-06T00:20:00.001-07:00

Just got the below example from the public forum.

CREATE TABLE Batch (Batch CHAR(1),Status INT)

INSERT INTO Batch VALUES ('A',1)
INSERT INTO Batch VALUES ('B',2)
INSERT INTO Batch VALUES ('C',3)

WITH Batch AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY Batch, Status) AS RowNum
FROM Batch
)
DELETE FROM Batch
WHERE RowNum=1

As you can see , running the query SQL Server throws the error.
Msg 252, Level 16, State 1, Line 1
Recursive common table expression 'Batch' does not contain a top-level UNION ALL operator.

SQL Server "thinks" that the CTE referenced to itself but there is no UNION ALL clause.In the blow example CTE named EmpCTE referenced within to itself to join with Employees table.

WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(

-- Anchor Member (AM)
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 7
UNION ALL

-- Recursive Member (RM)
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
SELECT * FROM EmpCTE

Finally,in order to resolve the problem you need that CTE and user table have different names. Something like that

WITH Batch_cte AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY Batch, Status) AS RowNum
FROM Batch
)
DELETE FROM Batch_cte
WHERE RowNum=1

MVP Award for 2011 year.

2011-04-04T01:55:00.000-07:00

I just got my MVP renewal email from MS, so this is my 5th award for SQL MVP since 2007.
I would say thanks for all support and good communication with all MVP around the world and hopefully it could make me more motivated to give more contribution for the community

To someone who specializes in SQL Server performance tuning

2011-03-30T03:27:00.001-07:00

Must read Conor's blog
http://blogs.msdn.com/b/conor_cunningham_msft/

Alias issue in T-SQL or defensive programming

2011-02-06T00:39:00.000-08:00

Recently I have talked to our developer who wanted to delete TOP x rows from the table. I pointed him to the below artcile http://blogs.msdn.com/b/sqlcat/archive/2009/05/21/fast-ordered-delete.aspx where a tip – a view with ORDER BY.

As alternative he wanted using a derived table but cannot understand why all rows are deleted from the table instead of TOP(x). See the below demo.

create table #t (c int)
insert into #t values (1)
insert into #t values (1)
insert into #t values (2)
insert into #t values (3)
insert into #t values (3)

delete #t from (select top (2) c
from t order by c) t

How does DELETE extension in T-SQL work?. The FROM clause after the DELETE specifies the target table to delete. The second optional FROM clause specifies the qualifying rows. But if I change 't' alias to '#t' as original name of the temporary table that would work...

delete #t from (select top (2) c
from t order by c) #t

Now, SQL Server 'sees' that derived table has the same name as a target and thus deletes only TOP(x) rows

There is no goal of this post to get into a discussion about how to write correlated subquery to perform such operations, I just wanted you to pay attention on if you choose using derived tables to perform deletion please make sure that alias you specify for derived table is the same as a target table..

PS. If you are testing and not sure about the result please use BEGIN TRAN... before executing the script.If you see that rows affected by the script is too many issue ROLLBACK TRAN to back to original data.

Dedup on huge table

2011-01-11T22:57:00.000-08:00

Hi friends
At this time I would like to share with you my experience to delete duplicates in very large table(800 million of rows).
A very general method is to use ROW_NUMBER function to PARTITION ON desired columns and then filter out only unique data.

WITH cte
AS
(
SELECT ,ROW_NUMBER() OVER (PARTITION BY ORDER BY ) rn
FROM tbl
) SELECT * FROM cte WHERE rn=1

As you imagine on huge table it will take too long. In order to optimize that query I used batch processing (divided that transaction into small chunks)

DECLARE @x INT
SET @x = 1
WHILE @x < 44,000,000 -- Set appropriately
BEGIN

;WITH cte
AS
(
SELECT ,ROW_NUMBER() OVER (PARTITION BY ORDER BY ) rn
FROM tbl WHERE ID BETWEEN @x AND @x + 10000
)SELECT * FROM cte WHERE rn=1

SET @x = @x + 10000
END
Ok ,it worked much faster as we have a clustered index on ID column such as SQL Server uses it to get the data based on defined range.However, we have another problem that data we are getting back is not actually unique.You see that for specific range ,say (from 1 to 10000) I get the data based on required partition and filter out for rn=1, BUT it is possible that the same row will occur in the next chunk (from 10001 to 20000) and we will also get it back because SQL Server does not recognize it as duplicate we have already got from the first chunk.

More reliable solution is checking on entire table and not to base on ranges.
SELECT ,COUNT(*) rn FROM
tbl GROUP BY
HAVING COUNT(*)>1

As you can see it could take long time ,so I also tried to create a Dedupt table with a key IGNORE_DUP_KEY option (thanks to Hugo) but insert into the table was pretty slow as well. Peter Larsson a fellow MVP has suggested the below technique that worked pretty well

CREATE TABLEe #unique (id primary key clustered)
INSERT INTO #temp (id) SELECT MIN(ID) AS ID
FROM tbl
GROUP BY Col1, col2, col3... (here you decide the uniqueness)

--And then insert into batches for

SET @id = 0
WHILE @id < (800 million or more)
BEGIN
SELECT t1.ID, t1.Col
FROM dbo.Table1 AS T1
INNER JOIN #unique as u ONu.id = t1.id
AND u.id BETWEEN @id AND @id +99999

SET @id += 100000
END

It would be great it put back here testing results.

What business says does not mean what businees wants

2010-12-16T02:28:00.001-08:00

This great sentence I learned form my exerience being a consultant

What business says does not mean what businees wants

TechEd 2010 in Eilat

2010-11-23T01:19:00.001-08:00

Hi
I am going to attend TechEd 2010 in Eilat next week. It is great opportunity to learn new things , meet new and old friends.It is my second TechEd and I am will be focusing on Data Platform direction and BI. Hope to see you there.

http://www.microsoft.com/israel/TechEd2010/Tracks/BI.aspx

Blobs and covering indexes

2010-11-08T23:26:00.001-08:00

Nowadays it is common to store/save pictures,documents in the databbase. Since SQL Server 2005 we use VARBINARY(MAX) datatype to store such data. The 'problem' I have seen recently at the client database is SELECT statement on very huge table that contain BLOB data and that data needs to be return to the client works pretty slowly. Run the below
SET STATISTICS IO ON

SELECT col1,col2, blobdata FROM tbl WHERE....

Table 'tbl'. Scan count 1, logical reads 8125, physical reads 0, read-ahead reads 0, lob logical reads 261521, lob physical reads 0, lob read-ahead reads 0.
CPU time = 1252 ms, elapsed time = 12632 ms.

We see high number of reads to return the data from index/blob pages.

Execution plan shows that there is Bookmark to return the BLOB data from Clustered Index Key because of our NCI(NonClustered Index) does not cover all columns in SELECT statement.
Ok, at first glance you would re-create NCI to INCLUDE blobdata column to 'cover' SELECT ...Well, I noticed that recreating NCI index takes long long time and LOG file was grown dramatically. I see that after INCLUDE blobdata column I reduced logical reads as shown below

Table 'tbl'. Scan count 1, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 421521, lob physical reads 0, lob read-ahead reads 0.
CPU time = 1622 ms, elapsed time = 16512 ms.

But what happened? BLOB page reads were done almost 1.8 times more with covering index and actually I have not seen much improvment in performance of the query.
So finally, if you create an index or INCLUDE the BLOBs SQL Server create copy of that index for every blob column and thus it takes time to create index and reading the data from index page. Well, you were able to save some IO by covering BLOB column but internally SQL Server works much hardly to return the data and maintain the index.