Bora.Choi

 January 2, 2008
New datetime datatypes in SQL Server 2008
By Muthusamy Anantha Kumar aka The MAK

From the beginning, Microsoft SQL Server DBAs have longed for two different datatypes, where you could store time and date separately. The long wait is over. In all of the older versions, we had only one data type that could store Datetime datatype, and it stored both the value of the date and the value of the time.

Finally, in Microsoft SQL Server 2008, Microsoft is introducing a set of new datatypes for storing date, time and both date and time together. The new data types store more data as well, which means you can store dates anywhere from 01-01-01 to 9999-12-31 and also store time up to the fraction of 9999999.

This article illustrates the usage and functionality of different datatypes in Microsoft SQL Server 2008, namely date, time, datetime, datetime2 and datetimeoffset datatypes.

Note: This article is written based on the Microsoft SQL Server 2008 – Nov CTP

TIME

The datatype TIME is primarily used for storing the time of a day. This includes Hours, minutes, Seconds etc. It is based on a 24-hour clock. The datatype TIME can store seconds up to the fraction of 9999999.

Let us declare a variable with the datatype TIME and store some data.

declare @mytime TIME
set @mytime = GETDATE()
select MyTime = @mytime

The result is shown below. [Refer Fig 1.0]

MyTime
01:48:18.4870000

When you declare a variable with datatype TIME with no precision, SQL Server assumes a 7 digit precision as shown above.

The time range is from 00:00:00 through 23:59:59.9999999.

We could limit the precision of the datatype as shown below.

declare @mytime TIME(3)
set @mytime = GETDATE()
select MyTime = @mytime 

The result is shown below. [Refer Fig 1.2]

Result

MyTime
01:51:25.580

DATE

The datatype DATE is used for storing the date only. This includes Month, Day and year.

The value of date can be anywhere from 01-01-01 to 9999-12-31.

Let’s declare a variable with the datatype DATE and store some data.

declare @mydate DATE
set @mydate = GETDATE()
Select Mydate = @MyDate
set @mydate = '01/01/1997'
Select Mydate = @MyDate
set @mydate = '01/31/9999'
Select Mydate = @MyDate
set @mydate = '01/01/0001'
Select Mydate = @MyDate

The range for date is from 0001-01-01 through 9999-12-31

The result is shown below. [Refer Fig 1.2]

Result

MyDate
2007-12-18
 
MyDate
1997-01-01
 
MyDate
9999-01-31
 
MyDate
0001-01-01

DATETIME2

The data type DATETIME2 is the combination of the datatype DATE and TIME. DATETIME2 is used to store both a date value ranging from 01-01-01 to 9999-12-31 as well as a time value up to the fraction of 9999999.

Let’s declare a variable with the datatype DATETIME2 and store some data as shown below.

declare @mydate DATETIME2
set @mydate = GETDATE()
Select Mydate = @MyDate
set @mydate = convert(datetime2,'01/01/1997 16:14:00.1234567')
Select Mydate = @MyDate
set @mydate = convert(datetime2,'01/01/0001 16:14:00.1234567')
Select Mydate = @MyDate
The range for DATETIME2 is from 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999

The result is shown below. [Refer Fig 1.3]

Result

MyDate
2007-12-18 02:11:45.3130000
 
MyDate
1997-01-01 16:14:00.1234567
 
MyDate
0001-01-01 16:14:00.1234567

We could limit the precision as shown below.

Declare @mydate DATETIME2(4)
set @mydate = GETDATE()
Select Mydate = @MyDate
set @mydate = convert(datetime2(4),'01/01/1997 16:14:00.1234567')
Select Mydate = @MyDate

The result is shown below. [Refer Fig 1.2]

Result

MyDate
2007-12-18 02:14:54.8130
 
MyDate
1997-01-01 16:14:00.1235

SMALLDATETIME and DATETIME

Microsoft SQL Server 2008 continues to support existing data types such as datetime and smalldatetime.

The range for the datatype smalldatetime is from 1900-01-01 through 2079-06-06. Execute the following query as shown.

Declare @Mydate datetime
Set @MyDate = getdate()
Select MyDate = @MyDate

The result is shown below. [Refer Fig 1.4]

Result

MyDate
2007-12-18 02:31:35.347

The range for the datatype datetime is from 1753-01-01 through 9999-12-31.

Execute the following query as shown.

Declare @Mydate smalldatetime
Set @MyDate = getdate()
Select MyDate = @MyDate

The result is shown below. [Refer Fig 1.5]

Result

MyDate
2007-12-18 02:34:00.000

DATETIMEOFFSET

Microsoft SQL Server 2008 introduces a new datetime datatype called datetimeoffset. Datetimeoffset actually defines the date with the combination of the time of a day that is timezone aware. In addition, the clock is a 24-hour cycle.

The timezone offset range is from -14:00 through +14:00

Execute the query below to get the timeoffset.

Select 
CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS 'datetimeoffset'

The result is shown below. [Refer Fig 1.6]

Result

datetimeoffset
2007-05-08 12:35:29.1234567 +12:15

You can use the convert and cast functions to convert to and from different datetime datatypes.

Example:

Declare @mydate DATETIME2(4)
set @mydate = GETDATE()
select @mydate as OriginalDate
select CONVERT (smalldatetime,@mydate ) as Smalldate
select CONVERT (time,@mydate ) as Justtime

Result

OriginalDate
2007-12-19 02:15:09.3130
 
Smalldate
2007-12-19 02:15:00.000
 
