Tech Learn

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 ()

Some times we need to query on the all the tables in one data base single statement. We use SP_MSForeachtable this is known as undocumented stored procedures . These all are system stored procedures. These stored procedures is place in Master database. NOTE: Please do not run all these queries in Production environment Example: create database UnDocumentedStoredProcedure use UnDocumentedStoredProcedure create table Emp ( ID int identity ( 1 , 1 ), Name varchar ( 50 ), Salary int ) insert into Emp ( Name , Salary ) values ( 'rakesh' , 8000 ),( 'raju' , 9000 ) create table Dept ( ID int identity ( 1 , 1 ), DeptName varchar ( 100 ) ) insert into Dept ( DeptName ) values ( 'CSE' ),( 'IT' ) We are created new database and also created some table with some dummy data. Select all tables data: exec sp_MSForeachtable 'select * from ?...

creating table with sample data. create table CommaSeparatedList ( ID int identity ( 1 , 1 ) primary key , Name varchar ( 100 ) ) insert into CommaSeparatedList ( Name ) values ( 'rakesh' ),( 'raju' ),( 'ravi' ) Method-1: Using ISNULL : declare @commalist varchar ( max )=null select @commalist = isnull (( @commalist + ',' ), '' )+ cast ( ID as varchar ( max )) from CommaSeparatedList select @commalist output: 1,2,3 Method-2: Using COALESCE : declare @commalist varchar ( max )=null select @commalist = coalesce ( @commalist + ',' , '' )+ cast ( ID as varchar ( max )) from CommaSeparatedList select @commalist output: 1,2,3 Method-3: Using FOR XML PATH : select stuff (( select distinct ',' + cast ( ID as varchar ( max )) from CommaSeparatedList group by ',' + cast ( ID as varchar ( max )) f...

Views are nothing but saved select query. Views nothing but virtual table on top of physical table. Views can contain rows and columns .views is not stored data. The main use of view is hiding some rows data or some columns. There are two types of views: System defined view. User defined view. System Defined view: System defined view is categorized into 3 types System defined views can discuses in future articles. User defined views There are two types of user defined views. Simple View: A simple view is nothing but just single saved select statement. create table Emp ( ID int identity ( 1 , 1 ), Name varchar ( 20 ), Department varchar ( 20 ) ) insert into Emp ( Name , Department ) values ( 'rakesh' , 'software' ),( 'raju' , 'bpo' ),( 'ali' , 'software' ) I have created “Emp” table with above following ...