SQL SERVER – Weekly Series – Memory Lane – #036

Here is the list of selected articles of SQLAuthority.com across all these years. Instead of just listing all the articles I have selected a few of my most favorite articles and have listed them here with additional notes below it. Let me know which one of the following is your favorite article from memory lane.

2007

Explanation of WITH ENCRYPTION clause for Stored Procedure and User Defined Functions
How to hide code of my Stored Procedure that no one can see it? 2) Our DBA has left the job and one of the function which retrieves important information is encrypted, how can we decrypt it and find original code?

Comparison SP_EXECUTESQL vs EXECUTE/EXEC
sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement a number of times when the change in parameter values to the statement is the only variation.

Comparison : Similarity and Difference #TempTable vs @TempVariable
#TempTable and @TempVariable are different things with different scope. Their purpose is different but highly overlapping. TempTables are originated for the storage and & storage & manipulation of temporal data. TempVariables are originated (SQL Server 2000 and onwards only) for returning date-sets from table-valued functions.

Definition, Comparison and Difference between HAVING and WHERE Clause
HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

2008

Insert Multiple Records Using One Insert Statement – Use of Row Constructor
How to insert multiple records using One Insert Statement is one of the most asked question in this blog post. Since I have written this blog post, every single time when I am asked this question, I have been referring my readers to this blog post.

Introduction to New Feature of Backup Compression
Backup and Data Storage is my most favorite subject and I have not written about this for some time. I was experimenting with a new feature of SQL Server 2008 and I come across a very interesting feature of Backup compression.

Difference Between Database Mail and SQLMail
Database mail is a newly introduced concept in SQL Server 2005 and it is the replacement of SQLMail of SQL Server earlier version. Database Mail has many enhancements over SQLMail.

Introduction to Row Compression
The row Compression feature applies to zeros and null values and optimize their space in SQL Server. In fact, due to Row Compression feature SQL Server does not take any disk space for zero or null values.

2009

Difference between Line Feed (\n) and Carriage Return (\r) – T-SQL New Line Char
What is the difference between Line Feed (\n) and Carriage Return (\r)?

Prior to continuing with this article let us first look into a few synonyms for LF and CR.

Line Feed – LF – \n – 0x0a – 10 (decimal)

Carriage Return – CR – \r – 0x0D – 13 (decimal)

2010

Introduction to Best Practices Analyzer – Quick Tutorial
This blog post explains step by step how one can use Best Practices Analyzer tool from Microsoft.

Parallelism – Row per Processor – Row per Thread
This blog post tries to answer following question – “When SQL Server executes any query on multiple processors, do all processors process equal numbers of rows?”

View XML Query Plans in SSMS as Graphical Execution Plan
You can save execution plan with the extension .sqlplan. The same plan can be sent to another user via email or a USB drive. Another user can just double click on the file and open the execution plan at another local computer without physically having any underlying object.

Index Levels, Page Count, Record Count and DMV – sys.dm_db_index_physical_stats
The following is the diagram on Clustered Index that I have quickly drawn using MS Word for the said developer.

Clustered Index B-Tree
Clustered Index B-Tree

PowerShell Version Info
I recently had a scenario where I was listing a PowerShell version installed in my computer systems. While searching online, I found two different commands that can determine the version of PowerShell. One of them worked fine in Version 1, while both worked on Version 2.

2011

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Introduction – Day 1 of 31

In this very first blog post – various aspect of the interview questions and answers are discussed. Some people like the subject for their helpful hints and thought provoking subject, and others dislike these posts because they feel it is nothing more than cheating.  I’d like to discuss the pros and cons of a Question and Answer format here.

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 2 of 31

  • What is RDBMS?
  • What are the Properties of the Relational Tables?
  • What is Normalization?
  • What is De-normalization?
  • How is ACID property related to Database?
  • What are the Different Normalization Forms?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 3 of 31

  • What is a Stored Procedure?
  • What is a Trigger?
  • What are the Different Types of Triggers?
  • What is a View?
  • What is an Index?
  • What is a Linked Server?
  • What is a Cursor?
  • What is Collation?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 4 of 31

  • What is the Difference between a Function and a Stored Procedure?
  • What is subquery? Explain the Properties of a Subquery?
  • What are Different Types of Join?
  • What are Primary Keys and Foreign Keys?
  • What is User-defined Functions? What are the types of User-defined Functions that can be created?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 5 of 31

  • What is an Identity?
  • What is DataWarehousing?
  • What languages BI uses to achieve the goal?
  • What is Standby Servers? Explain Types of Standby Servers.
  • What is Dirty Read?
  • Why can’t I use Outer Join in an Indexed View?
  • What is the Correct Order of the Logical Query Processing Phases?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 6 of 31

  • Which TCP/IP port does the SQL Server run on? How can it be Changed?
  • What are the Difference between Clustered and a Non-clustered Index?
  • What are the Different Index Configurations a Table can have?
  • What are Different Types of Collation Sensitivity?
  • What is OLTP (Online Transaction Processing)?
  • What’s the Difference between a Primary Key and a Unique Key?
  • What is Difference between DELETE  and TRUNCATE Commands?

2012

Validating Spatial Object as NULL using IsNULL
How is NULL handled by spatial functions? Well, NULL is NULL. It is very easy to work with NULL. There are two different ways to validate if the passed in the value is NULL or not. There are two different methods described in this blog post where this is discussed in detail.

Discard Results After Query Execution – SSMS
In SSMS 2012 go to Tools >> Options >> Query Results > SQL Server >> Results to Grid >> Discard Results After Query Execution. When enabled this option will discard results after the execution. The advantage of disabling the option is that it will improve the performance by using less memory.

Monitoring SQL Server Database Transaction Log Space Growth – DBCC SQLPERF(logspace) – Puzzle for You
In this blog post I have described how one can monitor the log space growth. After I have described the DMV method, I have asked two interesting puzzle. If you can answer it today, it is great, but if you can’t answer it, you need to continue reading the blog post.

Tricks to Comment T-SQL in SSMS – SQL in Sixty Seconds #019 – Video

Retrieve SQL Server Installation Date Time
Do you know when was your SQL Server installed? If you do not know you can figure it out using two different methods described in this blog post.

NTFS File System Performance for SQL Server
In this blog post I describe following a very essential topics which directly relates to SQL Server Performance and lots of other issues. I personally liked this blog post a lot.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s