When we use SP_UPDATESTATS

When we use SP_UPDATESTATS

We have common doubts when to update statistics, before index or after index. To resolve this query we must understand, what is the statistics is? Why we need the statistics? If we can solve this problem we must solve our previous query. In this article I am trying to solve this problem.

The query optimizer uses the statistics to create the query plan to improve the query performance. The query optimizer automatically generates the necessary statistics to build high quality query plan to improve the performance of the query.

In fewer cases we need to create statistics for better result.

The statistics of the query optimization are objects that contain statistical information about the distribution of values in one or more columns of a table or indexed views.

The query optimizer use this statistics to estimates the cardinality, or number of rows, in the query result. These cardinality estimates enable the query optimizer to create a high-quality query plan.  The query optimizer could use cardinality estimates to choose the index seek operator instead index scan operator, and in doing so improve query performance.

We can use the following query to determine that the query optimizer has created statistics for a query predicate column. It queries the catalog views sys.stats and sys.stats_columns to return the database object name, the column name, and the statistics name for all of the columns that have single-column statistics.

When the query optimizer creates statistics on single columns as a result of using the AUTO_CREATE_STATISTICS option, the statistics name starts with _WA.

SELECT OBJECT_NAME(s.object_id) AS object_name,

       COL_NAME(sc.object_id, sc.column_id) AS column_name,

       s.name AS statistics_name

FROM   sys.stats s

       INNER JOIN sys.stats_columns sc ON s.stats_id = sc.stats_id

                                          AND s.object_id =sc.object_id

WHERE s.name like '_WA%'

ORDER BY s.name;

GO

Now we are going to solve this query that I mentioned in the top of my article.

"When we are going to update the Statistics by sp_updatestats, after or before rebuilding the query"

To solve this query we must take some example of t-sql statements.

Step-1 [ Create Table with primary key ]

CREATE TABLE tbl_stat

(

  ID             INT,

  SNAME          CHAR(2000)

  CONSTRAINT PK_tbl_stat PRIMARY KEY CLUSTERED (ID)

)

Step-2 [ Now Insert Some records in the Table Objects ]

BEGIN

      DECLARE @RowID DECIMAL=0

      WHILE @RowID <> 15000

        BEGIN

             SET @RowID = @RowID + 1

             INSERT INTO tbl_stat(ID, SNAME)

             SELECT @RowID, 'Student-' + CONVERT(VARCHAR(5), @RowID)

        END

END

GO

SELECT * FROM tbl_stat

Step-3 [ Now see the statistics look like for primary key ]

DBCC SHOW_STATISTICS ('tbl_stat', PK_tbl_stat) WITH STAT_HEADER

Name	PK_tbl_stat
Updated	NULL
Rows	NULL
Rows Sampled	NULL
Steps	NULL
Density	NULL
Average key length	NULL
String Index	NULL
Filter Expression	NULL
Unfiltered Rows	NULL

