Moving Sql data files to different location

It is always good to have database files to be placed on a non-system partition (system partition is where default program files and windows folder resides)

Last week, unfortunately my PC got corrupted, had to restore to 3 days old system image of windows, and I lost my sql data. Now for future, I have taken a preventive measure, and moved my sql database files to non-system drive.

Here are the steps,

Database name is testDB,

Note: make sure new location is not read only, has sufficient privileges to Administrator account, and the drive is uncompressed 

Step 1 : Create a new location 

New location is D:\MSSQL\DATA\

Step 2 : Run these commands in master database

ALTER DATABASE testDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE testDB SET OFFLINE;

ALTER DATABASE testDB MODIFY FILE

(

   Name = testDB,

   Filename = 'D:\MSSQL\DATA\testDB.MDF'

);

ALTER DATABASE my MODIFY FILE

(

   Name = testDB_log,

   Filename ='D:\MSSQL\DATA\testDB_log.LDF'

);

Step 3 : Move original sql data files from C:\program files\mssql\data to newly created location

Make sure the newly pasted files are not read-only, remove the read-only or compression settings.

Step 4: Move original sql data files from C:\program files\mssql\data to newly created location

Bring the database to online mode

ALTER DATABASE testDB SET ONLINE;

ALTER DATABASE testDB SET MULTI_USER;

Another way of pagination in sql server

Today, I wrote a paginating query using ranking function row_number(), with help of my team mate Neha. Initial idea was from her, and later I hijacked it to post it here. :). Poor Neha..

Ceiling function seems to be crux of it.  First 3 columns are data columns, rest are informational for paging data.

select

          *

from

(

          SELECT                                                                         

          ID                                                      --Data Columns

          ,GrandSlamLocation                          --Data Columns    

          ,GrandSlamLogin                               --Data Columns

          ,ROW_NUMBER() OVER(ORDER BY ID) [S.No]                                             

          , ceiling(ROW_NUMBER() OVER(ORDER BY ID)/ 10.00)  as Page

          ,count(*) over() as TotalRecords

          ,ceiling(count(*) over()/10.00) as TotalPages

          from

          (

                   --Base Query Start

                   select

                             ID,                                  --Data Columns

                             GrandSlamLocation,      --Data Columns    

                             GrandSlamLogin            --Data Columns

                   from  

                             Ticket

                   --Base Query End

          )A

           

)B where Page =  1

Tally table - equivalent of "select * from dual" in oracle

This sql query genrates unlimited sequential rows/virtual rows, using CTE

; WITH

Table1 AS (SELECT 1 N UNION ALL SELECT 1 N),    -- 2            ,              2 raise to power 1

Table2 AS (SELECT 1 N FROM Table1 x, Table1 y),            -- 8            ,   2 raise to power 3

Table3 AS (SELECT 1 N FROM Table2 x, Table2 y),            -- 128         ,   2 raise to power 7

Table4 AS (SELECT 1 N FROM Table3 x, Table3 y),            -- 32768        , 2 raise to power 15

Table5 AS (SELECT 1 N FROM Table4 x, Table4 y),           -- 2147483648 , 2 raise to power 31

tally as

(

                   SELECT

                             ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N                      

                   FROM

                             Table1 , Table2 , Table3, Table4 --, Table5

                             -- you can delete or add tables here to increase decrease the rows, or

)

select * from tally where n < 5000

-- you can control number of rows here also

Truncate your database log file

CREATE PROC [dbo].[PROC_TruncateLog]

AS BEGIN

      DECLARE @DBName sysname,

                  @logName sysname

      --

      SET @DBName = DB_NAME()

      --

      BACKUP LOG @DBName WITH NO_LOG

      --

      DECLARE logCursor CURSOR FOR

      SELECT name

      FROM sysfiles

      WHERE groupid = 0

      --

      OPEN logCursor

      FETCH NEXT FROM logCursor

      INTO @logName

      WHILE @@FETCH_STATUS = 0 BEGIN

            SELECT @logName = RTRIM(@logName)

            DBCC SHRINKFILE (@logName)

            FETCH NEXT FROM logCursor

            INTO @logName

      END

      CLOSE logCursor

      DEALLOCATE logCursor

END

Code snippet for querying excel files as DataTable

public static DataSet ImportExcelXLS(string FileName, bool hasHeaders)
    {
        string HDR = hasHeaders ? "Yes" : "No";

        string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                            FileName +
                                    ";Extended Properties='Excel 12.0;HDR=" +
                         HDR + ";IMEX=1'";


        DataSet output = new DataSet();

        using (OleDbConnection conn = new OleDbConnection(strConn))
        {
            conn.Open();

            DataTable schemaTable = conn.GetOleDbSchemaTable(
              OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

            foreach (DataRow schemaRow in schemaTable.Rows)
            {
                string sheet = Convert.ToString(schemaRow["TABLE_NAME"]);

                OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn);
                cmd.CommandType = CommandType.Text;

                DataTable outputTable = new DataTable(sheet);
                outputTable.TableName = sheet;
                output.Tables.Add(outputTable);
                new OleDbDataAdapter(cmd).Fill(outputTable);
            }
        }
        return output;
    }

