Script related to DB Index Tune

 

When the data is modified (INSERT, UPDATE or DELETE) the table fragmentation can occurs. To rebuild the index again we used the DBCC DBREINDEX statements can used to rebuilds all the indexes of the table.

The syntax is mentioned bellow

DBCC DBREINDEX

(

        table_name

    [ , index_name [ , fillfactor ] ]

)

    [ WITH NO_INFOMSGS ]

 

table_name

Is the name of the table for which to rebuild the specified index or indexes.

Index_name

Is the name of the index to rebuilds. If the index is not specified it means all the index is going to rebuild.

fillfactors

 is the percentage (%) of space on each index page used storing data when index is rebuild.

WITH NO_INFOMSGS

Suppresses all informational messages that have severity levels from 0 through 10.

sp_updatestats

is used to run the update statistics against all user-defined and internal tables of the current database.

Using this technique, I am trying to create a stored procedure that retunes your index. The stored procedure is ready to use.

The parameters @P_TBLNAME

 If you supply the specified table name, it retunes all the index of the supplied table objects and updates the statistics. If not then it works with all table objects of the current database.

/*

   To Tune the Database. Reindexing The Entire DB

  

   Date: 31-March-2012

   by : joydeep Das

  

  

   EXEC up_RUN_REINDEXTUEN

        @P_TBLNAME=''

       

       

*/

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_RUN_REINDEXTUEN')

      BEGIN

            DROP  PROCEDURE  up_RUN_REINDEXTUEN

      END

 

GO

 

CREATE Procedure [dbo].[up_RUN_REINDEXTUEN]

      (

            @P_TBLNAME  VARCHAR(MAX) =    NULL   

    )

AS

    DECLARE  @v_TblName VARCHAR(MAX)

BEGIN

        -- Cursor Declerations

        IF ISNULL(@P_TBLNAME,'')=''

           BEGIN

                        DECLARE db_cursor CURSOR FOR

                                    SELECT name

                                    FROM    sys.sysobjects

                                    WHERE   xtype='U'

                                                AND name LIKE @P_TBLNAME+'%'

               END

            ELSE

               BEGIN

                    DECLARE db_cursor CURSOR FOR

                                    SELECT name

                                    FROM    sys.sysobjects

                                    WHERE   xtype='U'

                                                AND name = @P_TBLNAME

               END                                   

        -- Cursor Open

        OPEN db_cursor

        -- Fatching the Cursor 

        FETCH NEXT FROM db_cursor

                   INTO @v_TblName

 

        WHILE @@FETCH_STATUS = 0  

                    BEGIN

                    PRINT 'Reindexing Table:  ' + @v_TblName

                    DBCC DBREINDEX(@v_TblName, '', 80)

                   

                    FETCH NEXT FROM db_cursor

                               INTO @v_TblName

              END

                      

       CLOSE db_cursor  

         DEALLOCATE db_cursor

        

         -- update teh Statistics of the Table After Reindex.

         EXEC sp_updatestats

END

 

GO

 

To execute

EXEC up_RUN_REINDEXTUEN

        @P_TBLNAME='My_Table'

 

 

OR

 

EXEC up_RUN_REINDEXTUEN

        @P_TBLNAME=''

 

 

Hope you like that.

 

Posted by: MR. JOYDEEP DAS

  

 

Distributed queries

This article contains a beautiful collection related to distributed query. It is a small contains, hope you like.
Distributed queries access data from multiple heterogeneous data sources. Microsoft SQL Server supports distributed queries by using OLE DB.

Distributed query can be accessed by the:

·         From multiple instances of SQL Server.

·         Heterogeneous data stored in various relational and no relational data sources accessed by using an OLE DB provider.

OLE DB providers expose data in tabular objects called rowsets.
The linked server allows us to create OLE DB provider.

Reference row seats from the OLE DB data sources as tables in Transact-SQL statements. Pass commands to the OLE DB data sources and include the resulting row sets as tables in Transact-SQL statements.

OLE DB defines optional Interface:

·         ITransactionLocal 
Supports local transactions in the OLE DB data source.

·         ITransactionJoin 
Lets the provider join a distributed transaction that includes other resource managers.

