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 !!!

Download BI Architecture and Design Guide – Microsoft Business Intelligence

Today, I came across very useful design guide for Microsoft BI and thought to share with the readers.
This guide is available for download on CodePlex.com. It contains following chapters.

  1. BI Framework
  2. Data Extraction
  3. Data Staging
  4. Data Transformations and Data Quality
  5. Data Loading
  6. Dimensional Modeling
  7. Data Partitions
  8. Online Analytical Processing (OLAP)
  9. Data Mining

Please visit : http://biarchanddesignguide.codeplex.com/ for more information on the design guide and to download the material.

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());