Download SQL Server 2008 Samples

Now we have a new location for SQL Server 2008 Samples. The new location is on the codePlex web site. Please find the below link to download the SQL Server 2008 Samples
http://sqlserversamples.codeplex.com/Wikipage

Quickly :
Download AdventureWorks Family Database:
http://msftdbprodsamples.codeplex.com/releases/view/37109
Download All the samples together without AdventureWorks
http://www.codeplex.com/Release/ProjectReleases.aspx?ProjectName=SqlServerSamples

Happy Learning !!!

Support for DTS in SQL Server 2008

As we all know that SQL Server 2000 Data Transformation Services is already deprecated but SQL Server 2008 still supports DTS (Limited Functionality). SQL Server 2008 provides optional 32 bit management, run-time, and design-time support for packages that were created by using the DTS tools and object model.

Note:

  1. Please note that not all the functionality of DTS is not supported in SQL Server 2008.
  2. To get the DTS support in SQL Server 2008 you might have to manually install additional, optional components that are not available during setup.

For more information on which are the functionalities of DTS is supported in SQL Server 2008, please visit the below link

http://msdn.microsoft.com/en-us/library/bb500440(v=SQL.105).aspx

Happy Learning !!!

Get the list of all the constraints in a table in SQL Server

Often we think of How to get a list of all the constraints in a table. The below post answers that question. We can make use of INFORMATION_SCHEMA views to get the details of the meta data in SQL Server.

    • INFORMATION_SCHEMA view is contained in each database.
    • Each INFORMATION_SCHEMA view contains meta data about each object stored in that particular database.
      • information schema views are defined in a special schema named INFORMATION_SCHEMA.

The below query will list all the details about the meta data available in that particular database.

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS;

For example the above query will give you the below output i.e. List of all the constraints in a particular database. You can even filter data by specific constraint type , schema name, table name etc.

 

image

Example 2: List all the constraint in a table Employee under Schema STTest.
Answer: Following query will result the correct answer

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE TABLE_NAME =‘Employee’ AND TABLE_SCHEMA =‘STTest’;

 

image

Note: Here TABLE_CONSTRAINTS is a view defined in a special schema named INFORMATION_SCHEMA.

Happy Learning !!!

How to Get the Current Database Name, Database ID in SQL Server

The DB_NAME() function returns the current database name. You can use this in the following way.
 

SELECT DB_NAME();

Get Database ID: The DB_ID() function returns the database ID for the given database name. You can use this in the following way.

Syntax : DB_ID([‘database name’]) 

Example:

SELECT DB_ID(DB_NAME());

 

What is Package in SSIS?

Let’s understand the basics of Package in SSIS. This could also be a good interview question for SSIS.

  • A package is a unit of work that is retrieved, executed and saved.image
  • It’s a collection of various objects i.e. Control Flow elements, data flow elements,event handlers, variables, logging which together builds a package.
  • Various functionalities can be added into package using Control Flow elements, Data Flow elements.
  • We can set the security settings on the package.
  • We can also incorporate transactions in packages to better control the package execution.
  • Using connection managers in package helps us to connect with different data sources to extract and load data.
  • Logging in packages allows us to better control the package execution and also enables efficient error handling.
  • Event handlers handles the events raised during the package runtime.

 

Happy Learning !!!