some useful sql tricks

There are many ways which can help us to save our time to write some repeated code or to do some repeated action, in this post we will see some very common code which we need very frequently in our development life. I am not saying that after this post you will save your half say work every day but hope fully you will get something for you which can may you happier, so let’s start one by one

To create a new table from another existing table without data:

Select * Into [new_table_name]
From [existing_table]
Where 1 > 2  -- it will never be true so no data will be copied

Where clause is not needed here if we want to copy entire data from existing table. One more thing to note, if we don't want to copy the existing table as it is, say we want some selected columns and want to add some my own columns then we can use column names in our select statement, and to add our own column use null as [columnname] or some value as [columnname]

How to copy records from an existing table to another existing table:

Insert Into [destination_table]
Select * from [source_table]

In this case both table should exists, if we want only those records which is useful for us then we can put here a where class at the end to select only those records.

Conditional where clause: suppose on our page there is a drop down "Gender" having values, 0: All, 1: Male, 2: Female, so in our procedure we would need to by pass the gender checking if value is all (0) so how to do this in procedure, it simple, see this

Where (@gender = 0 OR [Gender] = @gender)

When user will select 0:All where clause will be 0 = 0 or Gender = 0 so true

When user will select 1: Male where clause will be 1 = 1 or Gender = 1 so true for only those records where gender is 1 and similarly for Female.

Row Number in SQL Server: how to get the row number in our result

SELECT  ROW_NUMBER() OVER(ORDER BY [col1], [col2]......)  AS [RowNumber],
  * From table_name

Dynamic query: In dynamic query we need to check whether we have to add AND or not, so best would be to add where 1 = 1 and before every condition put AND

With Statement: Some times we need to use view to calculate complicated data but with statement no need to create a separate view but directly use With, let's see this for a paginated gridview data procedure

WITH Temp AS  
 (  
  Select ROW_NUMBER() OVER(ORDER BY CreationDate DESC) [RowNo]  
   , C.BlogCommentID  
  From BlogComments C  
  Where C.CreationDate BETWEEN @StartDate AND  @EndDate
 )  
 Select *
 From BlogComments C  
  INNER JOIN Temp ON Temp.BlogCommentID = C.BlogCommentID  
 Where Temp.[RowNo] BETWEEN  @startRowIndex + 1 AND @startRowIndex + @PageSize  
 ORDER BY Temp.[RowNo]

In this example we used with to create a Temp table which can be used like a view.

Concatenate strings without using loops:

DECLARE @str varchar(max) 
SELECT @str = coalesce(@str + ', ' + customer_name, customer_name) 
FROM Customer_Name Order By customer_name
PRIINT @str

Use Auto generated columns in Grid View with our given column name:

Select col1 AS [Customer Id]
   , col2 AS [Customer Name]
   , col3 AS [Customer City]
FROM Customers

We can adjust our column heading name in SQL Server so auto generated column can be used and show a proper heading.

Ali Adravi Having 13+ years of experience in Microsoft Technologies (C#, ASP.Net, MVC and SQL Server). Worked with Metaoption LLC, for more than 9 years and still with the same company. Always ready to learn new technologies and tricks.
  • sql server
By Ali Adravi On 31 Mar, 13  Viewed: 924

Other blogs you may like

Adding identity to an existing column in SQL Server

We can not directly change a column to identity columns, say we have a primary key column or any normal column in our table and want to make it identity column then we cannot do it directly by altering the table but there are only two way to change column to identity column 1. Create a new... By Ali Adravi   On 02 May 2013  Viewed: 1,084

Get precision and decimal value in SQL Server by using PARSENAME

In SQL server there are decimal and money data type to store precision and scale both together or say decimal values. Suppose we want to get precision and scale separately then how to get it. There are many ways to do this but there is a function in SQL named PARSENAME which can be used to get... By Myghty   On 26 Mar 2013  Viewed: 3,311

Available wildcards in sql server with examples

To write a good performing procedure or function we should be aware about available wildcards in SQL Server. I found most developer depends on ‘%’ wildcard only while we have others as well. In this article we will discuss all the available wildcards in detail with examples. First of all let’s see... By Dev D   On 25 Feb 2013  Viewed: 667

Concatenate multiple columns in SQL Server with NULL value

When we need to concatenate two columns simply we can use `+` sign and that is correct, but what if any of them is null, Will it return what we want, NO, it will return null. So let's discuss how we should concatenate two or more columns without creating extra space or extra comma problem. Let's... By Ali Adravi   On 13 Feb 2013  Viewed: 12,557

Error cannot be caught by a TRY…CATCH Construct in SQL Server

TRY…CATCH constructs do not trap the following conditions: - Warnings or informational messages that have a severity of 10 or lower. - Errors that have a severity of 20 or higher those stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of... By Ali Adravi   On 28 Dec 2012  Viewed: 579