Anil Singh | Code-sample.com

In this Article, I will explain some important “ SQL Server Queries”. I think “ most of each SQL Developer ” needs these important queries in the daily life! “ How to use below listed SQL Quires ”? And “ What are the Advantages”? As per this, I am tried to write these important “ SQL Queries ” and sharing with you! If you have any other smart query related to this (You can share to me and I will publish this post with your query!) Stayed Informed – SQL Server Procedures, Functions, View, Triggers, Index and Cursor ! List of Queries with Result As, --GET LIST OF DATABASES EXEC SP_HELPDB -- RESULT name db_size owner dbid created status compatibility_level CLR_Demo_BD 7 . 00 MB sa 7 Dec 30 2016 Status = ONLINE, Updateability = READ_WRITE, UserAccess = MULTI_USER, Recovery = FULL , Version = 706 , Collation = Latin1_General_CI_AI, SQLSortOrder = 0 , IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled 110 Demo 7 . 00 MB sa 8 J...

What is @@ERROR in SQL? @@ERROR returns only current error information (error number and error) after T-SQL statements executed. @@ERROR returns 0, if the previous SQL statement has no errors otherwise return 1. @@ERROR is used in basic error handling in SQL Server and @@ERROR is a global variable of SQL and this @@ERROR variable automatically handle by SQL. If error is occurred set error number otherwise reset 0. It is work only within the current scope and also contains the result for the last operation only. Syntax : - @@ERROR   Return Type : - INT When we should use @@ERROR? 1.       While executing any stored procedures 2.       In the SQL statements like Select, Insert, Delete and Update etc. 3.       In the Open, Fetch Cursor. When we should use Try Catch Block? The Try Catch Block is generally used where want to catch errors for multiple SQL statements. ...

What is SQL view? The View is a virtual table, which not create physically, it create logically only. We insert, update & delete the records from a view. In view there are no any physical relations in the database and the view is a virtual table based on the result set of an SQL statement. Why use View instead of a Table? A table contains data but the View dose not stored set of data values in a database and the view is only a SELECT statement. A view can combine columns/rows from multiple tables. Views are work like a security layers and we can handle security issues. Views can be used as security mechanisms and its display only those data that you granting the permissions in this view. Advantages of View:- We can hide so of table columns. Views can model complex joins easily. Views are work like a security layers and we can handle security issues. Drawbacks of View: - When table is dropped or modified, view becomes inactive; i...

The CASE expressions compare an expression to a set of simple expressions to determine the result and CASE can be used in any statement that allows a valid expression. It is IF-THEN-ELSE statement. Syntax: For a simple CASE expression CASE COLL WHEN 1 THEN 'One' WHEN 2 THEN 'Two' WHEN 3 THEN 'Three' ELSE 'None' END AS COLL_Name -- CREATE TABLE 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 --INSERT TABLE ROWS SET IDENTITY_INSERT [dbo].[Tbl_Demo] ON GO INSERT [dbo].[Tbl_Demo] ([ID], [Name], [Age], [IsA...

The AND keyword is logical operator. It is used to filter table records with AND conditions. Syntax:  SELECT col1, col2 FROM table_name WHERE Condition_1 AND Condition_2 -- 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...

Before create a symmetric key, you first setup your database’s master key and certificate and this master key and certificate help us to protect of your  symmetric  key store using the  ALGORITHM   AES_256. In the below examples, I am using a default master key password is System!123 and the certificate name is Certificate_demo . The following steps as given below, --====================================== --MASTER KEY ENCRYPTION WITH PASSWORD --======================================= CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'System!123' GO --=============================== --OPEN MASTER KEY --============================== OPEN MASTER KEY DECRYPTION BY PASSWORD = 'System!123' GO --============================= --CREATE CERTIFICATE --================================== CREATE CERTIFICATE Certificate_demo WITH SUBJECT = 'Certificate_demo' GO --================================================...