LIKE operator "issue"

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

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