SQL SERVER – Weekly Series – Memory Lane – #033
- by Pinal Dave
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
Spatial Database Definition and Research Documents
Here is the definition from Wikipedia about spatial database :
A spatial database is a database that is optimized to store and query data related to objects in space, including points, lines and polygons. While typical databases can understand various numeric and character types of data, additional functionality needs to be added for databases to process spatial data types.
Select Only Date Part From DateTime – Best Practice
A very common question which I receive is how to only get Date or Time part from datetime value. In this blog post I explain the same in very simple words.
T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) – CTE vs. Derived Table
I have received few emails and comments about my post SQL SERVER – T-SQL Paging Query Technique Comparison – SQL 2000 vs SQL 2005. The main question was is this can be done using CTE? Absolutely! What about Performance? It is identical! Please refer above mentioned article for the history of paging.
SQL SERVER – Cannot resolve collation conflict for equal to operation
One of the very first error I ever encountered in my career was to resolve this conflict. I have blogged about it and I have realized that many others like me who are facing this error.
LEN and DATALENGTH of NULL Simple Example
Here is the question for you what is the LEN of NULL value? Well it is very easy – just read the blog.
Recovery Models and Selection
Very simple and easy explanation of the Database Backup Recovery Model and how to select the best option for you.
Explanation SQL SERVER Hash Join
Hash join gives best performance when two more join tables are joined and at-least one of them have no index or is not sorted. It is also expected that smaller of the either of table can be read in memory completely (though not necessary).
Easy Sequence of SELECT FROM JOIN WHERE GROUP BY HAVING ORDER BY
SELECT yourcolumns
FROM tablenames
JOIN tablenames
WHERE condition
GROUP BY yourcolumns
HAVING aggregatecolumn condition
ORDER BY yourcolumns
NorthWind Database or AdventureWorks Database – Samples Databases
In this blog post we learn how to install Northwind database. I also shared the source where one can download this database as that is used in many examples on MSDN help files.
sp_HelpText for sp_HelpText – Puzzle
A simple quick puzzle – do you know the answer of it? If not, go ahead and read the blog.
2008
SQL SERVER – 2008 – Step By Step Installation Guide With Images
When SQL Server 2008 was newly introduced lots of people had no clue how to install SQL Server 2008 and the amount of the question which I used to receive were so much. I wrote this blog post with the spirit that this will help all the newbies to install SQL Server 2008 with the help of images. Still today this blog post has been bible for all of the people who are confused with SQL Server installation.
Inline Variable Assignment
I loved this feature. I have always wanted this feature to be present in SQL Server. The last time when I met developers from Microsoft SQL Server, I had talked about this feature. I think this feature saves some time but make the code more readable.
Introduction to Policy Management – Enforcing Rules on SQL Server
If our company policy is to create all the Stored Procedure with prefix ‘usp’ that developers should be just prevented to create Stored Procedure with any other prefix. Let us see a small tutorial how to create conditions and policy which will prevent any future SP to be created with any other prefix.
2009
Performance Counters from System Views – By Kevin Mckenna
Many of you are not aware of this fact that access to performance information is readily available in SQL Server and that too without querying performance counters using a custom application or via perfmon. Till now, this fact has remained undisclosed but through this post I would like to explain you can easily access SQL Server performance counter information. Without putting much effort you will come across the system viewsys.dm_os_performance_counters. As the name suggests, this provides you easy access to the SQL Server performance counter information that is passed on to perfmon, but you can get at it via tsql.
Customize Toolbar – Remove Debug Button from Toolbar
I was fond of SQL Server Debugger feature in SQL Server 2000. To my utter disappointment, this feature was withdrawn from SQL Server 2005. The button of the debugger is similar to a play button and is used to run debugging commands of Visual Studio. Because of this reason, it gets very much infuriating for developers when they are developing on both – Visual Studio and SSMS. Let us now see how we can remove debugging button from SQL Server Management Studio.
Effect of Normalization on Index and Performance
A very interesting conversation which started from twitter. If you want to read one link this is the link I encourage you to read it.
SSMS Feature – Multi-server Queries
Using SQL Server Management Studio (SSMS) DBAs can now query multiple servers from one window. It is quite common for DBAs with large amount of servers to maintain and gather information from multiple SQL Servers and create report. This feature is a blessing for the DBAs, as they can now assemble all the information instantaneously without going anywhere.
Query Optimizer Hint ROBUST PLAN – Question to You
“ROBUST PLAN” is a kind of query hint which works quite differently than other hints. It does not improve join or force any indexes to use; it just makes sure that a query does not crash due to over the limit size of row. Let me elaborate upon it in the blog post.
2010
Do you really know the difference between various date functions available in SQL Server 2012?
Here is a three part story where we explored the same with examples:
Fastest Way to Restore the Database
Difference Between DATETIME and DATETIME2
Difference Between DATETIME and DATETIME2 – WITH GETDATE
Shrinking NDF and MDF Files – Readers’ Opinion
Shrinking Database always creates performance degradation and increases fragmentation in the database. I suggest that you keep that in mind before you start reading the following comment. If you are going to say Shrinking Database is bad and evil, here I am saying it first and loud. Now, the comment of Imran is written while keeping in mind only the process showing how the Shrinking Database Operation works. Imran has already explained his understanding and requests further explanation. I have removed the Best Practices section from Imran’s comments, as there are a few corrections.
2011
Solution – Puzzle – SELECT * vs SELECT COUNT(*)
This is very interesting question and I am very confident that not every one knows the answer to this question. Let me ask you again – Which will be faster SELECT* or SELECT COUNT (*) or do you think this is apples and oranges comparison.
2012
Service Broker and CAP_CPU_PERCENT – Limiting SQL Server Instances to CPU Usage
In SQL Server 2012 there are a few enhancements with regards to SQL Server Resource Governor. One of the enhancement is how the resources are allocated. Let me explain you with examples. Let us understand the entire discussion with the help of three different examples.
Finding Size of a Columnstore Index Using DMVs
One of the very common question I often see is need of the list of columnstore index along with their size and corresponding table name. I quickly re-wrote a script using DMVs sys.indexes and sys.dm_db_partition_stats. This script gives the size of the columnstore index on disk only. I am sure there will be advanced script to retrieve details related to components associated with the columnstore index. However, I believe following script is sufficient to start getting an idea of columnstore index size.
Developer Training Resources and Summary Roundup
Developer Training - Importance and Significance - Part 1
In this part we discussed the importance of training in the real world. The most important and valuable resource any company is its employee. Employees who have been well-trained will be better at their jobs and produce a better product. An employee who is well trained obviously knows more about their job and all the technical aspects. I have a very high opinion about training employees and it is the most important task.
Developer Training – Employee Morals and Ethics – Part 2
In this part we discussed the most crucial components of training. Often employees are expecting the company to pay for their training and the company expresses no interest in training the employee. Quite often training expenses are the real issue for both the employee and employer.
Developer Training – Difficult Questions and Alternative Perspective - Part 3
This part was the most difficult to write as I tried to address a few difficult questions and answers. Training is such a sensitive issue that many developers when not receiving chance for training think about leaving the organization.
Developer Training – Various Options for Developer Training – Part 4
In this part I tried to explore a few methods and options for training. The generic feedback I received on this blog post was short and I should have explored each of the subject of the training in details. I believe there are two big buckets of training 1) Instructor Lead Training and 2) Self Lead Training.
Developer Training – A Conclusive Summary- Part 5
There is no better motivation than a personal desire to learn new technology. Honestly there is nothing more personal learning. That “change is the only constant” and “adapt & overcome” are the essential lessons of life. One cannot stop the learning and resist the change. In the IT industry “ego of knowing all” and the “resistance to change” are the most challenging issues.
A Quick Look at Logging and Ideas around Logging
Question: What is the first thing comes to your mind when you hear the word “Logging”? Strange enough I got a different answer every single time. Let me just list what answer I got from my friends. Let us go over them one by one.
Beginning Performance Tuning with SQL Server Execution Plan
Solution of Puzzle – Swap Value of Column Without Case Statement
Earlier this week I asked a question where I asked how to Swap Values of the column without using CASE Statement. Read here: SQL SERVER – A Puzzle – Swap Value of Column Without Case Statement. I have proposed 3 different solutions in the blog posts itself. I had requested the help of the community to come up with alternate solutions and honestly I am stunned and amazed by the qualified entries.
Reference: Pinal Dave (http://blog.sqlauthority.com)
Filed under: Memory Lane, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology
