SQL SERVER – Weekly Series – Memory Lane – #046

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

User Defined Function – Get Number of Days in Month
A straight to script blog post where I find the number of days in month.

Start Stop Restart SQL Server From Command Prompt
Very frequently I use following command prompt script to start and stop a default instance of SQL Server. Our network admin loves these commands as this is very easy.

Frequency of SQL Server Reboot and Restart
This is a very interesting question. I will keep the answer of this question very simple. First of all there is no scientific research or white paper I can backup my results with. The answer contains part simple observation and part experience. There is no need to reboot SQL Server. Once it is on it is ON!

Scrum: Agile Software Development for Project Management
Scrum is organized around the following roles:

  • Product Owner – Determines what functionality is needed
  • ScrumMaster – Leads the Scrum and is primarily responsible for making sure the Scrum process is followed and removing impediments that keep the Team from working
  • The Team – Those who do the actual work that translates what the Product Owner has requested into usable functionality

Difference Between EXEC and EXECUTE vs EXEC() – Use EXEC/EXECUTE for SP always
Using EXEC or EXECUTE is good practice as it always executes the stored procedure, when not using EXEC can confuse SQL SERVER to misinterpret commands and may create errors.

2008

Creating Primary Key, Foreign Key and Default Constraint
Primary key, Foreign Key and Default constraint are the 3 main constraints that need to be considered while creating tables or even after that. It seems very easy to apply these constraints but still we have some confusions and problems while implementing it. So I tried to write about these constraints that can be created or added at different levels and in different ways or methods.

SharePoint Stop Working After Changing Server (Computer) Name
When you change the physical server name the SharePoint is already connected to the SQL instance of the old computer name (OldServerName/SQLInstance) and on changing the name the SharePoint will not able to connect the SQL Server  as now the SQL Server instance will run on new computer name (NewServerName/SQLInstance).

Steps To Create A Custom WebPart – Deploy It SharePoint Site
SharePoint does not allow custom coding for any of the webpart. It is possible to create a webpart in Visual Studio and integrate it with SharePoint. The process to create a webpart in .NET framework and make it work in SharePoint often fails due to lack of guidance on this subject on the internet.

2009

Difference between SQL Server Express and MySQL
Both SQL Server express and MySQL are two of the Relational Database Systems (RDBMS) available today. Both are freely available and meant for running smaller or embedded databases, yet there are also significant differences between them.

Plan Caching and Schema Change – An Interesting Observation
As per the white paper, “Schema change” is defined as follows:

  • Adding or dropping columns to a table or view.
  • Adding or dropping constraints, defaults, or rules to/from a table.
  • Adding an index to a table or an indexed view.
  • Dropping an index defined on a table or an indexed view (only if the index is used by the query plan in question).
  • Dropping a statistic (not creating or updating!) defined on a table will cause a correctness-related recompilation of any query plans that use that table. Such recompilations occur at that instant when the query plan in question begins execution. Updating a statistic (both manual and auto-update) will cause an optimality-related (data related) recompilation of any query plans that uses this statistic.

2010

Find Row Count in Table – Find Largest Table in Database – Part 2
Last Year I wrote articles on the subject SQL SERVER – Find Row Count in Table – Find Largest Table in Database – T-SQL. It is very good to see excellent participation there. In my script I had not taken care of table schema. SQL Server Expert Ameena has modified the same script to include the schema. Here is the new modified script.

Find Automatically Created Statistics – T-SQL
Earlier, I wrote about my experience at an organization here: SQL SERVER – Plan Cache – Retrieve and Remove – A Simple Script. This blog post briefly narrates another experience I had at the same organization.

What are Wait Types, Wait Stats and its Importance
 As per BOL, there are three types of wait types, namely:

  • Resource Waits. Resource waits occur when a worker requests access to a resource that is not available because that resource is either currently used by another worker, or it’s not yet available.
  • Queue Waits. Queue waits occur when a worker is idle, waiting for work to be assigned.
  • External Waits. External waits occur when an SQL Server worker is waiting for an external event.

To check the wait types for any server, just run the following simple statistics:

SELECT *
FROM sys.dm_os_wait_stats

You can get the Wait Stats and identify which of the Wait Stats is causing the issue that troubles you.

Disabled Index and Index Levels and B-Tree
This blog post tries to answer a very important question – What will be the status of the B-Tree structure when the index is disabled?

In the following script, the following operations are to be done:

  • Create Table
  • Create Clustered Index
  • Check the Index Levels
  • Disable Index
  • Check the Index Levels

2011

I have put up a quick guide here where I am writing all the 14 new functions linking them to my blog post as well Book On-Line for a quick reference.

SQLAuthority.com Book On-Line
Conversion functions
PARSE PARSE
TRY_CONVERT TRY_CONVERT
TRY_PARSE TRY_PARSE
Date and time functions
DATEFROMPARTS DATEFROMPARTS
DATETIME2FROMPARTS DATETIME2FROMPARTS
DATETIMEFROMPARTS DATETIMEFROMPARTS
DATETIMEOFFSETFROMPARTS DATETIMEOFFSETFROMPARTS
EOMONTH EOMONTH
SMALLDATETIMEFROMPARTS SMALLDATETIMEFROMPARTS
TIMEFROMPARTS TIMEFROMPARTS
Logical functions
CHOOSE CHOOSE
IIF IIF
String functions
CONCAT CONCAT
FORMAT FORMAT

I have personally bookmarked this post for my future reference.

2012

Unable to DELETE Project in Data Quality Projects (DQS)
Here is the problem. I am not able to delete the project which I have created earlier. I am able to open it and play with it but the delete option is disabled and grayed out (see attached image). Now I believe there is nothing wrong with this project as it was just a test project. Would you please write to my manager that it is not harmful to leave that project there as it is? It is also not using any resources. I think he will believe you.”

Configuring Interactive Cleansing Suggestion Min Score for Suggestions in Data Quality Services (DQS) – Sensitivity of Suggestion
Would you please tell me how to increase the numbers of suggestion? I do understand this may not be preferable solution in many case but all the business cases go on it depends. There are cases when the high sensitivity required and there are cases when higher sensitivities are not required. I would like to seek your help here.

Why Do We Need Data Quality Services – Importance and Significance of Data Quality Services (DQS)
In a database, these sorts of anomalies are incredibly important.  Databases are often used by multiple people who rely on this data to be true and accurate, so data quality is key.  That is why the improved SQL Server features Master Data Management talks about Data Quality Services.  This service has the ability to recognize and flag anomalies like out of range numbers and similarities between data.  This allows a human brain with its pattern recognition abilities to double-check and ensure that P. Dave is the same as Pinal Dave.

Why Do We Need Master Data Management – Importance and Significance of Master Data Management (MDM)
Let me paint a picture of everyday life for you.  Let’s say you and your wife both have address books for your groups of friends.  There is definitely overlap between them, so that you both have the addresses for your mutual friends, and there are addresses that only you know, and some only she knows.  They also might be organized differently.  You might list your friend under “J” for “Joe” or even under “W” for “Work,” while she might list him under “S” for “Joe Smith” or under your name because he is your friend.  If you happened to trade, neither of you would be able to find anything!

