SqlServer-QA.net

  Row_number() function is used to generate row number to rows based on the column which is ordered What if you want to generate row number without ordering any column Here is the method (Consider Suppliers table from northwind database) 1 Assign dummy column with literal 0 and order it by that columnselect row_number() over(order by ...

In Forums sometimes I read questions on how to find out the difference between two times which are stored as float Here are some possible answers declare @t1 char(5), @t2 char(5), @sum float select @t1='12.56', @t2='7.58' set @sum=cast(@t1 as float)-cast(@t2 as float) --Method 1 : Convert to Datetime select ...

If you want to know the datatype or length of a column, you can query on the system table syscolumns or system view INFORMATION_SCHEMA.COLUMNS But you dont need to query on system objects if you use SQL_VARIANT_PROPERTY function ExampleSELECT     TOP 1        ...

In the post Import/Export to Excel, I showed how to export data to EXCELThe problem that most users find it is it wont include column names in the file and file should exists already with headingsThis procedure would solve that problem   create procedure proc_generate_excel_with_columns(        ...

The TOP Clause returns top rows from the table based on the number or percentage valueWhat if you want to have TOP N rows for each group?The following explains it (The purpose is to return top 3 orders for each customer based on the most recent orderdate from the table Orders in Northwind database)1 Use IN select ...

In this post splitting-csv-to-columns I showed how to split delimited data into multiple columnsSimon in his post Set-based-splitting-of-delimited-strings-to-columns showed how to use that in a set based approach which would work on a set of data.In his approach he has limited it to maximum of four columnsI have modified his approach to work for ...

In the Part 1, I expressed my opinion on having the alias names preceded by the keyword AS Adding to the examples given at the link, consider this example too SELECT         10number,        10.number,        10 ...

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 ...