TSQL - Index and Internals

SQL Blogs
https://sqlwithmanoj.com
http://www.techbrothersit.com/
http://www.gandhipritesh.com/ --

https://technet.microsoft.com/en-us/virtuallabs/ -- Microsoft Virtual Labs
SQL Books Online - https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql

Encrypting and Decrypting SQL Server Stored Procedures, Views and User-Defined Functions

https://www.mssqltips.com/sqlservertip/2964/encrypting-and-decrypting-sql-server-stored-procedures-views-and-userdefined-functions/

Lock Escalation

https://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx

Deadlock

https://technet.microsoft.com/en-us/library/ms177433(v=sql.105).aspx
SQL Server Index Design Guide

https://technet.microsoft.com/en-us/library/jj835095(v=sql.110).aspx

https://www.simple-talk.com/sql/performance/14-sql-server-indexing-questions-you-were-too-shy-to-ask/

http://sqlity.net/en/2445/b-plus-tree/

https://www.mssqltips.com/sqlservertip/1206/understanding-sql-server-indexing/

Force Index

http://blog.sqlauthority.com/2009/02/07/sql-server-introduction-to-force-index-query-hints-index-hint/

 Running Total

http://blog.sqlauthority.com/2014/10/04/sql-server-how-to-find-running-total-in-sql-server/

SELECT ID, Value,SUM(Value) OVER(ORDER BY ID ROWS UNBOUNDED PRECEDING) AS RunningTotalFROM TestTable

http://blog.sqlauthority.com/2008/03/02/sql-server-how-to-retrieve-top-and-bottom-rows-together-using-t-sql/

http://blog.sqlauthority.com/2015/05/05/sql-server-generating-row-number-without-ordering-any-columns/

Performance Tuning

http://blog.sqlauthority.com/2008/04/06/sql-server-15-best-practices-for-better-database-performance/

http://blog.sqlauthority.com/2010/01/12/sql-server-fragmentation-detect-fragmentation-and-eliminate-fragmentation/

https://www.mssqltips.com/sql-server-tip-category/9/performance-tuning/

https://www.mssqltips.com/sqlservertip/2140/improving-sql-server-performance-when-using-table-variables/

http://blog.sqlauthority.com/2010/02/16/sql-server-stored-procedure-optimization-tips-best-practices/

https://technet.microsoft.com/en-us/library/ms191426(v=sql.105).aspx

Hints

http://blog.sqlauthority.com/2009/11/19/sql-server-understanding-table-hints-with-examples/

Generating SQL Server Test Data with Visual Studio 2010

https://www.mssqltips.com/sqlservertip/2190/generating-sql-server-test-data-with-visual-studio-2010/

Nth Highest Salary TSQL

http://www.programmerinterview.com/index.php/database-sql/find-nth-highest-salary-sql/

https://www.codeproject.com/articles/38998/most-commonly-used-functions-in-sql-server

Interview Questions

https://intellipaat.com/interview-question/sql-interview-questions/

http://blog.sqlauthority.com/2011/07/01/sql-server-interview-questions-and-answers-frequently-asked-questions-complete-downloadable-list-day-0-of-31/

http://www.dotnettricks.com/learn/sqlserver/difference-between-stored-procedure-and-function-in-sql-server

http://blog.sqlauthority.com/2007/05/29/sql-server-user-defined-functions-udf-limitations/

http://blog.sqlauthority.com/2007/09/07/sql-server-correlated-and-noncorrelated-subquery-introduction-explanation-and-example/

http://www.studytonight.com/dbms/database-normalization.php

Table Partitioning in SQL Server

https://www.cathrinewilhelmsen.net/2015/04/12/table-partitioning-in-sql-server/

https://technet.microsoft.com/en-us/library/ms178148(v=sql.105).aspx

https://www.mssqltips.com/sqlservertip/1796/creating-a-table-with-horizontal-partitioning-in-sql-server/

https://sqlandme.com/2011/07/22/sql-server-horizontally-partitioning-a-sql-server-database-table/

http://stackoverflow.com/questions/18302773/what-are-horizontal-and-vertical-partitions-in-database-and-what-is-the-differen

Partition Switching

https://technet.microsoft.com/en-us/library/ms191160(v=sql.105).aspx

https://www.cathrinewilhelmsen.net/2015/04/19/table-partitioning-in-sql-server-partition-switching/#switch1

DB Unit Test

http://blog.sqlauthority.com/2016/10/18/creating-running-sql-server-unit-test-best-ways-test-sql-queries/

http://tsqlt.org/

http://blog.sqlauthority.com/2016/04/03/sql-server-changed-password-sql-login-interview-question-week-065/

http://blog.sqlauthority.com/2013/02/21/sql-server-shortcut-to-select-only-1-row-from-table/

Keys

http://www.agiledata.org/essays/keys.html

Connection Pooling

https://www.pythian.com/blog/sql-server-understanding-and-controlling-connection/

Maximum Capacity Specifications for SQL Server

https://msdn.microsoft.com/en-in/library/ms143432(v=sql.110).aspx

LOG SHIPPING VS. MIRRORING VS. REPLICATION

