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.
- Search Stored Procedure Code – Search Stored Procedure Text
- Find Tables With Foreign Key Constraint in Database
- Find Tables With Primary Key Constraint in Database
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.
- Conventional Backup and Restore
- Spilt File Backup and Restore
- Mirror File Backup
- Understanding FORMAT Clause
- 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?
- SELECT Price, ProductName
FROM CurrentProducts AS ProductExport
FOR XML PATH(‘Product’) - SELECT Price, ProductName
FROM CurrentProducts
FOR XML AUTO, ROOT(‘ProductExport’) - SELECT Price [@Price], ProductName AS [*]
FROM CurrentProducts AS ProductExport
FOR XML AUTO, ELEMENTS - SELECT Price [@Price], ProductName AS [*]
FROM CurrentProducts
FOR XML PATH(‘Product’), ROOT(‘ProductExport’)
Q) What process will transform XML data to a rowset?
- Shredding
- 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?
- The handle as an INT
- The handle as an XML
- 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?
- SELECT StoreName, StoreHours. value(‘/hours[1]/@open’,’time’)
FROM Buildings
WHERE StoreHours.value(‘/hours[1]/@dayofWeek’,’varchar(20)’) = @Day - SELECT StoreName, StoreHours. value(‘/hours[1]/@open’,’time’)
FROM Buildings
WHERE StoreHours.exist(‘/hours[@dayofWeek=”Wednesday”]‘) = 1 - 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.
- Installing Data Quality Services (DQS) on SQL Server 2012
- Connecting Error to Data Quality Services (DQS) on SQL Server 2012
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 (https://blog.sqlauthority.com)
