Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

Temporary table's

In SQL server there is important concept called temporary tables. With temporary table you can store and process intermediate results by using the same selection, update, and join capabilities that you can use with typical SQL Server tables.

1.  These tables can be created at run time and can do the all kinds of operations that one normal table can do.
2.  SQL server provide two types of temp table (local and global), based on these two type the scope is limited.
3.  Temporary tables are created inside tempdb database.

Types:

Local Temp Table: Hash (#) sign is used while creating local temporary table, use hash (#) sign before table name.   Local temporary tables are only available to the current connection to the database for the current user. They are automatically deleted when the user disconnects from instances.  

Example:

---Create local temp table using Create DDL command
CREATE TABLE #TempProducts(
            [ProductID] [int]  NOT NULL,
            [ProductName] [nvarchar](100) NOT NULL,
            [ImagePath] [nvarchar](max) NULL,
            [UnitPrice] [float] NULL,
            [CategoryID] [int] NULL
            )
 
INSERT INTO #TempProducts(
            [ProductID] ,
            [ProductName],
            [ImagePath],
            [UnitPrice],
            [CategoryID]
            )
SELECT [ProductID],[ProductName],[ImagePath] ,[UnitPrice],[CategoryID] FROM Products
 
SELECT * FROM #TempProducts
 


---Create local temp table using Select * into
SELECT * INTO #ProductTemp From Products
 
SELECT * FROM #ProductTemp


Global Temp Table: Double Hash (##) sign is used while creating local temporary table, use double hash (##) sign before table name.

Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.

Example:

---Create global temp table using CREATE DDL command
CREATE TABLE ##TempProductsGlobal(
            [ProductID] [int]  NOT NULL,
            [ProductName] [nvarchar](100) NOT NULL,
            [ImagePath] [nvarchar](max) NULL,
            [UnitPrice] [float] NULL,
            [CategoryID] [int] NULL
            )
 
INSERT INTO ##TempProductsGlobal(
            [ProductID] ,
            [ProductName],
            [ImagePath],
            [UnitPrice],
            [CategoryID]
            )
SELECT [ProductID],[ProductName],[ImagePath] ,[UnitPrice],[CategoryID] FROM Products
 
SELECT * FROM ##TempProductsGlobal

 

---Create global temp table using Select * into
SELECT * INTO ##ProductTempGlobal From Products
 
SELECT * FROM ##ProductTempGlobal

 

All temporary  tables are created in TempDB system database:

AND & OR operators

The logical AND & OR operators are used to filter records based on two expression or more than one conditions.

AND Operator:

Combines two expressions and return a Boolean value, if both expressions are true it returns true else false. When more than one logical operator is used in a statement, the AND operators are evaluated first. You can change the order of evaluation by using parentheses.

Syntax:

boolean_expression AND boolean_expression 

Example:

Declare @ActiveProductInfo int
Declare @ActiveCategoriesInfo int
--Check active products count
SELECT @ActiveProductInfo=count(ProductID) FROM products
 
-- Check active categories count
SELECT @ActiveCategoriesInfo=count(CategoryID) FROM Categories WHERE  DeletedDate IS NULL
 
--If found active products and active categories ,ruturn data using inner join with only active categories   
IF @ActiveProductInfo>0 AND @ActiveCategoriesInfo>0
BEGIN
SELECT ProductName AS [Product Name] ,UnitPrice AS [Price]  ,CategoryName AS [Product Category],ImagePath AS [Image]
 FROM Products P INNER JOIN Categories C ON P.CategoryID=C.CategoryID  Where C.DeletedDate is null
END
 

 

OR Operator:

Or operator combines two conditions and returns TRUE when either of the conditions is TRUE. When more than one logical operator is used in a statement, OR operators are evaluated after AND operators. However, you can change the order of evaluation by using parentheses.

Syntax:

boolean_expression OR boolean_expression 

Example:

Declare @ActiveCategoriesInfo int
 
Declare @ExpiredCategoriesInfo int
 
--Select count of active  categories
SELECT @ActiveCategoriesInfo=count(CategoryID) FROM Categories WHERE  DeletedDate IS NULL
 
--Select count of expired categories
SELECT @ExpiredCategoriesInfo=count(CategoryID) FROM Categories WHERE  DeletedDate IS NOT NULL
 
