Problem:How to check whether the SQL server is configured for case sensitivity or not, use:
SELECT DATABASEPROPERTYEX (”, ‘Collation’)
Result: SQL_Latin1_General_CP1_CI_AS (means Case Insensitive)
Result: SQL_Latin1_General_CP1_CS_AS (means Case Sensitive)
Problem: How to kill a SSIS package execution in the Integration Services catalog? This can be achieved via two methods i.e. using GUI or TSQL. The GUI is very slow in fetching list of active sessions so you can use TSQL to kill the SSIS package session.
Solution: Use following script to Kill SSIS package in Integration Services catalog.
--change "YourpackageName" with the actual package name
DECLARE @package_name SYSNAME ='YourpackageName.dtsx'
DECLARE @execution_id BIGINT
SELECT @execution_id = Max(execution_id)
WHERE package_name = @package_name
AND end_time IS NULL
Problem: Find Nth highest salary in Minimal time.
Interviewer often asks to find Nth Highest Salary in an interview.Many solutions are available on internet to do so.Couple of commonly used solutions are Solution 1 and Solution 2
All these solutions are bit slow.Using CTE you can find the Nth highest salary very efficiently and impress your interviewer.
- Create a sample table and insert sample data in it
CREATE TABLE #T1 (ID1 INT, [Name] NVARCHAR(50), Salary INT)
INSERT INTO #T1 VALUES (1, ‘pds’, 10000)
INSERT INTO #T1 VALUES (2, ‘vicky’, 10000)
INSERT INTO #T1 VALUES (3, ‘divyan’, 30000)
INSERT INTO #T1 VALUES (4, ‘deepak’, 40000)
INSERT INTO #T1 VALUES (5, ‘anu’, 50000)
INSERT INTO #T1 VALUES (6, ‘jai’, 60000)
INSERT INTO #T1 VALUES (7, ‘abhi’, 20000)
INSERT INTO #T1 VALUES (8, ‘disha’, 40000)
INSERT INTO #T1 VALUES (9, ‘anika’, 60000)
INSERT INTO #T1 VALUES (10, ‘vijay’, 50000)
- My Version of Query Using CTE
declare @n smallint;
set @n =4
;with cte as (SELECT name,salary,
dense_rank() OVER( ORDER BY salary DESC) AS rowid
WHERE rowid = @n
- Comparison stats with other Solutions
Introduction This document list is the ways of improving the performance of TSQL.It contains a few techniques and tips used in our project. Query tuning If your SQL Server database is not configured to be case sensitive, you don’t need to use LOWER or UPPER to force the case of text to be equal for […]
It gets the system information like OS,version name,components and many more.
Opens the SQL server configuration manager if the latest SQL server installed on the machine is SQL 2012
open the SQL server configuration manager if the latest SQL server installed on the machine is SQL 2008
It opens the computer management window which in turn used for user management,event viewer,Disk management etc.
It opens the IIS window which int-run used for managing FTP,Web sites and SMTP
It fetches the list of all the SQL server installed on the current network.
run>cmd>netstat -na|find "1433"
Finds the status of the 1433 port
The host file path.In case client SSMS is not able to connect to the SQL server using the server name but can connect using the IP address then try making an entry(associate IP address with the Server name) into this file.
open the local security setting window which is used to set security options,IP security,Password policy etc.
If you want to find the currently ruining jobs then following queries will help you.
List All currently running SQL agent jobs:
exec msdb..sp_help_job @execution_status=1
List extra information about you job:
exec msdb..sp_help_job @Job_name=<yourjobname>
possible values of @execution_status
||Returns only those jobs that are not idle or suspended.
||Waiting for thread.
||Performing completion actions.