SQL Server 2017 Features

 1. TRANSLATE: It basically takes a string as input and then translates the characters with some new characters, see below Syntax:  

TRANSLATE ( inputString, characters, translations)

For Example : SELECT TRANSLATE ('4*{1+1}/[81-41]','[]{}','()()')

Output : 4*(1+1)/(81-41)

This function is work as REPLACE function. but TRANSLATE function is easier to use and also useful when we have to replace more than one value in the string.

2. CONCATE_WS:  It simply concats all input arguments with the specified input delimiter.

Syntax : CONCAT_WS ( separator, argument1, argument1, [argumentN]… )

For Example :

SELECT CONCAT_WS(',','Count', '1', '2', '3', '4' ) AS counter;

Output : nikhil, hardik, nilesh, sandip

3. TRIM:  It simply works as the C# trim function, removes all spaces from start and end of the string.

Syntax: TRIM(inputString)

For Example :

SELECT TRIM('     Samlpe demo    ') AS result;

Output : Samlpe demo

It removes only space from start and ends, not between the word and string.

4. String_AGG:  It will used to create single comma-separated string.

Syntax: String_AGG(column name,',')

For Example :

create table names ( [name] varchar(50) )go 

insert into names values ('1'),('2'),('3'),('4')

Here I have used stuff

select stuff((select ',' + [name] as [text()] 

from names for xml path('')),1,1,'')

Here I have used string_agg

select string_agg([name],',') from names

Output : 1,2,3,4

How To See Logs Of Dropped Tables From The Database in Microsoft SQL SERVER

 

Here, I will explain you how you can see logs of users.

Step 1 : First, create a new database with name "test".
Step 2 : Create a new table.

Step 3 : Now, go and drop the table by running the following command.

Step 4 : Now, select your database under Object Explorer and go to Reports >> Standard Reports >> Schema Changes History.

Step 5 : You will then see the schema change history. The report will show you who has dropped this table.

Finally, you can locate the user activity with the help of log.

sql server user login history

 SELECT * 

FROM master.dbo.sysprocesses sp 

JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid

ORDER BY spid 

go

 SELECT 

      c.session_id, 

      c.net_transport, 

      s.host_name, 

      s.program_name, 

      s.nt_user_name,

      c.connect_time, 

      s.client_interface_name,

      c.client_net_address,

      c.local_net_address, 

      s.login_name, 

      s.nt_domain, 

      s.login_time 

  FROM sys.dm_exec_connections AS c

  JOIN sys.dm_exec_sessions AS s

    ON c.session_id = s.session_id;

go

SELECT *

FROM sys.dm_exec_sessions

WHERE database_id > 0 -- OR 4 for user DBs

--GROUP BY database_id, login_name, status, host_name, program_name, nt_domain, nt_user_name;

How to convert records in a table to XML format using T-SQL

DECLARE @stSqlString NVARCHAR(MAX) = '' DECLARE @stStrXml XML SET @stSqlString = 'set @stStrXml=(select * from TableName FOR XML AUTO, elements)' EXEC sp_executesql @stSqlString, N'@stStrXml XML OUTPUT',@stStrXml OUTPUT SELECT @stStrXml

How to get list of all Folder in network sharing path using SQL Server

IF OBJECT_ID('tempdb..#TempTableName') IS NOT NULL DROP TABLE #TempTableName ; CREATE TABLE #TempTableName ( id INT IDENTITY(1, 1) , subdirectory NVARCHAR(512) , depth INT ) ; INSERT #TempTableName ( subdirectory ) EXEC MASTER.dbo.xp_cmdshell 'dir DriveName:\ /b /o:n /ad' IF NOT EXISTS ( SELECT 1 FROM #TempTableName ) BEGIN EXEC XP_CMDSHELL 'net use DriveName: \\ServerName\DriveName /user:domain\USERNAME PASSWORD'---code for Mapping drive in local Pc END

Query For Calculate CPU Usage in SQL Server

