sql server 2019 string_split function defult

select value from string_split('A,B',',')

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

Pagination with OFFSET and FETCH in SQL Query

 Pagination with OFFSET and FETCH in SQL Server.

In the real world, we load the data on the front from the database one time and load it into memory via data table or in some other form (main table and other supported tables) and

then loop through individual records in Parallel For Each for better performance. However, in some cases, if the data load is huge,

we may end up having memory issues loading all the records on the front so weload each individual record inside the loop in SQL which could potentially affect the performance.

Sample : 

SELECT First_Name + ' ' + Last_Name FROM REgistration

ORDER BY First_Name OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

In this example, it will skip the first 10 rows and return the next 5 rows.

Limitations while Using OFFSET-FETCH

ORDER BY is mandatory to use OFFSET and FETCH clause.

OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.

TOP cannot be combined with OFFSET and FETCH in the same query expression.

The OFFSET/FETCH row count expression can be any arithmetic, constant, or parameter expression that will return an integer value. The row count expression does not support scalar sub-queries.

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;