Tech Learn

Here is the simple script for backup all database except system database. declare @DbBackup varchar ( max )=null select @DbBackup = coalesce ( @DbBackup + char ( 10 )+ char ( 13 ), '' )+ 'backup database ' + Name + ' to disk=''D:\Backup\' + name + CONVERT ( varchar ( 10 ), getdate (), 112 )+ '.BAK''' from sys . databases where name not in( 'master' , 'tempdb' , 'model' , 'msdb' ) print @DbBackup

Step-1: --create Emp type as table create type Emp as table ( Id int , Name varchar ( 50 ) null) Step-2: --create stored procedure with table variable parameter create proc sp_GetEmp ( @In_emp as dbo . Emp readonly ) as begin set nocount on select * from @In_emp set nocount off end Step-3: --Insert some dummy data declare @temp_emp as Emp insert into @temp_emp values ( 1 , 'rakesh' ),( 2 , 'raki' ),( 3 , 'ramu' ),( 4 , 'raju' ) Step -4: --exec stored procedure exec sp_GetEmp @temp_emp

LEN: LEN function returns the number of characters in a variable .it also removes the trailing spaces and then then return the length. Example-1: DECLARE @Name VARCHAR ( 20 )= 'rakesh' SELECT LEN ( @Name ) as [len] Output: Example-2: DECLARE @Name VARCHAR ( 20 )= 'rakesh ' SELECT LEN ( @Name ) as [len] Output: When we observe above variable assigned 'rakesh ' string after that added 3 spaces . Len function removes trailing spaces not leading spaces. DATALENGTH : DATALENG function returns the number of bytes occupy in a variable .it also considered the spaces also. Example-1: DECLARE @Name VARCHAR ( 20 )= 'rakesh' SELECT DATALENGTH ( @Name ) as [DataLength] Output: Example-2: DECLARE @Name VARCHAR ( 20 )= ' rakesh ' SELECT DATALENGTH ( @Name ) as [DataLength] Output: In above example before ' r ' and after ...

--Creating the Student Table CREATE TABLE Student ( ID INT IDENTITY ( 1 , 1 ) PRIMARY KEY , FirstName VARCHAR ( 50 ), LastName VARCHAR ( 50 ), Marks INT ) --Inerting the some sample data INSERT INTO Student ( FirstName , LastName , Marks ) VALUES ( 'rakesh' , 'kalluri' , 500 ) INSERT INTO Student ( FirstName , LastName , Marks ) VALUES ( 'Ali' , 'MD' , 600 ) INSERT INTO Student ( FirstName , LastName , Marks ) VALUES ( 'Raju' , 'Ganga' , 700 ) --create procedure with out parameters CREATE PROCEDURE Get_StudentInformation AS BEGIN SELECT * FROM Student END --create Student table in Tempdb CREATE TABLE Tempdb . #Student ( ID INT , FirstName VARCHAR ( 50 ), LastName VARCHAR ( 50 ), Marks INT ) --Inserting data from stored procedure to Temp Table INSERT INTO Tempdb . #Student EXEC Get_StudentInformation -- select d...

Both Primary Key and Unique Key enforces the Uniqueness of columns in table. Primary Key Unique Key Primary key does not allow NULL values. Because of Primary Key=Unique Key+ NOT NULL Unique key allows NULL Values. But it allows only single NULL value When we creating the primary key on a table automatically cluster index is created on table. When we the Unique key on a table automatically Unique non-clustered index created on table. A table has a only one primary key. But Primary key can created the multiple columns this is known as composite primary key. A table has more than one unique key. Syntax for Primary key On single Column. CREATE TABLE dbo . Student ( Id INT NOT NULL PRIMARY KEY , FirstName VARCHAR ( 100 ), LastName VARCHAR ( 100 ), City VARCHAR ( 50 ) ) Syntax for Unique key On single Column. CREATE TA...

WAITFOR: WAITFOR used to pauses the execution of query from certain time. There are two type's of Wait for. 1.WAITFOR DELAY 2.WAITFOR TIME WAITFOR DELAY : WAITFOR DELAY Cause the execution to delay from specified duration. For example it pause's the execution for 5 second's Example WAITFOR DELAY '00:00:05' SELECT * FROM EMP WAITFOR TIME : WAITFOR TIME is pause's the execution wait for specified time. For example '13:15:00' whenever time reached the query will be executed. WAITFOR TIME '13:15:00' SELECT * FROM EMP SELECT GETDATE ()