SELECT TOP 10 ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) ,TextData = qt.text ,DiskReads = qs.total_physical_reads -- The worst reads, disk reads ,MemoryReads = qs.total_logical_reads --Logical Reads are memory reads ,Executions = qs.execution_count ,TotalCPUTime = qs.total_worker_time ,AverageCPUTime = qs.total_worker_time/qs.execution_count ,DiskWaitAndCPUTime = qs.total_elapsed_time ,MemoryWrites = qs.max_logical_writes ,DateCached = qs.creation_time ,DatabaseName = DB_Name(qt.dbid) ,LastExecutionTime = qs.last_execution_time FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY qs.total_worker_time DESC -- Find queries that have the highest average CPU usage SELECT TOP 10 ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) ,TextData = qt.text ,DiskReads = qs.total_physical_reads -- The worst reads, disk reads ,MemoryReads = qs.total_logical_reads --Logical Reads are memory reads ,Executions = qs.execution_count ,TotalCPUTime = qs.total_worker_time ,AverageCPUTime = qs.total_worker_time/qs.execution_count ,DiskWaitAndCPUTime = qs.total_elapsed_time ,MemoryWrites = qs.max_logical_writes ,DateCached = qs.creation_time ,DatabaseName = DB_Name(qt.dbid) ,LastExecutionTime = qs.last_execution_time FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY qs.total_worker_time/qs.execution_count DESC

Not Empty Table List In Sql Server

WITH TableName AS
(
   SELECT
      SUM(row_count) AS [row_count],
      OBJECT_NAME(OBJECT_ID) AS TableName
   FROM
      sys.dm_db_partition_stats
   WHERE
      index_id = 0 OR index_id = 1
   GROUP BY
      OBJECT_ID
)

SELECT *
FROM TableName
WHERE [row_count] > 0

Get Hour And minute, Second From Second in Sql Server

DECLARE @Second1 BIGINT=5401
SET @Second1=ABS(@Second1)
DECLARE @ETIME AS VARCHAR(20)
SELECT @ETIME = RIGHT(CAST(@Second1 / 3600 AS VARCHAR(10)),7) + '.' +
RIGHT('0' + CAST((@Second1 / 60) % 60 AS VARCHAR(10)),2) + ':' + RIGHT('0' + CAST(@Second1 % 60 AS VARCHAR(2)),2)
SELECT @ETIME

Convert Minute To Hour in SQL Server function

CREATE FUNCTION [dbo].[MinToHour]
(
@Second1 BIGINT=0
)
RETURNS TIME
AS
BEGIN
DECLARE @Res TIME =NULL

SET @Second1=ABS(@Second1)
DECLARE @ETIME AS VARCHAR(20)
SELECT @ETIME = RIGHT(CAST(@Second1 / 60 AS VARCHAR(10)),7) + '.' + RIGHT('0' + CAST((@Second1) % 60 AS VARCHAR(10)),2)
SELECT @RES= CAST(REPLACE(@ETIME,'.',':') AS TIME)
RETURN @RES
END

Convert Second to Hour in Sql Server function

CREATE FUNCTION [dbo].[SecTOHour]
(
@Second1 BIGINT=0
)
RETURNS TIME
AS
BEGIN
DECLARE @Res TIME =NULL

SET @Second1=ABS(@Second1)
DECLARE @ETIME AS VARCHAR(20)
SELECT @ETIME = RIGHT(CAST(@Second1 / 3600 AS VARCHAR(10)),7) + '.' +
RIGHT('0' + CAST((@Second1 / 60) % 60 AS VARCHAR(10)),2) + ':' + RIGHT('0' + CAST(@Second1 % 60 AS VARCHAR(2)),2)
SELECT @RES= CAST(REPLACE(@ETIME,'.',':') AS TIME)
RETURN @RES
END

Split value using Charindex in SQL Server

Convert (varchar,Substring('11/12',0,charindex('/','11/12')))

OutPut => 11

