Index Seek Vs. Index Scan (Table Scan) - SQL SERVER

Convert Column values to Comma separated one row value

It is very common task to create comma separated values (CSV) from table column. While doing some development, we often need to do this. I will explain this with a very easy example.

Example:

 Assume we are having a table with the following structure and data.

Syntax: 

To Create the above mentioned table and insert data into it.

CREATE TABLE EMPLOYEE_DETAILS (COUNTRY VARCHAR(20), CITY VARCHAR(20), NAME VARCHAR(20))
INSERT INTO EMPLOYEE_DETAILS VALUES ('INDIA','DELHI','AMAN')
INSERT INTO EMPLOYEE_DETAILS VALUES ('INDIA','DELHI','AMAR')
INSERT INTO EMPLOYEE_DETAILS VALUES ('INDIA','DELHI','AKASH')
INSERT INTO EMPLOYEE_DETAILS VALUES ('INDIA','DELHI','ARAVIND')
INSERT INTO EMPLOYEE_DETAILS VALUES ('INDIA','CHENNAI','MAYUR')
INSERT INTO EMPLOYEE_DETAILS VALUES ('INDIA','CHENNAI','MUKESH')
INSERT INTO EMPLOYEE_DETAILS VALUES ('INDIA','CHENNAI','MAYANK')
INSERT INTO EMPLOYEE_DETAILS VALUES ('INDIA','AGRA','RAVI')
INSERT INTO EMPLOYEE_DETAILS VALUES ('INDIA','AGRA','RINKU')

Required Output:

In the required output we want to have all the names in a single row with comma separated having same country and city.

Syntax :

 To create Comma Separated values in single row.

SELECT DISTINCT

COUNTRY,

CITY,          

SUBSTRING(  

(  

SELECT ','+ED1.NAME  AS [text()]  

FROM EMPLOYEE_DETAILS ED1  

WHERE ED1.COUNTRY = ED2.COUNTRY  AND ED1.CITY = ED2.CITY 

FOR XML PATH ('')  

), 2, 1000

) [NAME]  

FROM EMPLOYEE_DETAILS ED2

Note: 

Text keyword having in red color as background color should be in lower case.

Batch Script to execute multiple SQL files

If we are working on a project and we need to execute multiple .sql files then executing these files one by one is very tedious work. There are multiple options available to execute the .sql files, we can open the .sql file in SQL Server Management Studio and execute it or to write a batch script to execute all the files each time if the deployment moves to another environment.

So, here is the batch script which is dynamic in nature so that we can easily use this script across any SQL Script files.

This batch file is capable of executing all the .sql files in a folder which contains sub directory or not. It executes the scripts in alphabetical order, so our main task is to order the files based on the dependency.

Example: 

If you have script to create the tables and then insert the data in to that table, then table should be created first then only you can insert the data.

To get this work done, you can create the folders like.

Batch Script:

@echo off

set /p servername=Enter DataBase Servername :
set /p dbname=Enter Database Name :
set /p username=Enter Username:
set /p password=Enter Password :
set /p spath=Enter Script Path :
set hr=%time:~0,2%
if "%hr:~0,1%" equ " " set hr=0%hr:~1,1%
set logfilepath= %spath%\output_%date:~-4,4%%date:~-10,2%%date:~-7,2%_%hr%%time:~3,2%%time:~6,2%.log
set cmd='dir %spath%\*.sql /b/s'
FOR /f %%G IN (%cmd%) DO (
echo ******PROCESSING %%G FILE******
echo ******PROCESSING %%G FILE****** >> %logfilepath%

sqlcmd -S %servername%\SQLSERVER -U %username% -P %password% -d %dbname% -i"%%G">> %logfilepath%

IF !ERRORLEVEL! NEQ 0 GOTO :OnError
)
GOTO :Success

:OnError
echo **********ERROR******************
echo One\more script(s) failed to execute, terminating path.
echo Check output.log file for more details
EXIT /b

:Success
echo ALL the scripts deployed successfully!!
EXIT /b

Copy the above script and paste it in a notepad and save it as Batch.bat. Now execute the batch and provide the parameters, it will execute all the files from the folder specified. This batch file will create a output log file with all the information about which script it executed and it's result.

Note: Your folder name should not contains the space.