Get Date and Time From Current DateTime – SQL in Sixty Seconds #025 – Video

http://www.youtube.com/watch?v=BL5GO-jH3HA

Core Concepts – Elasticity, Scalability and ACID Properties – Exploring NuoDB an Elastically Scalable Database System
The goal of this article is to answer following simple questions

  1. What is Elasticity?
  2. What is Scalability?
  3. How ACID properties vary from NOSQL Concepts?
  4. What are the prevailing problems in the current database system architectures?

Grouping by Multiple Columns to Single Column as A String
One of the most common questions I receive in email is how to group multiple column data in comma separate values in a single row grouping by another column.

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

About these ads

SQL SERVER – Weekly Series – Memory Lane – #045

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

Here are three blog posts where I have written scripts to do various helpful task developers have to frequently.

Introduction and Explanation to sqlcmd
SQL Server 2005 has introduced new utility sqlcmd to run an ad hoc Transact-SQL statements and scripts from the command prompt. T-SQL commands are entered in command prompt window and result is displayed in the same window, unless result set are sent to output files. sqlcmd can execute single T-SQL statement as well as the batch file. sqlcmd utility can connect to earlier versions of SQL Server as well.

Correlated and Noncorrelated – SubQuery Introduction, Explanation and Example
A correlated subquery is an inner subquery which is referenced by the main outer query such that the inner query is considered as being executed repeatedly. A noncorrelated subquery is subquery that is independent of the outer query and it can execute on its own without relying on the main outer query.

2008

Introduction to Filtered Index – Improve performance with Filtered Index
Filtered Index is a new feature in SQL SERVER 2008. Filtered Index is used to index a portion of rows in a table, that means it applies a filter on INDEX which improves query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

Creating Full Text Catalog and Full Text Search
Full Text Index helps to perform complex queries against character data.  These queries can include words or phrase searching. We can create a full-text index on a table or indexed view in a database. Only one full-text index is allowed per table or indexed view. The index can contain up to 1024 columns.

Explanation about Usage of Unique Index and Unique Constraint
I enjoy reading questions from blog readers and answering them. One of the other SQL enthusiastic is Imran who also regularly answer questions of users on this community blog. Recently he answered in detail about when to use Unique Index and when to use Unique Constraint.

2009

September 1st 2009 was a fantastic day. Checkout Why?

Mirrored Backup and Restore and Split File Backup
This article is based on a real life experience of the author while working with database backup and restore during his consultancy work for various organizations. We will go over the following important concepts of database backup and restore.

  1. Conventional Backup and Restore
  2. Spilt File Backup and Restore
  3. Mirror File Backup
  4. Understanding FORMAT Clause
  5. Miscellaneous details about Backup and Restore

What is Data Mining – A Simple Introductory Note
Data mining is defined as “the process of analyzing data to find hidden patterns using automatic methodologies.” Consider the following simple example that explains this concept. By analyzing the data on the items purchased from a supermarket or a chain of such stores, information on the products that are sold most can be obtained and accordingly supply of that particular product are increased and vice versa. Data mining, in short, is an analytical activity that studies the hidden patterns in a huge pile of data after appropriately classifying and sorting it.

Find Gaps in The Sequence
This is straight to script blog post where I explain how to find gaps in the sequence generated manually.

Importance of Database Schemas in SQL Server
The default schema for a user can be defined by using the DEFAULT_SCHEMA option of the CREATE USER or ALTER USER commands. If no default schema is defined for a user account, SQL Server will assume dbo is the default schema. It is important note that if the user is authenticated by SQL Server via the Windows operating system, no default schema will be associated with the user. Therefore if the user creates an object, a new schema will be created and named the same as the user, and the object will be associated with that user schema, though not directly with the user.

2010

Soft Delete – IsDelete Column – Your Opinion
I had gone for performance tuning consultation and I was reviewing a large table. I spotted one table called Orders. Naturally, the size of the table was in millions of the rows. I thought – it is fine, a table can have that many rows. Then I checked another table called customers and it had under thousand records. The question which came to my mind was how come thousand customers ordered millions of items. I asked the local DBA coordinator the same question. He said oh, we just cleaned the customer table but the orders table is yet to clean – consider it as a small table.

Index Created on View not Used Often – Limitation of the View 3
I have heard many people saying that if they create a view and index on it, this will reduce the load on the original table as all the subsequent queries on view will not access the basic table. This is not true always and the view may not give you the performance optimizations which you are looking for.

Index Levels and Delete Operations – Page Level Observation
When data are deleted from any table, the SQL Server does not reduce the size of the table right away, but marks those pages as free pages, showing that they belong to the table. When new data are inserted, they are put into those pages first. Once those pages are filled up, SQL Server will allocate new pages. If you wait for some time background process de-allocates the pages and finally reducing the page size.  Follow the example below.

2011

Using Root With Auto XML Mode – Day 32 of 35

Q) You need to generate the following XML document from your CurrentProducts table:

<ProductExport>
<Product Price=”99″>Product1</Product>
<Product Price=”199″>Product2</Product>
<Product Price=”299″>Product3</Product>
<Product Price=”399″>Product4</Product>
</ProductExport>

Which query should you use?

  1. SELECT Price, ProductName
    FROM CurrentProducts AS ProductExport
    FOR XML PATH(‘Product’)
  2. SELECT Price, ProductName
    FROM CurrentProducts
    FOR XML AUTO, ROOT(ProductExport’)
  3. SELECT Price [@Price], ProductName AS [*]
    FROM CurrentProducts AS ProductExport
    FOR XML AUTO, ELEMENTS
  4. SELECT Price [@Price], ProductName AS [*]
    FROM CurrentProducts
    FOR XML PATH(‘Product’), ROOT(ProductExport’)

Shredding XML – Day 33 of 35

Q) What process will transform XML data to a rowset?

  1. Shredding
  2. Retrieving

Preparing XML in Memory – Day 34 of 35

Q) The sp_XML_PrepareDocument stored procedure requires a parameter that is an XML data type. What is the output parameter for?

  1. The handle as an INT
  2. The handle as an XML
  3. The handle as a Varchar

OpenXML Options – Day 35 of 35

Q) You have a table named Buildings that has an XML column named StoreHours. This column contains the opening and closing times:

<hours dayofWeek=“Monday” open=“8:00” closed= “18:00”/>
<hours dayofWeek=“Tuesday” open=“8:00” closed= “18:00”/>
<hours dayofWeek=“Wednesday” open=“8:00” closed= “18:00”/>

<hours dayofWeek=“Saturday” open=“9:00” closed= “17:00”/>

