<body><script type="text/javascript"> function setAttributeOnload(object, attribute, val) { if(window.addEventListener) { window.addEventListener('load', function(){ object[attribute] = val; }, false); } else { window.attachEvent('onload', function(){ object[attribute] = val; }); } } </script> <div id="navbar-iframe-container"></div> <script type="text/javascript" src="https://apis.google.com/js/plusone.js"></script> <script type="text/javascript"> gapi.load("gapi.iframes:gapi.iframes.style.bubble", function() { if (gapi.iframes && gapi.iframes.getContext) { gapi.iframes.getContext().openChild({ url: 'https://www.blogger.com/navbar.g?targetBlogID\x3d8211560\x26blogName\x3dTech+Tips,+Tricks+%26+Trivia\x26publishMode\x3dPUBLISH_MODE_BLOGSPOT\x26navbarType\x3dBLUE\x26layoutType\x3dCLASSIC\x26searchRoot\x3dhttp://mvark.blogspot.com/search\x26blogLocale\x3den\x26v\x3d2\x26homepageUrl\x3dhttp://mvark.blogspot.com/\x26vt\x3d-5147029996388199615', where: document.getElementById("navbar-iframe-container"), id: "navbar-iframe" }); } }); </script>

Tech Tips, Tricks & Trivia

by 'Anil' Radhakrishna
An architect's notes, experiments, discoveries and annotated bookmarks.

Search from over a hundred HOW TO articles, Tips and Tricks


Notes from Pluralsight course - SQL Server Questions and Answers

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.


Labels:

Tweet this | Google+ it | Share on FB

« Home | Next »
| Next »
| Next »
| Next »
| Next »
| Next »
| Next »
| Next »
| Next »
| Next »

»

Post a Comment