TSQL

describe table tsql

by Ali Raza Zaidi on March 13, 2012

Currently I was searching describe  like function, which used in Oracle PLSQL to get the detail of table.  I found equvilent function in TSQL “sp_help”.

you can used it as

sp_help <table_name>

 

i.e

sp_help studentdet

{ Comments on this entry are closed }

Delete with identity reset

by Ali Raza Zaidi on February 27, 2012

I am dropping the rows but insertion start with next to last max identity . Something was missing in my knowledge. To reset Identity i have use one extra statement, other option is use trunc  statement to drop rows.  but if i have too drop rows with delete statement and reset the identity what should do,   i have to use

DBCC CHECKIDENT("table_name", RESEED, "reseed_value")
This will reset identity back to 1. for example




DELETE from tblstudent;

DBCC CHECKIDENT("tblstudent, RESEED, 0)










{ Comments on this entry are closed }

				

			


			

				

					
How to convert date to interger In TSQL

					
by Ali Raza Zaidi on February 27, 2012

				

				


During my assignment I have to generate Integer value from datetime filed for Date Dimension . I found excellent  id this way.


 


 


CAST(CONVERT(varchar(8),StartDATE,112) AS int) DateDateKey,



 


The whole Query is  as follow.


 



SELECT

CAST(CONVERT(varchar(8),BEGDATE,112) AS int) DateDateKey,

,Name

,Address

,Joindate

,EndDate

FROM dbo.from Student

 



 :)  its works for me.


{ Comments on this entry are closed }

				

			


			

				

					
Using a Common Table Expression (CTE) with an Delete statement

					
by Ali Raza Zaidi on February 14, 2012

				

				


Delete Part of ETL Process


If any row is deleted at source table, but destination table rows  exits. So I did to make a inner join with source table with destination table I used right join in this case, so I got all rows from destination which I placed at right side of query. The result set I got with null at left side form source table. I filter the result set and got all the Keys which have null at source table. And call delete at destination on filter keys.


My common table Expression for Delete will be as follow


 


with DeleteRows as

(

SELECT     sourceEmp.empid, DestEmployee.SourceEmpId

FROM         TSQLFundamentals2008.HR.Employees sourceEmp right join

TSQLFundamentals2008DW.HR.DimEmployees DestEmployee

on

sourceEmp.empid = DestEmployee.SourceEmpId

)

--select SourceEmpId from DeleteRows where empid is null

delete from TSQLFundamentals2008DW.HR.DimEmployees

where TSQLFundamentals2008DW.HR.DimEmployees.SourceEmpId in (

select SourceEmpId from DeleteRows where empid is null)



{ Comments on this entry are closed }

				

			


			

				

					
Using a Common Table Expression (CTE) with an UPDATE statement

					
by Ali Raza Zaidi on February 14, 2012

				

				


For update part of ETL Process I have to write another Common table expression to get  all records where are changed at source to run update query on destination table


 


My update part of basic ETL using TSQL as


 


with ChangedRows as

(

 

SELECT     sourceEmp.empid, sourceEmp.lastname, sourceEmp.firstname, sourceEmp.title, sourceEmp.titleofcourtesy, sourceEmp.birthdate, sourceEmp.hiredate, sourceEmp.address, sourceEmp.city, sourceEmp.region, sourceEmp.postalcode, sourceEmp.country, sourceEmp.phone, sourceEmp.mgrid,DestEmployee.SourceEmpId

FROM         TSQLFundamentals2008.HR.Employees sourceEmp inner join

TSQLFundamentals2008DW.HR.DimEmployees DestEmployee

on

sourceEmp.empid = DestEmployee.SourceEmpId

where

 

sourceEmp.lastname <> DestEmployee.lastname

or sourceEmp.firstname <> DestEmployee.firstname

or sourceEmp.title <> DestEmployee.title

or sourceEmp.titleofcourtesy <>DestEmployee.titleofcourtesy

or sourceEmp.birthdate<>DestEmployee.birthdate

or sourceEmp.hiredate <> DestEmployee.hiredate

or sourceEmp.address <> DestEmployee.address

or sourceEmp.city <> DestEmployee.city

or sourceEmp.region <> DestEmployee.region

or sourceEmp.postalcode<>DestEmployee.postalcode

or sourceEmp.country <>DestEmployee.country

or sourceEmp.phone<>DestEmployee.phone

or sourceEmp.mgrid <> DestEmployee.mgrid

 

)

update  TSQLFundamentals2008DW.HR.DimEmployees

set    lastname = ChangedRows.lastname

,  firstname = ChangedRows.firstname

,  title = ChangedRows.title

,  titleofcourtesy =ChangedRows.titleofcourtesy

,  birthdate=ChangedRows.birthdate

,  hiredate = ChangedRows.hiredate

,  address = ChangedRows.address

,  city = ChangedRows.city