You need to write a query that returns a list of Buildings and their opening time for Wednesday.  Which code segment should you use?

  1. SELECT StoreName, StoreHours. value(‘/hours[1]/@open’,’time’)
    FROM Buildings
    WHERE StoreHours.value(‘/hours[1]/@dayofWeek’,’varchar(20)’) = @Day
  2. SELECT StoreName, StoreHours. value(‘/hours[1]/@open’,’time’)
    FROM Buildings
    WHERE StoreHours.exist(‘/hours[@dayofWeek=”Wednesday”]‘) = 1
  3. SELECT Storename, StoreHours.query(‘/hours[@dayofWeek=”Wednesday”]/@open’)
    FROM Buildings

Conversion Function – PARSE() – A Quick Introduction
PARSE() function can convert any string value to Numeric or Date/Time format. If the passed string value cannot be converted to Numeric or Date/Time format, it will result in an error. PARSE () function relies on the Common Language Runtime (CLR) to convert the string value.

2012

Step by Step Guide to Beginning Data Quality Services in SQL Server 2012 – Introduction to DQS
Data Quality Services is a very important concept of SQL Server. I have recently started to explore the same and I am really learning some good concepts. Here are two very important blog posts which one should go over before continuing this blog post. This article is introduction to Data Quality Services for beginners.

Fun Post – Connecting Same SQL Server using Different Methods
I created a list of 5 different way but I am sure there are many more ways and I would like to document there here. Here is my setup. I am attempting to connect to the default instance of SQL Server from the same system where it is installed.

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

SQL SERVER – Weekly Series – Memory Lane – #044

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

Use Always Outer Join Clause instead of (*= and =*)
Instead of using LEFT OUTER JOIN clause he was using *= and similarly instead of using RIGHT OUTER JOIN clause he was using =*. Once I replaced did necessary modification, queries run just fine.

Actual Execution Plan vs. Estimated Execution Plan
I always use the Actual Execution Plan as it is accurate. Why not Estimated Execution Plan? It is not accurate. Sometime it is easier or useful to to know the plan without running the query. I just run a query and have correct and accurate Execution Plan.

Shortcut for Display Estimated Execution Plan : CTRL + L
Shortcut for Include Actual Execution Plan : CTRL + M

Difference and Explanation among DECIMAL, FLOAT and NUMERIC
Converting from Decimal or Numeric to float can cause some loss of precision. For the Decimal or Numeric data types, SQL Server considers each specific combination of precision and scale as a different data type. DECIMAL(2,2) and DECIMAL(2,4) are different data types. This means that 11.22 and 11.2222 are different types though this is not the case for float. For FLOAT(6) 11.22 and 11.2222 are same data types.

Find Database Collation Using T-SQL and SSMS
This article is written based on feedback I have received on SQL SERVER – Cannot resolve collation conflict for equal to operation. Many readers asked me how to find the collation of the current database. There are two different ways to find out SQL Server database collation.

Find Database Status Using sys.databases or DATABASEPROPERTYEX
While writing article about database collation, I came across sys.databases and DATABASEPROPERTYEX. It was very interesting to me that this two can tell user so much about database properties.

2008

How to Rename a Column Name or Table Name

Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE
One of the most important advantage of MERGE statement is all the data is read and processed only once. In previous versions three different statement has to be written to process three different activity (INSERT, UPDATE or DELETE), however using MERGE statement all update activity can be done in one pass of database table. This is quite an improvement in performance of database query.

Introduction to Table-Valued Parameters with Example
Table-valued parameters are declared using user-defined table types. To use a Table Valued Parameters we need follow the steps shown below:

  1. Create a table type and define the table structure
  2. Declare a stored procedure that has a parameter of the table type.
  3. Declare a table type variable and reference the table type.
  4. Using the INSERT statement and occupy the variable.
  5. We can now pass the variable to the procedure.

2009

SQL Server Express – A Complete Reference Guide
SQL Server Express is one of the most valuable products of Microsoft. Very often, I face many questions with regard to SQL Server Express. Today, we will be covering some of the most commonly asked questions.

2010

Does Order of Column in WHERE clause Matter?
Quick puzzle time - Does the order column used in WHERE clause matter for performance? Here are the rules for you -

  • You can use any numbers of the tables in your query
  • You can only change the order of columns in WHERE clause
  • You need to use either AND or OR clause between conditions of the WHERE clause
  • Performance will be measured using Actual Execution Plan and SET IO Statistics ON
  • The resultset returned from the query should be the same before changing order of columns in WHERE condition and after changing order of columns in WHERE condition.

Adding Column is Expensive by Joining Table Outside View – Limitation of the Views Part 2
In this episode of limitation of the View, we will see how adding an additional column outside the view can be very expensive, whereas the same situation does not happen with regular T-SQL query.

Plan Cache – Retrieve and Remove – A Simple Script
In this blog post we discuss about query that demonstrates cache plans which are ‘ad hoc’ or called only once in a life time. You can see how much memory is already bloated by not-so-useful queries. If you want to remove any large plan cache which you do not think is useful to you, you can run the another command to remove it.

2011

Table-Valued Store Procedure Parameters – Day 25 of 35

Q) You need to create a stored procedure which accepts a table-valued parameter named @Suppliers. What code will achieve this result?

  1. CREATE PROCEDURE AddSuppliers
    @Suppliers Float READONLY
  2. CREATE PROCEDURE AddSuppliers
    @Suppliers Int READONLY
  3. CREATE PROCEDURE AddSuppliers
    @Suppliers Money READONLY
  4. CREATE PROCEDURE AddSuppliers
    @Suppliers SupplierType READONLY
  5. CREATE PROCEDURE AddSuppliers
    @Suppliers GeographyType READONLY

Table-Valued Functions – Day 26 of 35

Q) You need to create two functions that will each return a scalar result of the number of hours each user has logged in: 1) the current day, and 2) month to date.  You will pass in the user ID as a parameter value. What two things must you do?

  1. Create a function that returns a list of values representing the login times for a given user.
  2. Create a function that returns a list of values representing the people who have logged more hours than the current user has logged.
  3. Create a function that returns a numeric value representing the number of hours that a user has logged for the current day.
  4. Create a function that returns a number value representing the number of hours that a user has logged for the current month.

SQL Server Error Messages – Day 27 of 35

Q) When does SQL Server always raise an error message? (Choose two)

  1. When a statement in SQL Server cannot run
  2. When multiple records are updated in one table
  3. When you issue a RAISERROR message

Structured Error Handling – Day 28 of 35

Q) You have tables named CurrentProducts and SalesInvoiceHeader. The CurrentProducts table has a foreign key relationship with the SalesInvoiceHeader table on the ProductID column. You are deleting ProductID 77 from the Product table and then trying to insert a sale for Product77 into the SalesInvoiceHeader table.

BEGIN TRY
BEGIN TRANSACTION
DELETE FROM CurrentProducts  WHERE ProductID = 77
BEGIN TRANSACTION
INSERT INTO SalesInvoiceHeader VALUES ( 95894, 77, 2 )
COMMIT TRANSACTION
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT ERROR_MESSAGE()
END CATCH

