Move MSSQL database file path

The solution if found at: https://msdn.microsoft.com/en-us/library/ms345408.aspx


The steps are:

1) Change file path:
ALTER DATABASE [db_name] MODIFY FILE ( NAME = [db_logic_name], FILENAME = '[new path]' )
2) Stop MSSQL server.
3) Move the file.
4) Restart MSSQL server.

That's all. Now you can check status by:

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'[db_name]');

Underscore "_" is a wild card match character in MSSQL

Besides '%', underscore '_' is also a wild card match character in MSSQL. This can lead to some very subtle bug.

Say your table name has underscore in it, e.g., there are 2 tables my_Tablename and my_WeirdTablename. You find table using 'name like %_Tablename', then you will get both tables, instead of the first one. This leads to hidden bugs that are hard to identify.

To fix this issue, use this: 'name like %[_]tablename'. The '_' char is escaped by quoting it with brackets.

Paging with SQL

In MySQL, this can be easily done with:

SELECT * FROM tbl limit [start], [end]

In MSSQL, this can be done as in [1] :

1.Paging rows with Limit (MSSQL 2005 or later)

--VIEWING THE PAGE "2" WITH 5 ROWS
DECLARE @PageNumber AS INT, @RowspPage AS INT
SET @PageNumber = 2
SET @RowspPage = 5 SELECT * FROM (
             SELECT ROW_NUMBER() OVER(ORDER BY ID_EXAMPLE) AS NUMBER,
                    ID_EXAMPLE, NM_EXAMPLE, DT_CREATE FROM TB_EXAMPLE
               ) AS TBL
WHERE NUMBER BETWEEN ((@PageNumber - 1) * @RowspPage + 1) AND (@PageNumber * @RowspPage)
ORDER BY ID_EXAMPLE

2. Paging in SQL Server 2012, with FETCH/OFFSET

--CREATING A PAGING WITH OFFSET and FETCH clauses IN "SQL SERVER 2012"
DECLARE @PageNumber AS INT, @RowspPage AS INT
SET @PageNumber = 2
SET @RowspPage = 10 
SELECT ID_EXAMPLE, NM_EXAMPLE, DT_CREATE
FROM TB_EXAMPLE
ORDER BY ID_EXAMPLE
OFFSET ((@PageNumber - 1) * @RowspPage) ROWS
FETCH NEXT @RowspPage ROWS ONLY;

For performance, 2 is better than 1. Both are better than getting all rows and use specific range.

[1] http://social.technet.microsoft.com/wiki/contents/articles/23811.paging-a-query-with-sql-server.aspx

Detach a series of databases in MSSQL

declare CUR cursor for
    SELECT name
    FROM master..sysdatabases
    where name like 'ABC_%'

declare @db varchar(50)
declare @msg varchar(200)

open CUR

fetch next from CUR into @db
while @@FETCH_STATUS = 0
begin
    set @msg = 'detach ' + @db
    raiserror (@msg, 0, 1) with nowait
   
    exec sp_detach_db @db, 'true'
   
    fetch next from CUR into @db
end

close CUR
deallocate CUR


---- Below is batch script to quickly remove disk files. Note you can use multiple wild card match. ----

ECHO OFF

FOR /f "tokens=*" %%i in ('DIR /a:d /b D:\mssql_db\*abc* D:\mssql_db\*xyz*') DO (
    ECHO %%i
    rmdir /s /q "D:\mssql_db\%%i"
)

Estimate size of MSSQL Table with indices

How to estimate the size of a MSSQL table with indices?

It's not enough to just multiply number of rows with the size of each row, because there are other entities involved, mostly the index. Relevant concepts are clustered/non-clustered index, unique/non-unique index, fill factor of an index, fix/variable length column, page size (8192 or 8K bytes), Null bitmap etc.

For MSSQL 2008, see MSDN articles [1][2][3]. For other versions of MSSQL, relevant links are in articles [1][2].

[1] Estimating the Size of a Clustered Index (MSSQL 2008)
[2] Estimating the Size of a Nonclustered Index (MSSQL 2008)
[3] Estimating the Size of a Table with a Clustered Index (MSSQL 2000)

Note that "Step 1. Calculate the Space Used to Store Data in the Leaf Level" in [1] is basically copied from [3], the only difference in [1] is the addition of "3. If the clustered index is nonunique, account for the uniqueifier column:". This actually calculates the combined size of both data and index in the leaf level nodes. In that the title of article [1] is inaccurate.

A query that directly read data and index information from the database is below (from here):