,  region = ChangedRows.region

,  postalcode=ChangedRows.postalcode

,  country =ChangedRows.country

,  phone=ChangedRows.phone

,  mgrid = ChangedRows.mgrid

from ChangedRows

where TSQLFundamentals2008DW.HR.DimEmployees.SourceEmpId=ChangedRows.empid



{ Comments on this entry are closed }

				

			


			

				

					
Using a Common Table Expression (CTE) with an INSERT INTO statement

					
by Ali Raza Zaidi on February 14, 2012

				

				


My current assignment was to write ETL Process using TSQL.  So I have to get all rows which are newly added to Source Table and are not part of destination table. I wrote simple Common Table Expression to get all rows and insert into destination table.


So part of ETL Process using CTE as


 


with NewRows as

(

SELECT     sourceEmp.empid, sourceEmp.lastname, sourceEmp.firstname, sourceEmp.title, sourceEmp.titleofcourtesy, sourceEmp.birthdate, sourceEmp.hiredate, sourceEmp.address, sourceEmp.city, sourceEmp.region, sourceEmp.postalcode, sourceEmp.country, sourceEmp.phone, sourceEmp.mgrid,DestEmployee.SourceEmpId

FROM         TSQLFundamentals2008.HR.Employees sourceEmp left join

TSQLFundamentals2008DW.HR.DimEmployees DestEmployee

on

sourceEmp.empid = DestEmployee.SourceEmpId

where DestEmployee.SourceEmpId is null

)

INSERT INTO [TSQLFundamentals2008DW].[HR].[DimEmployees]

([lastname]

,[firstname]

,[title]

,[titleofcourtesy]

,[birthdate]

,[hiredate]

,[address]

,[city]

,[region]

,[postalcode]

,[country]

,[phone]

,[mgrid]

,[SourceEmpId])

(select

NewRows.lastname, NewRows.firstname, NewRows.title, NewRows.titleofcourtesy, NewRows.birthdate,

NewRows.hiredate, NewRows.address, NewRows.city, NewRows.region, NewRows.postalcode, NewRows.country, NewRows.phone, NewRows.mgrid,

NewRows.empid  from NewRows)



{ Comments on this entry are closed }

				

			


			

				

					
T-SQL: How to do SQL Server paging with ROW_NUMBER()?

					
by Ali Raza Zaidi on February 7, 2012

				

				


DECLARE @PageNum AS INT;
DECLARE @PageSize AS INT;
SET @PageNum = 2;
SET @PageSize = 10;

WITH OrdersRN AS
(

SELECT     ROW_NUMBER() OVER(ORDER BY DimProduct.ProductKey) AS RowNum,
 DimProduct.ProductKey,
           DimProduct.EnglishProductName as Product,
           DimProductSubcategory.ProductSubcategoryKey as SubCategoryKey,
           DimProductSubcategory.EnglishProductSubcategoryName as SubCategory,
           DimProductCategory.ProductCategoryKey as CategoryKey,
           DimProductCategory.EnglishProductCategoryName  as Category
FROM         DimProduct INNER JOIN
                      DimProductSubcategory ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey INNER JOIN
                      DimProductCategory ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey
                   )
                      SELECT *
FROM OrdersRN
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
      AND @PageNum * @PageSize
ORDER BY ProductKey;


{ Comments on this entry are closed }

				

			


			

				

					
How to get list of tables in Database TSQL

					
by Ali Raza Zaidi on February 7, 2012

				

				


You can use the list down all tables in specific database with this query


select TABLE_NAME from information_schema.tables where Table_Type = 'BASE TABLE'


 


{ Comments on this entry are closed }

				

			


			

				

					
Using WITH (NOLOCK) in T-SQL?

					
by Ali Raza Zaidi on February 7, 2012

				

				


Nolock is T-Sql hint, That used to ignore the locks on table during transactions. It allows retrieving data and did not wait to complete the transaition applied on table. It has some pros can cons


Pros:


    

  • NoLock provides significant improvements on large table, where upserts commands take time.
    • 

  • You can retirve data during the same time while others are performing Insert and update on table.
    • 



Cons:


    

  • Possibility of data that was partially updated or inserted, because you retrieve data during update or insert on table.
    • 

  • It often results in very obscure, hard to reproduce bugs and can cause data to get corrupted.
    • 



{ Comments on this entry are closed }

				

			


			

				

					
How to get Tables list in database TSQL

					
by Ali Raza Zaidi on January 15, 2012

				

				


 


Main object is sys.tables. you can get all information about tables by query on sys.tables.


 


User databasename


Select * from sys.tables;.


 


 


SELECT SCHEMA_NAME(schema_id) AS table_schema_name, name AS table_Name


FROM  sys.tables;


 


 


SCHEMA_NAME convert  schema_id into schema name.


{ Comments on this entry are closed }

				

			


			

				
← Previous Entries