Using Row_Number and Over clause without table’s column name

Recently got a requirement to show the data with serial number from function which splits a delimited string and returns individual items. For instance dbo.Split(‘honda’,’bmw’) returns the honda and bmw as rows. Here the challenge is to show the row number along with the split items in the exact order in which the function returns the data without altering the function definition. Using OVER clause on the function column will give ASC/DESC order rather than what we needed. The below is the code which solves this issue without changing the existing split function.

SELECT 
    ROW_NUMBER() OVER (ORDER BY (SELECT 0))[RowNum],
    items [Item]
FROM 
    dbo.split('Honda,Bmw,Mazda',',')

Below is the result

RowNum    Item

1             Honda

2             Bmw

3             Mazda

Hope this helps.

Advertisements

Shortcut key for entering NULL value into a table cell in SSMS

Here is a simple tip to enter null value in SQL Server Management Studio. It is a common case to enter null value into a cell. By default the cell values will be displayed with null if the table data is opened in SSMS. If we need to enter null value into a cell which has some data already into it we should explicitly type as NULL. Note that this should be in CAPS and if we give as null, SSMS assumes this null as a string value. A shortcut key for entering NULL value is ctrl+0 [ctrl key+zero].

Hope this helps


Populating Sequence values in a column to an already existing table

Recently we got a requirement to add a new identity column to an already existing table and to assign the sequential values to it. The table has thousands of rows already and manually entering the values into this column isn’t the right solution. The below query which automatically updates all the rows in a single shot has done the trick.

To do this, add the required column and update the column with the below query (change the table name and the column name as per your context) before making it as Identity column.

declare @value int=0;
update MyTable set @value=MyTableId=@value+1

 

Hope this helps


Custom key board mapping in Sql Server Management Studio to get the results of any table

Here is a simple trick to get the results of any table with custom key mapping in SSMS. With this we can get rid of writing the different Select queries to get the records and can save few key strokes. Below 3 steps will do the trick.

step 1:  Create a stored procedure which accepts the table name, no of rows to be returned and the order of the results

1: CREATE PROCEDURE [dbo].[GetAnyTableRowsInSSMS]
2: (
3: @TableName VARCHAR(20),
4: –No of rows to be returned in the result set
5: @NoOfRows SMALLINT=5,
6: –‘a’ for ascending, ‘d’ for descending
7: @Order VARCHAR(4) =’a’
8: )
9: AS
10: BEGIN
11: IF @Order=’a’
12: SET @Order =’asc’
13: ELSE
14: SET @Order=’desc’;
15: DECLARE @ResultQry NVARCHAR(100);
16: SET @ResultQry= ‘SELECT TOP ‘ + CAST (@NoOfRows AS VARCHAR(5))+ ‘ * FROM ‘ + @TableName + ‘ ORDER BY 1 ‘ + @Order
17: EXEC SP_ExecuteSql @ResultQry
18: END

Step 2: Bind this stored procedure to SSMS custom key as in the below screenshot

Step 3: Execute in the query analyzer with the required parameters

Here we got top 2 rows from user table in ascending order. Like wise by pressing ctrl+5 on “users” will get the top 5 results in ascending order as we have given no of rows as 5 and order as ascending by default.

Points to Ponder:

1. Place this stored procedure in some test database rather the original database to avoid accidental copy into production.
2.  The results will be sorted by considering the first column the table. You can change this by passing the column name also as the parameter to the Stored Procedure.

Getting Top 1 and Bottom 1 results with a simple SQL query

Whenever there is a requirement to get a top 1 and the bottom 1 records in a single query, we start writing the query as below

SELECT TOP 1 * FROM Customers order by CustomerID  ASC
UNION ALL
SELECT TOP 1 * FROM Customers order by CustomerID DESC

But this will result below error.

Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword ‘UNION’

This will be resolved simply by making use of CTEs which are introduced in SQL2005 and higher

;WITH TopBottomRows
AS
(
SELECT TOP 1 * FROM Customers order by CustomerID  ASC
UNION ALL
SELECT TOP 1 * FROM Customers order by CustomerID DESC
)
SELECT * FROM TopBottomRows

Hope this helps..