HomeTom - CS

The solution if found at: https://msdn.microsoft.com/en-us/library/ms345408.aspx The steps are: 1) Change file path: ALTER DATABASE [db_...

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

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 w...

declare CUR cursor for     SELECT name     FROM master..sysdatabases     where name like 'ABC_%' declare @db varchar(50) decla...

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, bec...

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 colu...

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

Estimating Data Compression Savings in SQL Server 2008 and 2008 R2

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

p.103. CH 7. Partitioning p.219. CH 15. DB snapshots. CREATE database AS Snapshot p.375. Part VII. Business Intelligence. CH24. SSIS - ...

1. Examples of using sp_executesql with output parameters. Two scenarios: 1) execute a query, 2) execute a stored procedure. See [1] http...

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 man...

-- -- This script solves the problem: -- Given an Employee table, each rows has empID, mgrID, firstName, lastName, -- return all the mana...

- 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/t...

There are some noticeable issues with MSSQL Server 2008. - Cannot call function from remote machine. - IDE issue: right click on a datab...

DROP SCHEMA (Transact-SQL) DROP SCHEMA schema_name To drop a schema, any objects (e.g.: tables) associated with the schema must be dropped f...

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

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

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...

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 r...