HI WELCOME TO SIRIS
Showing posts with label SQL server. Show all posts
Showing posts with label SQL server. Show all posts

Using CASE to create new column based on specific data in a column

Leave a Comment
SELECT field1,
       field2,
       field3,
       fieldname = CASE [field4] 
        WHEN '9' THEN 'Semi Trailer'
        WHEN '7' THEN 'Pole Trailer'
        WHEN '6' THEN 'Other'
        WHEN '5' THEN 'Motor Carrier'
        WHEN '3' THEN 'Full Trailer'
        WHEN '2' THEN 'Dolly Converter'
        WHEN '14' THEN 'Intermodal Chassis'
        WHEN '12' THEN 'Van'
        WHEN '11' THEN 'Truck Tractor'
        WHEN '10' THEN 'Straight Truck'
        ELSE 'Invalid'
       END
FROM [dbo].[kansiris];

Difference between CTE and Temp Table and Table Variable

Leave a Comment
Temp Table or Table variable or CTE are commonly used for storing data temporarily in SQL Server. In this article, you will learn the differences among these three.

CTE

CTE stands for Common Table expressions. It was introduced with SQL Server 2005. It is a temporary result set and typically it may be a result of complex sub-query. Unlike temporary table its life is limited to the current query. It is defined by using WITH statement. CTE improves readability and ease in maintenance of complex queries and sub-queries. Always begin CTE with semicolon.

A sub query without CTE is given below :


  1. SELECT * FROM (

  2. SELECT Addr.Address, Emp.Name, Emp.Age From Address Addr

  3. Inner join Employee Emp on Emp.EID = Addr.EID) Temp

  4. WHERE Temp.Age > 50

  5. ORDER BY Temp.NAME


By using CTE above query can be re-written as follows :


  1. ;With CTE1(Address, Name, Age)--Column names for CTE, which are optional

  2. AS

  3. (

  4. SELECT Addr.Address, Emp.Name, Emp.Age from Address Addr

  5. INNER JOIN EMP Emp ON Emp.EID = Addr.EID

  6. )

  7. SELECT * FROM CTE1 --Using CTE

  8. WHERE CTE1.Age > 50

  9. ORDER BY CTE1.NAME


When to use CTE

  1. This is used to store result of a complex sub query for further use.
  2. This is also used to create a recursive query.

Temporary Tables

In SQL Server, temporary tables are created at run-time and you can do all the operations which you can do on a normal table. These tables are created inside Tempdb database. Based on the scope and behavior temporary tables are of two types as given below-
  1. Local Temp Table

    Local temp tables are only available to the SQL Server session or connection (means single user) that created the tables. These are automatically deleted when the session that created the tables has been closed. Local temporary table name is stared with single hash ("#") sign.

    1. CREATE TABLE #LocalTemp

    2. (

    3. UserID int,

    4. Name varchar(50),

    5. Address varchar(150)

    6. )

    7. GO

    8. insert into #LocalTemp values ( 1, 'Shailendra','Noida');

    9. GO

    10. Select * from #LocalTemp


    The scope of Local temp table exist to the current session of current user means to the current query window. If you will close the current query window or open a new query window and will try to find above created temp table, it will give you the error.
  2. Global Temp Table

    Global temp tables are available to all SQL Server sessions or connections (means all the user). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed. Global temporary table name is stared with double hash ("##") sign.

    1. CREATE TABLE ##GlobalTemp

    2. (

    3. UserID int,

    4. Name varchar(50),

    5. Address varchar(150)

    6. )

    7. GO

    8. insert into ##GlobalTemp values ( 1, 'Shailendra','Noida');

    9. GO

    10. Select * from ##GlobalTemp



    Global temporary tables are visible to all SQL Server connections while Local temporary tables are visible to only current SQL Server connection.

Table Variable

This acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory. This also allows you to create primary key, identity at the time of Table variable declaration but not non-clustered index.

  1. GO

  2. DECLARE @TProduct TABLE

  3. (

  4. SNo INT IDENTITY(1,1),

  5. ProductID INT,

  6. Qty INT

  7. )

  8. --Insert data to Table variable @Product

  9. INSERT INTO @TProduct(ProductID,Qty)

  10. SELECT DISTINCT ProductID, Qty FROM ProductsSales ORDER BY ProductID ASC

  11. --Select data

  12. Select * from @TProduct


  13. --Next batch

  14. GO

  15. Select * from @TProduct --gives error in next batch



Note

  1. Temp Tables are physically created in the Tempdb database. These tables act as the normal table and also can have constraints, index like normal tables.
  2. CTE is a named temporary result set which is used to manipulate the complex sub-queries data. This exists for the scope of statement. This is created in memory rather than Tempdb database. You cannot create any index on CTE.
  3. Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory.
What do you think?
I hope you will enjoy the tips while programming with SQL Server. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.

Calculate Running Total, Total of a Column and Row

Leave a Comment
Many times, you required to show information of each transaction and also keep a Running Total and Final Total like GridView in Asp.Net. In this article, I am going to explain, how can you achieve this using SQL Query in simple and easy way.
Suppose you have the below CustomerOrders table and has the data as shown below:

  1. CREATE TABLE CustomerOrders

  2. (

  3. OrderID int identity,

  4. Amount Decimal(8,2),

  5. OrderDate SmallDatetime default getdate()

  6. )

  7.  

  8. Go


  9. INSERT INTO CustomerOrders(Amount) Values(120.12)

  10. INSERT INTO CustomerOrders(Amount) Values(20.12)

  11. INSERT INTO CustomerOrders(Amount) Values(10.12)

  12. INSERT INTO CustomerOrders(Amount) Values(30.12)

  13. INSERT INTO CustomerOrders(Amount) Values(40)

  14.  

  15. GO


  16. SELECT * FROM CustomerOrders


Calculating Running Total

Let's see how to calculate the running total using SQL Query as given below:

  1. select OrderID, OrderDate, CO.Amount

  2. ,(select sum(Amount) from CustomerOrders

  3. where OrderID <= CO.OrderID)

  4. 'Running Total'

  5. from CustomerOrders CO


Calculating Final Total

Let's see how to calculate the final total using ROLLUP with in SQL Query as given below:

  1. SELECT OrderID, SUM(Amount) AS Amount

  2. FROM CustomerOrders

  3. GROUP BY OrderID WITH ROLLUP


Calculating Total of All Numeric columns in a row

Let's see how to calculate the total of all numeric fields with in a row using SQL Query as given below:

  1. SELECT OrderID, Amount, SUM(OrderID+Amount) AS RowNumericColSum

  2. FROM CustomerOrders

  3. GROUP BY OrderID,Amount

  4. ORDER BY OrderID


What do you think?
I hope you will enjoy the tips while writing query in SQL Server. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.