SQL SERVER – Weekly Series – Memory Lane – #037
- 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
Convert Text to Numbers (Integer) – CAST and CONVERT
If table column is VARCHAR and has all the numeric values in it, it can be retrieved as Integer using CAST or CONVERT function.
List All Stored Procedure Modified in Last N Days
If SQL Server suddenly start behaving in un-expectable behavior and if stored procedure were changed recently, following script can be used to check recently modified stored procedure. If a stored procedure was created but never modified afterwards modified date and create a date for that stored procedure are same.
Count Duplicate Records – Rows
Validate Field For DATE datatype using function ISDATE()
We always checked DATETIME field for incorrect data type. One of the user input date as 30/2/2007. The date was sucessfully inserted in the temp table but while inserting from temp table to final table it crashed with error. We had now task to validate incorrect date value before we insert in final table. Jr. Developer asked me how can he do that? We check for incorrect data type (varchar, int, NULL) but this is incorrect date value. Regular expression works fine with them because of mm/dd/yyyy format.
2008
Find Space Used For Any Particular Table
It is very simple to find out the space used by any table in the database.
Two Convenient Features Inline Assignment – Inline Operations
Here is the script which does both – Inline Assignment and Inline Operation
DECLARE @idx INT = 0
SET @idx+=1
SELECT @idx
Introduction to SPARSE Columns
SPARSE column are better at managing NULL and ZERO values in SQL Server. It does not take any space in database at all. If column is created with SPARSE clause with it and it contains ZERO or NULL it will be take lesser space then regular column (without SPARSE clause).
SP_CONFIGURE – Displays or Changes Global Configuration Settings
If advanced settings are not enabled at configuration level SQL Server will not let user change the advanced features on server. Authorized user can turn on or turn off advance settings.
2009
Standby Servers and Types of Standby Servers
Standby Server is a type of server that can be brought online in a situation when Primary Server goes offline and application needs continuous (high) availability of the server. There is always a need to set up a mechanism where data and objects from primary server are moved to secondary (standby) server.
BLOB – Pointer to Image, Image in Database, FILESTREAM Storage
When it comes to storing images in database there are two common methods. I had previously blogged about the same subject on my visit to Toronto. With SQL Server 2008, we have a new method of FILESTREAM storage. However, the answer on when to use FILESTREAM and when to use other methods is still vague in community.
2010
Upper Case Shortcut SQL Server Management Studio
I select the word and hit CTRL+SHIFT+U and it SSMS immediately changes the case of the selected word. Similar way if one want to convert cases to lower case, another short cut CTRL+SHIFT+L is also available.
The Self Join – Inner Join and Outer Join
Self Join has always been a noteworthy case. It is interesting to ask questions about self join in a room full of developers. I often ask – if there are three kinds of joins, i.e.- Inner Join, Outer Join and Cross Join; what type of join is Self Join? The usual answer is that it is an Inner Join. However, the reality is very different.
Parallelism – Row per Processor – Row per Thread – Thread 0
If you look carefully in the Properties window or XML Plan, there is “Thread 0?. What does this “Thread 0” indicate? Well find out from the blog post.
How do I Learn and How do I Teach
The blog post has raised three very interesting questions. How do you learn? How do you teach? What are you learning or teaching? Let me try to answer the same.
2011
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 7 of 31
What are Different Types of Locks?
What are Pessimistic Lock and Optimistic Lock?
When is the use of UPDATE_STATISTICS command?
What is the Difference between a HAVING clause and a WHERE clause?
What is Connection Pooling and why it is Used?
What are the Properties and Different Types of Sub-Queries?
What are the Authentication Modes in SQL Server? How can it be Changed?
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 8 of 31
Which Command using Query Analyzer will give you the Version of SQL Server and Operating System?
What is an SQL Server Agent?
Can a Stored Procedure call itself or a Recursive Stored Procedure? How many levels of SP nesting is possible?
What is Log Shipping?
Name 3 ways to get an Accurate Count of the Number of Records in a Table?
What does it mean to have QUOTED_IDENTIFIER ON? What are the Implications of having it OFF?
What is the Difference between a Local and a Global Temporary Table?
What is the STUFF Function and How Does it Differ from the REPLACE Function?
What is PRIMARY KEY?
What is UNIQUE KEY Constraint?
What is FOREIGN KEY?
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 9 of 31
What is CHECK Constraint?
What is NOT NULL Constraint?
What is the difference between UNION and UNION ALL?
What is B-Tree?
How to get @@ERROR and @@ROWCOUNT at the Same Time?
What is a Scheduled Job or What is a Scheduled Task?
What are the Advantages of Using Stored Procedures?
What is a Table Called, if it has neither Cluster nor Non-cluster Index? What is it Used for?
Can SQL Servers Linked to other Servers like Oracle?
What is BCP? When is it Used?
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 10 of 31
What Command do we Use to Rename a db, a Table and a Column?
What are sp_configure Commands and SET Commands?
How to Implement One-to-One, One-to-Many and Many-to-Many Relationships while Designing Tables?
What is Difference between Commit and Rollback when Used in Transactions?
What is an Execution Plan? When would you Use it? How would you View the Execution Plan?
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 11 of 31
What is Difference between Table Aliases and Column Aliases? Do they Affect Performance?
What is the difference between CHAR and VARCHAR Datatypes?
What is the Difference between VARCHAR and VARCHAR(MAX) Datatypes?
What is the Difference between VARCHAR and NVARCHAR datatypes?
Which are the Important Points to Note when Multilanguage Data is Stored in a Table?
How to Optimize Stored Procedure Optimization?
What is SQL Injection? How to Protect Against SQL Injection Attack?
How to Find Out the List Schema Name and Table Name for the Database?
What is CHECKPOINT Process in the SQL Server?
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 12 of 31
How does Using a Separate Hard Drive for Several Database Objects Improves Performance Right Away?
How to Find the List of Fixed Hard Drive and Free Space on Server?
Why can there be only one Clustered Index and not more than one?
What is Difference between Line Feed (\n) and Carriage Return (\r)?
Is It Possible to have Clustered Index on Separate Drive From Original Table Location?
What is a Hint?
How to Delete Duplicate Rows?
Why the Trigger Fires Multiple Times in Single Login?
2012
CTRL+SHIFT+] Shortcut to Select Code Between Two Parenthesis
Shortcut key is CTRL+SHIFT+]. This key can be very useful when dealing with multiple subqueries, CTE or query with multiple parentheses. When exercised this shortcut key it selects T-SQL code between two parentheses.
Monday Morning Puzzle – Query Returns Results Sometimes but Not Always
I am beginner with SQL Server. I have one query, it sometime returns a result and sometime it does not return me the result. Where should I start looking for a solution and what kind of information I should send to you so you can help me with solving. I have no clue, please guide me.
Remove Debug Button in SSMS – SQL in Sixty Seconds #020 – Video
Effect of Case Sensitive Collation on Resultset
Collation is a very interesting concept but I quite often see it is heavily neglected. I have seen developer and DBA looking for a workaround to fix collation error rather than understanding if the side effect of the workaround.
Switch Between Two Parenthesis using Shortcut CTRL+]
Earlier this week I wrote a blog post about CTRL+SHIFT+] Shortcut to Select Code Between Two Parenthesis, I received quite a lot of positive feedback from readers. If you are a regular reader of the blog post, you must be aware that I appreciate the learning shared by readers.
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