--If found active categories or Disabled categories ,ruturn data using inner join with all data    
IF  @ActiveCategoriesInfo>0 OR @ExpiredCategoriesInfo>0
BEGIN
SELECT ProductName AS [Product Name] ,UnitPrice AS [Price]  ,CategoryName AS [Product Category],ImagePath AS [Image]
 FROM Products P INNER JOIN Categories C ON P.CategoryID=C.CategoryID 
END
 


Combined use of AND operator & OR operator:

--If found  active categories or Disabled
categories count is zero ,ruturn data using inner join with active categories    
IF  (@ActiveCategoriesInfo>0 AND @ActiveCategoriesInfo>0) OR @ExpiredCategoriesInfo=0
BEGIN
SELECT ProductName AS [Product Name] ,UnitPrice AS [Price]  ,CategoryName AS [Product Category],ImagePath AS [Image]
 FROM Products P INNER JOIN Categories C ON P.CategoryID=C.CategoryID  Where C.DeletedDate is null
END
 
 

CHECK constraints

CHECK constraints are rules used to limit values that are accepted by one or more columns.

For example, Products with unit price between INR 5000 to INR 100000 are allowed only. This prevents product unit price from being entered beyond the regular price range.

   The logical expression would be the following: UnitPrice>= 5000 AND UnitPrice <= 100000

Points to Remember:

1.  If you define a CHECK constraint on a single column it allows only certain values for this column.
2.  You can apply multiple CHECK constraints to a single column.
3.  You can also apply a single CHECK constraint to multiple columns by creating it at the table level.
4.  CHECK constraints reject values that evaluate to FALSE.
5.  CHECK constraints are not validated during DELETE statements.
6.  A CHECK constraint returns TRUE when the condition it is checking is not FALSE for any row in the table.

 

Using SQL Server Management Studio:

1.  In Object Explorer, expand the table to which you want to add a check constraint, right-click   Constraints and click New Constraint.


2.  In the Check Constraints dialog box, click in the Expression field and then click the ellipses (…).


3.  In the Check Constraint Expression dialog box, type the SQL expressions for the check constraint. For example, to add unit price between INR 5000 to INR 100000, add Expression in dialog box which prevents product unit price from being entered beyond the regular price range.


4.  Click OK.

 

5.  In the Identity category, you can change the name of the check constraint and add a description (extended property) for the constraint.

6.  In the Table Designer category, you can set when the constraint is enforced.

7.  Check constraint.


Check constraints using T-SQL:

How to create check constraint with Create Table?

Single column:

CREATE TABLE [dbo].[Products](
            [ProductID] [int] NOT NULL Primary Key,
            [ProductName] [nvarchar](100) NOT NULL,
            [Description] [nvarchar](max) NOT NULL,
            [ImagePath] [nvarchar](max) NULL,
            [UnitPrice] [float] NOT NULL CHECK (UnitPrice>= 5000 AND UnitPrice <= 100000 ),
            [CategoryID] [int] NULL
)
GO

Multiple columns:

CREATE TABLE [dbo].[Products](
            [ProductID] [int] NOT NULL Primary Key,
            [ProductName] [nvarchar](100) NOT NULL,
            [Description] [nvarchar](max) NOT NULL,
            [ImagePath] [nvarchar](max) NULL,
            [UnitPrice] [float] NOT NULL,
            [CategoryID] [int] NULL,
            CONSTRAINT CK__ProductsUnitPrice  CHECK (UnitPrice>= 5000 AND UnitPrice <= 100000 AND [CategoryID]<20)          

)

How to add Check Constraint with alter table?

Single column:

ALTER TABLE Products
ADD CHECK(UnitPrice>=5000 AND UnitPrice <=100000)
OR
ALTER TABLE Products ADD CONSTRAINT CK__ProductsUnitPrice
CHECK (UnitPrice>= 5000 AND UnitPrice <=100000)

Multiple column:

ALTER TABLE Products
ADD CHECK (UnitPrice>=5000 AND UnitPrice <=100000 AND [CategoryID]<20)
OR
ALTER TABLE Products
ADD CONSTRAINT CK__ProductsUnitPrice CHECK (UnitPrice>= 5000 AND UnitPrice <= 100000 AND [CategoryID]<20)                      

 

Hot to drop drop Check Constraint?

ALTER TABLE Products
DROP CONSTRAINT CK__ProductsUnitPrice
GO