SQL Server 2008

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 }

SSIS – Pass a date variable to a OLE DB Source in a Data Flow

by Ali Raza Zaidi on February 28, 2012

Currently i have to make query dynamic, i have to  pass date variable to query to execute for specific date range, and this date range could be very time by time, Previously I tried for script component or expression of variable to build query but failed, but then I found command option of  oledb command. I placed query there with “?” sign at places where my parameter will replace the date value . Consider following

 

When I passed query there Parameter button on wizard activate.  I set there variable to parameter .

 

 

This approach is work fine for me, but if you have places are more then 3 to 4 , query parsing failed. so you have to write query in such a way that minimum number of places parameter are used.

{ 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 }

				

			


			

				

					
How to create dynamic connection string with variables  SSIS

					
by Ali Raza Zaidi on February 22, 2012

				

				


Create Parameter on package level with string datatype with following Name, set default value with respect to your machine configuration I set according to mine


VServerName =”pc-aliraza”

VSQLUserName=”aliraza”

VSQLDbName =”Nwind”

VSQLPassword =”123”


If  integrated securtity with database is false or you connect with windows authentication following is the expression you have to set  expression at connection string property.


 


"Data Source=" + @[User::VServerName]  + ";Initial Catalog=" + @[User:: VSQLDbName]   + ";Provider=SQLNCLI10.1;Integrated Security=SSPI;"


 


If you want to connect with database with  Sql server authentication you have to use following expression string at  expression of connection string at connection in SSIS.


"Data Source="+ @[User::VServerName] +";User ID="+ @[User::VSQLUserName]  +"Password= "+ @[User::VSQLPassword] +" ;Initial Catalog=" + @[User::VSQLDbName] + ";Provider=SQLNCLI10.1;Persist Security Info=True;"


{ 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 }

				

			


			

				

					
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 }

				

			


			

				
← Previous Entries