SSQA.net

In this blog post, I explained different uses of GO command Here is a Fun when you use GO as object name CREATE PROCEDURE GO(    @I INT)AS    SELECT @I AS number Well. The procedure is created and let us try to execute GO 2What we see is Command(s) completed successfullyBecuase it becomes a syntax to execute a ...

Often newbies use empty string and consider it as NULL. But when an empty string is passed, it may not be stored as empty string or NULL. It actually depends on the datatype of the column Consider this exampleDECLARE @t TINYINT,@s SMALLINT,@i INT,@big BIGINT,@f FLOAT,@bit BIT, @sm SMALLMONEY,@m MONEY,@d DATETIME SELECT @t='', ...

The PIVOT operator available in SQL Server 2005 is used to generate the Cross-tab results Consider this example select * from (    select Year(OrderDate) as pivot_col,e.lastname, o.OrderDate FROM northwind..Employees as e    INNER JOIN northwind..Orders as o ON (e.EmployeeID=o.EmployeeID) ) as t pivot ...

Many users complain that some new features are not working in SQL Server 2005 when the server is migrated from SQL Server 2000 These are the common tasks that should be done to avoid some migration issues 1 Change the compatibility level to 90   EXEC sp_dbcmptlevel 'your_db_name',90  This will solve the errors that occur ...

As you all know, GO command signals the end of the batch of T-SQL statements However in SQL Server 2005, it is also used to execute set of commands for a specified number of times Consider that you want to create a table that should have hundred random integer values. You can the methods like the ones specified in ...

Sometimes it may be useful to reuse the result of the DBCC commands. If the DBCC command resturns a resultset, it can be outputted to a table. Consider the following command DBCC useroptions It returns a single resultset. To copy the resultset to a table, you can use the following Create table #dbcc_useroptions ([set option] varchar(100), ...

I have seen many newbies asking ''How do I sort the numbers stored in varchar columns?'' Here are some methods declare @t table(data varchar(15)) insert into @tselect '6134' union allselect '144' union allselect '7345' union allselect '109812' union allselect '100074'union allselect '1290' union allselect '45764' --Method 1 select data from ...

As you know, Replicate function is used to repeat a character expression for a specified number of times. But by default the result is converted to varchar of maximum size 8000 when you dont convert the expression to specific type Consider the following example declare @v varchar(max)set @v=replicate('a',50000)select len(@v),datalength(@v) Note ...

Sometimes you may want to run a query against each database Suppose you want to find all dabase names where particular table exists. You can use undocumented procedure EXEC sp_msforeachdb 'SELECT table_catalog FROM ?.INFORMATION_SCHEMA.TABLES where table_name=''your_table'''Because they are undocmented, you cant always rely on them. ...

In addition to INFORMATION_SCHEMA VIEWS, in SQL Server 2005, we can use Object Catalog Views to know more informations about the objects Refer this http://msdn.microsoft.com/en-us/library/ms189783.aspx