What will be the outcome when you run this query?

  1. 1 The product will be deleted from the CurrentProducts table.
    2) The order details will be inserted into the SalesInvoiceHeader table.
  2. 1) The product will be deleted from the CurrentProducts table.
    2) The order details will not be inserted into the SalesInvoiceHeader table.
  3. 1) The product will not be deleted from the CurrentProducts table.
    2) The order details will be inserted into the SalesInvoiceHeader table.
  4. 1) The product will not be deleted from the CurrentProducts table.
    2) The order details will not be inserted into the SalesInvoiceHeader table.

What is XML? – Day 29 of 35

Q) If you don’t specify any option, then XML RAW will have your data streamed in…

  1. Element text.
  2. Attributes.

What is XML? – Day 30 of 35

Q) Without XSINIL, what happens to null values from your result set?

  1. They error out since XSINIL does not allow nulls.
  2. They appear as empty tags.
  3. No tags are present for null values.

Using Root With Auto XML Mode – Day 31 of 35

Q) You have a query which joins tables. You want to create a well-formed XML stream, which is attribute-based and nests the results in the table from the first field of the select list. Which code do you append to the SQL statement?

  1. FOR XML AUTO
  2. FOR XML RAW
  3. FOR XML AUTO, ROOT
  4. FOR XML RAW, ROOT

2012

Answer – Value of Identity Column after TRUNCATE command
Earlier I had one conversation with reader where I almost got a headache. I suggest all of you to read it before continuing this blog post SQL SERVER – Reseting Identity Values for All Tables. I believed that he faced this situation because he did not understand the difference between SQL SERVER – DELETE, TRUNCATE and RESEED Identity. I wrote a follow up blog post explaining the difference between them. I asked a small question in the second blog post and I received many interesting comments. Let us go over the question and its answer here one more time.

A Brief Note on SET TEXTSIZE
I do not think they can be directly comparable even though both of them give the exact same result while using SSMS. LEFT is applicable only on the column of a single SELECT statement. Where it is used but it SET TEXTSIZE applies to all the columns in the SELECT and follow up SELECT statements till the SET TEXTSIZE is not modified again in the session. Incomparable!

Three Methods to Insert Multiple Rows into Single Table – SQL in Sixty Seconds #024 – Video
One of the biggest ask I have always received from developers is that if there is any way to insert multiple rows into a single table in a single statement. Currently when developers have to insert any value into the table they have to write multiple insert statements. First of all this is not only boring it is also very much time consuming as well. Additionally, one has to repeat the same syntax so many times that the word boring becomes an understatement.

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

SQL SERVER – Weekly Series – Memory Lane – #043

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

Find Last Day of Any Month – Current Previous Next
Few questions are always popular. They keep on coming up through email, comments or from co-workers. Finding Last Day of Any Month is similar question. I have received it many times and I enjoy answering it as well.

T-SQL Script to Insert Carriage Return and New Line Feed in Code
Very simple and very effective. We use all the time for many reasons – formatting, while creating dynamically generated SQL to separate GO command from other T-SQL, saving some user input text to database etc.

Use of Non-deterministic Function in UDF – Find Day Difference Between Any Date and Today
I always thought that use of Non-Deterministic function is prohibited in UDF. I even wrote about it earlier SQL SERVER – User Defined Functions (UDF) Limitations. It seems like SQL Server 2005 either have removed this restriction or it is a bug. I think I will not say this is bug but I will categorize it as a feature.

T-SQL Script to Attach and Detach Database
Following script can be used to detach or attach the database. If the database is to be from one database to another database following script can be used to detach from old server and attach to a new server.

2008

Detailed Explanation of Transaction Lock, Lock Type, Avoid Locks
This blog explains in detail the details about Transaction Lock, Lock Type and how to avoid the same.

Disable All the Trigger of Current Database
I have previously written article about SQL SERVER – Disable All Triggers on a Database – Disable All Triggers on All Servers. This is an alternate method to achieve the same task.

Behind the Scene of SQL Server Activity of – Transaction Log – Shrinking Log
When a transaction comes to SQL Server, it first comes to transaction log buffer, and then it is hardened to disk ( log file, .Def ) and then it is written to the data file ( .mdf). Then we say the transaction is committed or it is inactive, because the transaction performed all the actions that it should.

UDF – Function to Convert Text String to Title Case – Proper Case – Part 2
I had previously written SQL SERVER – UDF – Function to Convert Text String to Title Case – Proper Case and I had really enjoyed writing it. Above script converts the first letter of each word from sentence to upper case.

Configure Database Mail – Send Email From SQL Database

2009

Get Query Plan Along with Query Text and Execution Count
I use the following handy script, which I use when I need to know the details regarding how many times any query has ran on my server along with its execution plan. You can add an additional WHERE condition if you want to learn about any specific object.

Index Seek vs. Index Scan – Diffefence and Usage – A Simple Note
An index scan means that SQL Server reads all rows in a table, and then returns only those rows that satisfy the search criteria. When an index scan is performed, all the rows in the leaf level of the index are scanned. This essentially means that all of the rows of the index are examined instead of the table directly. This is sometimes contrasted to a table scan, in which all the table data is read directly. However, there is usually little difference between an index scan and a table scan.

2010

Negative Identity Seed Value and Negative Increment Interval
A very lesser known fact and even a lesser implemented feature of negative seed value and negative increment interval is described in this blog.

Function to Retrieve First Word of Sentence – String Operation
This is straight to the script kind of blog where I have described how to retrieve a first word of the sentence.

ORDER BY Does Not Work – Limitation of the Views Part 1
Order By clause does not work in View. I agree with all of you  who say that there is no need of using ORDER BY in the View. ORDER BY should be used outside the View and not in the View. This example is another reason why one should not use ORDER BY in Views.

SQL SERVER – Computed Column – PERSISTED and Storage
This article talks about how computed columns are created and why they take more storage space than before.

SQL SERVER – Computed Column – PERSISTED and Performance
This article talks about how PERSISTED columns give better performance than non-persisted columns.

SQL SERVER – Computed Column – PERSISTED and Performance – Part 2
This article talks about how non-persisted columns give better performance than PERSISTED columns.

SQL SERVER – Computed Column and Performance – Part 3
This article talks about how Index improves the performance of Computed Columns.

SQL SERVER – Computed Column – PERSISTED and Storage – Part 2
This article talks about how creating index on computed column does not grow the row length of table.

SQL SERVER – Computed Columns – Index and Performance
This article summarized all the articles related to computed columns.

2011

Geography Data Type – Calculating Distance Between Two Points on the Earth – Day 18 of 35

Q) The STDistance function of the Geography Data type calculates the distance between two points in

  1. Feet
  2. Meters
  3. Kilometers
  4. Miles
  5. Units

The Clustered Index – Simple Understanding – Day 19 of 35

Q) A heap has:

  1. No clustered indexes.
  2. One clustered index.
  3. Many clustered indexes.

Introduction to Page Split – Day 20 of 35