Step-4 [Let's rebuild the index to start with ]

ALTER INDEX PK_tbl_stat ON tbl_stat REBUILD

Step-5 [ Now See the Statistics Again ]

DBCC SHOW_STATISTICS ('tbl_stat', PK_tbl_stat) WITH STAT_HEADER

Name	PK_tbl_stat
Updated	Apr 19 2012 12:09AM
Rows	15000
Rows Sampled	15000
Steps	3
Density	1
Average key length	4
String Index	NO
Filter Expression	NULL
Unfiltered Rows	15000

Step-6 [ Conclusion ]

Here in this example we find that the rebuilding the index automatically update the statistics.

But what about the statistics that are not the part of the Index.

Step-7 [ Create Non-Index Statistics ]

CREATE STATISTICS stats_NIndxData ON tbl_stat(SNAME)

Step-8 [ Observation ]

DBCC SHOW_STATISTICS ('tbl_stat', PK_tbl_stat) WITH STAT_HEADER

n  No Changes Found

Rebuild the Index Again

ALTER INDEX PK_tbl_stat ON tbl_stat REBUILD

Now check

DBCC SHOW_STATISTICS ('tbl_stat', PK_tbl_stat) WITH STAT_HEADER

n  No Changes Found

Step-9 [ Now run the sp_updatestats ]

EXEC sp_updatestats

DBCC SHOW_STATISTICS ('tbl_stat', stats_NIndxData) WITH STAT_HEADER

Name	PK_tbl_stat
Updated	Apr 19 2012 12:18AM
Rows	15000
Rows Sampled	7068
Steps	15
Density	1
Average key length	2000
String Index	YES
Filter Expression	NULL
Unfiltered Rows	15000

Step-8 [ Remarks ]

So the answer of the query is "You must run the sp_updatestats after rebuilding the index.

Reclaiming Table Space

Reclaiming Table Space

SQL Server will reuse space made available when rows are deleted from a table. This occurs automatically without any outside intervention on our part.

However under certain circumstances, SQL Server does not automatically reclaim space once used. If a table definition is altered to drop one or more variable length columns, the space consumed by those columns is not immediately made available for reuse by SQL Server. But that's to say that the space is forever lost. 
To illustrate this behavior and the aforementioned DBCC utility, let's consider an example. Let's create a table with three columns and populate it with some test data as shown in the following code. 

--create a test table

CREATE TABLE dbo.Test

        (

         col1 INT,

         col2 VARCHAR(50),

         col3 VARCHAR(MAX)

        ) ;

--Inserting Data

INSERT INTO dbo.Test (col1, col2, col3)

VALUES (1, 'AA', 'AAAA'),

       (1, 'BB', 'BBBB'),

       (1, 'CC', 'CCCC'),

       (1, 'DD', 'DDDD'),

       (1, 'EE', 'EEEE')……….. Needs Lot of Data to Entered
  

Now let's view the table to make sure we have what we think we have. 

SELECT *

FROM   dbo.Test

Using a Dynamic Management View, let's see how much space our newly created table is consuming. 

SELECT alloc_unit_type_desc,

       page_count,

       avg_page_space_used_in_percent,

       record_count

FROM   sys.dm_db_index_physical_stats(DB_ID(),

                                      OBJECT_ID(N'dbo.Test'),

                                      NULL,

                                      NULL,

                                      'Detailed') ;
  

alloc_unit_type_desc    : IN_ROW_DATA 
page_count        :84
avg_page_space_used_in_percent : 78.51964418087472  
record_count : 5000

Now let's drop the third column, the one that consumes the most space, and check the space used once again. 

ALTER TABLE dbo.Test

DROP COLUMN col3 ;
 
 

We get the same results - 84 data pages, storing 1000 rows, each 78.6% full - even after dropping the column that consumed the most space. 
Now, let's reclaim the space using the DBCC CLEANTABLE command and recheck the space consumed. 
Syntax:

DBCC CLEANTABLE

      ( { 'database' | database_id | 0 }

         , { 'table' | table_id | 'view' | view_id }

            [ , batch_size]

       ) [WITH NO_INFOMSGS]

Key:

    batch_size   - The no. of rows to process per transaction.

                       default (or if 0 is specified) = whole table

    NO_INFOMSGS  - Suppress all information messages.

If 0 is specified, the current database will be used.

DBCC CLEANTABLE('tempdb', 'dbo.Test')
 
This time, considerable less space is consumed; the average page is only filled 4.5% full! 

MERGE is SQL 2008

MERGE is SQL 2008

 The SQL server 2008 introduced a special TSQL statement called MERGE.

The MERGE statement is combining of INSERT/ UPDATE / DELETE statement. It is specially used to manipulate source table to target table operations in a single tatement.

The syntax is mentioned bellow:

MERGE <Target Table> AS mld

     USING <Source Table SQL> AS mpd

          ON mld.MemberID = mpd.MemberID

WHEN MATCHED AND <Specify Others Conditions if needed> 

     THEN DELETE

WHEN MATCHED 

     THEN UPDATE SET mld.UserPassword = 'DefaultPassword'

WHEN NOT MATCHED 

     THEN <Insert Satement>

Example :

INSERT INTO sourceTable

            (Roll, StudentName, Class)

VALUES (1, 'Joydeep Das', 1),

       (2, 'Palash Paul', 1),

       (3, 'Sukamal Jana',1)

      

GO

INSERT INTO TargetTable

            (Roll, StudentName, TotalGarde)

VALUES (1, 'Raja', 'A'),

         (2, 'Palash Paul', 'B')

GO

        

MERGE TargetTable AS t

USING (SELECT Roll, StudentName, Class FROM SourceTable) AS s

       ON s.Roll = t.Roll

    WHEN MATCHED AND s.Roll>3

         THEN DELETE

    WHEN MATCHED

         THEN UPDATE SET t.StudentName = s.StudentName

    WHEN NOT MATCHED

         THEN INSERT(Roll, StudentName, TotalGarde)

         VALUES(s.Roll, s.StudentName, 'C');

GO

Understanding about LINKED SERVER In SQL

Understanding about LINKED SERVER In SQL

Linked Server in nothing but combining two different Server database together and perform a single query or joining table objects from different database of different server.

For Example:

You have Server-A with Database-A and Server-B with Database-B in different geographical locations. Now you want to make a SQL statement by JOINING Table-A from Server-A / Database-A and Table-B from Server-B/Database-B.  to do this you need to configure linked server configurations.

Configuring Linked Server.

Syntax

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]

     [ , [ @provider= ] 'provider_name' ]

     [ , [ @datasrc= ] 'data_source' ]

     [ , [ @location= ] 'location' ]

     [ , [ @provstr= ] 'provider_string' ]

     [ , [ @catalog= ] 'catalog' ]

Suppose You are currently logged in at SERVER-A

USE master

GO

EXEC sp_addlinkedserver  

   @server=N'S1_instance1', -- Linked Server Name

   @srvproduct=N'',

   @provider=N'SQLNCLI',    -- Provider Name

   @datasrc=N'Server-B';    -- SQL Server Instance Name

Provider Details:

SQL Server	Microsoft SQL Server Native Client OLE DB Provider	SQLNCLI
Oracle	Microsoft OLE DB Provider for Oracle	MSDAORA

It can connect any server supporting Microsoft OLEDB Provider.

Linked Server Login

 sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname' 

     [ , [ @useself = ] 'TRUE' | 'FALSE' | NULL ] 

     [ , [ @locallogin = ] 'locallogin' ] 

     [ , [ @rmtuser = ] 'rmtuser' ] 

     [ , [ @rmtpassword = ] 'rmtpassword' ] 

Creates or updates a mapping between a login on the local instance of SQL Server and a security account on a remote server

EXEC  sp_addlinkedsrvlogin

       @rmtsrvname = 'Server-B',

       @useself = 'TRUE',

       @locallogin = 'Domain\Mary',

       @rmtuser =  'mysqllog',

       @rmtpassword = 'rmtpassword' 

  

To Drop linked Server Login

sp_droplinkedsrvlogin [ @rmtsrvname= ] 'rmtsrvname' , 

                      [ @locallogin= ] 'locallogin'

 Execute SQL

SELECT a.Roll, a.StudentName

  FROM Server-B.Dtabase-B.dbo.Table-B