Tag Archives: sql server 2005

Very useful Catalog View in SQL Server 2005:

Catalog View is used to get very crucial and important information about your database and server. There are lots of useful Catalog Views are available in Microsoft SQL Server 2005. Herewith, I am going to explain some of them.
Sys.Databases: this catalog view is useful to get information about all the available databases in sql server instance. It will provide you useful information like database name, database id, date of creation, compatibility level (database is compatible to which version of SQL Server, 90 is for SQL Server 2005), collation name (which language we can use in database) etc.
You can use following query for Sys.Databases:
Select * from sys.databases
GO
Sys.Database_files: this catalog view will give us information about particular database’s file. It will give you list of all files which have been used for database. You will get information about which type of files are there for particular database like LOG, DATA and FULLTEXT etc. Along with type of file, it will give you file name, its physical address on the server, state description whether it is ONLINE or OFFLINE, size of each file, growth rate of file etc.
You can use following query for Sys.Database_Files:
–return all files for Adventureworks database
USE AdventureWorks
SELECT * FROM sys.database_files
GO
Sys.Objects: This catalog will cater your need about all the objects available in database. You can get object name, its parent id(if there is any), type description like whether object is system table or user table or foreign key etc.
Below query will return all user tables:
USE AdventureWorks
SELECT * FROM Sys.Objects WHERE Type_Desc=‘User_Table’
GO
Sys.Key_Constraint: this catalog view will give you details about primary key in your database. You can get Parent name, constraint name, and schema id and type description by below query.
SELECT Object_Name(Parent_Object_Id) as ParentName,name,Schema_id,type_desc FROM sys.key_constraints
Sys.Foreign_Key_Columns: You will get information about available foreign key in your database along with its table name and referenced table name with following query.
SELECT Object_Name(Constraint_Object_Id) as ‘Name’,Object_Name(Parent_Object_Id) AS ‘TableName’,Object_Name(Referenced_Object_id) AS ‘Referenced Table’ FROM sys.foreign_key_columns
Sys.Columns: You will get information about all columns available in all tables for selected database. You will get Table name, column name, and collation name, null value status for column, maximum length and precision of columns by following query.
SELECT Object_Name(Object_Id) as ‘TableName’,Name as ‘ColumnName’,collation_Name,is_nullable,max_length,precision FROM Sys.Columns
There many more catalog views are available in SQL Server 2005, I will cover few more in my later article.
Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Schemas in SQL Server 2005 – Definition of Schema and Use of Schema:

By introducing Schemas’ concept and making User and Schema separate, Microsoft has made drastic change in security model of Microsoft SQL Server 2005. Before Microsoft SQL Server 2005 objects were owned by User or by Schema Object but in Microsoft SQL Server 2005 Microsoft separated Schema and User so now no more objects are owned by user.
Schema is nothing but just one logical name which will going to hold your object. If you are working with many objects than this is the time to wake up and start using powerful feature of SQL Server 2005 “Schemas”. AdventureWorks database which ships with Microsoft SQL Server 2005, is a good example of Schema use.
In AdventureWorks database, you can find many different Schemas like Sales, HumanResource, Person, Production, Purchasing etc. Each schema has meaningful name and it has hold the related object. Like “Sales” schema used to hold all the objects related to sales e.g. “Sales.Customer”.
The schema name is the third part in four part object name. for example
SQLServ.Adventureworks.Sales.Customer
First part in above object is server name, second part is database name, third part is schema name (sales) and last one fourth parts is object name (Customer).
You can view all the schemas available in your database by following query:
–list all the schemas of AdventureWorks database
use adventureworks
select * from sys.schemas
GO
Now let me show you how you can create your own schema in your database to maintain your objects.
–Creating Schema
CREATE SCHEMA Testing
GO

–Creating table which will be hold by schema “Testing”
CREATE TABLE Testing.Test
(
ID INT NOT NULL
)

–Insert data in table with schema name
INSERT INTO Testing.Test
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4

–returning dataset from table with schema name
select * from testing.test