Q) When do page splits happen?

  1. When records from one memory page are moved to another page during changes to your table.
  2. When records from one memory page are collapsed into fewer pages from excessive deletes.
  3. When you insert records in order by the clustered index and you table needs to claim more memory space.

All about SQL Statistics – Day 21 of 35

Q) What advantage do SQL statistics offer the query optimizer?

  1. They allow the table to save space.
  2. The query optimizer knows the selectivity level of values before a query is run.
  3. They show the performance statistics history since the last SQL restart.
  4. They prevent page splits by buffer data page memory for later.

All about SQL Constraints – Day 22 of 35

Q) You have a table named Feedback that contains every record of how a customer felt about their purchase. One field is called Complaint, where 0 is no complaint and 1 is a complaint. You also have a field called Rating that ranges from 0 to 100. If a customer complains they should not be giving a perfect rating of 100. If they complain then they can enter a score between 0 and 90. If they don’t then it can be between 1 and 100. Which check constraint would you use?

  1. CHECK (Rating BETWEEN 1 and 100)
  2. CHECK (Rating <=90 AND Complaint = 1)
  3. CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
    OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )
  4. CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1)
    AND ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

Introduction to Views – Day 23 of 35

Q) What are the two ways to see the code that created a view? (Choose Two)

  1. WITH SCHEMABINDING
  2. WITH ENCRYPTION
  3. sp_helptext
  4. sp_depends
  5. sys.syscomments

Easy Introduction to CHECK Options – Day 24 of 35

Q) You have a table named dbo.Sales. You need to create three views from the sales table.

vSalesSeattle

vSalesBoston

vSalesSpokane

Each view will be used by each region to make changes to their rows.  One day a Seattle sales manager updated his sales data to have a new LocationID and the record showed up on the vSalesBoston view. Changes made to the vSalesSeattle view must not be made in a way that the record falls outside of the scope of the view.  Which view should you create for Region1?

  1. CREATE VIEW dbo.vSalesSeattle
    AS
    SELECT SalesID, OrderQty, SalespersonID, RegionID
    FROM dbo.Sales
    WHERE RegionID = 1
    WITH DIFFERENTIAL
  2. CREATE VIEW dbo.vSalesSeattle
    AS
    SELECT SalesID, OrderQty, SalespersonID, RegionID
    FROM dbo.Sales
    WHERE RegionID = 1
    WITH CHECK OPTION
  3. CREATE VIEW dbo.vSalesSeattle
    WITH SCHEMABINDING
    AS
    SELECT SalesID,OrderQty,SalespersonID, RegionID
    FROM dbo.Sales
    WHERE RegionID = 1
  4. CREATE VIEW dbo.vSalesSeattle
    WITH NOCHECK
    AS
    SELECT SalesID, OrderQty, SalespersonID, RegionID
    FROM dbo.Sales
    WHERE RegionID = 1

2012

Installing Data Quality Services (DQS) on SQL Server 2012
In this blog post, I explain in the simple words how to install Data Quality Services.

Color Coding SQL Server Management Studio Status Bar – SQL in Sixty Seconds #023 – Video

Reseting Identity Values for All Tables
Very interesting conversation - DBA: “I deleted all of the data from my database and now it contains a table structure only. However, when I tried to insert new data in my tables I noticed that my identity values starts from the same number where they actually were before I deleted the data.”

DELETE, TRUNCATE and RESEED Identity
Question for you: If I reseed value with some random number followed by the truncate command on the table what will be the seed value of the table. (Example, if original seed value is 11 and I reseed the value to 1. If I follow up with truncate table what will be the seed value now?

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

SQL SERVER – Weekly Series – Memory Lane – #042

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

Validate Integer Function
ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0. ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($). Now this is an issue with ISNUMERIC () function. It even suggest variable as numeric when it is not. I use following function to validate instead of ISNUMERIC () to validate if the number is numeric or not.

Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE

Difference and Similarity Between NEWSEQUENTIALID() and NEWID()
NEWSEQUENTIALID() and NEWID() both generates the GUID of datatype of uniqueidentifier. NEWID() generates the GUID in random order whereas NEWSEQUENTIALID() generates the GUID in sequential order.

Explanation and Script for Online Index Operations – Create, Rebuild, Drop
Online operation means when online operations are happening in the database are in normal operational condition, the processes which are participating in online operations does not require exclusive access to the database. In case of Online Indexing Operations, when Index operations (create, rebuild, dropping) are occuring they do not require exclusive access to database, they do not lock any database tables. This is a major important upgrade in SQL Server from previous versions.

2008

Get Date Time in Any Format – UDF – User Defined Functions
A very comprehensive function which returns date time in any format. 

Introduction to Online Indexing Operation
SQL Server 2005 and later versions have provided feature called “Online Indexing”. Everytime index is updated it puts a lock on a table where index operations are happening. Depending on the situation SQL Server puts schema lock or shared lock while the index is modified. If a feature of “Online Indexing” is used SQL Server will behave normally when the Index is modified.

2009

Backup master Database Interval – master Database Best Practices

  • The master database is the most important database of all and the most recent version of the backup should be available in the case of disaster.
  • Backup of the master database should be made for:
    • Changing server-level configuration settings;
    • Changing database-level configuration settings; and
    • Changing any logon accounts details.

Measure CPU Pressure – CPU Business
Let us understand the simple meaning of CPU pressure. CPU pressure is a state wherein the CPU is fully occupied with currently assigned tasks and there are more tasks in the queue that have not yet started.

2010

SHRINKDATABASE For Every Database in the SQL Server
Shrinking the database is indeed bad and it reduces the performance of the server. In this blog post, I clearly demonstrate the real world scenario for the same.

Why SQL Server is better than any other RDBMS Applications?
Very interesting arguments on this subject in this blog. I really enjoyed and I am very sure everybody who loves SQL Server will enjoy this blog post.

Finding the Occurrence of Character in String
A straight to script blog where I explained how to find the occurrence of character in any string.

2011

Tips from the SQL Joes 2 Pros Development Series – Advanced Aggregates with the Over Clause – Day 11 of 35
Q) You have a table named CurrentProducts. The table contains a column named Category. You need to make a T-SQL statement that calculates the percentage (with decimals) of products in each product Category. Which query should you use?

  1. SELECT DISTINCT Category,
    COUNT(*) OVER (PARTITION BY Category)*100/ COUNT(*) OVER() as PctCategory
    FROM CurrentProducts
  2. SELECT DISTINCT Category,
    COUNT(*) OVER (PARTITION BY Category)*100.0/ COUNT(*) OVER() as PctCategory
    FROM CurrentProducts
  3. SELECT DISTINCT Category,
    COUNT(*) OVER ( )*100.0/ COUNT(*) OVER(PARTITION BY Category) as PctCategory
    FROM CurrentProducts

Tips from the SQL Joes 2 Pros Development Series – Ranking Functions – RANK( ), DENSE_RANK( ), and ROW_NUMBER( ) – Day 12 of 35
Q) The figure below shows the scores of 6 contest winners. Tom is the highest

