SQL

T-Sql and Sql Server related tips and tricks

SQL Server - Kill any live connections to the DB

Often you need to restore a DB or take oit offline only to find out the process aborts due to active connected sessions to the DB. Here is a quick sript that will kill all active sessions to the DB.USE [master];DECLARE @kill varchar(8000) = ''; SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';' FROM sys.dm_exec_sessionsWHERE database_id = db_id('myDataBase')EXEC(@ki... ......

LINQ Contains Case Insensitive

Goal: Search for content in the database via LINQ to ENTITIES or LINQ to SQL that is case-insensitive (case-agnostic). Example: var con = BusinessContext.Create(); var owners = con.Shipments.Where(s => s.Dispatches.All(x => x.DriverLBCompany.Contains(... ).Select(x => x); This will fetch all dispatches where the DriverLBCompany contains the word “(exempt)”. You do not have to be concerned about the case as SQL Server is case-agnostic when it performs filtering. The sql being generated ......

Backing up SQL Server to Windows Azure Store using Credentials

Goal: How to backup my SQL Server database to a Windows Azure Store, which is just a folder on your Azure account. Of course you need a Windows Azure account to do this with a Store subscription. In your store subscription you will have a Manage Keys option which will show you your keys needed to connect to your Windows Azure account's Store.You also will need to create a CREDENTIALS file in SQL Server Management Studio that contains your credentials info to use when SQL Manager tries to connect ......

T-SQL: Target server product version and edition

To find out what the target server product version and edition is:SELECT SERVERPROPERTY('ProductVers... SERVERPROPERTY('Edition');This may seem trivial, but a mistake some folks make is that just because you are for example running SQL Server 2012 Enterprise does not mean the target server you are connecting to is. For example, you are wondering why the "SQL Server Agent" does not appear on the tree in your Object Explorer in SQL Server Management studio. Well it could be that your target ......

T-SQL IntelliSense: Some objects or columns don't show up, or are incorrectly marked as invalid

Reasons by T-SQL intellisense does not pickup and complete a list or that an object or column is underlined and marked as incorrect. There could be multiple reasons but I will cover the three most prevalent reasons here:The most common reason is that your local in-memory cache has become stale. Typically this happens if you switch databases, or if you make DDL changes in your query window. Of course if more than one person is actively making changes to the schema will also render your cache stale ......

T-Sql: How to find records added in duplicate within a few seconds from each other

Goal: You need to find out if in a particular table, records that seem to have been added in duplicate due to a possible double-click. In this example I am querying a Shipment table and the natural key is the GBL number.Solution:In this example we will be looking for entries within a minute from each of the duplicate records (30 seconds before to 30 seconds after). See minutes example commented out as an example.select s.GBL, count(*)from Shipment swhere s.GBL is not nulland 1 < (select count(*)from ......

T-Sql: How to find records added in duplicate within a few seconds from each other

Goal: You need to find out if in a particular table, records that seem to have been added in duplicate due to a possible double-click. In this example I am querying a Shipment table and the natural key is the GBL number.Solution:In this example we will be looking for entries within a minute from each of the duplicate records (30 seconds before to 30 seconds after). See minutes example commented out as an example.select s.GBL, count(*)from Shipment swhere s.GBL is not nulland 1 < (select count(*)from ......

After restoring a SQL Server database from another server - get login fails

Issue: After you have restored a sql server database from another server, lets say from production to a Q/A environment, you get the "Login Fails" message for your service account. Reason: User logon information is stored in the syslogins table in the master database. By changing servers, or by altering this information by rebuilding or restoring an old version of the master database, the information may be different from when the user database dump was created. If logons do not exist for the users, ......

How to find your table within a server

Goal: How to find a table on your server: SET NOCOUNT ON CREATE TABLE myTable99(TABLE_CATALOG sysname, TABLE_SCHEMA sysname, TABLE_NAME sysname, TABLE_TYPE varchar(50)) GO CREATE PROC usp_FindMyTable (@TABLE_NAME sysname = null) AS SET NOCOUNT ON TRUNCATE TABLE myTable99 IF @TABLE_NAME IS NULL BEGIN PRINT 'No Table to look for. Please supply a tabke name. Like: ' + CHAR(13) + ' EXEC usp_FindMyTable Orders' GOTO usp_FindMyTable_Exit END DECLARE @MAX_dbname sysname, @dbname sysname, @sql varchar(8000) ......

Temporarily disabling foreign key constraints in SQL Server

Goal: Is to temporarily disable all foreign key constraint and later enable the Constraint again?Solutions-- Disable all the constraint in databaseEXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"-- Enable all the constraint in databaseEXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all" ......

Why or why not to pad an index with fill fator on sql server

Goal:Using sql server's fill factor to pad an index with free space seems like a great idea if you have high fragmentation on an index or you think you have a very high write to read ratio, or simply a write intensive table.The problem:So you go an add lets say a 70% fill factor and suddenly your reads slow down. Why is that?! I just improved performance by adding free space to the leaf pages of the index.You assumed that since the index has to be updated frequently due to inserts or updates that ......

SQL Server - index usage/utilization stats

Goal:Find out what indexes are being used, if at all, and if the stats on seeks, scans and lookups on the indexes:index-seek (preferred)index-scan (worst)index-lookupSolution... this scrip against your databaseSELECTo.name AS object_name,i.name AS NameOfIndex,i.type_desc as IsItClustered,u.user_seeks as IndexSeeks, u.user_scans as IndexScans,u.user_lookups as IndexLookups, u.user_updates as HowManyUpdates,o.typeFROMsy... iJOINsys.objects o ON i.object_id = o.object_idLEFT JOINsys.dm_db_index_usage_s... ......

SQL Server - Rebuilding Indexes

Goal: Rebuild indexes in SQL server. This can be done one at a time or with the example script below to rebuild all index for a specified table or for all tables in a given database. Why? The data in indexes gets fragmented over time. That means that as the index grows, the newly added rows to the index are physically stored in other sections of the allocated database storage space. Kind of like when you load your Christmas shopping into the trunk of your car and it is full you continue to load some ......

T-Sql - rename a table or column name via stored procedure

Goal: Rename a database table or column name using a t-sql stored procedure. How to: Rename a table: EXEC sp_rename 'TableToBeRename', 'NewTableName' Rename a column: EXEC sp_rename @objname = 'TableNameColumnToBeRenamed, @newname = 'NewColumnName', @objtype = 'COLUMN' ......

How to stop a run-away SQL transaction

The Problem: Recently was testing my web-app against a SQL-Server 2008 database and got a time-out expired message from the SQL database. No matter what I did after that it just did not want to drop the transaction and I continually got the time-out. I needed to find and stop the SQL transaction. The solution: Sign onto the SQL-Server with the Enterprise Manager. Execute sp_who2 and look for rows that are blocked on another connection (the BlkBy column will have the spid of the blocking connection ......