–if you will not give schema name before table name
–you will be greeted with error
–Invalid object name ‘test’.
select * from test
After seeing above error, you might think that it would good if I would not created object with schema, I could use object name alone. But let me tell you one thing if you don’t even specify the schema name while creating the object, it falls under default schema which is “DBO”. If you will create all your objects under “DBO” than it would difficult to handle when you will have large DB. So, it is good practice to use schema name always before object name.
Sometime you wish to delete some schema so before doing that you have to transfer all the object from that schema to another schema. How to do that? We will do it by TRANSFER option in ALTER SCHEMA.
–transfering Test object from Testing schema to DBO
ALTER SCHEMA dbo
TRANSFER Testing.Test
GO

–you will not see any error this time
–if you will run below query.
select * from test

–After making sure that your object (“Test”) is transferred
–you can drop the schema
DROP SCHEMA Testing
Hope you have enjoyed SCHEMA. Do give your feedback for this article.

Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

OUTPUT (Deleted and Inserted) from insert, delete and update statement in SQL-Server 2005 (kind of pseudo table of Trigger):

You may remember pseudo table in trigger from which we can get manipulated data. There was no way out to access that pseudo table outside the trigger before Microsoft SQL Server 2005. The same concept you can get outside of trigger as well in Microsoft SQL Server 2005.

Let us create one table for demonstration

–create first table for demonstration

CREATE TABLE CAR

(

ID int IDENTITY(1,1),

CarName VARCHAR(16),

CarDesc VARCHAR(50)

)

–insert records

Insert into Car (CarName,CarDesc) values (‘Honda Pilot’,‘SUV’)

Insert into Car values (‘Honda CRV’,‘SUV’)

Return data from INSERT statement with INSERTED table

Insert into Car

OUTPUT INSERTED.* –this statement will return all the field of CAR table

— from INSERTED table

values (‘Honda CRV’,‘SUV’)

GO

Return data from DELETE statement WITH DELETED table

DELETE FROM CAR

OUTPUT DELETED.* –this statement will return all records

–which are just deleted based on where condition

where ID=1
GO

Return data from UPDATE statement from INSERTED and DELETED table.

UPDATE CAR SET CarDesc=‘Luxury car’

OUTPUT DELETED.CarDesc as ‘Old Value’, INSERTED.CarDesc as ‘New Value’

WHERE ID=2
GO

Enjoy the power of Microsoft SQL Server 2005

Happy SQLing!!!!!

Reference: Ritesh Shah

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

DML INSERT with multiple ways in SQL Server 2005: (Multiple INSERT statement)

Whoever is aware with SQL-Server must be aware with simple INSERT statement. Generally we used INSERT records in table with either of the following SQL statements.
–create first table for demonstration

CREATE TABLE CAR

(

ID int IDENTITY(1,1),

CarName VARCHAR(16),

CarDesc VARCHAR(50)

)

1.)

–must give column value in sequence of column defined in table.

Insert into Car values (‘Honda CRV’,‘SUV’)

OR

2.)

–you can change the sequence order of column after table name

–and can give value in defined order in INSERT statement

Insert into Car (CarName,CarDesc) values (‘Honda Pilot’,‘SUV’)

3.)

Above are the common INSERT statement and widely used as well. Apart from these, there are several ways for INSERT statement.

With SELECT……..UNION ALL

INSERT INTO Car(CarName,CarDesc)

SELECT ‘Toyota a’,‘toyota’ UNION ALL

SELECT ‘Toyota b’,‘toyota’ UNION ALL

SELECT ‘Toyota C’,‘toyota’

4.)

Suppose you have one more table from which you want to populate your CAR table.

–create first table for demonstration

CREATE TABLE CAR_Master

(

ID int IDENTITY(1,1),

CarName VARCHAR(16),

CarDesc VARCHAR(50)

)

INSERT INTO Car_Master(CarName,CarDesc)

SELECT ‘Mercedes s’,‘Mercedes’ UNION ALL

SELECT ‘Mercedes c’,‘Mercedes’

Now, I will populate CAR table from CAR_Master table.

INSERT INTO Car(CarName,CarDesc)

SELECT CarName,CarDesc FROM Car_Master where CarName like ‘m%’

5.)

If you wish to populate your INSERT statement with stored procedure than do follow the below given query.

–Create SP which will return result set

–NOTE: You can use multiple resultset in one SP

–and all the records will be INSERTed to our table

–Make sure, you have same number of column with same datatype in

–all record set you choose in SP

CREATE PROC CarNames

AS

SET NOCOUNT ON

SELECT CarName,CarDesc FROM Car_Master

RETURN

Now simply, I can run following INSERT query.

