% (Modulo) in SQL Server

Provides the remainder of one number divided by another.

Syntax:-

dividend % divisor


Example:-

 

SQL Server System Defined Databases

SQL Server contains five special databases its own, which are known as system databases.
These databases used by SQL Server for its own maintenance and management.

Below are the description of system data bases with their functionality :-

1. Master Database :- The master database holds instance-wide metadata information, server configuration, information about all databases in the instance, and initialization information.
2. Resource Database :- It is a read only database that holds the definition of all system objects. When we access any system database then it seems to define in sys schema of local database but in actual, they reside in Resource Database.
3. Model Database :- It is used as a template for new user database.
4. Tempdb Database :- In this database, SQL Server stores temporary data. 
5. MSDB Database :- In this database, SQL Server have SQL Server Agent configurations.

Facebook page

SET ANSI_NULLS ON/OFF

It specify the behavior of the Equals (=) and Not Equal to (<>) comparison operators when used with null values.

When SET ANSI_NULLS is ON then comparison operator(=,<>) doesn’t work with NULL values, in that case to work with NULL values we have to use “IS NULL” or “IS NOT NULL”.

When SET ANSI_NULLS is OFF then comparison operator(=,<>) work with NULL values.

 

 

SET QUOTED_IDENTIFIER ON/OFF

It specifies how SQL Server treats the data that is defined in Single Quotes and Double Quotes.

When it is set to ON

• Any character set that is defined in the double quotes “” is treated as a T-SQL Identifier (Table Name, Proc Name, Column Name….etc) 
• The T-SQL rules for naming identifiers will not be applicable to it 
• Any character set that is defined in the Single Quotes ‘’ is treated as a literal.

Below is the example:-

 In above example as tbl_Name and col_Name are treated as identifier and the t-sql rules for identifier names are ignored.




When it is set to OFF

• any character set that is defined either in Single Quotes or in Double Quotes is treated as a literal.

 

CONVERT() IN SQL SERVER

What is CONVERT ()

It is a system defined function which help us to convert an expression of one data type to another.

Syntax:-

CONVERT ( <target data type [& length]> , expression [ , style ] )

here:-

<target data type [,length]>:- Data type in which we want to convert the data type of our expression.

Expression:- A valid expression.

[style ]:- With this clause, we can specify how the convert function translate the expression.

The best feature of CONVERT() is to convert the date in desired format, below are some of examples :- 

SELECT GETUTCDATE() --- '2013-03-02 10:21:08.123'

SELECT CONVERT(VARCHAR(20), GETUTCDATE())  --  'Mar  2 2013 10:21AM'

SELECT CONVERT(VARCHAR(20), GETUTCDATE(), 101)  --  '03/02/2013' -- mm/dd/yyyy

SELECT CONVERT(VARCHAR(20), GETUTCDATE(), 103)  --  '02/03/2013' -- dd/mm/yyyy

SELECT CONVERT(VARCHAR(20), GETUTCDATE(), 110)  --  '03-02-2013' -- mm-dd-yyyy

Facebook page