Note that any provider that supports ITransactionJoin also supports ITransactionLocal.

Only read operations are allowed against providers that do not support ITransactionLocal. All update operations are allowed against any providers that support ITransactionLocal.
Only read operations are allowed against providers that do not support ITransactionJoin. Providers that do not support any transactions or only support ITransactionLocal cannot participate in update operations.
If SET XACT_ABORT is ON, all update operations are allowed against any providers that support ITransactionJoin. The controlling instance of the Database Engine automatically calls ITransactionJoin in each linked server participating in an update operation to enroll it in the distributed transaction. Microsoft Distributed Transaction Coordinator (MS DTC) then either commits them or rolls them back when the controlling server indicates that the transaction is either committed or rolled back.
If SET XACT_ABORT is OFF, the linked server must also support nested transactions before update operations are allowed. Nested transactions are supported if the provider supports calling ITransactionLocal::StartTransaction when there is already an existing transaction for the session. This allows SQL Server to roll back individual statements in distributed queries without rolling back the entire transaction.

Posted By: MR. JOYDEEP DAS

ACID property

 

In this article I am trying to describe the ACID property of the database in a short and simple manner. Hope you like it.

The AICD stands for

1.    Atomicity

2.    Consistency

3.    Isolation

4.    Durability

 

Let's describe each to understand it properly.

 

Atomicity

 

Modification of the data in the database, either failure or success. The begin of the such modifications starts with BEGIN TRANSACTION and ends with COMMIT TRANSACTION or ROLLBACK TRANSACTION.

 

BEGIN

  BEGIN TRY

        BEGIN TRANSACTION

        <..........>

        COMMIT TRANSACTION

  END TRY

  BEGIN CATCH

        <..........>

        ROLLBACK TRANSACTION

  END CATCH

END

 

Features to consider for atomicity:

• a transaction is a unit of operation - either all the transaction's actions are completed or none are
• atomicity is maintained in the presence of deadlocks
• atomicity is maintained in the presence of database software failures
• atomicity is maintained in the presence of application software failures
• atomicity is maintained in the presence of CPU failures
• atomicity is maintained in the presence of disk failures
• atomicity can be turned off at the system level
• atomicity can be turned off at the session level

 

 

Consistency

 

Only the valid data according to integrity constraints may be committed.

 

Isolation

 

One transaction does not interact with others.

  

Durability

 

When a transaction is committed the data will not be lost.

 

Features to consider for durability:

• recovery to the most recent successful commit after a database software failure
• recovery to the most recent successful commit after an application software failure
• recovery to the most recent successful commit after a CPU failure
• recovery to the most recent successful backup after a disk failure
• recovery to the most recent successful commit after a data disk failure 

 

 

Posted by: MR. JOYDEEP DAS

 

 

Temporary table and Table variable

 

  

In this article I am trying to collects some facts related to temporary table and table variable. So let's start about it.

 

The concepts of the temporary tables, which help developer a great, relax to maintain T-SQL statements. This table is created on run time and can do the all kind of operations that normal table can do. But based on the type of table the scope is limited.

The temp tables are created in the tempdb database. So before starting about temp table, let's take a quick look about tempdb database.

 

Tempdb database

 

It is a system database global resource that is available to all users. Some facts are mentioned bellow.

 

1.     Temporary use objects that are explicitly created such as, global and local   

temporary tables, temporary  stored procedure, table variables or cursors.

2.     Internal objects created by SQL Server database engine. For example work  

tables to store intermediate  result for spools or sorting.

3.     Row versions that re generated by data modifications transactions in a database

that use read-committed using row versioning isolation or snapshot isolation transaction.

4.     Row version that are generated by data modification transactions for feature.

 

 

Type of temporary table

 

Based on behavior and scope the temporary table is divided into two categories.

 

     1.       Local Temp Table

     2.       Global Temp Table

 

Local temp table

 

Local temp tables are only available to the current connection for the user and automatically deleted when the user disconnects from instances. It is started with # sign.

 

Example:

 

CREATE TABLE #tbl_localTemp

             (sRoll  Int,

              sName  Varchar(50),

              sClass Int)

GO

 

Global Temp table

 

