SQL Function to Convert Seconds to HH:MM
Create a function as like below:
CREATE FUNCTION [dbo].[ufn_GetHHMM] ( @pInputSecs BIGINT )
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @HHMM Varchar(Max)
IF @pInputSecs < 60 and @pInputSecs<>0
BEGIN
SET @HHMM= '0:01'
END
ELSE
BEGIN
SET @HHMM=ISNULL(CAST(@pInputSecs/3600 AS VARCHAR(MAX)) +':'
+RIGHT('0'+CAST(ROUND(CAST((@pInputSecs%3600.0)/60.0 as float),0) as varchar(MAX)),2),'0:00')
END
RETURN @HHMM
END
--To Get Results
SELECT dbo.ufn_GetHHMM(DurationinSeconds) --Assume you have column name 'DurationinSeconds'
Tuesday, April 16, 2013
T-SQL to Clean Buffers and Cache in SQL Server
T-SQL to Clean Buffers and Cache in SQL Server
DBCC FREEPROCcache
DBCC FREESystemCache('ALL')
DBCC DROPCLEANBUFFERS
DBCC FREEPROCcache
DBCC FREESystemCache('ALL')
DBCC DROPCLEANBUFFERS
Tuesday, February 26, 2013
Attach mdf file to Database in SQL Server
Below is the code to attach *.mdf file to existing database:
USE [master]
GO
Method 1:
EXEC sp_attach_single_file_db @dbname='BISource',
@physname=N'C:\Users\mvaradhan\Downloads\AdventureWorksDW2008R2.mdf'
GO
Method 2:
CREATE DATABASE BISource ON
(FILENAME = N'C:\Users\mvaradhan\Downloads\AdventureWorksDW2008R2.mdf')
FOR ATTACH_REBUILD_LOG
GO
Method 3:
CREATE DATABASE BISource ON
( FILENAME = N'C:\Users\mvaradhan\Downloads\AdventureWorksDW2008R2.mdf')
FOR ATTACH
GO
USE [master]
GO
Method 1:
EXEC sp_attach_single_file_db @dbname='BISource',
@physname=N'C:\Users\mvaradhan\Downloads\AdventureWorksDW2008R2.mdf'
GO
Method 2:
CREATE DATABASE BISource ON
(FILENAME = N'C:\Users\mvaradhan\Downloads\AdventureWorksDW2008R2.mdf')
FOR ATTACH_REBUILD_LOG
GO
Method 3:
CREATE DATABASE BISource ON
( FILENAME = N'C:\Users\mvaradhan\Downloads\AdventureWorksDW2008R2.mdf')
FOR ATTACH
GO
Monday, February 18, 2013
Determine Size of SQL Table
How to determine size of SQL server table:
Use built-in code: sp_spaceused ‘Tablename’
Example: sp_SpaceUsed 'Employee'
Use built-in code: sp_spaceused ‘Tablename’
Example: sp_SpaceUsed 'Employee'
Thursday, January 24, 2013
How to determine largest value comparing two or multiple columns using T-SQL
The below function can be used as a alternate for GREATEST() function in MYSQL:
Create a function with below code in your SQL SERVER database:
Below function compares 11 columns in a table.
CREATE FUNCTION dbo.fnGreatest
( @Value0 sql_variant,
@Value1 sql_variant,
@Value2 sql_variant,
@Value3 sql_variant,
@Value4 sql_variant,
@Value5 sql_variant,
@Value6 sql_variant,
@Value7 sql_variant,
@Value8 sql_variant,
@Value9 sql_variant,
@Value10 sql_variant
)
RETURNS sql_variant
AS
BEGIN
DECLARE @ReturnValue sql_variant
DECLARE @MaxTable table
( RowID int IDENTITY,
MaxColumn sql_variant
)
INSERT INTO @MaxTable VALUES ( @Value0 )
INSERT INTO @MaxTable VALUES ( @Value1 )
INSERT INTO @MaxTable VALUES ( @Value2 )
INSERT INTO @MaxTable VALUES ( @Value4 )
INSERT INTO @MaxTable VALUES ( @Value5 )
INSERT INTO @MaxTable VALUES ( @Value6 )
INSERT INTO @MaxTable VALUES ( @Value7 )
INSERT INTO @MaxTable VALUES ( @Value8 )
INSERT INTO @MaxTable VALUES ( @Value9 )
INSERT INTO @MaxTable VALUES ( @Value10 )
SELECT @ReturnValue = MAX(MaxColumn)
FROM @MaxTable
RETURN @ReturnValue
END
GO
SELECT fnGreatest(date1,date2,date3.....date11) from <tablename>
Tuesday, December 11, 2012
Convert Seconds to HH:MM:SS
DECLARE @SECONDS INT = 5200
SELECT CONVERT(CHAR(8),DATEADD(second,@SECONDS,0),108) 'TOS HHMMSS'
Thursday, November 22, 2012
How to Apply Read/Write Mode to a Datbase
TO SET TO READ WRITE
-----------------------------------------------------------------
USE MASTER
GO
/*Mark it as Singe User*/
ALTER DATABASE [DATABASE_NAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
/*Mark the database as Read Write*/
ALTER DATABASE [DATABASE_NAM] ESET READ_WRITE WITH ROLLBACK IMMEDIATE
/*Mark it back to Multi User now*/
ALTER DATABASE DATABASE_NAME SET MULTI_USER
GO
/*Mark it as Singe User*/
ALTER DATABASE [DATABASE_NAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
/*Mark the database as Read Write*/
ALTER DATABASE [DATABASE_NAM] ESET READ_WRITE WITH ROLLBACK IMMEDIATE
/*Mark it back to Multi User now*/
ALTER DATABASE DATABASE_NAME SET MULTI_USER
TO SET TO READ ONLY
-----------------------------------------------------------------
USE MASTER
GO
/*Mark it as Singe User*/
ALTER DATABASE [DATABASE_NAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
/*Mark the database as Read Only*/
ALTER DATABASE [DATABASE_NAME] SET READ_ONLY WITH ROLLBACK IMMEDIATE
/*Mark it back to Multi User now*/
ALTER DATABASE [DATABASE_NAME] SET MULTI_USER
GO
/*Mark it as Singe User*/
ALTER DATABASE [DATABASE_NAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
/*Mark the database as Read Only*/
ALTER DATABASE [DATABASE_NAME] SET READ_ONLY WITH ROLLBACK IMMEDIATE
/*Mark it back to Multi User now*/
ALTER DATABASE [DATABASE_NAME] SET MULTI_USER
T-SQL to Filter Records Containing Special Characters and Latin Characters
The below SQL code helps to filter record containing Non-English characters:
SELECT * FROM [Table_Name]
WHERE [Column_Name] LIKE N'%[^ -~]%' collate Latin1_General_BIN
SELECT * FROM [Table_Name]
WHERE [Column_Name] LIKE N'%[^ -~]%' collate Latin1_General_BIN
Thursday, November 1, 2012
To Check SQL Server Status & Blocking Process
-- 1. To check
any blocking in SQL server
SELECT CMD, * FROM SYS.SYSPROCESSES WHERE BLOCKED > 0
-- 2. Check the
log space in the server
DBCC SQLPERF (LOGSPACE)
GO
-- 3. Check any
process is running for long time.
SP_WHO2
Thursday, September 27, 2012
Attaching MDF file to a Database
Below is the query to attach a .mdf file to a database
CREATE DATABASE AdventureWorks2008DWR2 ON
( FILENAME = N'C:\Users\mvaradhan\Downloads\AdventureWorksDW2008R2.mdf')
FOR ATTACH
GO
Tuesday, September 25, 2012
Query to get list of packages in a Integration Server
Below is the SQL query to get the list of packages deployed to an Integration Server:
WITH
ChildFolders
AS
(
SELECT
PARENT.parentfolderid,
PARENT.folderid,
PARENT.foldername,
CAST('' AS SYSNAME) AS RootFolder,
CAST(PARENT.foldername AS VARCHAR(MAX)) AS FullPath,
0 AS
Lvl
from msdb.dbo.sysssispackagefolders
PARENT
WHERE
PARENT.parentfolderid IS
NULL
UNION ALL
SELECT
CHILD.parentfolderid,
CHILD.folderid,
CHILD.foldername,
CASE
ChildFolders.Lvl
WHEN
0 THEN CHILD.foldername
ELSE
ChildFolders.RootFolder
END AS RootFolder,
CAST(ChildFolders.FullPath
+ '/' + CHILD.foldername AS VARCHAR(MAX))
AS
FullPath,
ChildFolders.Lvl
+ 1 AS Lvl
FROM msdb.dbo.sysssispackagefolders
CHILD
inner join ChildFolders ON
ChildFolders.folderid =
CHILD.parentfolderid
)
SELECT F.RootFolder, F.FullPath, P.name AS PackageName,
P.[description]
AS PackageDescription,
P.packageformat,
P.packagetype,
P.vermajor, P.verminor, P.verbuild, P.vercomments,
CAST(CAST(P.packagedata AS VARBINARY(MAX)) AS XML) AS PackageData
FROM
ChildFolders F
inner join msdb.dbo.sysssispackages P on
P.folderid = F.folderid
ORDER BY F.FullPath ASC, P.name ASC;
Monday, July 30, 2012
Forgot or Reset SQL Authentication 'sa' password
To reset password for an SQL Authentication login user, execute the following code:
Say for example, you forgot 'sa' password, then you can execute the below code to assign new password for 'sa' user:
EXEC SP_PASSWORD @new='900%sec', @loginame='sa'.
Now try connecting to SQL server with your 'sa' login and new password.
Say for example, you forgot 'sa' password, then you can execute the below code to assign new password for 'sa' user:
EXEC SP_PASSWORD @new='900%sec', @loginame='sa'.
Now try connecting to SQL server with your 'sa' login and new password.
Wednesday, July 4, 2012
New T-SQL functions in SQL Server 2012
New T-SQL functions in SQL Server 2012:
--PARSE()
--Parse Currency Symbol
SELECT PARSE('$100' AS MONEY USING 'EN-US') AS Currency
--Result = 100.00
--Parse DATETIME
SELECT PARSE('Monday, 13 December 2010' AS datetime2 USING 'en-US') AS US_Date
--Result = 2010-12-13 00:00:00.0000000
--CONCAT()
SELECT CONCAT('Firstname',' ','Surname') AS MyName
--Result = Firstname Surname
--CHOOSE()
DECLARE @Letter INT = 4
SELECT CHOOSE(@Letter, 'A','B','C','D', 'E','F')
--Result = D
--IIF()
DECLARE @Letter INT = 5
SELECT IIF(@Letter % 2 > 0,'ODD','EVEN')
--Result = ODD
--Get Date for the provided yy,MM,dd
SELECT DATEFROMPARTS(1999,2,3)
--GET LastDate of Month
SELECT EOMONTH(GETDATE(),0)
--PARSE()
--Parse Currency Symbol
SELECT PARSE('$100' AS MONEY USING 'EN-US') AS Currency
--Result = 100.00
--Parse DATETIME
SELECT PARSE('Monday, 13 December 2010' AS datetime2 USING 'en-US') AS US_Date
--Result = 2010-12-13 00:00:00.0000000
--CONCAT()
SELECT CONCAT('Firstname',' ','Surname') AS MyName
--Result = Firstname Surname
--CHOOSE()
DECLARE @Letter INT = 4
SELECT CHOOSE(@Letter, 'A','B','C','D', 'E','F')
--Result = D
--IIF()
DECLARE @Letter INT = 5
SELECT IIF(@Letter % 2 > 0,'ODD','EVEN')
--Result = ODD
--Get Date for the provided yy,MM,dd
SELECT DATEFROMPARTS(1999,2,3)
--GET LastDate of Month
SELECT EOMONTH(GETDATE(),0)
SQL 2012 FORMAT() Function
Formatting in T-SQL - SQL 2012
SELECT
FORMAT(GETDATE(), 'yyyy-MM-dd') AS [ISO_Date],
FORMAT(GETDATE(), 'yyyy-MM-dd hh:mm:ss') AS [ISO_Date_time],
FORMAT(GETDATE(), 'MMMM dd, yyyy') AS [EN_DateFormat],
FORMAT(GETDATE(), 'MMMM dd, yyyy', 'fr-FR') AS [French_Format],
FORMAT(22.7, 'C', 'en-US') AS [US_Currency],
FORMAT(22.7, 'C', 'en-GB') AS [UK_Currency],
FORMAT(99 * 2.226, '000.000') AS [Decimal],
FORMAT(12345678, '0,0') AS [Thousand Separator]
;
SELECT
FORMAT(GETDATE(), 'yyyy-MM-dd') AS [ISO_Date],
FORMAT(GETDATE(), 'yyyy-MM-dd hh:mm:ss') AS [ISO_Date_time],
FORMAT(GETDATE(), 'MMMM dd, yyyy') AS [EN_DateFormat],
FORMAT(GETDATE(), 'MMMM dd, yyyy', 'fr-FR') AS [French_Format],
FORMAT(22.7, 'C', 'en-US') AS [US_Currency],
FORMAT(22.7, 'C', 'en-GB') AS [UK_Currency],
FORMAT(99 * 2.226, '000.000') AS [Decimal],
FORMAT(12345678, '0,0') AS [Thousand Separator]
;
Pagination OFFSET and FETCH Commands in SQL 2012
A new built-in functions for pagination is introduced in SQL 2012. Using this we can skip 'n' number of top rows and retrieve other rows.
CREATE TABLE dbo.Employee
(
ContactId int IDENTITY(1,1) NOT NULL,
FirstName varchar(60),
LastName varchar(60),
Phone varchar(60),
Email nvachar(100)
LocationID int
);
INSERT INTO dbo.Employee
Select firstname, lastname, phone, EmailId, LocationID
FROM dbo.EmployeeAddress
The below query skip top 100 rows and retrieves the next 10 records:
SELECT ContactId, FirstName, LastName, Phone
FROM dbo.Employee
ORDER BY ContactId
OFFSET 100 ROWS
FETCH NEXT 10 ROWS ONLY;
CREATE TABLE dbo.Employee
(
ContactId int IDENTITY(1,1) NOT NULL,
FirstName varchar(60),
LastName varchar(60),
Phone varchar(60),
Email nvachar(100)
LocationID int
);
INSERT INTO dbo.Employee
Select firstname, lastname, phone, EmailId, LocationID
FROM dbo.EmployeeAddress
The below query skip top 100 rows and retrieves the next 10 records:
SELECT ContactId, FirstName, LastName, Phone
FROM dbo.Employee
ORDER BY ContactId
OFFSET 100 ROWS
FETCH NEXT 10 ROWS ONLY;
Monday, July 2, 2012
Configure Change Data Capture Parameters in SQL Server
After enabling CDC in SQL Server (see: http://mahadevanrv.blogspot.in/2011/05/change-data-capture-in-sql-server.html). We can modify the retention period and the number of transactions that to be handled in Change Data Capture table.
Before configureing one should understand the basic terms in CDC Configuration:
Execute the below query to get the CDC configured values:
Execute the below query to change capture instances:
EXEC sys.sp_cdc_change_job @job_type = 'capture'
,@maxtrans = 501
,@maxscans = 10
,@continuous = 1
,@pollinginterval = 5
Execute the below query to change retention period:
EXEC sys.sp_cdc_change_job @job_type = 'cleanup'
,@retention = 4320 -- Number of minutes to retain (72 hours)
,@threshold = 5000
Using this method we can use CDC hold the required period of historical data, i.e., for last 1 month, last 1 year or last 10 days, etc.
Get Hierarchial Level of Employee Using SQL
Create a new table as below:
CREATE TABLE [dbo].[Employee](
[EmployeeID] [int] NOT NULL,
[EmpName] [varchar](100) NULL,
[Department] [varchar](100) NULL,
[Designation] [varchar](100) NULL,
[Salary] [money] NULL,
[ManagerID] [int] NULL
) ON [PRIMARY]
Insert valuse to the table:
SELECT L3.EmpName AS LEVEL2, L2.EmpName LEVEL1, L1.EmpName LEVEL0
,CAST(ISNULL(L3.EmployeeID,'') AS VARCHAR(5))+'/' + CAST(ISNULL(L2.EmployeeID,'') AS VARCHAR(5))+'/' + CAST(ISNULL(L1.EmployeeID,'') AS VARCHAR(5)) AS LEVELPATH
,CAST(ISNULL(L3.EmpName,'') AS VARCHAR(5))+'/' + CAST(ISNULL(L2.EmpName,'') AS VARCHAR(5))+'/' + CAST(ISNULL(L1.EmpName,'') AS VARCHAR(5)) AS EMPLOYETREE
FROM Employee L1
LEFT JOIN Employee L2 ON L1.ManagerID = L2.EmployeeID
LEFT JOIN Employee L3 ON L2.ManagerID = L3.EmployeeID
LEFT JOIN Employee L4 ON L3.ManagerID = L4.EmployeeID
LEFT JOIN Employee L5 ON L4.ManagerID = L5.EmployeeID
CREATE TABLE [dbo].[Employee](
[EmployeeID] [int] NOT NULL,
[EmpName] [varchar](100) NULL,
[Department] [varchar](100) NULL,
[Designation] [varchar](100) NULL,
[Salary] [money] NULL,
[ManagerID] [int] NULL
) ON [PRIMARY]
Insert valuse to the table:
SELECT L3.EmpName AS LEVEL2, L2.EmpName LEVEL1, L1.EmpName LEVEL0
,CAST(ISNULL(L3.EmployeeID,'') AS VARCHAR(5))+'/' + CAST(ISNULL(L2.EmployeeID,'') AS VARCHAR(5))+'/' + CAST(ISNULL(L1.EmployeeID,'') AS VARCHAR(5)) AS LEVELPATH
,CAST(ISNULL(L3.EmpName,'') AS VARCHAR(5))+'/' + CAST(ISNULL(L2.EmpName,'') AS VARCHAR(5))+'/' + CAST(ISNULL(L1.EmpName,'') AS VARCHAR(5)) AS EMPLOYETREE
FROM Employee L1
LEFT JOIN Employee L2 ON L1.ManagerID = L2.EmployeeID
LEFT JOIN Employee L3 ON L2.ManagerID = L3.EmployeeID
LEFT JOIN Employee L4 ON L3.ManagerID = L4.EmployeeID
LEFT JOIN Employee L5 ON L4.ManagerID = L5.EmployeeID
Tuesday, May 29, 2012
Using CHARINDEX in SQL Query
CHARINDEX returns a starting position of a string which helps developer to split a data based on th erequirement. Below is an example of CHARINDEX usage:
DECLARE @AMT Varchar(50) = '$50K - $70K'
SELECT REPLACE(SUBSTRING('$50K - $70K',charindex('$', '$50K - $70K')+1, charindex('K', @AMT)),'K','') AS MinRange
,REPLACE(SUBSTRING('$50K - $70K',charindex('-', '$50K - $70K')+3, charindex('K', @AMT)),'K','') As MaxRange
The output is:
----------------------------------
MinRange MaxRange
-----------------------------------
50 70
-----------------------------------
Monday, May 14, 2012
SQL Server 2008 Date & Time Data Type
DATE: This data type is useful to store the dates without the time part, we can store dates starting from 00001-01-01 through 9999-12-31 i.e. January 1, 1 A.D. through December 31, 9999 A.D. It supports the Gregorian Calendar and uses 3 bytes to store the date.
DATETIME: This is a well known data type by most of us the date range supported is 01-01-1753 through 9999-12-31 or January 1, 1753, through December 31, 9999 and time range supported is 00:00:00 through 23:59:59.997. It takes 8 bytes to store the date/time data.
SMALLDATETIME: This is a data type that has the accuracy to 1 minute and useful for storing dates and time when the precision doesn't matter too much for example. The order booking date and time of a user. The range supported by this type of data type is 1990-01-01 through 2079-06-06 or January 1, 1900, through June 6, 2079 and time range between 00:00:00 through 23:59:59. The data type takes 4 fixed bytes to store the data.
DATETIME2: This is a new data type introduced in SQL Server 2008 and this date/time data type is introduced to store the high precision date and time data. The data type can be defined for variable lengths depending on the requirement. This data type also follows the Gregorian Calendar. The Time Zone can't be specified in this data type. This is still useful because it gives you a complete flexibility to store the date time data as per your requirement.
DATETIMEOFFSET: This is the new data type that is included in SQL Server 2008 and this data type is the most advanced in the league. We can store high precision date/ time with the Date Time Offset. We can't store the Time Zone like Eastern Time, Central Time etc. in the data type but can store the offset -5:00 for EST and -6:00 CST and so on.
The date range is between 0001-01-01 and 9999-12-31 or January 1,1 A.D. through December 31, 9999 A.D. and the Time Range is between 00:00:00 and 23:59:59.9999999. The offset range is between -14:00 through +14:00. The precision of the data type can be set manually and it follows the Gregorian Calendar.
TIME: Allows only Time format data.
No comments:
Post a Comment