Justtime
02:15:09.3130000

Note: This article is written based on the Microsoft SQL Server 2008 – Nov CTP.

Conclusion

This article has illustrated the usage and function of the various datatypes related to Date and time, namely date, time, datetime, datetime2 and datetimeoffset.

이 글은 스프링노트에서 작성되었습니다.

SQL 2008

 December 19, 2007
BACKUP compression in SQL Server 2008
By Muthusamy Anantha Kumar aka The MAK

After a long wait, SQL Server 2008 is going to be released with inbuilt compression for backups. Until the release of SQL Server 2005, compression was only available via third party backup software such as  SQL LiteSpeed, SQLZip, etc.

This article demonstrates how to take Full, Differential and Transactional log backups with compression, without compression and how to enable compression as a default.

Let us create a database “MyDB” as shown below.

USE [master]
GO
 
/****** Object:  Database [MyDB]    
        Script Date: 12/10/2007 01:08:14 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'MyDB')
DROP DATABASE [MyDB]
GO
 
USE [master]
GO
/****** Object:  Database [MyDB]    
        Script Date: 12/10/2007 01:05:09 ******/
CREATE DATABASE [MyDB] ON  PRIMARY 
( NAME = N'MyDB_Data', 
  FILENAME = 
  N'F:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\
        MSSQL\DATA\MyDB_Data.mdf' , 
  SIZE = 2176KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'MyDB_log', 
  FILENAME = 
  N'F:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\
        MSSQL\DATA\MyDB_log.LDF' , 
  SIZE = 504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [MyDB] SET RECOVERY FULL 
GO

Now let us create a table “MyTable” in the database “MyDB” as shown below

USE [MyDB]
GO
 
/****** Object:  Table [dbo].[MyTable]    
        Script Date: 12/10/2007 01:12:00 ******/
IF  EXISTS (SELECT * FROM sys.objects 
        WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') 
        AND type in (N'U'))
DROP TABLE [dbo].[MyTable]
GO
USE [MyDB]
GO
 
/****** Object:  Table [dbo].[MyTable]    
        Script Date: 12/10/2007 01:12:26 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[MyTable](
          [id] [int] NULL,
          [name] [char](100) NULL
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO

Let’s add 10000 rows of data to the table “MyTable” as shown below.

USE [MyDB]
GO
 
declare @myid int
set @myid=1
while @myid<=10000
begin
insert into MyTable select @myid, 'A'+convert(varchar(10),@myid)
set @myid =@myid +1
end

Select the data using the following T-SQL command. [Refer Fig 1.0]

use MyDB
go
Select * from MyTable
go

Create a folder, D:\Backup, as shown below. [Refer Fig 1.1]

Now let us take a full backup as shown below. [Refer Fig 1.2]

Backup Database MyDB to disk ='d:\Backup\MyDB_Full.bak' with init

Let’s add some more data [1000 rows] to the table “MyTable” in the database “MyDB” as shown below.

USE [MyDB]
GO
 
declare @myid int
set @myid=1
while @myid<=1000
begin
insert into MyTable select @myid, 'A'+convert(varchar(10),@myid)
set @myid =@myid +1
end

Now let us a take a transaction log backup, as shown below. [Refer Fig 1.3]

Backup log  MyDB to disk ='d:\Backup\MyDB_TLog_1.bak' with init

By default, SQL Server does not compress the backups. We can compress the backups in two different ways.

The database MyDB and the Full backup, Transactional log backup that we took were without compression. That is the default SQL Server behavior.

Now let’s take a full backup of the database with compression as shown below. [Refer Fig 1.4]

Backup Database MyDB to disk ='d:\Backup\MyDB_Full2.bak' with COMPRESSION

From figure 1.4, you can see that the size of MyDB_Full2.bak is much smaller when compared to MyDB_Full.Bak and MyDB_Tlog_1.bak.

Add some more data [1000 rows] to the table “MyTable” in the database “MyDB” as shown below.

USE [MyDB]
GO
 
declare @myid int
set @myid=1
while @myid<=1000
begin
insert into MyTable select @myid, 'A'+convert(varchar(10),@myid)
set @myid =@myid +1
end

Now let’s take a transaction log backup as shown below. [Refer Fig 1.5]

Backup log  MyDB to disk ='d:\Backup\MyDB_TLog_new.bak' with COMPRESSION

In figure 1.5, you can see that the size of MyDB_Tlog_new.bak is much smaller when compared to MyDB_Tlog_1.bak.

Let’s take a differential backup without compression and compare it with the differential backup with compression.

Execute the following commands as shown below. [Refer Fig 1.6]

backup database MyDB to disk ='d:\Backup\MyDB_Diff.bak' with differential
 
backup database MyDB to disk ='d:\Backup\MyDB_Diff2.bak' with differential, COMPRESSION

Fig 1.6 shows the compression ratio between MyDB_Diff.bak and MyDB_Diff2.bak.

Let’s change the default behavior of SQL Server from uncompressed backup to compressed. This can be done using the SP_CONGIFURE command.

Execute the command as shown below.

USE master
GO
EXEC sp_configure 'backup compression default', '1'
GO
RECONFIGURE WITH OVERRIDE
GO

Now let’s take a full backup of the MyDB database, without the optional keyword “WITH COMPRESSION”. [Refer Fig 1.7]

Backup Database MyDB to disk ='d:\Backup\MyDB_Full3.bak'

From figure 1.7, we can clearly see that the backup by default is compressed.

이 글은 스프링노트에서 작성되었습니다.