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
Hope this helps.
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
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
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
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’
11: IF @Order=’a’
12: SET @Order =’asc’
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
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.
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..