INSERT INTO CAR (CarName,CarDesc)

EXEC carnames

Reference: Ritesh Shah

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Full Text Search in SQL Server 2005 Part 3

I have covered basic information about Full Text Search at http://ritesh-a-shah.blogspot.com/2009/03/full-text-search-in-sql-server-2005.html

Configuring and maintaining Full Text Search Catalog at http://ritesh-a-shah.blogspot.com/2009/03/full-text-search-in-sql-server-2005_16.html

And now finally I am going to show you how to select data after configuring the catalog with Full Text Search in Microsoft SQL Server 2005.

Contains:

Use Adventureworks

select * from HumanResources.Employee

WHERE Contains(HumanResources.Employee.LoginID,‘”*je*”‘)

I have used Double Quote and Asterisk as wild card as Full Text Search uses standard DOS convention as a wild card. Above query will return all the records whose “LoginID” column contain “je” anywhere in the data.

Note: I gave “LoginID” column name in CONTAINS but if you wish to search “je” in every column which has been added to index while creating catalog, you can use “*” there.

Use Adventureworks

select * from HumanResources.Employee

WHERE Contains(HumanResources.Employee.*,‘”*je*”‘)

FreeText:

Use Adventureworks

select * from HumanResources.Employee

WHERE freetext(HumanResources.Employee.*,‘jolynn0 david0’)

Contains uses exact word search while FreeText uses fuzzy or approximate word search. Above query will show all the records which either contain “jolynn0” or “david0”.

Reference: Ritesh Shah

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Full Text Search in SQL Server 2005 Part 2

I will be showing how to configure and maintain Full Text Search catalog in Microsoft SQL Server 2005 in this article. However, if you are new to FTS and want to get basic idea about the same then you can refer my previous article at http://ritesh-a-shah.blogspot.com/2009/03/full-text-search-in-sql-server-2005.html

Configuring Full Text Catalog (FTC):

You can use one FTC for one database, one database may have more than one FTC but you can’t use same FTC in other database. It is not sharable. FTC is nothing but just a collection of Full-Text indexes. One FTC may store more than one index for more than one table but each table can belongs to one FTC only. You can’t not create FTC on system table, table variable, views and temporary table.

You can configure Full Text Catalog by wizard of SSMS and from T-SQL Script. Since I am a script bee, I will explain T-SQL method.

–Enable database for FTS

use adventureworks

exec sp_fulltext_database ‘enable’

GO

–creating catalog with the name ‘FirstFTC-AdventureWorks

exec sp_fulltext_catalog ‘FirstFTC-AdventureWorks’, ‘Create’

GO

–mark table for full text search with primary key

exec sp_fulltext_table ‘HumanResources.Employee’,‘Create’,‘FirstFTC-AdventureWorks’,‘PK_Employee_EmployeeID’

GO

–add column to the catalog, NOTE: you can give more then one column also

exec sp_fulltext_column ‘HumanResources.Employee’,‘LoginID’,‘Add’

GO

–activate table for FTS

exec sp_fulltext_table ‘HumanResources.Employee’,‘activate’

GO

–run FTS index on table

exec sp_fulltext_table ‘HumanResources.Employee’,‘start_full’

GO

Wow, you have just successfully created one catalog for Full Text Search now what if you wish to maintain it.

You can maintain Full Text Search in two ways. “Incremental” and “Change Tracking and background population”

If you have Times stamp column in your table and you want to update your index at specific time, may be in the evening every day, you can use incremental.

If you wish SQL-Server to track back and keep watch on FTS enabled table and update the index automatically as and when data gets changed than you should go for “Change Tracking and Background population”.

— start incremental index update at specific time

exec sp_fulltext_table ‘HumanResources.Employee’,‘start_incremental’

GO

— start tracking and background update index automatically

exec sp_fulltext_table ‘HumanResources.Employee’,‘start_change_tracking’

exec sp_fulltext_table ‘HumanResources.Employee’,‘start_background_updateindex’

GO

–deleting catalog with the name ‘FirstFTC-AdventureWorks

exec sp_fulltext_catalog ‘FirstFTC-AdventureWorks’, ‘Drop’

GO

–rebuilding index ‘FirstFTC-AdventureWorks

exec sp_fulltext_catalog ‘FirstFTC-AdventureWorks’, ‘rebuild’

