In the Pluralsight course "SQL Server Questions and Answers", Pinal & Vinod debunk some SQL Server misconceptions with examples and highlight uncommon facts -
- Use SCOPE_IDENTITY() instead of @@IDENTITY or IDENT_CURRENT("tablename") to get the latest IDENTITY value for the table in the session
- @@IDENTITY returns the last IDENTITY value produced on a connection
- SCOPE_IDENTITY() returns the last IDENTITY value produced on a connection (explicity created by you rather than a trigger) and by a statement in the same scope
- IDENT_CURRENT("tablename") returns the last IDENTITY value produced in a table, regardless of the connection
- Identity column value can be positive or negative
- Identity column can be reseeded with DBCC CHECKIDENT command
- Deleting records from table using DELETE does not reset Identity values whereas deleting records from table using TRUNCATE resets Identity values.
- TRUNCATE statements are logged and can be part of transaction. Such transactions can be rolled back.
- Executing TRUNCATE is not possible when a table is referenced by a foreign key or if the table is used in replication or with Indexed views
- WHERE can be used with SELECT, DELETE, UPDATE statements whereas HAVING is used along with GROUP BY clause in a SELECT statement
- The order of the conditions in the WHERE clause does not affect index used. This can be verified from the Execution plan.
- If parenthesis is used with a combination of OR & AND conditions, the result & index usage may vary
- Table variables & Temporary tables both exist in TempDB
- Table variables too can have a clustered index but they do not participate in the Transaction context.
- A Table variable cannot be assigned to another Table variable
- A Table variable cannot be truncated
- Stored procedures are not compiled when created.
- Stored procedures are compiled on first run & this can be verified by watching the SP:CacheInsert event in Profiler.
- Using Filtered Index (uses WHERE in an UNIQUE INDEX) which is new in SQL Server 2008, it is possible to have multiple nulls even when UNIQUE constraint is imposed.
- In SQL Server 2008, date and time can be stored independent of each other in a db using the DATE & TIME datatypes
- DateTime2 datatype has a precision of 100th of a nanosecond.
- Precision of SmallDateTime datatype is 1 minute
- Multiple UNIQUE constraints can be defined on a table
- A quick way to populate table rows - CREATE TABLE Test (id INT IDENTITY(1,1), Long_Name CHAR(7500) DEFAULT 'dummy')
- INSERT INTO Test DEFAULT VALUES; GO 15; --inserts 15 rows
- fn_dblog is an undocumented system UDF that lets you to read from your transaction log
- Lock duration is dependent on the ISOLATION LEVEL (READ COMMITTED, READ UNCOMMITTED, REPEATABLE READ, SERIALIZABLE)
- When a nested transaction is rolled back it always rolls back to the outermost BEGIN TRANSACTION statement (unless SAVEPOINT is used)
- DENY takes precedence over GRANT except at a column level GRANT
- Blocked process report Event under Error & Warnings section of Events Selection tab in the Trace Properties dialog box in Profiler helps in analyzing Blocking issues
- Recent Expensive Queries tab within Activity Monitor in SSMS shows queries that you can optimize.