https://nilebride.wordpress.com/2011/07/24/log-shipping-vs-mirroring-vs-replication/

https://www.codeproject.com/articles/715550/sql-server-replication-step-by-step

https://www.mssqltips.com/sqlservertip/2301/step-by-step-sql-server-log-shipping/

Differences Among CTE, Derived Table, Temp Table, Sub Query And Temp Variable

http://www.c-sharpcorner.com/article/difference-among-cte-derived-table-temp-table-sub-query/

SQL Server Versions

https://www.mssqltips.com/sqlservertutorial/2202/choosing-a-version-of-sql-server/

https://www.microsoft.com/en-us/sql-server/sql-server-editions

Page Split

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/06/28/what-is-a-page-split-what-happens-why-does-it-happen-why-worry.aspx

http://blog.sqlauthority.com/2011/08/20/sql-server-tips-from-the-sql-joes-2-pros-development-series-introduction-to-page-split-day-20-of-35/

http://www.sql-server-performance.com/2006/reduce-page-splits/

Indexed Views

https://www.simple-talk.com/sql/learn-sql-server/sql-server-indexed-views-the-basics/

https://msdn.microsoft.com/en-IN/library/ms191432.aspx

Stored Procedures

http://blog.sqlauthority.com/2013/05/27/sql-server-how-to-insert-data-from-stored-procedure-to-table-2-different-methods/

http://blog.sqlauthority.com/2007/04/13/sql-server-stored-procedures-advantages-and-best-advantage/

Estimated Vs Actual Execution Plans

http://www.sqlshack.com/sql-server-estimated-vs-actual-execution-plans/

Find Currently Running Query – T-SQL

http://blog.sqlauthority.com/2009/01/07/sql-server-find-currently-running-query-t-sql/

Identify Blocking Query

http://blog.sqlauthority.com/2010/10/06/sql-server-quickest-way-to-identify-blocking-query-and-resolution-dirty-solution/

Get size of all tables in database

http://www.sqlmatters.com/Articles/Listing%20all%20tables%20in%20a%20database%20and%20their%20row%20counts%20and%20sizes.aspx

Creating CSV Files Using BCP and Stored Procedures

https://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/

XML Reading / Parsing

https://blog.sqlauthority.com/2009/02/13/sql-server-simple-example-of-reading-xml-file-using-t-sql/

https://www.red-gate.com/simple-talk/sql/database-administration/manipulating-xml-data-in-sql-server/

https://sqlsunday.com/2013/04/28/parsing-xml-data/


Change Data Capture (CDC)
https://www.red-gate.com/simple-talk/sql/learn-sql-server/introduction-to-change-data-capture-cdc-in-sql-server-2008/
https://www.mssqltips.com/sqlservertip/1474/using-change-data-capture-cdc-in-sql-server-2008/


Merge Statement
https://stackoverflow.com/questions/6592643/how-to-avoid-inserting-duplicate-records-when-using-a-t-sql-merge-statement
Optimizing MERGE Statement Performance
https://technet.microsoft.com/en-us/library/cc879317(v=sql.105).aspx
https://www.sqlservercentral.com/Forums/Topic1465931-391-1.aspx
CTE

Recursive CTE error – The maximum recursion 100 has been exhausted before statement completion

https://sqlwithmanoj.com/tag/option-maxrecursion/




The OUTPUT Clause for UPDATE Statements
http://www.sqlservercentral.com/articles/T-SQL/156604/
Columnstore Indexes in SQL Server 2012|
https://www.red-gate.com/simple-talk/sql/database-administration/columnstore-indexes-in-sql-server-2012/

Logical Query Processing Phases – Order of Statement Execution
https://blog.sqlauthority.com/2009/04/06/sql-server-logical-query-processing-phases-order-of-statement-execution/
http://sqlmag.com/sql-server/logical-query-processing-what-it-and-what-it-means-you
Interesting Observation of ON Clause on LEFT JOIN – How ON Clause affects Resultset in LEFT JOIN
https://blog.sqlauthority.com/2009/03/15/sql-server-interesting-observation-of-on-clause-on-left-join-how-on-clause-effects-resultset-in-left-join/
SQL Server Professional Development Interview Questions DBA Tips
https://www.mssqltips.com/sql-server-tip-category/46/professional-development-interview-questions-dba/
Database Snapshots
https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-snapshots-sql-server
https://technet.microsoft.com/en-us/library/ms187054(v=sql.105).aspx
https://docs.microsoft.com/en-us/sql/relational-databases/databases/create-a-database-snapshot-transact-sql
Using Session Context Information
https://msdn.microsoft.com/en-us/library/ms189252(v=sql.90).aspx
Create Primary Key with Specific Name when Creating Table
https://blog.sqlauthority.com/2010/04/22/sql-server-create-primary-key-with-specific-name-when-creating-table/
Get a List of Fixed Hard Drive and Free Space on Server
https://blog.sqlauthority.com/2009/08/05/sql-server-get-a-list-of-fixed-hard-drive-and-free-space-on-server/
Get Directory Structure using Extended Stored Procedure xp_dirtree
https://blog.sqlauthority.com/2011/11/26/sql-server-get-directory-structure-using-extended-stored-procedure-xp_dirtree/