with pages as (
    SELECT object_id, SUM (reserved_page_count) as reserved_pages, SUM (used_page_count) as used_pages,
            SUM (case 
                    when (index_id < 2) then (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
                    else lob_used_page_count + row_overflow_used_page_count
                 end) as pages
    FROM sys.dm_db_partition_stats
    group by object_id
), extra as (
    SELECT p.object_id, sum(reserved_page_count) as reserved_pages, sum(used_page_count) as used_pages
    FROM sys.dm_db_partition_stats p, sys.internal_tables it
    WHERE it.internal_type IN (202,204,211,212,213,214,215,216) AND p.object_id = it.object_id
    group by p.object_id
)
SELECT object_schema_name(p.object_id) + '.' + object_name(p.object_id) as TableName,  

       (p.reserved_pages + isnull(e.reserved_pages, 0)) * 8 as reserved_kb,
        pages * 8 as data_kb,
        (CASE WHEN p.used_pages + isnull(e.used_pages, 0) > pages 

              THEN (p.used_pages + isnull(e.used_pages, 0) - pages) ELSE 0 END) * 8 as index_kb,
        (CASE WHEN p.reserved_pages + isnull(e.reserved_pages, 0) > p.used_pages + isnull(e.used_pages, 0) 

         THEN (p.reserved_pages + isnull(e.reserved_pages, 0) - p.used_pages + isnull(e.used_pages, 0)) else 0 end) * 8 as unused_kb
from pages p
left outer join extra e on p.object_id = e.object_id

MSSQL table row and column limit

The max number of columns allowed in MSSQL database is 1024. Wide table can contain up to 30000 columns, but it applies only to sparse columns, meaning most columns should be null and regular/calculated columns number is still 1024, and there is performance issue. This is from this article in MSDN.

Also bytes per row is limited to max 8060 (also from the link above, or here). varchar types can be longer but still will decrease performance. Since max length for any field is 8 bytes, this is 1024*8 = 8192, a little higher than 8060.

Bulk Insert with error Violation of PRIMARY KEY

In MSSQL 2008, when doing a bulk insert, it sometimes gets into the error of "Violation of PRIMARY KEY".

It should report the violation key value: "The duplicate key value is (...)". Then one can remove the duplicate and insert again.

However, sometimes this is not reported, due to unknown reason; or sometimes one just wants to continue the insert without interruption.

This can be achieved by adding the "WITH (IGNORE_DUP_KEY = ON)" option when creating the table, at the cost of a much more complex execution plan.

An alternative is to add a temporary unique key on the same column as the primary key, adding the "WITH (IGNORE_DUP_KEY = ON)" option on this temporary key and do the insert. After insert is done, remove the temporary key.

See A creative use of IGNORE_DUP_KEY for the details, and more discussions.

NOTE: for bulk insert, if it fails (like due to violation of PK constraint), then none record is inserted at all.

Bulk insert syntax:

BULK INSERT Test1
FROM 'C:\\temp\test1.txt'
WITH
(
   FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\n'
)

Add a unique index with IGNORE_DUP_KEY on:

ALTER TABLE dbo.Test1
ADD CONSTRAINT UQ_idk
UNIQUE NONCLUSTERED (objID)
WITH (IGNORE_DUP_KEY = ON, ONLINE = ON);

Remove the index:

ALTER TABLE dbo.Test1
DROP CONSTRAINT UQ_idk

Estimating Data Compression Savings in SQL Server 2008 and 2008 R2

Estimating Data Compression Savings in SQL Server 2008 and 2008 R2

Sams Teach Yourself SQL in 24 hours

CH 4. p.61.
   Normalization - reduce redundancy (will reduce performance due to more JOINs, will use more CPU/mem/IO).
   Denormalization. Combines tables, controlled redundancy. Increased performance.

CH 6. Transaction.
   Commit, Rollback, Savepoint

CH 8. All, Some, Any

CH 9. Aggregate functions.

CH 10. Sorting and Grouping.
   Group by.
   - rollup - get subtotal
   - cube - crosstab reports
   - having - GROUP BY/HAVING is similar to SELECT/WHERE
   - p. 243. UNION (no duplicate rows), UNION ALL (including duplicate rows).
   - INTERSECT
   - EXCEPT

CH 16. p. 256. Indexes.
   - When to avoid using indexes.. p. 261

CH 17. Improve DB performance
   - DB tuning / SQL tuning
   - To avoid full table scan, then use Index

CH 18. Manage DB Users
   - Schema - a collection of DB objects that a user owns.
   - DB user - aschema owner
   - Default schema - dbo (db owner)

CH 19. p. 299. Manage DB security.
   - Privilege
   - Control user access. GRANT, REVOKE, ROLE

CH 20. View, Synonym

CH 21. System Catalog

CH 22. Advanced SQL

MSSQL Server 2008

p.103. CH 7. Partitioning

p.219. CH 15. DB snapshots. CREATE database AS Snapshot

p.375. Part VII. Business Intelligence.
CH24. SSIS - SQL Server Integration Services
CH25. SSRS - SQL Server Reporting Services
CH26. SSAS - SQL Server Analysis Services

SSMS - SQL Server Management Studio

T-SQL III

1. Examples of using sp_executesql with output parameters.

Two scenarios: 1) execute a query, 2) execute a stored procedure.
See [1] http://support.microsoft.com/kb/262499

