|
|
-
|
|
We can populate a table using a dump of data available as a text file. You can also import data from various type of files including CSV and raw file into SQL. In the example we’ll look at how to import data from a Text file into SQL Table.
For this example, I have a Text file ProductList.txt......
|
|
-
|
|
Recently, I encountered this message in one of our production servers. When I checked the SQL Agent Log I found below error message.
I searched for a solution and found a KB article which describes the issue, KB928317.
The KB article describes only one scenario that a Full backup must exist be......
|
|
-
|
|
If you are managing a lot of databases, you might need to scratch your head for a while to remember where and when did you backed up a database. Fortunately, this information is stored in MSDB database and can be easily retrieved using below tables:
1. backupmediafamily – contains one row for......
|
|
-
|
|
You can create shortcuts for frequently used queries in Management Studio, some of the shortcuts are predefined, like pressing “Ctrl + 1” will execute “sp_who2”. You can create shortcuts for your own queries or change the existing shortcuts. To create a new shortcut go to Too......
|
|
-
|
|
We use the undocumented stored procedure sp_who2 to check for blocked process on the server. The output returned by this stored procedure returns all processes on the server.
now, to find the processes which are actually blocked, you need to go through the result set, which will be almost > 50 ......
|
|
-
|
|
Often we need to transpose the result set converting rows to columns. let’s look at an example. you can find the syntax on BOL. consider the following data:
We need to convert this to a pivot table, the out put should transpose the WeekIDs to rows from columns, the expected result is:
......
|
|
-
|
|
A CTE can be referenced multiple time in a single statement, this can be useful in a self-join scenario. age old employee manager example…. consider following data,
Now to list employees with their respective managers we need to do a self-join
SELECT EMP1.EmpID, EMP1.EmpName, EMP1.......
|
|
-
|
|
What do we do when we open SQL Server Management Studio? 99% of the time we connect to the server!
This can be automated using the command-line options available for management studio. You can see full list of available command-line options, go to Start > Run > type “SSMS /?”:
<......
|
|
-
|
|
There are other ways too which can be used to GROUP BY a calculated column such as using a derived table.
Consider, the below data which needs to be categorized based on the Cost Range.
USE AdventureWorks2008R2
SELECT StandardCost, ProductID
FROM Production.Product
The data can be categoriz......
|
|
-
|
|
Another use of CTE can be to simply a query which joins a lot of tables. we can divide he number of joins between the CTE and the query. Hence, making it more readable and easier to understand.
For this example, I have create five different tables for Product Name, Category, Description, Model and ......
|
|