and Eric made 6th place. There were 6 people but only 5 distinct scores.

5 Highest Scores        5 Highest Distinct Scores

9.9                               9.9

9.8                               9.8

9.7                               9.7

9.6                               9.6

9.6                               9.2

You are writing a query to list the 5 highest distinct scores. The Ranked field should be called ScoreRating. You have written the following code.

SELECT * FROM
(SELECT  *  More code here.
FROM [Contestants])  AS ContestantFinal
WHERE ScoreRating <= 5

What code will achieve this goal?

  1. SUM(*) OVER(ORDER BY Score DESC) as ScoreRating
  2. RANK( ) OVER(ORDER BY Score DESC) as ScoreRating
  3. COUNT(*) OVER(ORDER BY Score DESC) as ScoreRating
  4. DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating

Tips from the SQL Joes 2 Pros Development Series – Ranking Functions – Advanced NTILE in Detail – Day 13 of 35
Q) You want to find the top 2% of all students’ Grade Point Averages (GPA). Which NTILE would you use?

  1. NTILE(1) OVER(ORDER BY GPA DESC)
  2. NTILE(2) OVER(ORDER BY GPA DESC)
  3. NTILE(25) OVER(ORDER BY GPA DESC)
  4. NTILE(50) OVER(ORDER BY GPA DESC)

Tips from the SQL Joes 2 Pros Development Series – Output Clause in Simple Examples – Day 14 of 35
Q) You have an HourlyPay table and are giving all hourly employees a $1 raise. When you run the update statement you want to see the EmpID, OldPay, and NewPay. What code will achieve this result?

  1. UPDATE HourlyPay SET Hourly = Hourly + 1
    OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Updated.Hourly as NewPay
    WHERE Hourly IS NOT NULL
  2. UPDATE HourlyPay SET Hourly = Hourly + 1
    OUTPUT Deleted.EmpID , Updated.Hourly as OldPay, Deleted.Hourly as NewPay
    WHERE Hourly IS NOT NULL
  3. UPDATE HourlyPay SET Hourly = Hourly + 1
    OUTPUT Deleted.EmpID , Inserted.Hourly as OldPay, Deleted.Hourly as NewPay
    WHERE Hourly IS NOT NULL
  4. UPDATE HourlyPay SET Hourly = Hourly + 1
    OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay
    WHERE Hourly IS NOT NULL

Tips from the SQL Joes 2 Pros Development Series – Data Row Space Usage and NULL Storage – Day 15 of 35
Q) You have three variable length data fields. What are the rules that go into the calculation of how large the variable block will be (Choose two)?

  1. You will allocate 2 bytes to the creation of the variable block
  2. You will allocate 3 bytes to the creation of the variable block
  3. You will allocate 2 more bytes for each of the three variable fields
  4. You will allocate 1 byte for every eight columns in the table.

Tips from the SQL Joes 2 Pros Development Series – System and Time Data Types – Day 16 of 35
Q) Which one of the following functions will return the date and time in the current time zone to a precision of milliseconds?

  1. GETDATE( )
  2. SYSDATETIME( )
  3. GETUTCDATE( )
  4. SYSUTCDATETIME( )

Tips from the SQL Joes 2 Pros Development Series – Sparse Data and Space Used by Sparse Data – Day 17 of 35
Q) You have two fields, of INT and MONEY, in your Bonus table. You have 1000 records and all instances of the money column are null. When you set up the money field, you used the Sparse option. How much space are the 1000 rows of the money field using?

  1. None
  2. 4000 bytes
  3. 8000 bytes

2012

Curious Case of Disappearing Rows – ON UPDATE CASCADE and ON DELETE CASCADE – Part 1 of 2
In simple words – due to ON DELETE CASCASE whenever is specified when the data from Table A is deleted and if it is referenced in another table using foreign key it will be deleted as well.

Curious Case of Disappearing Rows – ON UPDATE CASCADE and ON DELETE CASCADE – T-SQL Example – Part 2 of 2
My friend was confused as there was no delete was firing over ProductsDetails Table still there was a delete happening. The reason was because there is a foreign key created between Products and ProductsDetails Table with the keywords ON DELETE CASCADE. Due to ON DELETE CASCADE whenever is specified when the data from Table A is deleted and if it is referenced in another table using foreign key it will be deleted as well.

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

SQL SERVER – Weekly Series – Memory Lane – #041

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

Stop SQL Server Immediately Using T-SQL
How would you stop SQL Server using T-SQL – this is a simple script demonstrating the same.

List Tables in Database Without Primary Key
A simple to the script blog where user can find all the tables where there is no Primary Key.

List All The Column With Specific Data Types
SQL Server 2005 has new datatype of VARCHAR(MAX), we decided to change all our TEXT datatype columns to VARCHAR(MAX). The reason to do that as TEXT datatype will be deprecated in a future version of SQL Server and VARCHAR(MAX) is superior to TEXT datatype in features. We run following script to identify all the columns which are TEXT datatype and developer convert them to VARCHAR (MAX).

2008

Query to Find Column From All Tables of Database
This blog post is straight to script blog post where I demonstrate script to find a table name in the database.

EXCEPT Clause in SQL Server is Similar to MINUS Clause in Oracle
EXCEPT clause in SQL Server is exactly similar to MINUS operation in Oracle. The EXCEPT query and MINUS query returns all rows in the first query that are not returned in the second query. Each SQL statement within the EXCEPT query and MINUS query must have the same number of fields in the result sets with similar data types.

2009

Forgot the Password of Username SA
There may be many more similar situations where employees who have sysamin password leaves the job or a hacker disables the SA account. Resetting the password of SA is a breeze!

Get a List of Fixed Hard Drive and Free Space on Server
At one point in optimization project, I have to decide on index filegroup placement as well TempDB files (.ldf and .mdf) placement. It is commonly known that system gives enhanced performance when index and tempdb are on separate drives than where the main database is placed.

Get Time in Hour:Minute Format from a Datetime – Get Date Part Only from Datetime
I have seen scores of expert developers getting perplexed with SQL Server in finding time only from datetime datatype. Let us have a quick glance look at the solution.

<strong>Quick Video on the same subject</strong>

Here is the video discussing the same concepts.

Multiple CTE in One SELECT Statement Query
I have previously written many articles on CTE. One question I get often is how to use multiple CTE in one query or multiple CTE in SELECT statement. Let us see quickly two examples for the same. I had done my best to take simplest examples in this subject.

2010

This is the list of the all the articles in the series of computed columns.

SQL SERVER – Computed Column – PERSISTED and Storage
This article talks about how computed columns are created and why they take more storage space than before.

SQL SERVER – Computed Column – PERSISTED and Performance
This article talks about how PERSISTED columns give better performance than non-persisted columns.

SQL SERVER – Computed Column – PERSISTED and Performance – Part 2
This article talks about how non-persisted columns give better performance than PERSISTED columns.

