Saturday 31 March 2012

Monthly Summary of My Article (MS SQL 2005/2008)

Month: March 2012

 

Date

Description

Web References

1

30-03-2012

Script related to DB Index Tune

http://sqlservernet.blogspot.in/2012/03/script-related-to-db-index-tune.html

2

30-03-2012

Distributed Partition views

http://sqlservernet.blogspot.in/2012/03/distributed-partition-views.html

3

30-03-2012

Distributed queries

http://sqlservernet.blogspot.in/2012_03_01_archive.html

4

28-03-2012

ACID property

http://sqlservernet.blogspot.in/2012/03/acid-property.html

5

28-03-2012

Temporary table and Table variable

http://sqlservernet.blogspot.in/2012/03/temporary-table-and-table-variable.html

6

27-03-2012

Model Database

http://sqlservernet.blogspot.in/2012/03/model-database.html

7

27-03-2012

OPTIMIZE FOR

http://sqlservernet.blogspot.in/2012/03/optimize-for.html

 

8

26-03-2012

Attaching Database Error

http://sqlservernet.blogspot.in/2012/03/attaching-database-error.html

9

26-03-2012

SQL Table Constraints

http://sqlservernet.blogspot.in/2012/03/sql-table-constraints.html

10

23-03-2012

Materialize view and the performance issue

http://sqlservernet.blogspot.in/2012/03/materialize-view-and-performance-issue.html

11

23-03-2012

Normal Vs Correlated Sub Query

http://sqlservernet.blogspot.in/2012/03/normal-vs-correlated-sub-query.html

12

22-03-2012

Backup and its strategy

http://sqlservernet.blogspot.in/2012/03/backup-and-its-strategy.html

13

22-03-2012

Don't use the KEYWORD in COLUMNS name

http://sqlservernet.blogspot.in/2012/03/dont-use-keyword-in-columns-name.html

14

22-03-2012

DB Restore from Network Drive

http://sqlservernet.blogspot.in/2012/03/db-restore-from-network-drive.html

15

21-03-2012

Cannot open backup device

http://sqlservernet.blogspot.in/2012/03/cannot-open-backup-device.html

16

20-03-2012

Comma delimited string to table

http://sqlservernet.blogspot.in/2012/03/comma-delimited-string-to-table.html

17

20-03-2012

Delete Orphan User

http://sqlservernet.blogspot.in/2012/03/delete-orphan-user.html

18

20-03-2012

What to use ISNULL() or COALESCE()

http://sqlservernet.blogspot.in/2012/03/what-to-use-isnull-or-coalesce.html

19

19-03-2012

NOLOCK is the SQL hint

http://sqlservernet.blogspot.in/2012/03/nolock-is-sql-hint.html

20

15-03-2012

HerichyID in MS SQL 2008

http://sqlservernet.blogspot.in/2012/03/herichyid-in-ms-sql-2008.html

21

15-03-2012

Calculating number of Weekdays

http://sqlservernet.blogspot.in/2012/03/calculating-number-of-weekdays.html

22

14-03-2012

CTE

http://sqlservernet.blogspot.in/2012/03/cte.html

23

09-03-2012

Transaction Isolation Level

http://sqlservernet.blogspot.in/2012/03/transaction-isolation-level.html

24

09-03-2012

How to read Execution Plan

http://sqlservernet.blogspot.in/2012/03/how-to-read-execution-plan.html

25

07-03-2012

FOR | AFTER | INSTEAD OF

http://sqlservernet.blogspot.in/2012/03/for-after-instead-of.html

 

26

06-03-2012

INDEX Scan/Seek

http://sqlservernet.blogspot.in/2012/03/index-scanseek.html

27

03-03-2012

Database Maintenance and Wizard

http://sqlservernet.blogspot.in/2012/03/database-maintenance-and-wizard.html

28

01-03-2012

Cursor Function

http://sqlservernet.blogspot.in/2012/03/cursor-function.html

 

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 Partition views




Distributed Partition views are used to access data that has been horizontally splits or partitioned across multiple tables. Table objects can be located in different database or even separated across multiple servers.

We combine table by partition view by using UNION ALL statements that causes data on separate table to appear as if they were on one table.
There are several restrictions to create this views mentioned bellow.


1.  All the columns of the members table should be included in the views.

2.  The columns are in same ordinal positions in the SELECT statement and have the same 
      data type.

3.  The same columns cannot be used multiple times.

4.  The partition columns cannot be computed, identity, or a time stamp columns.

5. The data value of the partition columns cannot be overlap in the underlying table.

6.  The partition columns must be the members of the primary key of the members table.

7.  Members table of the partition views need CHECK constraint of the partition columns.

8.  There is no index of computed columns of the members table.