GO

Herewith, I am finishing this article and now finally my next article shows you how to select data with Full Text Catalog in Microsoft SQL-Server 2005

Reference: Ritesh Shah

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Full Text Search in SQL Server 2005 Part 1:

While you think about text searching, LIKE operator come in your mind as you can search text within column with LIKE operator very easily in Microsoft SQL Server 2005. Suppose you have employee table than you may use below query to find first name:
Use AdventureWorks
Select * from Employee where FName LIKE ‘rite%’
GO
But what if you wish to search part of the word? You may go for following query.
Use AdventureWorks
Select * from Employee where FName LIKE ‘%rite%’
GO
Yes, you can use above given query if you wish to find any string data in column contained “rite” but it will terribly slow as Indexes are searchable from the beginning of the words. Searching string within string won’t use B-Tree structure of an index to perform fast index search rather it scan full table and will slow up your search.
You can get rid of above problem by using FTS (SQL 2005 Full Text Search) which is third generation search component. You can find this tool with WorkGroup, Standard and enterprise version of SQL Server 2005.
Microsoft named this service as MSFTESQL (Microsoft Full Text Engine for SQL) which is disabled by default. You have to enable it in order to use it. I will explain you the benefit of the same with example and how to enable it.
First of all let me tell you how you can enable MSFTESQL services.
— Open Service area configuration
— Go to Surface Area Configuration for services and configuration
— You will find “Full-Text Search”, Click on “Start” if it is not started yet.

After running your MSFTESQL service, you are ready to use Full Text Search functionality of Microsoft SQL Server 2005. Once you enabled FTS, you will get advanced feature like wildcard search, search one word near another word, searching character data with embedded binary objects stored with SQL Server.
I will be writing series of articles for Full Text Catalog. My next article will cover creating and maintaining Full Text catalog.

Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Difference between Microsoft SQL Server 2005 and 2008

This question may rise every time new version comes into the market. People will buy new version if it is going to make their life easy and this is one of the common interview question as well if you wrote in your CV that you know MS-SQL Server 2005 and 2008.

Anyway, let us move on the topic and starts our journey. BTW, if you are interested to know difference between Microsoft SQL Server 2000 and 2005 than do visit http://ritesh-a-shah.blogspot.com/2009/03/difference-between-microsoft-sql-server.html

Basically one can write a book on the differences as Microsoft SQL Server 2008 comes up with lots of new features but we will look at it at glance.

Microsoft has divided new features of SQL-Server 2008 in four parts.

Enterprise Data Platform
Dynamic Development
Beyond Relational
Pervasive Insight

You can dig details about sub-category of all above four categories from Microsoft Website. URL of the same is given at the end of this article. I will explain some of the features which I like most.

New Date and Time Data Types: This feature gave solution to age old problem of Microsoft SQL Server. You can now have only Date data type without any time and you can have only Time data type without having any date stamp. You can have precision up to 100 nanoseconds for Time data type. One more advancement is you will have Date Time Offset which will store UTC time zone aware value.
Table Value Parameter: We had not any way to pass complete table to Stored Procedure in any of the previous version of SQL Server so we have worked with workaround solutions but now we have a way to pass complete table to stored procedure.

Spatial Data: this is really amazing data type. It allows storing Latitude, Longitude and GPS based data entry to be stored.

Encryption: This feature allows encrypting the database backup so that it will prevent from the data tempering.

Office 2007: You can directly export your report from SQL Server Reporting Service (SSRS) to word file and you can use your word and excel file as a source of report in SSRS.

Merge: This is also one of the beautiful features. You can now merge UPDATE, INSERT and DELETE statement with SELECT based on the condition.

Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Different Types of Isolation Levels – Microsoft SQL Server 2005 – Part 3

This is third and last article of Isolation series. I will be introducing Snapshot Isolation Level (SI) and Read Committed Snapshot Isolation (RCSI). Both these isolation levels are new in Microsoft SQL Server 2005. These are amazing new feature and I would love to work with it because these are mandatory for Row Level Versioning. My next article will be on Row Level Versioning.

Snapshot Isolation Level: As I just wrote SI works with row level versioning whenever any modification made on the data, SQL Server stores consistence version of record in version store. All these activity will be done in TempDB database so you have to have enough space in TempDB to store your transaction. You have to enable your database for SI as it is disable by default because of performance issue.

