SQL SERVER – Weekly Series – Memory Lane – #053 – Final Post in Series
- by Pinal Dave
It has been a fantastic journey to write memory lane series for an entire year. This series gave me the opportunity to go back and see what I have contributed to this blog throughout the last 7 years. This was indeed fantastic series as this provided me the opportunity to witness how technology has grown throughout the year and how I have progressed in my career while writing this blog post.
This series was indeed fantastic experience readers as many joined during the last few years and were not sure what they have missed in recent years.
Let us continue with the final episode of the Memory Lane Series.
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
Get Current User – Get Logged In User
Here is the straight script which list logged in SQL Server users.
Disable All Triggers on a Database – Disable All Triggers on All Servers
Question : How to disable all the triggers for a database? Additionally, how to disable all the triggers for all servers? For answer execute the script in the blog post.
Importance of Master Database for SQL Server Startup
I have received following questions many times. I will list all the questions here and answer them together.
What is the purpose of Master database?
Should our backup Master database?
Which database is must have database for SQL Server for startup?
Which are the default system database created when SQL Server 2005 is installed for the first time?
What happens if Master database is corrupted?
Answers to all of the questions are very much related.
2008
DECLARE Multiple Variables in One Statement
SQL Server is a great product and it has many features which are very unique to SQL Server. Regarding feature of SQL Server where multiple variable can be declared in one statement, it is absolutely possible to do.
2009
How to Enable Index – How to Disable Index – Incorrect syntax near ‘ENABLE’
Many times I have seen that the index is disabled when there is a large update operation on the table. Bulk insert of very large file updates in any table using SSIS is usually preceded by disabling the index and followed by enabling the index. I have seen many developers running the following query to disable the index.
2010
List of all the Views from Database
Many emails I received suggesting that they have hundreds of the view and now have no clue what is going on and how many of them have indexes and how many does not have an index. Some even asked me if there is any way they can get a list of the views with the property of Index along with it.
Here is the quick script which does exactly the same. You can also include many other columns from the same view.
Minimum Maximum Memory – Server Memory Options
I was recently reading about SQL Server Memory Options over here. While reading this one line really caught my attention is minimum value allowed for maximum memory options. The default setting for min server memory is 0, and the default setting for max server memory is 2147483647. The minimum amount of memory you can specify for max server memory is 16 megabytes (MB).
2011
Fundamentals of Columnstore Index
There are two kinds of storage in a database. Row Store and Column Store. Row store does exactly as the name suggests – stores rows of data on a page – and column store stores all the data in a column on the same page. These columns are much easier to search – instead of a query searching all the data in an entire row whether the data are relevant or not, column store queries need only to search a much lesser number of the columns.
How to Ignore Columnstore Index Usage in Query
In summary the question in simple words “How can we ignore using the column store index in selective queries?” Very interesting question – you can use I can understand there may be the cases when the column store index is not ideal and needs to be ignored the same. You can use the query hint IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX to ignore the column store index. The SQL Server Engine will use any other index which is best after ignoring the column store index.
2012
Storing Variable Values in Temporary Array or Temporary List
SQL Server does not support arrays or a dynamic length storage mechanism like list. Absolutely there are some clever workarounds and few extra-ordinary solutions but everybody can;t come up with such solution. Additionally, sometime the requirements are very simple that doing extraordinary coding is not required. Here is the simple case.
Move Database Files MDF and LDF to Another Location
It is not common to keep the Database on the same location where OS is installed. Usually Database files are in SAN, Separate Disk Array or on SSDs. This is done usually for performance reason and manageability perspective. Now the challenges comes up when database which was installed at not preferred default location and needs to move to a different location. Here is the quick tutorial how you can do it.
UNION ALL and ORDER BY – How to Order Table Separately While Using UNION ALL
If your requirement is such that you want your top and bottom query of the UNION resultset independently sorted but in the same result set you can add an additional static column and order by that column. Let us re-create the same scenario.
Copy Data from One Table to Another Table – SQL in Sixty Seconds #031 – Video
http://www.youtube.com/watch?v=FVWIA-ACMNo
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