Join hints in Sql Server
 
SQL Server employs three types of join operations:

*      Nested loops joins

*      Merge joins

*      Hash joins

If one join input is small (fewer than 10 rows) and the other join input is fairly large and indexed on its join columns, an index nested loops join is the fastest join operation because they require the least I/O and the fewest comparisons. For more information about nested loops, see Understanding Nested Loops Joins.

If the two join inputs are not small but are sorted on their join column (for example, if they were obtained by scanning sorted indexes), a merge join is the fastest join operation. If both join inputs are large and the two inputs are of similar sizes, a merge join with prior sorting and a hash join offer similar performance. However, hash join operations are often much faster if the two input sizes differ significantly from each other. For more information, see Understanding Merge Joins.

Hash joins can efficiently process large, unsorted, non indexed inputs.


Source : MSDN Advanced Query Tuning Concepts:

Boosting SQL Server throughput - Part II

This is continuation to part 1 of boosting sql server throughput series. I had been away from this series for a long long time. It is all about finding missing indexes on a particular database/table. You can modify this script as well to cater your needs. Missing indexes- refers to indexes those should have been on that table to optimize its performance. Here we go,

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO

IF OBJECT_ID('dbo.Proc_MissingIndexes', 'P') IS NOT NULL DROP PROCEDURE dbo.Proc_MissingIndexes
GO


  --  EXECUTE Proc_MissingIndexes 'model', '%', 1


CREATE PROCEDURE dbo.Proc_MissingIndexes
    @SchemeName sysname='',
    @TableName sysname='',
    @Sort Tinyint=1,
    @Delimiter VarChar(1)=','
AS

SELECT
    sys.schemas.schema_id, sys.schemas.name AS schema_name,
    sys.objects.object_id, sys.objects.name AS object_name, sys.objects.type,
    partitions.Rows, partitions.SizeMB,
    CASE WHEN @Delimiter=',' THEN sys.dm_db_missing_index_details.equality_columns ELSE REPLACE(sys.dm_db_missing_index_details.equality_columns, ',', @Delimiter) END AS equality_columns,
    CASE WHEN @Delimiter=',' THEN sys.dm_db_missing_index_details.inequality_columns ELSE REPLACE(sys.dm_db_missing_index_details.inequality_columns, ',', @Delimiter) END AS inequality_columns,
    CASE WHEN @Delimiter=',' THEN sys.dm_db_missing_index_details.included_columns ELSE REPLACE(sys.dm_db_missing_index_details.included_columns, ',', @Delimiter) END AS included_columns,
    sys.dm_db_missing_index_group_stats.unique_compiles,
    sys.dm_db_missing_index_group_stats.user_seeks, sys.dm_db_missing_index_group_stats.user_scans,
    sys.dm_db_missing_index_group_stats.avg_total_user_cost, sys.dm_db_missing_index_group_stats.avg_user_impact,
    sys.dm_db_missing_index_group_stats.last_user_seek, sys.dm_db_missing_index_group_stats.last_user_scan,
    sys.dm_db_missing_index_group_stats.system_seeks, sys.dm_db_missing_index_group_stats.system_scans,
    sys.dm_db_missing_index_group_stats.avg_total_system_cost, sys.dm_db_missing_index_group_stats.avg_system_impact,
    sys.dm_db_missing_index_group_stats.last_system_seek, sys.dm_db_missing_index_group_stats.last_system_scan,
    (CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.user_seeks)+CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.unique_compiles))*CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.avg_total_user_cost)*CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.avg_user_impact/100.0) AS Score
FROM
    sys.objects
    JOIN (
        SELECT
            object_id, SUM(CASE WHEN index_id BETWEEN 0 AND 1 THEN row_count ELSE 0 END) AS Rows,
            CONVERT(numeric(19,3), CONVERT(numeric(19,3), SUM(in_row_reserved_page_count+lob_reserved_page_count+row_overflow_reserved_page_count))/CONVERT(numeric(19,3), 128)) AS SizeMB
        FROM sys.dm_db_partition_stats
        WHERE sys.dm_db_partition_stats.index_id BETWEEN 0 AND 1 --0=Heap; 1=Clustered; only 1 per table
        GROUP BY object_id
    ) AS partitions ON sys.objects.object_id=partitions.object_id
    JOIN sys.schemas ON sys.objects.schema_id=sys.schemas.schema_id
    JOIN sys.dm_db_missing_index_details ON sys.objects.object_id=dm_db_missing_index_details.object_id
    JOIN sys.dm_db_missing_index_groups ON sys.dm_db_missing_index_details.index_handle=sys.dm_db_missing_index_groups.index_handle
    JOIN sys.dm_db_missing_index_group_stats ON sys.dm_db_missing_index_groups.index_group_handle=sys.dm_db_missing_index_group_stats.group_handle