-- Example 1.

DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @IntVariable INT
DECLARE @Lastlname varchar(30)
SET @SQLString = N'SELECT @LastlnameOUT = max(lname)
                   FROM pubs.dbo.employee WHERE job_lvl = @level'
SET @ParmDefinition = N'@level tinyint,
                        @LastlnameOUT varchar(30) OUTPUT'
SET @IntVariable = 35
EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@level = @IntVariable,
@LastlnameOUT=@Lastlname OUTPUT
SELECT @Lastlname

-- Example 2.

CREATE PROCEDURE Myproc
    @parm varchar(10),
    @parm1OUT varchar(30) OUTPUT,
    @parm2OUT varchar(30) OUTPUT
    AS
      SELECT @parm1OUT='parm 1' + @parm
     SELECT @parm2OUT='parm 2' + @parm
GO
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @parmIN VARCHAR(10)
DECLARE @parmRET1 VARCHAR(30)
DECLARE @parmRET2 VARCHAR(30)
SET @parmIN=' returned'
SET @SQLString=N'EXEC Myproc @parm,
                             @parm1OUT OUTPUT, @parm2OUT OUTPUT'
SET @ParmDefinition=N'@parm varchar(10),
                      @parm1OUT varchar(30) OUTPUT,
                      @parm2OUT varchar(30) OUTPUT'

EXECUTE sp_executesql
    @SQLString,
    @ParmDefinition,
    @parm=@parmIN,
    @parm1OUT=@parmRET1 OUTPUT,@parm2OUT=@parmRET2 OUTPUT

SELECT @parmRET1 AS "parameter 1", @parmRET2 AS "parameter 2"
go
drop procedure Myproc

2. Use FETCH to go through a cycle.

-- Example 3. Note the use of: 1) cursor, 2) fetch, 3) raiserror to print without wait.

DECLARE @tbl varchar(20)
DECLARE c CURSOR FOR SELECT empID FROM test.dbo.Employee
OPEN c

FETCH NEXT FROM c INTO @tbl
WHILE @@FETCH_STATUS = 0
BEGIN
    --print @tbl
    RAISERROR(@tbl, 0, 1) WITH NOWAIT
    FETCH NEXT FROM c INTO @tbl
END

CLOSE c
DEALLOCATE c

T-SQL II

Follow T-SQL I. Now assume some employees make phone calls. How to get the total amount of phone call time of the employees managed by a manager?

Say manager's empID is 1, then it's easy to get:

select SUM(phoneusage) AS PhoneUsage from Employee where mgrID = 1

What if you want the manager's name etc. to be returned on the same row? You could do this:

select A.EmpID, A.ManagerName, B.PhoneUsage FROM
(
    (select ID = 1, EmpID, (firstname + ' ' + lastname) AS ManagerName from Employee where empID = 1) A
    INNER JOIN
    (select ID = 1, SUM(phoneusage) AS PhoneUsage from Employee where mgrID = 1) B
    ON A.ID = B.ID
)

Or, you can first get the phone usage of employees for all the managers:

SELECT A.empID, (A.firstName + ' ' + A.lastName) as [ManagerName], B.phoneUsage FROM
(
    (select mgrID, SUM(phoneUsage) as phoneUsage from Employee group by mgrID) as B
    INNER JOIN Employee A
    ON A.empID = B.mgrID
)

Then you can add "AND A.empID = 1" to get the phone usage of employees managed by manager whose empID is 1:

SELECT A.empID, (A.firstName + ' ' + A.lastName) as [ManagerName], B.phoneUsage FROM
(
    (select mgrID, SUM(phoneUsage) as phoneUsage from Employee group by mgrID) as B
    INNER JOIN Employee A
    ON A.empID = B.mgrID
    AND A.empID = 1
)

The last method is better than the first method, since it does not use an artificial ID.

T-SQL I

--
-- This script solves the problem:
-- Given an Employee table, each rows has empID, mgrID, firstName, lastName,
-- return all the managers in hierarchy for a given employee.
-- This is easy, but can also be extended to something of medium complexity.
--
-- This T-SQL script demonostrates:
-- +  create/drop stored procedure
-- +  declare table and variable
-- +  while loop
-- +* assign parameter in dynamically constructed query
-- +  stored procedure returns variable or table
-- + convert data type
-- + execute stored procedure in t-sql
--
-- @execute from command line: sqlcmd -S localhost -d test -i getList.sql
--   -s: server, -d: database, -i: input file
--
-- @Author: HomeTom
-- @Created on: 3/15/2013
-- @Last modified: 3/15/2013
--

