.gif)
Introduction to XML Data Type Methods - SQL SERVER.
You can use the xml data type methods to query an XML instance stored in a variable or column of xml type.
This video explains the fundamentals of the XML Data Type Methods.
Tricks to Replace SELECT * with Column Names - SQL SERVER
This video explains tricks to replace SELECT * with Column Names using SQL Server Management Studio.
This video explains tricks to replace SELECT * with Column Names using SQL Server Management Studio.
In this SQL in Sixty Seconds Video we will see how quickly and easily we can import CSV file into database table.
Importing CSV into SQL Server - SQL SERVER
Importing CSV into SQL Server - SQL SERVER
Three Tricks to Comment T-SQL in SQL Server Management Studio - SQL SERVER
In this SQL in Sixty Seconds Video we will see three methods to comment T-SQL code in SQL Server Management Studio.
In this SQL in Sixty Seconds Video we will see three methods to comment T-SQL code in SQL Server Management Studio.
SQL Server 2008/R2 and SQL Server 2012 can generate script containing Schema and Data. It is fairly easy process and can be quickly done using SQL Server Management Studio (SSMS).
A very popular question is how to insert multiple values into a single table using single statement. In this edition of SQL in Sixty Seconds we are going three different methods.
A very common question is how to get the current datetime in SQL Server. The most obvious question which is asked as a follow up to an earlier question is how to find only date or only time from current datetime in SQL Server. This sixty second video describes the same.
During performance tuning conversation the very first question people often ask is what are the queries offending the server or in another word let us identify the queries which are the most resource intensive. In this quick video we will learn to identify those queries which are resource intensive.
Above script returns three important details about all the tables of the database.
1) No of NonClustered Index
2) No of the Rows in the Table
3) Table Clustered Index Property (Heap or Clustered Index)
One of the most annoying SQL Server Error comes up when attempting to connect to SQL Server. There are many reasons for this error and there are multiple ways to fix this error. In this article I have included the most common resolutions of this error.
There are two different ways to copy data from one table to another table.
Method 1 : INSERT INTO SELECT
Method 2 : SELECT INTO
Method 1 : INSERT INTO SELECT
Method 2 : SELECT INTO
There are always cases when we believe certain names are perfect for columnname or tablename but as time passes and needs are changed. We can use SP_RENAME stored procedure to change the name of the column or table. Though, one has to make sure that all the places where the name is used in the code also reflects the new name as well.
A very common requirement of developer is to format datetime to their specific need. In this quick example we will go over two different methods of displaying datetime in specific format. Using 1) Convert Function 2) SQL Server 2012 Format Function.
Every developers once in a while facing unfortunate situation where they have not yet saved the work and their SQL Server Management Studio crashes. Well, you can minimize the loss by optimizing auto recovery settings. In this video we can see how to set the auto recovery settings.
Concatenating string is one of the most common tasks in SQL Server and every developer has to come across it. We have to concat the string when we have to see the display full name of the person from first name and last name. In this video we will see various methods to concatenate the strings. SQL Server 2012 has introduced new function CONCAT which concatenates the strings much efficiently .
In this article I am going to explain how you can find nth highest salary of the employee in SQL Server.
It is very common question asked by interviewer that how to find 2nd highest salary of employee or how to find 3rd highest salary of employee or how to find nth highest salary of employee in SQL Server. Here in this Sql Server Query Example i will Show you how to find 2nd,3rd,4th,.......,nth highest salary of employee.For this sql server query example i have created a Temporary Sql server Table in Sql Server Database named #EmpDetails .And enterd some values to this sql server table.The script is given below.
CREATE TABLE #EmpDetails(EMPID int,EMPNAME NVARCHAR(10),EMPSAL int)INSERT INTO #EmpDetails VALUES (1,'RAJ',2000)INSERT INTO #EmpDetails VALUES(2,'SURESH',12000)INSERT INTO #EmpDetails VALUES(3,'SURAJ',15000)INSERT INTO #EmpDetails VALUES(4,'SUNEEL',14000)INSERT INTO #EmpDetails VALUES(5,'SANJEEV',13000)INSERT INTO #EmpDetails VALUES(6,'ATUL',12070)INSERT INTO #EmpDetails VALUES(7,'VINEET',12600)INSERT INTO #EmpDetails VALUES(8,'VIVEK',12700)
Now Check the table for values.
select * from #EmpDetails
It will display The following Rows.
| EMPID | EMPNAME | EMPSAL |
|---|---|---|
| 1 | RAJ | 2000 |
| 2 | SURESH | 12000 |
| 3 | SURAJ | 15000 |
| 4 | SUNEEL | 14000 |
| 5 | SANJEEV | 13000 |
| 6 | ATUL | 12070 |
| 7 | VINEET | 12600 |
| 8 | VIVEK | 12700 |
Once table creation is completed now we will see different ways to get 2nd, 3rd, etc or nth highest salary of employee. The below sql query can be used to get 1st, 2nd, 3rd, 4th, 5th ….etc highest salary of employee.
SELECT TOP 1 EMPSAL FROM ( SELECT DISTINCT TOP n EMPSAL FROM #EmpDetails ORDER BY EMPSAL DESC) a ORDER BY EMPSAL
Replace n with 2 to find the second (2nd) highest salary of employee query will look like this .
SELECT TOP 1 EMPSAL FROM ( SELECT DISTINCT TOP 2 EMPSAL FROM #EmpDetails ORDER BY EMPSAL DESC) a ORDER BY EMPSAL
Result will be
EMPSAL
----------
14000
Replace n with 3 to find the third (3rd) highest salary of employee query will look like this .
SELECT TOP 1 EMPSAL FROM ( SELECT DISTINCT TOP 3 EMPSAL FROM #EmpDetails ORDER BY EMPSAL DESC) a ORDER BY EMPSAL
Result will be
EMPSAL
-------------
13000
This topic is about sql server Comma Separated Values(csv) from Table Column.
There are Many times where we need to convert a set of rows in a single column to a comma separated (CSV) string , in this article i am going to tell you how you can get csv values from sql server table column.
For this sql server query example i have created a Temporary Table in Sql Server Database.
CREATE TABLE #MyTable(Column1 NVARCHAR(10))
INSERT INTO #MyTable VALUES('sdf')INSERT INTO #MyTable VALUES('dfg')INSERT INTO #MyTable VALUES('ghj')INSERT INTO #MyTable VALUES('rty')INSERT INTO #MyTable VALUES('hjk')
Here i am showing Two methods to create Comma Separated Values(csv) from Table Column.
1-Create Comma Seperated Values(CSV) Using COALESCE Method
DECLARE @MakeStr VARCHAR(200)
SELECT @MakeStr = COALESCE(@MakeStr+',' ,'') + Column1
FROM #MyTable
SELECT @MakeStr as MyCSV
2-Create Comma Seperated String Using XML Method
DECLARE @MakeStr VARCHAR(200)
SELECT SUBSTRING(
(SELECT ',' + Column1
FROM #MyTable
ORDER BY Column1
FOR XML PATH('')),2,200) AS MyCSV
And the result for the above Sql Query Will be
MyCSV
——————–
sdf,dfg,ghj,rty,hjk
MyCSV
——————–
dfg,ghj,hjk,rty,sdf
And the result for the above Sql Query Will be
MyCSV
——————–
sdf,dfg,ghj,rty,hjk
MyCSV
——————–
dfg,ghj,hjk,rty,sdf
Here is the sql query that will return recently executed T-SQL query on database.
SELECT QS.last_execution_time AS
[Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS
QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) AS dest
ORDER BY
QS.last_execution_time DESC
Error Message:
.Net SqlClient Data Provider: Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 – An existing connection was forcibly closed by the remote host.)
This error Occurs when the connection is forcibly closed by SQL Server.
Possible Reasons:
- When the connection was IDLE for a long time
- SQL Server Service is restarted / Stopped
- The database to which the connection is connected to is restored
- KILL Command is issued by any other user.
Solution/fix:
The best solution to this problem is Try to reconnect to the server.
.Net SqlClient Data Provider: Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 – An existing connection was forcibly closed by the remote host.)
This error Occurs when the connection is forcibly closed by SQL Server.
Possible Reasons:
- When the connection was IDLE for a long time
- SQL Server Service is restarted / Stopped
- The database to which the connection is connected to is restored
- KILL Command is issued by any other user.
Solution/fix:
The best solution to this problem is Try to reconnect to the server.
Here is a SQL Query to find the list of Functions in SQL Server database.When you run this query it will show the list of all functions in SQL Server DataBase.
SELECT name FROM sys.objects
WHERE type
IN ('AF ','FN', 'IF', 'TF', 'FS', 'FT')