Once the table has been created by a connection, like a permanent table it then available to any user by any connection.  It is only be deleted when all connections have been closed. Global temp table name starts with ##.

 

Example:

 

CREATE TABLE ##tbl_localTemp

             (sRoll  Int,

              sName  Varchar(50),

              sClass Int)

GO

 

Some points we must remember related to temporary table

 

 

1.     As they are created in a separate database named tempdb, so additional overhead and can causes performance issue.

2.     Number of rows and columns need to be minimised as we needed.

 

 

Table variable

 

Alternate of the temporary table is table variable. Which can do all kinds of operation we can do with the temporary table.  It is always useful for less data. If result set returns large amount of data we use the temporary table.

 

Example:

 

DECLARE @tblvrbl_local  TABLE

        (sRoll  Int,

         sName  Varchar(50),

         sClass Int)

GO

 

Difference between Temp table and Table Variable

 

The main differences are mentioned bellow.

 

1.       Table variable are transaction neutral. They are variables and are not bound to a transaction

2.       Temp table behave same as normal table and are bound by transaction.

 

Hope you like it. 

 

Posted by: MR. JOYDEEP DAS

 

 

 

Attaching Database Error

 

 

There is a very common error wend going to attach database in SQL Server 2008R2. It generally gives the error

"CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file... (Microsoft SQL Server, Error 5123)"

The problem is due to two different login do detach and attach.

So the files, when detached, were owned by the first login, but attach failed because the login that was used was not the owner of the mdf and ldf files.

We can detach the database by:

EXEC sp_detach_db mydb;

We can attach the database by:

CREATE DATABASE mydb

ON

(FILENAME='C:\MSSQL\mydb.mdf'),

(FILENAME='C:\MSSQL\mydb.ldf')

FOR ATTACH;

 

To solve this problem we have to give the full permission to both mdf and ldf file to attach login name by right clicking the file and selecting property and then security and then adding login name.

Hope you like that.

 

Posted by: MR. JOYDEEP DAS

 

 

 

 

SQL Table Constraints

 

Some of my readers mailed me that,

"Why you write simple article in your web. As you your web contains very good stuff… but some tropics are really simple."

It is facts. I have long list of friends from Linked In web. They send me regular mail to write different types of tropics. From them there is a huge number of junior professional and students. I have to serve them all. Students are a very important reader in my article and I think they really need guideline and I can't ignore them. I also take free class for them in weekends and try to share my knowledge with them. I really like it….

In this article I am trying to illustrate some points related to table constraints. It's not only useful for junior professional but also useful for all readers. So let's starts.

Data integrity rules falls into three categories:

1.    Entity

2.    Referential

3.    Domain

 

Entity integrity

 

Entity integrity ensures that the each row of the database is uniquely identified.  You can specify the PRIMARY KEY constraint in the table.

 

Referential Integrity

 

It ensure that the relationship between tables remain preserved as data is INSERTED, DELETED and MODIFIED.

We can ensure the referential integrity by FOREIGN KEY constraints.

 

Take an example of "Person" and "Orders" table:

The "Persons" table:

P_Id		LastName		FirstName		Address		City
1		Hansen		Ola		Timoteivn 10		Sandnes
2		Svendson		Tove		Borgvn 23		Sandnes
3		Pettersen		Kari		Storgt 20		Stavanger

The "Orders" table:

O_Id		OrderNo		P_Id
1		77895		3
2		44678		3
3		22456		2
4		24562		1

Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons" table.

The "P_Id" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.

The "P_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

The FOREIGN KEY constraint also prevents that invalid data form being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

 

SQL Statements

CREATETABLE Person

                        ( P_id            int                    NOTNULL     PRIMARYKEY,

                          LastName  Varchar(50)   NOTNULL,

                          FirstName  Varchar(50)   NOTNULL,

                         Address       Varchar(100) NULL,

                         City               Varchar(50)   NULL

                        )

GO

CREATETABLE Orders

                        ( O_Id        int NOTNULLPRIMARYKEY,

                          OrderNo  int NOTNULL,

                          P_Id        int FOREIGNKEY REFERENCES Persons(P_Id)

                        )

If the table objects are already created we can use the Alter statements to set the foreign key.

ALTERTABLE Orders

