I agree with Cade Roux. This article should get you on the right track: Indexes in SQL Server 2005/2008 – Best Practices, Part 1 Indexes in SQL Server 2005/2008 – Part 2 – Internals One thing to note, clustered indexes should have a unique key (an identity column I would recommend) as the first column. … Read more
Is it correct to do the following? IF EXISTS(SELECT * FROM dbo.Scores) DROP TABLE dbo.Scores No. That will drop the table only if it contains any rows (and will raise an error if the table does not exist). Instead, for a permanent table you can use IF OBJECT_ID(‘dbo.Scores’, ‘U’) IS NOT NULL DROP TABLE dbo.Scores; … Read more
Optimistic Locking is a strategy where you read a record, take note of a version number (other methods to do this involve dates, timestamps or checksums/hashes) and check that the version hasn’t changed before you write the record back. When you write the record back you filter the update on the version to make sure … Read more
There is no difference. Reason: Books on-line says “COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )“ “1” is a non-null expression: so it’s the same as COUNT(*). The optimizer recognizes it for what it is: trivial. The same as EXISTS (SELECT * … or EXISTS (SELECT 1 … … Read more
The MSDN documentation for datetime recommends using datetime2. Here is their recommendation: Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications. datetime2 has larger … Read more
Most databases support != (popular programming languages) and <> (ANSI). Databases that support both != and <>: MySQL 5.1: != and <> PostgreSQL 8.3: != and <> SQLite: != and <> Oracle 10g: != and <> Microsoft SQL Server 2000/2005/2008/2012/2016: != and <> IBM Informix Dynamic Server 10: != and <> InterBase/Firebird: != and <> … Read more
The DBCC CHECKIDENT management command is used to reset identity counter. The command syntax is: DBCC CHECKIDENT (table_name [, { NORESEED | { RESEED [, new_reseed_value ]}}]) [ WITH NO_INFOMSGS ] Example: DBCC CHECKIDENT (‘[TestTable]’, RESEED, 0); GO It was not supported in previous versions of the Azure SQL Database but is supported now. Thanks … Read more
SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description FROM Orders JOIN LineItems ON LineItems.LineItemGUID = ( SELECT TOP 1 LineItemGUID FROM LineItems WHERE OrderID = Orders.OrderID ) In SQL Server 2005 and above, you could just replace INNER JOIN with CROSS APPLY: SELECT Orders.OrderNumber, LineItems2.Quantity, LineItems2.Description FROM Orders CROSS APPLY ( SELECT TOP 1 LineItems.Quantity, LineItems.Description FROM LineItems WHERE … Read more
Not sure why no one suggested but I use sp_fkeys to query foreign keys for a given table: EXEC sp_fkeys ‘TableName’ You can also specify the schema: EXEC sp_fkeys @pktable_name=”TableName”, @pktable_owner=”dbo” Without specifying the schema, the docs state the following: If pktable_owner is not specified, the default table visibility rules of the underlying DBMS apply. … Read more
If you want to disable all constraints in the database just run this code: — disable all constraints EXEC sp_MSforeachtable “ALTER TABLE ? NOCHECK CONSTRAINT all” To switch them back on, run: (the print is optional of course and it is just listing the tables) — enable all constraints exec sp_MSforeachtable @command1=”print ‘?'”, @command2=”ALTER TABLE … Read more