Split Comma Separated String into Rows - Sql Server

May 02, 2019 BY xtreme

3 different approaches to split string into rows.Approach 1: Simple Split Approach declare @strString AS Varchar(4000) ='A,B,C,D,E' declare @Result TABLE(Value varchar(20)) DECLARE @str VARCHAR(20) DECLARE @ind Int IF(@strString is not null) BEGIN SET @ind = CharIndex(',...

Create a comma separated string- Sql Service

May 02, 2019 BY xtreme

In SQL Server, we have 2 best options to create a comma separated string. COALESCE Till SQL Server 7.0 and SQL Server 2000, we had only one option Use of COALESCE to create comma separated list DECLARE @List VARCHAR(8000) SELECT @List = COALESCE(@List + ',', '') + CAST(Color AS VARCH...

Temporarily Data Storage - CTE Vs Temp Table Vs Table Variable - Sql Server

May 02, 2019 BY xtreme

In SQL Server, we have various options for storing data temporarily. Temp Table, Table variable and CTE are commonly used way for storing temporary data. CTE - Common Table Expressions CTE was introduced with SQL Server 2005. It is a temporary result set and typically it may be a result o...

stored procedure Vs Functions - Sql Server

May 02, 2019 BY xtreme

.  Both stored procedures and functions are database objects which contain a set of SQL statements to complete a task. Stored Procedures are pre-compiled objects which are compiled for the first time and its compiled format is saved, which executes (compiled code) whenever it is called. ...

Views - Sql Server

Apr 28, 2019 BY xtreme

View is a virtual table based on the result-set of an SQL statement and that is Stored in the database with some name.CREATE VIEW StudentProducts ASSELECT  * ...GO   Why We Need Views? - To protect the data. If we have a table containing sensitive data in certain columns, we might...

Indexes - Sql Server

Apr 27, 2019 BY xtreme

Triggers Indexes help us to find data faster. It can be created on a single column or a combination of columns.  - It allows the server to retrieve requested data, in as few I/O operations. - It is used to find the records quickly in the database without scanning the entire table CREATE...

TSQL Queries - SQL

Apr 27, 2019 BY xtreme

.  Fetch duplicate records from a table. SELECT EmpId, Project, Salary, COUNT(*) FROM EmployeeSalary GROUP BY EmpId, Project, Salary HAVING COUNT(*) > 1; Remove duplicates from a table DELETE FROM EmployeeSalary WHERE EmpId IN ( SELECT EmpId FROM ...