SQL SERVER – Computed Column and Performance – Part 3
This article talks about how Index improves the performance of Computed Columns.

SQL SERVER – Computed Column – PERSISTED and Storage – Part 2
This article talks about how creating an index on a computed column does not grow the row length of the table.

SQL SERVER – Computed Columns – Index and Performance
This article summarized all the articles related to computed columns.

Parallelism Query in Database
If I have a dual core computer and I would like to get a query executed with parallelism in order to test it, how would I do that? You can use the AdventureWorks database and let me know if you can get a query to execute in parallel.

Best Practices for DBA Before Taking Vacation
Everybody wants to take a vacation. Who does not love vacation, anyway? However, it seems that it has been getting more and more difficult to take vacation recently. There are two reasons why a person is not able to enjoy his vacation. First is due to company policies (bad boss!), and second is your responsibilities. Well, I cannot guide you much about company policy issues simply because I cannot do something about it. I have a wonderful boss and I have been taking many vacations, doing a few things whenever I’m on vacations.

2011

Tips from the SQL Joes 2 Pros Development Series – Efficient Query Writing Strategy – Day 4 of 35
Q) Square brackets are required when…

  1. The table name conflicts with a keyword
  2. The table name is the same as another table.
  3. The table uses the same name as the database.
  4. To alias the table.

Tips from the SQL Joes 2 Pros Development Series – Finding un-matching Records – Day 5 of 35
Q) Which of the following queries will show all location that have no employees?

  1. SELECT P.EmployeeID, T.[Name]
    FROM Employee P LEFT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE T.TerritoryID IS NULL
  2. SELECT P.EmployeeID, T.[Name]
    FROM Employee P LEFT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL
  3. SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE T.TerritoryID IS NULL
  4. SELECT P.EmployeeID, T.[Name]
    FROM Employee P RIGHT OUTER JOIN Location T
    ON T.TerritoryID = P.TerritoryID
    WHERE P.TerritoryID IS NULL

Tips from the SQL Joes 2 Pros Development Series – Row Constructors – Day 6 of 35
Q) Which code will insert two records with 1 insert into statement?

  1. INSERT into tblSports value(1,’Football’,) (2,’Cricket’,)
  2. INSERT into tblSports values(1,’Football’), (2,’Cricket’)
  3. INSERT into tblSports values(1,’Football’) (2,’Cricket’)
  4. INSERT into tblSports values(1,’Football’):(2,’Cricket’)
  5. INSERT into tblSports values(1,’Football’)::(2,’Cricket’)

Tips from the SQL Joes 2 Pros Development Series – Dirty Records and Table Hints – Day 7 of 35
Q) What happens when you try to query records in the intermediate state?

  1. Your query waits until the data is no longer in the intermediate state.
  2. Your query runs normally.
  3. Your query splits the results into permanent and intermediate results.

Tips from the SQL Joes 2 Pros Development Series – Many to Many Relationships – Day 8 of 35
Q) You have tables named dbo.SalesInvoice and dbo.SalesInvoiceDetail. CustomerID is located in the SalesInvoice table and InvoiceID is located in both tables. You have been told to show the discount amounts from the SalesInvoiceDetail table that correspond to the sales of a specific CustomerID of 490. Which T-SQL statement should you use?

  1. SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490
  2. SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490
  3. SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    WHERE EXISTS (dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490)

Tips from the SQL Joes 2 Pros Development Series – Overriding Identity Fields – Tricks and Tips of Identity Fields – Day 9 of 35
Q) You need to explicitly insert a value into an identity field for the SalesInvoice table. What two things must you do in order for your insert statement to successfully execute? (Choose two)

  1. Turn the IDENTITY_INSERT  to ON for the SalesInvoice table
  2. Turn the IDENTITY_INSERT  to OFF for the SalesInvoice table
  3. Insert your values by position
  4. Insert your values by name

Use INSERT INTO … SELECT instead of Cursor
Sometimes there is a simpler solution to different problems. When we do not know enough we often rely on ancient techniques. In this blog post, I explain how we can solve multiple insert related problems very quickly.

Tips from the SQL Joes 2 Pros Development Series – Aggregates with the Over Clause – Day 10 of 35
Q) You want to show all fields of the Employee table. You want an additional field called StartDate that shows the first HireDate for all Employees. Which query should you use?

  1. SELECT *, Min(HireDate) as StartDate FROM Employee
  2. SELECT *, Max(HireDate) as StartDate FROM Employee
  3. SELECT *, Min(HireDate) OVER() as StartDate FROM Employee
  4. SELECT *, Max(HireDate) OVER() as StartDate FROM Employee

2012

In this week we ran contests where we asked every day new question and in return there was interesting giveaways to win. It will take a few seconds only – take a shot and see how many answer you know.

Query Hint – Day 1 of 5
Which of the following queries will return dirty data?

a) SELECT * FROM Table1 (READUNCOMMITED)
b) SELECT * FROM Table1 (NOLOCK)
c) SELECT * FROM Table1 (DIRTYREAD)
d) SELECT * FROM Table1 (MYLOCK)

Identity Fields – Day 2 of 5
Which of the following queries will return dirty data?

a) SELECT * FROM Table1 (READUNCOMMITED)
b) SELECT * FROM Table1 (NOLOCK)
c) SELECT * FROM Table1 (DIRTYREAD)
d) SELECT * FROM Table1 (MYLOCK)

Clustered Index and Primary Key – Day 3 of 5
Which of the following datatype is usually NOT the best choice for Primary Key and Clustered Index?

a) INT
b) BIGINT
c) GUID
d) SMALLINT

Expanding Views – Day 4 of 5
Which of the following key word will force the query to use indexes created on views?

a) ENCRYPTION
b) SCHEMABINDING
c) NOEXPAND
d) CHECK OPTION

Understanding XML – Day 5 of 5
Is following XML a well formed XML Document?

<?xml version=”1.0″?>
<address>
<firstname>Pinal</firstname>
<lastname>Dave</lastname>
<title>Founder</title>
<company>SQLAuthority.com</company>
</address>

a) Yes
b) No
c) I do not know

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

SQL SERVER – Weekly Series – Memory Lane – #040

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

Complete Series of SQL Server Interview Questions and Answers
Data Warehousing Interview Questions and Answers – Introduction
Data Warehousing Interview Questions and Answers – Part 1
Data Warehousing Interview Questions and Answers – Part 2
Data Warehousing Interview Questions and Answers – Part 3
Data Warehousing Interview Questions and Answers Complete List Download

2008

Simple Example of Recursive CTE
Recursive is the process in which the query executes itself. It is used to get results based on the output of base query. We can use CTE as Recursive CTE (Common Table Expression).

Simple Example of Recursive CTE – Part 2 – MAXRECURSION – Prevent CTE Infinite Loop
Now if your CTE goes beyond nth recursion it will throw an error and stop executing. If you put MAXRECURSION value too low it may be possible before your desire result is accomplished and will throw an error.

Get Current System Date Time

