LEFT() function in SQL Server
Left() Function
This function returns the specified number of characters from the left part of the given character string.
Syntax
Select LEFT (‘Vivek’,2)
Result
Vi
This function returns the specified number of characters from the left part of the given character string.
Syntax
Select LEFT (‘Vivek’,2)
Vi
This function is used to select the values within a specified range. These values can be of Int type or Date data type or Text data type.
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2
In this article, we are going to use the following table for the examples:-
CREATE TABLE [dbo]. [Employee](
[Empid] [Int] IDENTITY (1, 1) NOT NULL Primary key,
[EmpNumber] [nvarchar](50) NOT NULL,
[EmpFirstName] [nvarchar](150) NOT NULL,
[EmpLAStName] [nvarchar](150) NULL,
[EmpEmail] [nvarchar](150) NULL,
[Salary] [int] NULL
)
We insert following data into the above tables:-
Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A001','Vivek','Johari','samir@abcmail.com',200000)
Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A002','Amit','Kumar','amit@abcmail.com',100000)
Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A003','Neha','Sharma','neha@abcmail.com',300000)
Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A004','Chandra','Singh','vivek@abcmail.com',320000)
Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A005','Avinash', 'Dubey','avinash@abcmail.com',250000)
Example
For example, below query will give the Empnumber of all the employee whose salary lies between 250000 and 320000 (Including both the values (250000 & 320000 ))
SELECT EmpNumber
FROM employee
WHERE salary BETWEEN 250000 AND 320000
A003
A004
A005
Between command includes both the values (value1 and value 2) for returning the result. For example, suppose we want to get the records of the employees whose empid is lies between 1 and 4 then the query will be:-
select EmpNumber from employee where empid between 1 and 4
A001
A002
A003
A004
If we do not want to includes either of these specified minimum and maximum values or both these values , then we should use > or < operator instead of between command
For example below query will gives the EmpNumbers of employees whose empid are greater than 1 but less the 4
select EmpNumber from employee where empid>1and empid
A002
A003
If we want to select the values outside the specified range, we can use the NOT between function.
SELECT column_name(s)
FROM table_name
WHERE column_name Not BETWEEN value1 AND value2
For example, below query will give the Empnumber of all the employee whose salary lies outside the range specified by the minimum value of 250000 and maximum 320000.
SELECT EmpNumber
FROM employee
WHERE salary NOT BETWEEN 250000 AND 320000
A001
A002
Performance tuning in SQL is important exercise and index creation is an important part of it. Sometimes base on the frequent SQL queries, we need to create some indexes or missing indexes which are enhance the query performance. Below script will help in finding the missing indexes.
SELECT db_name(d.database_id) dbname , object_name(d.object_id) tablename , d.equality_columns , d.inequality_columns , d.included_columns ,'CREATE INDEX [missing_index_' + CONVERT (varchar, g.index_group_handle) + '_' + CONVERT (varchar, d.index_handle) + '_' + LEFT (PARSENAME(d.statement, 1), 32) + ']' + ' ON ' + d.statement + ' (' + ISNULL (d.equality_columns,'') + CASE WHEN d.equality_columns IS NOT NULL AND d.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (d.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + d.included_columns + ')', '') AS create_index_statement FROM sys.dm_db_missing_index_groups g join sys.dm_db_missing_index_group_stats gs ON gs.group_handle = g.index_group_handle join sys.dm_db_missing_index_details d ON g.index_handle = d.index_handle WHERE d.database_id = 'Database_id' and d.object_id = d.object_id ORDER BY 2 DESC
We need to pass the id (Database_id) of the database in the above query. This id of the database can be get with the help of the below query. This query returns the id of all the databases deployed on the server in which this query is executed.
select * from sys.databases
Please run the missing index SQL query on the development server or the test server first before running on the Production server. Also too many indexes on a table can adversely impact the query performance. So before creating the indexes return by the above query, first create the suggested missing indexes on the development database server or the UAT database server and see its effect on the query performance. If you got satisfied with performance increase then only execute the index creation script for missing indexes on the Production server.
VARCHAR(n) Character string of variable length with maximum length n
VARCHAR(MAX) Character string of variable length with maximum length can exceed 8000 byes
NCHAR(n) Fixed-length Unicode string data where n defines the string length and must be a value from 1 through 4,000
NVARCHAR(n) Variable length Unicode string data where n defines the string length and must be a value from 1 through 4,000
NVARCHAR(MAX) Variable length Unicode string data where MAX indicates that the maximum storage size is 2^31-1 bytes (2 GB)
BINARY(n) Binary string of fixed length n
BOOLEAN It stores TRUE or FALSE values
VARBINARY(n) Binary string of variable length with maximum length n
NTEXT Variable-length Unicode data with a maximum string length of 2^30 – 1 (1,073,741,823) bytes
TEXT Variable-length non-Unicode data in the code page of the server and with a maximum string length of 2^31-1 (2,147,483,647)
IMAGE Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes.
INTEGER Or Int A 32-bit signed integer value having range of INTEGER is -2147483648 to 2147483647
SMALLINT A 16-bit signed integer value having range of SMALLINT is -32768 to 32767.
BIGINT A 64-bit signed integer value having range of BIGINT is -9223372036854775808 to 9223372036854775807
DECIMAL(p,s) Exact numerical, precision p, scale s. For Example, Decimal(5,3) means 22.212
NUMERIC(p,s) Exact numerical, precision p, scale s. (Same as DECIMAL)
REAL Approximate numerical, mantissa precision 7
FLOAT Approximate numerical, mantissa precision 16
DOUBLE Approximate numerical, mantissa precision 16
DATE It stores year/month/day values
TIME It stores hour/minute/second values
TIMESTAMP It stores year/month/day/hour/minute/second values
XML It stores XML data
Convert functions is usually used with date/time data type to convert a date in different formats. Below examples will show the different formats in which we can display the date/time data type using Convert function. In the below examples we use getdate() to get the current date and time of the database server.
select convert(varchar(40), getdate(), 0)
Result:-
Jan 25 2015 9:36PM
select convert(varchar(40), getdate(), 101)
Result:-
01/25/2015
select convert(varchar(40), getdate(), 102)
Result:-
2015.01.25
select convert(varchar(40), getdate(), 103)
Result:-
25/01/2015
select convert(varchar(40), getdate(), 104)
Result:-
25.01.2015
select convert(varchar(40), getdate(), 105)
Result:-
25-01-2015
select convert(varchar(40), getdate(), 106)
Result:-
25 Jan 2015
select convert(varchar(40), getdate(), 107)
Result:-
Jan 25, 2015
select convert(varchar(40), getdate(), 108)
Result:-
21:39:21
select convert(varchar(40), getdate(), 109)
Result:-
Jan 25 2015 10:19:15:583PM
select convert(varchar(40), getdate(), 110)
Result:-
01-25-2015
select convert(varchar(40), getdate(), 111)
Result:-
2015/01/25
select convert(varchar(40), getdate(), 112)
Result:-
20150125
select convert(varchar(40), getdate(), 113)
Result:-
25 Jan 2015 23:22:49:453
select convert(varchar(40), getdate(), 114)
Result:-
23:23:09:970
select convert(varchar(40), getdate(), 120)
Result:-
2015-01-25 23:23:37
select convert(varchar(40), getdate(), 121)
Result:-
2015-01-25 23:24:07.577
select convert(varchar(40), getdate(), 126)
Result:-
2015-01-25T23:24:29.860
select convert(varchar(40), getdate(), 127)
Result:-
2015-01-25T23:24:55.037
select convert(varchar(40), getdate(), 131)
Result:-
5/04/1436 11:25:23:483PM
From the above examples we can see that how we can use the Convert () function to display date time in different formats.
One of the important task of any DBA is to find out all the jobs which are failed yesterday. Below SQL Script can be used to find out all the jobs which are failed yesterday.
SELECT DISTINCT CAST(CONVERT(datetime,CAST(run_date AS char(8)),101) AS char(11)) AS 'Failure Date', SUBSTRING(T2.name,1,40) AS 'Job Name', T1.step_id AS 'Step_id', T1.step_name AS 'Step Name', LEFT(T1.[message],500) AS 'Error Message' FROM msdb..sysjobhistory T1 JOIN msdb..sysjobs T2 ON T1.job_id = T2.job_id WHERE T1.run_status NOT IN (1,4) AND T1.step_id != 0 AND run_date >= CONVERT(char(8), (select dateadd (day,(-1), getdate())), 112)
SELECT
[name] as Tablename
,create_date
,modify_date
FROM
sys.tables where cast(create_date as date)>=’Given date’
;WITH DBSize (SqlServerInstanceName, DatabaseName, DatabaseSize, DBLogSize, TotalDBSize)
AS
(
SELECT @@SERVERNAME SqlServerInstanceName,
db.name AS DatabaseName,
SUM(CASE WHEN af.groupid = 0 THEN 0 ELSE af.size / 128.0E END) AS DatabaseSize,
SUM(CASE WHEN af.groupid = 0 THEN af.size / 128.0E ELSE 0 END) AS DBLogSize,
SUM(af.size / 128.0E) AS TotalDBSize
FROM master..sysdatabases AS db
INNER JOIN master..sysaltfiles AS af ON af.[dbid] = db.[dbid]
WHERE db.name NOT IN (‘distribution’, ‘Resource’, ‘master’, ‘tempdb’, ‘model’, ‘msdb’) — System databases
AND db.name NOT IN (‘Northwind’, ‘pubs’, ‘AdventureWorks’, ‘AdventureWorksDW’)
GROUP BY db.name
)
SELECT * FROM DBSize order by TotalDBSize desc
DECLARE @filename NVARCHAR(1000);
DECLARE @bc INT;
DECLARE @ec INT;
DECLARE @bfn VARCHAR(1000);
DECLARE @efn VARCHAR(10);
— Get the name of the current default trace
SELECT @filename = CAST(value AS NVARCHAR(1000))
FROM ::fn_trace_getinfo(DEFAULT)
WHERE traceid = 1 AND property = 2;
— rip apart file name into pieces
SET @filename = REVERSE(@filename);
SET @bc = CHARINDEX(‘.’,@filename);
SET @ec = CHARINDEX(‘_’,@filename)+1;
SET @efn = REVERSE(SUBSTRING(@filename,1,@bc));
SET @bfn = REVERSE(SUBSTRING(@filename,@ec,LEN(@filename)));
— set filename without rollover number
SET @filename = @bfn + @efn
— process all trace files
SELECT
ftg.StartTime
,te.name AS EventName
,DB_NAME(ftg.databaseid) AS DatabaseName
,ftg.Filename
,(ftg.IntegerData*8)/1024.0 AS GrowthMB
,(ftg.duration/1000)AS DurMS
FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg
INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id
WHERE (ftg.EventClass = 92 — Date File Auto-grow