ADDCONSTRAINT fk_PerOrders

FOREIGNKEY(P_Id)REFERENCES Persons(P_Id)

Also we can use CASCADE DELETE with referential integrity. In the example if we delete a specified person from the person table the corresponding Order will be automatically deleted. 

Example:

ALTERTABLE Orders

ADDCONSTRAINT fk_PerOrders

FOREIGNKEY(P_Id)

REFERENCES Persons(P_Id)ONDELETECASCADE

We can also use CASCADE UPDATE with referential integrity. CASCADE specifies that the column will be updated when the referenced column is updated.

ALTERTABLE Orders

ADDCONSTRAINT fk_PerOrders

FOREIGNKEY(P_Id)

REFERENCES Persons(P_Id)ONUPDATECASCADE

You can check if any foreign key is defined in your database with cascading actions usingsys.foreign_keys

SELECT    name AS [Constraint Name],

                    OBJECT_NAME(referenced_object_id) [Referenced Object],

                    OBJECT_NAME(parent_object_id) [Parent Object],

                    delete_referential_action_desc [ON DELETE],

                    update_referential_action_desc [ON UPDATE]

FROM        sys.foreign_keys

Domain Integrity

Domain integrity ensures that the values inside a database follow defined rules of values, range and format. Database can enforce the rules using

1.    CHECK Constraints

2.    UNIQUE Constraints

3.    DEFAULT Constraints

 

Check Constraints

Check constraints contain an expression the database will evaluate when we modify or insert a row. If the expression evaluates to false, the database will not save the row. Building a check constraint is similar to building a WHERE clause. You can use many of the same operators (>, <, <=, >=, <>, =) in additional to BETWEEN, IN, LIKE, and NULL. You can also build expressions around AND and OR operators. We can use check constraints to implement business rules, and tighten down the allowed values and formats allowed for a particular column.

 

Example:

 

CREATETABLE Products_2

(

    ProductID int        PRIMARYKEY,

    UnitPrice money  CHECK(UnitPrice > 0 AND UnitPrice < 100)

)

 

OR

 

ALTERTABLE  Employees_2

ADD  CONSTRAINT CK_HireDate CHECK(hiredate <GETDATE())

 

The UNIQUE Constraints

 

The unique constraints determine that the columns values must be unique. Only one NULL is allowed by UNIQUE constraints.

 

Example:

 

CREATETABLE Products_2

(

    ProductID        int    PRIMARYKEY,

    ProductName  nvarchar(40)   Constraint IX_ProductName  UNIQUE

)

 

OR

ALTERTABLE Products_2

ADD  CONSTRAINT  IX_ProductName UNIQUE(ProductName)

Default Constraints

Default constraints apply a value to a column when an INSERT statement does not specify the value for the column. Although default constraints do not enforce a rule like the other constraints we have seen, they do provide the proper values to keep domain integrity intact. A default can assign a constant value, the value of a system function, or NULL to a column. You can use a default on any column except IDENTITY columns and columns of type timestamp.

Example:

CREATETABLE Orders_2

(

    OrderID          int     IDENTITY    NOTNULL,

    EmployeeID   int                         NOTNULL,

    OrderDate     datetime               NULL  DEFAULT(GETDATE()),

    Freight            money                  NULL  DEFAULT(0)CHECK(Freight >= 0),

    ShipAddress  nvarchar(60)       NULL  DEFAULT('NO SHIPPING ADDRESS'),

    EnteredBy      nvarchar(60)        NOTNULL DEFAULT(SUSER_SNAME())

)

Constraint Maintenance

After creating the constraint, if you think that the you don't needed the constraints you can drop the constraints or disable/enable the constraints.

To DROP the constraints

ALTERTABLE Products

DROPCONSTRAINT CK_Products_UnitPrice

Disable/Enable Constraints

ALTERTABLE Products NOCHECKCONSTRAINT  CK_UnitsOnOrder

ALTERTABLE Products NOCHECKCONSTRAINT ALL

 

ALTERTABLE Products CHECKCONSTRAINT  CK_UnitsOnOrder

ALTERTABLE Products CHECKCONSTRAINT ALL

 

Hope you like it.

Posted by: MR. JOYDEEP DAS