Must read that blog

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

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?

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?

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

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

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

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)