Read Committed Snapshot Isolation Level: RCSI is an advanced version of Read Committed Isolation which we have seen in my previous article. You can’t set RCSI at session level, you must set it at database level this is one of the difference between RCSI and Read Committed Isolation. You will not get conflict detection in Read Committed Isolation whereas you will get it in RCSI.

I will show you practical usage of this Isolation Level in my next article which will cover Row Level Versioning.

Reference: Ritesh Shah

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Different Types of Isolation Levels – Microsoft SQL Server 2005 – Part 2

In my previous article, I introduced different types of ISOLATION LEVEL available in Microsoft SQL Server 2005. Let me introduce you some details about following four isolations.

Read UnCommitted (no lock) Isolation Level

Read Committed Isolation Level

Repeatable Read Isolation Level

Serializable Isolation Level

All of the above isolation was available in SQL-Server 2000 and we have it in SQL-Server version 2005 as well.

Note: I will be using following table and its records in my series of Isolation Levels article.

–CREATING FIRST ORDER TABLE

USE [AdventureWorks]

GO

/****** Object: Table [dbo].[Orders] Script Date: 03/08/2009 12:24:39 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Orders](

[OrderID] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[OrderDate] [datetime] NOT NULL,

[CompanyName] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

PRIMARY KEY CLUSTERED

(

[OrderID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING ON

GO

INSERT INTO Orders

SELECT ‘A1000’,’03/13/2009′,‘abc’ UNION ALL

SELECT ‘A1001’,’03/14/2009′,‘xyz’

GO

Read Uncommitted Isolation Level: This isolation level also known as “No Lock” (a.k.a Lowest Level Isolation) as it allows data modified by other transactions can be read by the current transaction even before transaction gets committed. This isolation level does not issue shared locks. It does not prevent other transactions from modifying the data that is being modified by the current transaction. This may raise an issue of read data which is still not committed, once we read it and user may roll back it but we don’t know about that as we have already received record sets. This situation is often called as “Dirty Reads” along with dirty reads; this isolation also produces some concurrency problem like lost updates, nonrepeatable reads, phantom reads.

Let us see how we can practically see what could happen with Read Uncommitted Isolation Level.

Open two instances of SSMS. Let’s call first instance as I1 and second instance as I2.

In I1, run following update query without committing the transaction.

BEGIN TRAN a

— update records withou commiting or roll back transaction

UPDATE Orders

SET OrderDate = ’01/11/2009′

WHERE [OrderID] = ‘a1000’

Now, try to run “Select” statement on Orders table in I2, you won’t get the results as Orders table is locked. Now, set Read Uncommitted Isolation level in I2 and run select statement.

use adventureworks

go

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

GO

select * from Orders

You will get updated data but think, if user 1 from I1 has rolled back data and we have already taken our critical decision after seeing data we got before roll back, what would happen??? It may lead us to some critical disaster. So, beware of using this isolation level as it is lowest level and creates some concurrency problem.

Read Committed Isolation Level: Read Committed Isolation Level is the default isolation level in SQL-Server 2005. It keeps you free from Dirty Read problem like we had it in previous isolation level. You must have observed that when we had not set any isolation level, we had not get any results set in I2 as table was locked due to non-committed data. By making table lock, RCIL made you free from dirty reading but it has some concurrency issue and we may lose update some time.

Repeatable Read Isolation Level: Repeatable Read isolation level makes sure that the data is being read and update by current transaction couldn’t be update by any other transaction until the current transaction completes. Unlike, read committed isolation level, it doesn’t release shared lock once data is read but it do locked it till the transaction is completed. Because of this property, it prevents from the “Lost Update”.

Serializable Isolation Level: This isolation level is quite restrictive isolation level and it avoids all concurrency level issue. It applies HOLDLOCK on the table. It never allows data to be read or modified in the transaction which is not been committed by another transaction and at the other end; it will not let any other transaction to modified or read data which is being read or modified by the current transaction. Not only this but if new record comes in, it falls in the same lock.

All of the above isolation level was supported by MS-SQL Server 2000 and also comes with MS-SQL Server 2005. In my next article I will give detail introduction of two new isolation level comes with MS-SQL Server 2005. 1.) Snapshot Isolation Level 2.) Read Committed Snapshot Isolation Level.

Reference: Ritesh Shah

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah