Force Index Use

An index hint forces the  query optimizer to use the index specified in the hint to retrieve the data . Index hints can be nice to use in the short-term for investigating, testing, and debugging.  However, they are almost never the correct long-term solution for fixing query performance. It’s better to look for the root-cause... » read more

SET QUOTED_IDENTIFIER

Causes SQL Server to follow the ISO rules regarding quotation mark delimiting identifiers and literal strings. Identifiers delimited by double quotation marks can be either Transact-SQL reserved keywords or can contain characters not generally allowed by the Transact-SQL syntax rules for identifiers. When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks,... » read more

UPDATE failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’

Error UPDATE failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934). Problem If you do index maintenance using DMO,... » read more

SQL Compare Scripts Execution

Running scripts from SQL Compare Create table objects first then create rest of database objects. Not NULL column definition – put in a default value. Object Reference error – Drop referenced object and then drop the object. Recreate object and then recreate the referenced object. Errors in general – comment out specific sections until run... » read more

SQL Server Drive Setup

The ideal database drives setup. Drive Use C: Windows OS System D: SQL Server, Database Engine, SQL Services X:\MSSQL\Data Data Files L:\MSSQL\Log Transaction Log Files I:\MSSQL\ Index Files T:\MSSQL\Data TempDB H:\MSSQL\Backup Backups

Database Types

Type Description Data Replication Transactional Used to record transactions as they come in from source. Yes Queue Used to queue data for processing purposes. Yes Staging Used to process data. Once the data is processed, the data can be discarded. No Data Staging Used to move/copy data from different environments. No Archive Used to store... » read more

Database Deployment Procedure

# Step 1 Pull latest version of code from respository and build database locally from scripts. 2 Disable all jobs and make sure no jobs are running on deployment database server. 3 Create database backups on deployment database server. 4 Apply “Alter” scripts. 5 Compare local databases with environment databases using “SQL Compare”. 6 Re-enable... » read more

Database Build Script

Database Folders Alter Alter sql scripts. Data Data sql scripts. DataTypes Data type sql scripts. DBScripts Create database sql scripts. ForeignKeys Foreign keys sql scripts. Functions Function sql scripts. Indexes Index sql scripts. PartitionFunctions Partition function sql scripts. PartitionSchemes Partition scheme sql scripts. Schemas Schema sql scripts. SQLJobs SQL job sql scripts. stbDDL Definition table... » read more

SSMS Tricks

Shortcuts Ctrl + C Copy Text Ctrl + V Paste Text Ctrl + X Cut Text Ctrl + A Select All Text Ctrl + W Select Word F5 Execute Query Ctrl + E Execute Query Ctrl + F Find Text Ctrl + H Replace Text Tab Increase Tab Shift + Tab Decrease Tab Ctrl +... » read more