Category: SQL

SQL May 14, 2021

Model Database in SQL Server

Model database is another important system database in SQL Server. It is also called as “Template DB”; SQL Server uses this database as a template db to create a new database. We need this database for SQL Server to come online, because this database is being used to create TempDB while restarting the service/server.

At times, few developers will place objects (tables/procedures etc.) that needs to be created for every newly created database. This would avoid the work of creating those every time.

Recovery Model

Recovery model of “Model” database is depending on the SQL editions. The editions mostly suitable for test and development comes up with recovery model “SIMPLE” and higher editions with “FULL”. This is mainly because Production environment databases require a point in time recovery options on the verge of going corrupted.

It is also a good idea to have a full back up of model database in a daily basis considering there would not be any user transactional databases on Model database.

Model will act as a template only for new database creation, it does not have any impact on restore databases.

Its a short topic, but the importance of Model is high for SQL Server!

If you enjoyed this blog post, feel free to share it with your friends!

SQL May 12, 2021May 12, 2021

Master Database in SQL Server

Let me start this blog post with a statement – “Master” database is the brain of SQL Server. We will go in detail why I would call it so.

If you ever worked in SQL Server, one way or other way you touched or used this system database. Master database is the primary configuration database in SQL Server. This system database is storing lots of information like configuration, login details, end points, linked server details, other object details etc. That means, In short – without master system database, the SQL Server will never be able to start its services.

Backing up of Master database is mastermind

Since Master database contains lots of important information as described above, it is very important to make sure we regularly backing up Master database. This would prevent to lose the important information at the time of corruption of this database. (We should also validate the backups as well.) Since master database would be generally small in size, backup process could be an instantaneous one.

Master database comes with simple recovery model, if you have a daily backup scheduled, there should not be a reason for change in recovery model. It is important to understand, not all objects can be scripted out from master databases, backup of mater database is unavoidable.

The most important aspect is that we can’t do transaction log backups for the master database, so there would be a chance of losing data that were made since the last full backup.

Forbidden operations to Master database

We cannot drop Master database
We cannot take Master database to offline
We cannot rename Master database
We cannot change the owner of Master database
We cannot set Change Data Capture (CDC) on Master database
We cannot set Change Tracking on Master database

Error Exception: Msg 3183, Level 16, State 2, Line 1 RESTORE detected an error on page (0:0) in database “dbname” as read from the backup set.

Recently we encountered the below issue while restoring a database. And we also observed the database has gone into “restoring” mode.

Msg 3183, Level 16, State 2, Line 1
RESTORE detected an error on page (0:0) in database “dbname” as read from the backup set.

The message is clear that there is something wrong on the backup set. There could be many issues related, but most likely reason would be the backup file would have got file corrupted.

You would likely to take a fresh backup and try with restore and in most cases it works good. So, the take away is “we should always validate our backups”. This is exactly what Paul S Randal explains with a survey and more details as here.

Ready reckoner – Date Functions in SQL Server

This blog post tries to provide few of very common scenarios and its solutions that developers would come across in development phase.

Standard Date CONVERT formats

Declare @Date Datetime = GETDATE()
Select @Date
SELECT DATEADD(month, DATEDIFF(month, '', @date), '');

Select CONVERT(Varchar(25),@date,100) as 'Converted Value', 'Mon DD YYYY HH:MIAM (or PM)' as 'Converted Format' union all
Select CONVERT(Varchar(25),@date,1),'MM/DD/YY' union all
Select CONVERT(Varchar(25),@date,101),'MM/DD/YYYY' union all
Select CONVERT(Varchar(25),@date,2),'YY.MM.DD' union all
Select CONVERT(Varchar(25),@date,102),'YYYY.MM.DD' union all
Select CONVERT(Varchar(25),@date,3),'DD/MM/YY' union all
Select CONVERT(Varchar(25),@date,103),'DD/MM/YYYY' union all
Select CONVERT(Varchar(25),@date,4),'DD.MM.YY' union all
Select CONVERT(Varchar(25),@date,104),'DD.MM.YYYY' union all
Select CONVERT(Varchar(25),@date,5),'DD-MM-YY' union all
Select CONVERT(Varchar(25),@date,105),'DD-MM-YYYY' union all
Select CONVERT(Varchar(25),@date,6),'DD Mon YY' union all
Select CONVERT(Varchar(25),@date,106),'DD Mon YYYY' union all
Select CONVERT(Varchar(25),@date,7),'Mon DD, YY' union all
Select CONVERT(Varchar(25),@date,107),'Mon DD, YYYY' union all
Select CONVERT(Varchar(25),@date,108),'HH:MM:SS' union all
Select CONVERT(Varchar(25),@date,109),'Mon DD YYYY HH:MI:SS:MMMAM (or PM)' union all
Select CONVERT(Varchar(25),@date,10),'MM-DD-YY' union all
Select CONVERT(Varchar(25),@date,110),'MM-DD-YYYY' union all
Select CONVERT(Varchar(25),@date,11),'YY/MM/DD' union all
Select CONVERT(Varchar(25),@date,111),'YYYY/MM/DD' union all
Select CONVERT(Varchar(25),@date,12),'YYMMDD' union all
Select CONVERT(Varchar(25),@date,112),'YYYYMMDD' union all
Select CONVERT(Varchar(25),@date,113),'DD Mon YYYY HH:MM:SS:MMM(24h)' union all
Select CONVERT(Varchar(25),@date,114),'HH:MI:SS:MMM(24H)' union all
Select CONVERT(Varchar(25),@date,120),'YYYY-MM-DD HH:MI:SS(24h)' union all
Select CONVERT(Varchar(25),@date,121),'YYYY-MM-DD HH:MI:SS.MMM(24h)' union all
Select CONVERT(Varchar(25),@date,126),'YYYY-MM-DDTHH:MM:SS:MMM' union all
Select CONVERT(nVarchar(25),@date,130),'DD Mon YYYY HH:MI:SS:MMMAM' union all
Select CONVERT(Varchar(25),@date,131),'DD/MM/YYYY HH:MI:SS:MMMAM'