Turning a Comma Separated string into individual rows in Sql Server

select t.ID,x.Code
    from Emp t
    cross apply (select Code from dbo.Split(t.Data,',') ) x

Find Tables With Foreign Key Constraint in SQL Server

SELECT f.name AS ForeignKey,
SCHEMA_NAME(f.SCHEMA_ID) SchemaName,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id

JSON Support in SQL Server 2016

--JSON Support  in SQL Server 2016
Example:=

Select top(1) TrnNo,Amount
from Sale
For JSON  PATH, ROOT('TrnNo')

Output:=

{"TrnNo":[{"TrnNo":1,"Amount":100}]}

What is an identity in SQL Server?

//SQL Server Auto-Increment Primary Key Field
//Example
CREATE TABLE [dbo].[DEMO]
(
SrNo [int] IDENTITY(1,1) NOT NULL,
)

auto-increment primary key field

MonthName and Month Number List between Two Date in SQL Server

//Month Name and Month Number List between Two Date in SQL Server

SELECT  MONTH(DATEADD(MONTH, x.number, '2013-05-31'))[MonthNo],
DATENAME(MONTH, DATEADD(MONTH, x.number,  '2013-05-31'))[Month],
DATENAME(YEAR, DATEADD(MONTH, x.number,  '2013-05-31'))[Year] ,
LEFT(DATENAME(MONTH, DATEADD(MONTH, x.number,  '2013-05-31')),3) + ' ' + DATENAME(YEAR, DATEADD(MONTH, x.number,  '2013-05-31')) [Name]
FROM    master.dbo.spt_values x
WHERE   x.type = 'P'      
AND     x.number <= DATEDIFF(MONTH,  '2013-05-31',  '2014-05-31')

Month Name List in SQL Server

//Month Name List in SQL Server

SELECT number,
DATENAME(MONTH, CAST(year(getdate()) as varchar(4)) +'-' + CAST(number as varchar(2)) + '-1') monthname

FROM master..spt_values
WHERE Type = 'P' and number between 1 and 12
ORDER BY Number

Find Month Name to Month number in SQL Server

//Current Month Name and Month number Find in SQL Server

SELECT MONTH(LEFT(DATENAME(MONTH,Getdate()),15) + ' 1 2016') Number ,(DATENAME(MONTH,Getdate())) MonthName

OutPut :-

Add Table Valued Parameters in SQL Server

//Table-Valued Parameters In SQL Server

//Table Data Pass to another Same Table

--Frist Create User Define Table Type

EX.
create type T_Demo as Table
(
Field Name DataType
)

//C# Code to Pass Sql Server
SqlCommand _LocalCommnad = new SqlCommand();

  SqlConnection LocalCon = new SqlConnection("Data Source=.\sql2012;Initial Catalog=demo;User ID=sa;Password=123;Persist Security Info = false;");
 
                        if (LocalCon.State == ConnectionState.Open)
                                {
                             
                                    _LocalCommnad.Connection = LocalCon;
                                    _LocalCommnad.CommandType = CommandType.StoredProcedure;
                                    _LocalCommnad.CommandText = "SP_DEMO";
                                    _LocalCommnad.Parameters.Clear();

                                    _LocalCommnad.Parameters.AddWithValue("Table", _dt);
                                    _LocalCommnad.CommandTimeout = 0;
                                    _LocalCommnad.ExecuteNonQuery();
                             
                                }
                            _LocalCommnad.Connection = null;
                        LocalCon.Close();


==============================================================
//Update Flg in Table

Create proc [dbo].[SP_DEMO] (  @Table T_Demo  READONLY  )
as begin
update  Demo set Trf=1,TrfDate=Getdate()
from Demo
  inner join  @Table  as TT on   TT.INVNO=BILLH.INVNO  and TT.TrfDate=BILLH.TrfDate
End

What is the instance name of 2016 sql server?

//MSSQLSERVER 
The default instance name is MSSQLSERVER