Anil Singh | Code-sample.com

“What is a Cursor in SQL Server”? “What is the exact use of Cursor”? “Why do people hate SQL Cursor”? A Cursor is a database object. Cursor is used, when you need to enumerate table records in row by row basic that means singleton fashion. Its work likes a RecordSet in the ASP.Net. We can say that a Cursor is a set of rows with a pointer that identify a current row. Why is it considered to use cursors in SQL Server ? Why do people hate SQL cursors so much? Actually, the main reason of avoid cursor is “ cursors take up memory and create locks ” and also create performance issues. What are the Alternatives of cursor? As per my thought, I am trying to use a “while loop” at the place of CURSOR to resolve this locking issues. The “while loops” are easy to use as a cursor but it’s sometime created more difficulty to understand. The main advantage of “while loop” is that no objects must be created in memory to facilitate the looping. How to Create C...

What is clustered Index? 1.       The clustered Index is created automatically on primary key column. 2.       One table can only create one and only clustered Index. 3.       Clustered index is sorting all the rows physically. 4.    Clustered index is works based on the Binary tree concept. How to Create Clustered Index? -- CREATE CLUSTERED INDEX CREATE CLUSTERED INDEX Indexname_EmlloyeeClust ON Employee (     [EmpName] ASC or DESC ,     [EmpDepartment] ASC or DESC ) -- OR -- CREATE CLUSTERED INDEX CREATE CLUSTERED INDEX Indexname_EmlloyeeClust ON Employee (    [EmpName] ASC ,    [EmpDepartment] ASC ) WITH ( PAD_INDEX   = OFF , STATISTICS_NORECOMPUTE   = OFF , SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , DROP_EXISTING = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS ...

In SQL, we can alias columns, views and tables. A table alias is also called a co-relation name. It is a temporarily names of columns or tables. The Alias temporarily assigns another name to a column, or table at the time of a SELECT query and the assigning alias doesn’t rename the column or table name actually! Basically, it make column, or table more readable to the programmers. Syntax: --USE OF TABLE ALIAS SELECT TBL . Id , TBL . Name FROM [dbo].[Tbl_Demo] AS TBL WHERE TBL . Id in ( 1 , 2 , 3 ) --USE OF COLUMN ALIAS SELECT TBL . Id AS UID, TBL . Name AS UNAME FROM [dbo].[Tbl_Demo] AS TBL WHERE TBL . Id in ( 1 , 2 , 3 )

The ALL keyword is used to select all fields from a table using the asterisk " * " in a SQL SELECT statement. Syntax: SELECT ALL * FROM Table_name -- CREATE TABLE AND INSERT ROWS CREATE TABLE [dbo].[Tbl_Demo]( [ID] [ int ] IDENTITY( 1 , 1 ) NOT NULL , [Name] [ varchar ]( 500 ) NULL , [Age] [ int ] NULL , [IsActive] [ bit ] NULL , [IsDeleted] [ bit ] NULL , CONSTRAINT [PK_Tbl_Demo] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO SET IDENTITY_INSERT [dbo].[Tbl_Demo] ON GO INSERT [dbo].[Tbl_Demo] ([ID], [Name], [Age], [IsActive], [IsDeleted]) VALUES ( 1 , N 'Anil Singh' , 30 , 1 , 0 ) GO INSERT [dbo].[Tbl_Demo] ([ID], [Name], [Age], [IsActive], [IsDeleted]) VALUES ( 2 , N 'Aradhya' , 3 , 1 , 0 ) GO INSERT [dbo].[Tbl_Demo] ([ID], [Name], [Age], [...

The formula compound Interest as, P => Principal Amount or Base Amount           r => Annual Rate of Interest           t => Number of Years.            A=> Accumulated Money after (n) years with including Interest.   n =>  Compound Interest Interval(Daily, Quarterly, Half yearly, Yearly). For example, Q.) An amount of $2,000 is deposited in a bank paying an annual interest rate of 14% , compounded yearly . What is the balance after 5 years? Ans.) a)       Principal amount: $2,000 b)      Interest Rate: 14% c)       Effective Annual Rate: 14% d)      Calculation Period: 5 years P = 2000, r = 14/100 = 0.14, n = 1, t = 5. Accordingly, The Balance after 5 years is approximately $3,850.83 . For live result, go link, http://www.thecalcu...

Hello everyone, I am going to share the query and functions for Encrypt and Decrypt GUID, Text, String using SQL Server. Table of Content:- 1.       Query for Encrypt and Decrypt. 2.       Create Function for Encrypt 3.       Create Function for Decrypt 4.       How to use Encrypt and Decrypt functions in SQL? 5.       Query Result. QUERY FOR  ENCRIPT & DECRIPT :- DECLARE @encrypt_Token VARBINARY ( 255 ) DECLARE @decrypt_Token NVARCHAR ( 255 ) SELECT @encrypt_Token = ENCRYPTBYPASSPHRASE ( 'TOKEN' , '997AEBEC-2362-40DA-A506-9F0E965BC4AE' ) SELECT @encrypt_Token AS GUID_TO_ENCRYPT SET @decrypt_Token = ( SELECT CONVERT ( VARCHAR ( 100 ), DECRYPTBYPASSPHRASE ( 'TOKEN' , @encrypt_Token ))) SELECT @decrypt_Token AS DECRYPT_TO_GUID FUNCTION - ENCRIPT TOKEN STRING :- CREATE FUNCTION FNC_ENCR...