How to separate date from a string

Create table testing(id int identity(1,1), enddate varchar(40))

insert into testing values('End Date: 04/30/2021')
insert into testing values('06/30/2027')
insert into testing values('something something something 06/30/2027')
insert into testing values('13.a')
insert into testing values('Section 23')
insert into testing values('End Date: 06/30/2027')
insert into testing values('End Date: 12/31/2016 a as asdadasd')

;With cte as
(
select ID, enddate,
SUBSTRING(enddate, patindex('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9]%', enddate), 10) AS Actualdate
FROM testing
)
select id,enddate,
case when Isdate(right(Actualdate,10)) = 1 then Actualdate
else null end Actualdate from cte

Drop table testing

How to calculate age from a date

create table students(student_id int,DOB date)

Insert into students Values(1,'19790111'),(2,'19790219'),(3,'20200408')

Select student_id,DOB,Cast(getdate() as date),
cast((DATEDIFF(m, DOB, GETDATE())/12) as varchar) as years,
cast((DATEDIFF(m, DOB, GETDATE())%12) as varchar) as ' Months',
cast((DATEDIFF(d, DOB, GETDATE())%365) as varchar) as ' Days'
From students

Drop table students

If you enjoyed this blog post, feel free to share it with your friends!

SQL May 8, 2021

Few use cases of splitting strings in SQL Server

Its a very frequent question in forums on splitting strings with various forms of data. This blog post will try to get few of use cases and provide solutions to those scenarios in a simpler way.

Case 1: Split a full name to first name & last name

This case is a very common one scenario, a full name in a column needs to be split into two as First Name and Last Name.

-- DDL and sample data population, start
 DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY,  FullName varchar(200));
 INSERT INTO @tbl (FullName) VALUES
 ('Vigneshwaran Arunachalam'), 
 ('Murugan Mani'), 
 ('Dharani Kumar Rajput'), 
 ('Uday Talluri'), 
 ('Soundarya Lakshmi'), 
 ('Noname'), 
 ('Latheesh Nedumpurath Kalappurakkal');
 

 -- DDL and sample data population, end
 DECLARE @separator CHAR(1) = SPACE(1);
    
 ;WITH rs AS
 (
  SELECT * 
  , TRY_CAST('<root><r>' + 
  REPLACE(FullName, @separator, '</r><r>') + 
  '</r></root>' AS XML) AS xmldata
  FROM @tbl
 )
 SELECT ID, rs.FullName 
  , rs.xmldata.value('(/root/r[1]/text())[1]', 'VARCHAR(30)') AS firstName
  , rs.xmldata.query('data(/root/r[position() gt 1]/text())').value('.' , 'VARCHAR(100)') AS lastName
 FROM rs;

Result

Case 2: Multi split a string to columns

This is a multi split scenario, where you would like to divide your single string into multiple columns according to the key name in the string. if there are no key then it has to consider NULL value while preparing in a tabular format.

-- DDL and sample data population, start
 DECLARE @tbl table (ID int, Comments VARCHAR(500));
 INSERT INTO @tbl VALUES 
 (1, 'StartDate: 01/01/2000 || EndDate: 01/31/2001 || Position: Manager || Salary: 100K || Age:50 || IsActive: No'),
 (2, 'StartDate: 01/01/2002 || Position: Sr.Manager || Salary: 150K || Age:55 || IsActive: Yes'),
 (3, 'StartDate: 01/01/2001 || Position: Director || Age:55');
 -- DDL and sample data population, end
    
 ;WITH rs AS
 (
  SELECT * 
  , '[{"' + REPLACE(REPLACE(REPLACE(Comments
  , ':', '":"')
  , SPACE(1), '')
  , '||', '","') + '"}]' AS jsondata
  FROM @tbl
 )
 SELECT rs.ID, rs.comments, report.*
 FROM rs
  CROSS APPLY OPENJSON(jsondata)
 WITH 
 (
     [StartDate] VARCHAR(10) '$.StartDate'
     , [EndDate] VARCHAR(10) '$.EndDate'
     , [Position] VARCHAR(30) '$.Position'
     , [Salary] VARCHAR(10) '$.Salary'
     , [Age] INT '$.Age'
     , [IsActive] VARCHAR(3) '$.IsActive'
 ) AS report;