AjasHadi

Database Normalization stuff here

Here are some good articles on SQL Server Indexes. SQL Server Indexes: The Basics SQL Server Indexing Basics SQL Server Indexes

I found this great article on SQLTeam which shows How to Use GROUP BY in SQL Server Very good step by step guide.

In my last post , I mentioned how @@ROWCOUNT gets affected when MIN, MAX i.e. aggregate functions are used. Today, I had similar issues while using EXISTS and NOT EXISTS. I was using NOT EXISTS and records were showing up when really it should have removed the results. select col1 from tbl1 where not exists (select min(something) from tbl2 where tbl2.col9 = tbl1.col2) select col1 from tbl1 where not exists (select distinct something from tbl2 where tbl2.col9 = tbl1.col2) Hope this helps.

Hi, I came across this crazy issue today. You expect @@Rowcount to return number of rows returned by query right. But in my case it was returning 1 for 0 rows returned. Here is an example. SELECT * FROM yourtable WHERE 1 = 2 SELECT @@ROWCOUNT -- THIS GIVES 0 But if you have MIN function being used, the result will be different declare @company_id int SELECT @company_id = min(company_id) FROM yourtable WHERE 1 = 2 SELECT @@ROWCOUNT -- THIS GIVES 1 Apparently, since NULL was the result set, @@rowcount was giving 1 as the count. I hope this helps someone.

We all know that if you have an identity column and if you try to insert a value in the identity column, you will get an error. Here is how you can avoid the error First thing to do here is to set Identity to ON on the tblname you want to insert row in. SET IDENTITY_INSERT tblname ON INSERT tblname (ID,colname) VALUES(5,'ajas') SET IDENTITY_INSERT tblname OFF Do not forget to set IDENTITY_INSERT to off once you are done with inserting the record because you dont want it to be off if you have an application where developers will be working on it. Hope this Helps.

If you ever wanted to get name of all the databases on your server, you can use this query In SQL Server 2000, select name from master..sysdatabases In SQL Server 2005, select name from sys.databases :-)

I had a recent need to get information of CFID & CFTOKEN to see how many records we have in our datasource for CDATA, CGLOBAL and also from which date i.e. Last Min date to Last Max date the CGLOBAL table has values for. So I wrote this short query and then finally i cast the column as datetime so we can sort by date otherwise the sort will occur by string. select cast( substring(data, charindex('ts',data) + 4 , 19) as datetime) as 'lastvisit' , cast( substring(data, charindex('ts',data,charindex('ts',data)+4 ) + 4 , 19) as datetime) as 'timecreated', lvisit from cglobal where charindex('ts',data) gets the first occurrence and len('2009-08-18 22:36:55') gives you an idea of how many characters you need to extract and turns out its 19 characters. Link to how ColdFusion Purges Client Variables Hope this helps someone.

Hi, This is very nice tip if you ever wanted to find the next value of identity column without doing an insert, I repeat without doing an Insert. Lets say you have a table named company. Then you would use this command SELECT IDENT_CURRENT('company') + IDENT_INCR('company') where IDENT_CURRENT gives the current or last identity value used by that table and IDENT_INCR gives the increment you/anyone had set during creation of table. It could 1, 2 etc depending on your logic. The other way of finding next value of identity is after an insert statement by using SCOPE_IDENTITY(). You would add 1 or 2 and so on depending on the autoincrement setting you used during the table creation. Hope this helps.

Found a very good article about SQL Server 2000 Deadlock problem. Here is the link SQL Server A Deadlock Occurrence and Resolution

Hi, This post covers 3 amazing functions in SQL Server 2000 ( I am sure you can use them in SQL 2005 or 2008 for that matter). They are Replace, Substring and CharIndex. So my challenge was to replace ssn in a text and return the full text with only SSN part formatted as SSN : xxx-xx-1234 format. So I combined these functions this way. 1. first find the place/locations where the text SSN appears in the column using charindex('ssn',status) 2. then use Substring to use the number returned from step 1 and give the lenght of characters to be returned substring(status,charindex('ssn',status), 11) 3. Finally use replace function, to use step 2 and step 1 i.e. replace the characters returned by both steps with your text i.e. select replace (status, substring (status, charindex ('ssn',status), 11), 'SSN : xxx-xx-') from sometable where condition = condition. Hope this helps.

The decimal data type in sql server 2000 has precision and scale. The precision defines how many length of chars that can exist from left to right including the the numbers after the decimal point. For example col1 decimal(3,2) can contain total of 3 numbers. 9.20 is valid whereas 99.2 is invalid because sql server will add a zero at end to make it 99.20 and that invalidates the rule of precision 3. try this drop table testDec2 create table testDec2(col1 decimal(8,2)) insert into testDec2 values (999996.9999999) --number on left is of len 6 and even thogh we have 7 digits after decimal point, sql -- will truncate the number to insert 999997.00 . Notice the rounding. -- if you try to insert 999999.99 it wont work because of rounding. The number will -- be 1000000.00 i.e. total of 9 chars. select * from testDec2 give it a try. ;-)