Effect of Order of Join In Query
When we use Left or Right Join, We have a base table Employee and the records are order by the primary key i.e The EmployeeID of the base table by default. But when we use the Inner Join, then the table having smallest number of records are used to order by. Here in our above example, the HumanResources.Department has 16 Records. So  the records are sorted by the departmentId of the HumanResources.Department table.

Difference Between INTERSECT and INNER JOIN – INTERSECT vs. INNER JOIN
INTERSECT operator in SQL Server 2005 is used to retrieve the common records from both the left and the right query of the Intersect Operator. INTERSECT operator returns almost same results as INNER JOIN clause many times.

2009

Design Process Decision Flow
The following six steps represent the most critical design elements in a well-planned SQL Server 2008 design:

  • Step 1: Determine the Project Scope
  • Step 2: Determine Which Roles Will Be Required
  • Step 3: Design the SQL Server Database Engine Infrastructure
  • Step 4: Design the SQL Server Integration Services Infrastructure
  • Step 5: Design the SQL Server Analysis Services Infrastructure
  • Step 6: Design the SQL Server Reporting Services Infrastructure

Copy Database With Data – Generate T-SQL For Inserting Data From One Table to Another Table
I had written on the subject of how to insert data from one table to another table without generating any script or using wizard in my article SQL SERVER – Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE. Today, we will go over a similar question regarding how to generate script for data from database as well as table. SQL Server 2008 has simplified everything.

Introduction to Cloud Computing
“Cloud Computing,” to put it simply, means “Internet Computing.” The Internet is commonly visualized as clouds; hence the term “cloud computing” for computation done through the Internet. With Cloud Computing users can access database resources via the Internet from anywhere, for as long as they need, without worrying about any maintenance or management of actual resources. Besides, databases in cloud are very dynamic and scalable.

Introduction to SQL Server 2008 Profiler
SQL Server Profiler is a powerful tool that is available with SQL Server since a long time; however, it has mostly been underutilized by DBAs. SQL Server Profiler can perform various significant functions such as tracing what is running under the SQL Server Engine’s hood, and finding out how queries are resolved internally and what scripts are running to accomplish any T-SQL command.

2010

Introduction to BINARY_CHECKSUM and Working Example
I was asked if I can give a working example of BINARY_CHECKSUM. This is usually used to detect changes in a row. If any row has any value changed, this function can be used to figure out if the values are changed in the rows. However, if the row is changed from A to B and once again changed back to A, the BINARY_CHECKSUM cannot be used to detect the changes. Let us see a quick example of the of same.

This is the list of the all the articles in the series of computed columns.

SQL SERVER – Computed Column – PERSISTED and Storage

This article talks about how computed columns are created and why they take more storage space than before.

SQL SERVER – Computed Column – PERSISTED and Performance

This article talks about how PERSISTED columns give better performance than non-persisted columns.

SQL SERVER – Computed Column – PERSISTED and Performance – Part 2

This article talks about how non-persisted columns give better performance than PERSISTED columns.

SQL SERVER – Computed Column and Performance – Part 3

This article talks about how Index improves the performance of Computed Columns.

SQL SERVER – Computed Column – PERSISTED and Storage – Part 2

This article talks about how creating index on computed column does not grow the row length of table.

SQL SERVER – Computed Columns – Index and Performance

This article summarized all the articles related to computed columns.

2011

SQL SERVER – Interview Questions and Answers – Guest Post by Nakul Vachhrajani – Day 28 of 31

Nakul explains the word – ‘interview ‘ with an entire different angle to the subject. What really attacks on Nakul’s writing is his extremely clear ideas which are super crisp and to the point. Nakul talks about ‘Educational Qualification has been never the only requirement’ with such a decent argument, that makes this article a must read.

SQL SERVER – Interview Questions and Answers – Guest Post by Feodor Georgiev – Day 29 of 31

Feodor is our most read gust blog authors. He always comes up with an interesting subject. Feodor start writing with interesting concept that – the job interview is like an enchanted dance between a potential employee and a potential employer which plays a defining role in their entire collaboration. This article has many witty comments and many interesting subjects. A long article which has not a single dull moment.

SQL SERVER – Interview Questions and Answers – Guest Post by Jacob Sebastian – Day 30 of 31

Jacob Sebastian never likes to talk about easy subject. He always talks on difficult subjects. This time he talks about the philosophy of XML. The purpose of the questions and their answers given below is to ensure that the people who read them and learn them will get a basic understanding of the XML functionalities provided by SQL Server. Jacob makes the dry subject like XML to worth reading and learning it.

SQL SERVER – The Difficult Interview Question – Moment in the Life – Day 31 of 31
I guess the title says all – Interview is the moment of life. Final write up on this subject by myself.

Learning is an extremely important part of life. From the first step, everybody progresses in life and learns something new.

SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Wildcard Basics Recap – Day 1 of 35

You want to find all first names that start with the letters A-M in your Customer table. Which SQL code would you use?

  1. SELECT * FROM Customer
    WHERE Firstname <= ‘m%’
  2. SELECT * FROM Customer
    WHERE Firstname  = ‘a-m%’
  3. SELECT * FROM Customer
    WHERE Firstname like ‘a-m%’
  4. SELECT * FROM Customer
    WHERE Firstname  = ‘[a-m]% ‘
  5. SELECT * FROM Customer
    WHERE Firstname like ‘[a-m]%’

Q 2) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Wildcard – Querying Special Characters – Day 2 of 35

You want to find all grant names that have an Underscore as the second letter. Which SQL code would you use?

  1. SELECT * FROM [Grant]
    WHERE GrantName like ‘_[_]% ‘
  2. SELECT * FROM [Grant]
    WHERE GrantName like ‘[_]_% ‘
  3. SELECT * FROM [Grant]
    WHERE GrantName like ‘_%[_]%_ ‘
  4. SELECT * FROM [Grant]
    WHERE GrantName = ‘_[_]% ‘
  5. SELECT * FROM [Grant]
    WHERE GrantName = ‘[_]_% ‘
  6. SELECT * FROM [Grant]
    WHERE GrantName = ‘_%[_]%_ ‘

SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Finding Apostrophes in String and Text – Day 3 of 35

You want to find all first names that have an apostrophe anywhere in the name. Which SQL code would you use?

  1. SELECT * FROM Employee
    WHERE Firstname like ‘_’% ‘
  2. SELECT * FROM Employee
    WHERE Firstname like ‘_”% ‘
  3. SELECT * FROM Employee
    WHERE Firstname like ‘_[]% ‘
  4. SELECT * FROM Employee
    WHERE Firstname like ‘%’% ‘
  5. SELECT * FROM Employee
    WHERE Firstname like ‘%”% ‘
  6. SELECT * FROM Employee
    WHERE Firstname like ‘%[]% ‘

2012

Difference Between ORIGINAL_LOGIN() and SUSER_SNAME()
Function ORIGINAL_LOGIN() returns the name of the original or very first login that connected to the instance of SQL Server and it is used to identity of the original login in sessions.

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