WHERE
    sys.dm_db_missing_index_details.database_id=DB_ID()
    AND sys.schemas.name LIKE CASE WHEN @SchemeName='' THEN sys.schemas.name ELSE @SchemeName END
    AND sys.objects.name LIKE CASE WHEN @TableName='' THEN sys.objects.name ELSE @TableName END
ORDER BY
    CASE @Sort
        WHEN 1 THEN
            (CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.user_seeks)+CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.unique_compiles))*CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.avg_total_user_cost)*CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.avg_user_impact/100.0)*-1
        WHEN 2 THEN
            (CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.user_seeks)+CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.unique_compiles))*CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.avg_total_user_cost)*CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.avg_user_impact/100.0)
        ELSE NULL
    END,
    CASE @Sort
        WHEN 3 THEN sys.schemas.name
        WHEN 4 THEN sys.schemas.name
        WHEN 5 THEN sys.schemas.name
        ELSE NULL
    END,
    CASE @Sort
        WHEN 1 THEN sys.dm_db_missing_index_group_stats.user_seeks*-1
        WHEN 2 THEN sys.dm_db_missing_index_group_stats.user_seeks
    END,
    CASE @Sort
        WHEN 3 THEN sys.objects.name
        WHEN 4 THEN sys.objects.name
        WHEN 5 THEN sys.objects.name
        ELSE NULL
    END,
    CASE @Sort
        WHEN 1 THEN sys.dm_db_missing_index_group_stats.avg_total_user_cost*-1
        WHEN 2 THEN sys.dm_db_missing_index_group_stats.avg_total_user_cost
        WHEN 4 THEN
            (CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.user_seeks)+CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.unique_compiles))*CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.avg_total_user_cost)*CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.avg_user_impact/100.0)*-1
        WHEN 5 THEN
            (CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.user_seeks)+CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.unique_compiles))*CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.avg_total_user_cost)*CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.avg_user_impact/100.0)
        ELSE NULL
    END,
    CASE @Sort
        WHEN 3 THEN sys.dm_db_missing_index_details.equality_columns
        ELSE NULL
    END
GO
--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

What’s new in SQL 2008 

Perquisites: You should have SQL 2008 and .NET Framework 4

Table-Valued Parameters

 

1) Create your data type first,

The following statement creates a table type named CategoryTableType that consists of CategoryID and CategoryName columns:

CREATE TYPE dbo.CategoryTableType AS TABLE

    ( CategoryID int, CategoryName nvarchar(50) )

2) Create a stored procedure, which will cosume this newly created table 'type'

After you create a table type, you can declare table-valued parameters based on that type. The following Transact-SQL fragment demonstrates how to declare a table-valued parameter in a stored procedure definition. Note that the READONLY keyword is required for declaring a table-valued parameter.

CREATE PROCEDURE usp_UpdateCategories

    (@tvpNewCategories dbo.CategoryTableType READONLY) 

/*

 -- You can use this @tvpNewCategories table for insert/update/select joins, anything as a normal table

*/

UPDATE dbo.Categories

    SET Categories.CategoryName = ec.CategoryName

    FROM dbo.Categories INNER JOIN @tvpEditedCategories AS ec

    ON dbo.Categories.CategoryID = ec.CategoryID;

3) Passing table from code to stored procedure

 // Assumes connection is an open SqlConnection object.

using (connection)

{

// Create a DataTable with the modified rows.

DataTable addedCategories =

  CategoriesDataTable.GetChanges(DataRowState.Added);

// Configure the SqlCommand and SqlParameter.

SqlCommand insertCommand = new SqlCommand(

    "usp_InsertCategories", connection);

insertCommand.CommandType = CommandType.StoredProcedure;

SqlParameter tvpParam = insertCommand.Parameters.AddWithValue(

    "@tvpNewCategories", addedCategories);

tvpParam.SqlDbType = SqlDbType.Structured;

// Execute the command.

insertCommand.ExecuteNonQuery();

}

Limitations

1)      You cannot pass table-valued parameters to user-defined functions.

2)     Table-valued parameters can only be indexed to support UNIQUE or PRIMARY KEY constraints. SQL Server does not maintain statistics on table-valued parameters.

3)     Table-valued parameters are read-only in Transact-SQL code. You cannot update the column values in the rows of a table-valued parameter and you cannot insert or delete rows. To modify the data that is passed to a stored procedure or parameterized statement in table-valued parameter, you must insert the data into a temporary table or into a table variable.

4)      You cannot use ALTER TABLE statements to modify the design of table-valued parameters.