9.  The primary key columns of the members table should have the same.

10. The entire member table should have the same ANSI PADDING.



If you are creating a distributed partitioned view, each member table is on a separate member server. For the greatest location transparency, the name of the member databases should be the same on each member server, although this is not a requirement.

Adding linked server definitions on each member server containing the connection information needed to execute distributed queries on the other member servers. This gives a distributed partitioned view access to data on the other servers.

For example, you are partitioning a Customer table into three tables. The CHECK constraint for these tables is:

-- On Server1:
CREATE TABLE Tabl-A
  (CustomerID INT PRIMARY KEY CHECK (CustomerID BETWEEN 1 AND 32999),
  ... -- Additional column definitions)

-- On Server2:
CREATE TABLE Tabl-B
  (CustomerID INTE PRIMARY KEY CHECK (CustomerID BETWEEN 33000 AND 65999),
  ... -- Additional column definitions)

-- On Server3:
CREATE TABLE Tabl-C
  (CustomerID INT PRIMARY KEY CHECK (CustomerID BETWEEN 66000 AND 99999),
  ... -- Additional column definitions)

To create distributed partitioned views for the preceding example, you must:


    1.  Add a linked-server definition named Server2 with the connection information for  
        Server2 and a linked server definition named Server3 for access to Server3

     2. Create this distributed partitioned view:

CREATE VIEW Customers
AS
SELECT * FROM CompanyDatabase.TableOwner.Tabl-A
UNION ALL
SELECT * FROM Server2.CompanyDatabase.TableOwner.Tabl-B
UNION ALL
SELECT * FROM Server3.CompanyDatabase.TableOwner.Tabl-C




3.  Perform the same steps on Server2 and Server3.


Updating Distributed Partition View

If a local or distributed partitioned view is not updatable, it can serve only as a read-only copy of the original table. An updatable partitioned view can exhibit all the capabilities of the original table.

A view is considered an updatable partitioned view if:
The view is a set of SELECT statements whose individual result sets are combined into one using the UNION ALL statement. Each individual SELECT statement references one SQL Server base table. The table can be either a local table or a linked table referenced using a four-part name, the OPENROWSET function, or the OPENDATASOURCE function. The view will not be updatable if a trigger or cascading update or delete is defined on one or more member tables.

Hope you like that.


Posted by: MR. JOYDEEP DAS

Friday 30 March 2012

Scripts to find the Size of Table Objects




One of my friends asks me about the scripts related to finding the Size of the table objects, index size. He thought that his DB is growing unnaturally and he want to monitor each table (User define) objects size.
So in this article I am providing a stored procedure that display all the table size and related things in the current database.
You can supply the table name as the parameter to see the size of the table or just blank to see the entire user define table size of the current database. The stored procedure is ready to use.

/*
   To check the DB Size and DB Object Size Like Table Size /Index Size etc.
   Note : U must Specify the @P_TBLNAME as the DESIRED table name
   Version: 1.00
  
   Date: 30-March-2012
   by : joydeep Das
  
  
   EXEC up_RUN_SIZOMEETER
        @P_TBLNAME='MY_TABLE'
       
       
*/
IF EXISTS (SELECT *
           FROM   sysobjects WHERE type = 'P'
                  AND name = 'up_RUN_SIZOMEETER')
      BEGIN
            DROP  PROCEDURE  up_RUN_SIZOMEETER
      END

GO

CREATE Procedure [dbo].[up_RUN_SIZOMEETER]
      (
            @P_TBLNAME  VARCHAR(MAX) =    NULL   
      )
AS
    DECLARE  @v_TblName VARCHAR(MAX)
    DECLARE  @v_TRBL TABLE
                   (Tblname   VARCHAR(MAX),
                    TblRows   INT,
                    TblRese   VARCHAR(MAX),
                    Tbldata   VARCHAR(MAX),
                    TblIndx   VARCHAR(MAX),
                    TblUnused 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
                    INSERT INTO @v_TRBL  EXEC ('sp_spaceused '+ @v_TblName)
                  
                    FETCH NEXT FROM db_cursor
                               INTO @v_TblName
              END
                      
       CLOSE db_cursor  
         DEALLOCATE db_cursor
         
         SELECT Tblname   name,   
                    TblRows   [rows],  
                    TblRese   [reserved],
                    Tbldata   [data],
                    TblIndx   [Index Size],
                    TblUnused [Unused]
         FROM @v_TRBL

END

GO


To Execute the Stored procedure

EXEC up_RUN_SIZOMEETER
        @P_TBLNAME='MY_TABLE'
  

 OR
EXEC up_RUN_SIZOMEETER
        @P_TBLNAME=''

Hope you like that.


Posted by: MR. JOYDEEP DAS