How to split the string with comma separated in sql server 2016

Use below function to split the comma separated string value in sql server.
to test use commented sql query

--select SplittedData from SplitStrings('test1.txt,test2.txt',',')
CREATE FUNCTION SplitStrings
(
@SplitData nvarchar(max),
@SplitCharacter nvarchar(5)
)
RETURNS @ReturnValue table
(
UniqueId int identity(1,1),
SplittedData nvarchar(100)
)
AS
BEGIN
Declare @Count int
Set @Count = 1

While (Charindex(@SplitCharacter,@SplitData)>0)
Begin
Insert Into @ReturnValue(SplittedData)
Select
SplittedData = ltrim(rtrim(Substring(@SplitData,1,Charindex(@SplitCharacter,@SplitData)-1)))

Set @SplitData = Substring(@SplitData,Charindex(@SplitCharacter,@SplitData)+1,len(@SplitData))
Set @Count = @Count + 1
End

Insert Into @ReturnValue (SplittedData)
Select SplittedData = ltrim(rtrim(@SplitData))

Return
END

SQL Queries for Beginners

--Creating Table in SqlServer 2008

CREATE TABLE EMPLOYEE
  (
  Empid int,
  EmpName varchar(50),
  Salary  Varchar(50)
  )

--Creating Stored procedure

CREATE PROCEDURE SP_INSERTEMP
(
  @EmpName varchar(50),
  @Sal Varchar(50)
)
AS
BEGIN
--statement that you want retrieve or modify here inserting new employee in Employee table
INSERT INTO EMPLOYEE (EMPNAME,SALARY) VALUES(@EmpName,@sal)
END


--Creating Functions in sql server returning table

CREATE  FUNCTION FN_RETURNTABLE
(@tblName varchar(50)) Returns
@tblRecords table(tblNames varchar(25)
as
begin
declare @names varchar(50)

Insert into tblRecords values(@names)

return
end

How to find the total amount of a Customer in By using Sql Query ?

How  to Find The Total Amount of one  customer who purchase number of products ?


Select C.CustomerId,C.CustomerName, sum(Amount) as Total From Customers C,Sales S
where C.CustomerId=S.CustomerId
group by C.CustomerId,C.CustomerName

What Is Triggers ?

TRIGGERS :

Triggers are one of the database objects, which performs their own operations when user performs any DML operations on specific table.
or

Triggers i a type of stored procedure that implicitly executed when user performs DML operation on the table. it will not accept any parameters.

In simple way triggers fire after DML operations (INSERT,DELETE,UPDATE) it will Executed its own operation which mentioned in trigger.

Like if we insert  new records it will insert into main table and in trigger with new record

Ex: CREATE TRIGGER T1 ON EMP FOR INSERT
      AS
      BEGIN
      INSERT INTO EMP1 SELECT *FROM INSERTED
      END
It will insert the new record into emp and newly inserted record into EMP1(Trigger) like this we can know
newly inserted records in table.
Same with INSERT,DELETE,UPDATE..

TYPES OF TRIGGERS 
AFTER TRIGGER
INSTEAD OF TRIGGER

After Triggers :These are the triggers, which performs their own operation after performing insert, delete, and update operations on a specific table.
syntax :
CREATE TRIGGER TRIGGERNAME ON TABLE NAME
FOR/AFTER{INSERT/UPDATE/DELETE}
AS
BEGIN 
SQL STATEMENT
END

How many types of Indexes ?

What is Index we know How many types of  indexes in sql server ?

--CLUSTERED INDEX
--NON CLUSTERED INDEX 
--UNIQUE INDEX

CLUSTERED INDEX :

Only one clustered index is allowed per table the order of values in  a table order of values in index is also same when cluster index is created on table data is arranged in ascending order cluster index will occupy 5% percent of the table

syntax:
CREATE CLUSTERED INDEX INDEXNAME ON TABLENAME(COLUMNNAME)

NONCLUSTERED INDEX :

It is the default index created by the server the physical order of the data in the table is different from the order of the values in index, max no. of non clustered indexed allowed for table 249(2005)/
999(2008)
CREATE NONCLUSTERED INDEX INDEXNAME ON TABLENAME(COLUMNNAMES)

CREATE NONCLUSTERED INDEX INDEXNAME ON TABLENAME(COL1,COL2)


UNIQUE INDEX :
An index with unique constraint. it will not allow duplicate values
CREATE UNIQUE INDEX INDEXNAME ON TABLENAME(COLUMN)
COMPOSITE INDEX  :  if we create unique index more than one column it will called composite index

Indexes in Sql Server

What is Index ?

--Indexes in sql server  are similar to indexes in textbooks like it is physical structure which pointing the data
--if we use indexes it will increase the speed sqlserver query execution
-- if we use indexes quickly locate the records in table more efficiently
--Indexes are used to improve the performance queries
--Indexes in simple language if we are using the column repeatedly in queries which is other than primary key than we make that column as Index so it will save the data into data pages in disk with index of That repeated column so if we use this column it will directly go to this index and retrieve the data more quickly and efficiently
we can also create indexes for primary keys also

which column should i create indexes
Primary Key Column
Foreign key column
frequently used column in where clause


where should not use this indexes ?
The column which are not used frequently used in where clause
columns containing the duplicate and null values
columns containing image, binary information and text information

How to insert the Records from one table to another

1.How to insert the records from one table to another?

ans:

 Here we have two options Directly inserting the Records from one table to another new table

1.SELECT COL1,COL2,COL3 INTO NEWTABLE FROM OLDTABLE
For above query one new table is created with new table name and all records on old table(table1)

2.INSERT INTO TABLE1(COL1,COL2,COL3) SELECT COL1,COL2,COL3 FROM TBALE2
It will insert the records of table2 into table1
we can use where condition also