--if object_id('dbo.getList') is not null
--    drop procedure dbo.getList
--go

--create procedure getList
--  @ID varchar,
--  @EID int output
--as
begin

SET NOCOUNT ON;

declare @tbl table (
    empID varchar,
    name  varchar(50)
)

declare @empID varchar --= 5
declare @mgrID varchar
declare @name varchar(100)
declare @cond int = 1
declare @query nvarchar(512)

set @empID = 5 --@ID

while @cond = 1
BEGIN
    --print @empID
    -- CONVERT(varchar, @empID)

    IF NOT EXISTS (select empID from Employee WHERE empID = @empID) BREAK

    set @query = 'select @name = firstname + '' '' + lastname from Employee WHERE empID = ' + @empID
    exec sp_executesql @query, N'@name varchar(100) output',  @name = @name output
    insert into @tbl (empID, name) values (@empID, @name)
   
    -- get manager's empID.
    set @query = 'select @empID = mgrID from Employee WHERE empID = ' + @empID
    exec sp_executesql @query, N'@empID varchar output',  @empID = @empID output
    if @empID is null set @cond = 0
END

select * from @tbl

--select @EID = -999

end
go


-------------------
-- execute getList
-------------------

--USE [test]
--GO

--DECLARE    @return_value int,
--        @EID int

--SELECT    @EID = 1

--EXEC    @return_value = [dbo].[getList]
--EXEC    [dbo].[getList]
--        @ID = N'1',
--        @EID = @EID OUTPUT

--SELECT    @EID as N'@EID'
--select @EID

--SELECT    'Return Value' = @return_value

--GO

-------------------
-- Table Employee
-------------------

-- CREATE TABLE [dbo].[Employee](
--     [empID] [int] NOT NULL,
--     [mgrID] [int] NULL,
--     [firstName] [varchar](50) NULL,
--     [lastName] [varchar](50) NULL,
--  CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
-- (
--     [empID] ASC
-- )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
-- ) ON [PRIMARY]

MSSQL management

- Log file size can grow very large. So its max size should be set at the beginning.
- If a log file is already too large, need to shrink/trunk it: 1) right click -> properties -> options (set recovery mode to simple), 2) Tasks > Shrink > Database (specify size to shrink to, and do it), 3) right click -> properties -> options (set recovery mode to full).
- Set default memory usage: right click on instance, choose memory.
- Get size of tables. See here for example. Below command will show size of data, index, reserved and free.

  EXEC sp_spaceused [table name]

Problems/Bugs with MSSQL server

There are some noticeable issues with MSSQL Server 2008.

- Cannot call function from remote machine.
- IDE issue: right click on a database, choose "Property -> Files", changes made to the Autogrowth dialog box sometimes cannot be saved. One has to repeat the steps several times before the change can take effect.
- Issues with extended property: 1) not included in replication synchronization process, and 2) no Information_Schema view is provided for extended properties. (from here)
- Also for extended property, I sometimes need to return extended properties of all the columns in a table, the order of returned list is un-deterministic and causes a headache when I want to display the columns in a certain order.

Drop Schema

DROP SCHEMA (Transact-SQL)

DROP SCHEMA schema_name

To drop a schema, any objects (e.g.: tables) associated with the schema must be dropped first.

MSSQL server memory setting

Suggested Max Memory Settings for SQL Server 2005/2008
Server Memory Options

MSSQL server memory setting

Suggested Max Memory Settings for SQL Server 2005/2008
Server Memory Options

SQL Clustered Index

A primary key is usually a unique clustered index. A primary key cannot be null.

A unique clustered index, if not a primary key, can be null.

A clustered index determines the physical order of rows in a table.

There can be at most one clustered index on a table. Non-clustered index is built upon clustered index, and can be created only when a clustered index already exists on the table.

A unique key is non-clustered by default, unless you specify it to be clustered.

[1] Creating Clustered Indexes
[2] Using Clustered Indexes
[3] CREATE INDEX (Transact-SQL)

MSSQL - Change table design not-allowed error

Sometimes, for example, when you add a column to a table, or when you want to create a primary key field on a table containing millions of rows, you may encounter the dialog message: "The changes you have made require the following tables to be dropped and re-created".

The solution is: From top menu select Tools -> Options -> Designers -> Table and Database Designers, uncheck the box "Prevent saving changes that require table re-creation". When the table is big, you may also need to increase the value of "Transaction Time-out after" textbox.

However, it is recommended not