How many times have we backed up a database and then we have tried to minimize the size of the backup set by compressing it with an archiving utility?
I am quite sure that such cases were too many :)
SQL Server 2008 introduces Backup Compression. This enhancement allows us when backing up a database to activate backup compression which is performed on the fly.
To do this you have to right-click on a database, select Tasks, then select Backup. In the Back Up Database dialog which appears after this, if you go to the Options tab, you will notice that there is a feature on the bottom of the dialog which says Compression. Then you can set the backup compression for the current database. To this end you are presented with the following options:
1. Use the server default setting
2. Compress backup
3. Do not compress backup
If you select the Compress backup or Use the server default setting and your instance of SQL Server is setup to compress the backups by default, when you initiate the backup process the backup set will be automatically compressed thus achieving high compression rations and reducing the disk storage needed for the backup set.
Note: If you have performed a backup operation on the database before and you have not used the backup compression option but now you are trying to use it you will get an error. Also, if you have used the backup compression before and now you are not using it when backing up the database, again you will get an error. The reason for this is that by the time you have a non compressed backup set, you cannot add a compressed backup to this and vice versa.
One solution to the above restriction is to use the option Backup to a new media set and erase all existing backup sets. This will allow you to proceed with the backup but it will erase the previous backup sets for the specific database. To this end, it is always better to decide the backup method to be followed for each database before you start taking backups of it.
Read more on this article...
Friday, December 19, 2008
Grouping Sets in SQL Server 2008
Consider a database table containing sales data.
The name of the table is dbo.[sales].
Consider a scenario where the management has asked the database developer to get all the sales records grouped by region and area, then the records grouped only by area and then the records grouped only by region. All of the above should be provided by one SQL query. The traditional way of implementing a query with these multiple groupings would be the following:
--Sales by region, by area
select region,area,SUM(sales_amount) as salesytd
from dbo.[sales]
group by region, area
UNION ALL
--Sales by area
select null as region,area,SUM(sales_amount) as salesytd
from dbo.[sales]
group by area
UNION ALL
--Sales by region
select region,null as area,SUM(sales_amount) as salesytd
from dbo.[sales]
group by region
Grouping Sets is a new T-SQL enhancement in SQL Server 2008. To this end with a special syntax we are able to define multiple groupings (that is, grouping sets) within a single T-SQL statement.
Rewritting the above statement by using grouping sets we have the following code:
--Sales (by region, by area), (by area), (by region)
select region,area,SUM(sales_amount) as salesytd
from dbo.[sales]
group by grouping sets (
(region, area),
(area),
(region)
)
It is obvious that grouping sets simplify the cases where we need to perform multiple groupings in our SQL queries. This applies very well on Data Warehouse Management System's queries among others.
Read more on this article...
The name of the table is dbo.[sales].
Consider a scenario where the management has asked the database developer to get all the sales records grouped by region and area, then the records grouped only by area and then the records grouped only by region. All of the above should be provided by one SQL query. The traditional way of implementing a query with these multiple groupings would be the following:
--Sales by region, by area
select region,area,SUM(sales_amount) as salesytd
from dbo.[sales]
group by region, area
UNION ALL
--Sales by area
select null as region,area,SUM(sales_amount) as salesytd
from dbo.[sales]
group by area
UNION ALL
--Sales by region
select region,null as area,SUM(sales_amount) as salesytd
from dbo.[sales]
group by region
Grouping Sets is a new T-SQL enhancement in SQL Server 2008. To this end with a special syntax we are able to define multiple groupings (that is, grouping sets) within a single T-SQL statement.
Rewritting the above statement by using grouping sets we have the following code:
--Sales (by region, by area), (by area), (by region)
select region,area,SUM(sales_amount) as salesytd
from dbo.[sales]
group by grouping sets (
(region, area),
(area),
(region)
)
It is obvious that grouping sets simplify the cases where we need to perform multiple groupings in our SQL queries. This applies very well on Data Warehouse Management System's queries among others.
Read more on this article...
Transparent Data Encryption (TDE) in SQL Server 2008
Transparent Data Encryption (TDE) is another new feature in SQL Server 2008.
It performs real-time I/O encryption and decryption of the data and log files, that is the entire database. For achieving that, it uses a database encryprion key stored in the database boot record.
A derived benefit of TDE is that whenever a database using TDE is backed up, the backup set is also encrypted.
All of the above provide significant data security in SQL Server 2008.
The procedure for encrypting a database is provided below by T-SQL Code example:
USE master
GO
--Step 1: Create a Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password_goes_here';
GO
--Step 2: Create or obtain a certificate protected by the master key
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'MyCertificate'
GO
--Step 3: Create a database encryption key and protect it by the certificate
USE [DATABASE_NAME]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert
GO
--Step 4: Set the database to use encryption
ALTER DATABASE [DATABASE_NAME]
SET ENCRYPTION ON
GO
After the above are performed, the database will enter the "Encrypted" state.
Remarks:
1 (Important SQL Server 2008 notice regarding TDE). When enabling TDE, you should immediately back up the certificate and the private key associated with the certificate . This is absolutely necessary when trying to restore or attach the encrypted database on another server because you will need to use these keys and certificates. In the opposite case the database will not be accessible. Additionally the encrypting certificate should be retained even if TDE is no longer enabled on the database as it may need to be accessed for some operations.
2. Steps 3 and 4 can be performed from within SQL Server 2008 Management Studio by right-clicking on the database and selecting Tasks --> Manage Database Encryption.
3. Note that four encryption are currently provided:
AES_128
AES_192
AES_256
Triple_DES
4. The entire TDE on a database is completely transparent to the user as it is performed in the background and on the fly.
More information regarding Transparent Data Encryption in SQL Server 2008 can be found in the following link.
Read more on this article...
It performs real-time I/O encryption and decryption of the data and log files, that is the entire database. For achieving that, it uses a database encryprion key stored in the database boot record.
A derived benefit of TDE is that whenever a database using TDE is backed up, the backup set is also encrypted.
All of the above provide significant data security in SQL Server 2008.
The procedure for encrypting a database is provided below by T-SQL Code example:
USE master
GO
--Step 1: Create a Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password_goes_here';
GO
--Step 2: Create or obtain a certificate protected by the master key
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'MyCertificate'
GO
--Step 3: Create a database encryption key and protect it by the certificate
USE [DATABASE_NAME]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert
GO
--Step 4: Set the database to use encryption
ALTER DATABASE [DATABASE_NAME]
SET ENCRYPTION ON
GO
After the above are performed, the database will enter the "Encrypted" state.
Remarks:
1 (Important SQL Server 2008 notice regarding TDE). When enabling TDE, you should immediately back up the certificate and the private key associated with the certificate . This is absolutely necessary when trying to restore or attach the encrypted database on another server because you will need to use these keys and certificates. In the opposite case the database will not be accessible. Additionally the encrypting certificate should be retained even if TDE is no longer enabled on the database as it may need to be accessed for some operations.
2. Steps 3 and 4 can be performed from within SQL Server 2008 Management Studio by right-clicking on the database and selecting Tasks --> Manage Database Encryption.
3. Note that four encryption are currently provided:
AES_128
AES_192
AES_256
Triple_DES
4. The entire TDE on a database is completely transparent to the user as it is performed in the background and on the fly.
More information regarding Transparent Data Encryption in SQL Server 2008 can be found in the following link.
Read more on this article...
Row Constructors in SQL Server 2008
There are many cases were we just want to insert some data to a table.
The traditional ways of doing that in SQL Server versions earlier than 2008 were by using separate insert statements for each record to be inserted or by using the UNION ALL clause.
Consider the following table:
CREATE TABLE [dbo].[employee](
[ID] [int] NOT NULL,
[Name] [varchar](50) NULL
)
Code Example - Separate Insert Statements:
insert into dbo.employee(id,name)
values (1,'EmpA')
insert into dbo.employee(id,name)
values (2,'EmpB')
insert into dbo.employee(id,name)
values (3,'EmpC')
Code Example - Using the UNION ALL:
insert into dbo.employee(id,name)
select 1,'EmpA'
UNION ALL
select 2,'EmpB'
UNION ALL
select 3,'EmpC'
Row constructors allow us to insert multiple records to a table within a single SQL Statement. To this end, records must be contained in parentheses and be separated with commas.
Check out the following code example:
insert into dbo.employee(id,name)
values (1,'EmpA'),(2,'EmpB'),(3,'EmpC')
By using one single SQL statement instead of three we get the same result!
Now check this out:
select c.empID,c.empName from
(values (1,'EmpA'),(2,'EmpB'),(3,'EmpC')) as c(empID,empName);
In the above example by using Row Constructors we created a "temporary table", defined its values and column names and performed a selection. All were done by using a single SQL Statement! That's great stuff!
Read more on this article...
The traditional ways of doing that in SQL Server versions earlier than 2008 were by using separate insert statements for each record to be inserted or by using the UNION ALL clause.
Consider the following table:
CREATE TABLE [dbo].[employee](
[ID] [int] NOT NULL,
[Name] [varchar](50) NULL
)
Code Example - Separate Insert Statements:
insert into dbo.employee(id,name)
values (1,'EmpA')
insert into dbo.employee(id,name)
values (2,'EmpB')
insert into dbo.employee(id,name)
values (3,'EmpC')
Code Example - Using the UNION ALL:
insert into dbo.employee(id,name)
select 1,'EmpA'
UNION ALL
select 2,'EmpB'
UNION ALL
select 3,'EmpC'
Row constructors allow us to insert multiple records to a table within a single SQL Statement. To this end, records must be contained in parentheses and be separated with commas.
Check out the following code example:
insert into dbo.employee(id,name)
values (1,'EmpA'),(2,'EmpB'),(3,'EmpC')
By using one single SQL statement instead of three we get the same result!
Now check this out:
select c.empID,c.empName from
(values (1,'EmpA'),(2,'EmpB'),(3,'EmpC')) as c(empID,empName);
In the above example by using Row Constructors we created a "temporary table", defined its values and column names and performed a selection. All were done by using a single SQL Statement! That's great stuff!
Read more on this article...
Table-Valued Parameters (TVP) in SQL Server 2008
Support of Table-Valued Parameters is another great new feature in SQL Server 2008.
I am sure that all of us had to make a stored procedure or function call in SQL Server many times. In the cases where many values should be passed as parameters it was a little bit difficult as we had to use some really long lines of code. Not any more with Table-Valued Parameters :)
The usual procedure for using TVPs is the following:
1. Create a user-defined table type that can be passed as a TVP to a function or stored procedure
Code Example:
--Create a table type
CREATE TYPE employee AS TABLE
(ID int,
NAME varchar(50))
GO
2. Create a stored procedure or function that uses a TVP
Code Example:
--Create a stored procedure that takes as a parameter a TVP
CREATE PROCEDURE showTVPValues(@TVParameter employee READONLY)
AS
SET NOCOUNT ON
SELECT * FROM @TVParameter
GO
3. Declare the table type variable
Code Example:
--Declare a variable that references the user-defined table type
DECLARE @TVP_Param AS employee;
4. Populate the table type variable with data
Code Example:
--Add data to the table variable
INSERT INTO @TVP_Param (ID, NAME)
SELECT [ID],[NAME]
FROM [table_name].[column_name];
5. Pass the table type variable as a parameter to the stored procedure
Code Example:
--Pass the table variable to the stored procedure
EXEC showTVPValues @TVP_Param;
GO
With this way you can pass tabular data to a function or stored procedure as a single parameter.
Read more on this article...
I am sure that all of us had to make a stored procedure or function call in SQL Server many times. In the cases where many values should be passed as parameters it was a little bit difficult as we had to use some really long lines of code. Not any more with Table-Valued Parameters :)
The usual procedure for using TVPs is the following:
1. Create a user-defined table type that can be passed as a TVP to a function or stored procedure
Code Example:
--Create a table type
CREATE TYPE employee AS TABLE
(ID int,
NAME varchar(50))
GO
2. Create a stored procedure or function that uses a TVP
Code Example:
--Create a stored procedure that takes as a parameter a TVP
CREATE PROCEDURE showTVPValues(@TVParameter employee READONLY)
AS
SET NOCOUNT ON
SELECT * FROM @TVParameter
GO
3. Declare the table type variable
Code Example:
--Declare a variable that references the user-defined table type
DECLARE @TVP_Param AS employee;
4. Populate the table type variable with data
Code Example:
--Add data to the table variable
INSERT INTO @TVP_Param (ID, NAME)
SELECT [ID],[NAME]
FROM [table_name].[column_name];
5. Pass the table type variable as a parameter to the stored procedure
Code Example:
--Pass the table variable to the stored procedure
EXEC showTVPValues @TVP_Param;
GO
With this way you can pass tabular data to a function or stored procedure as a single parameter.
Read more on this article...
DATETIME2 vs DATETIME in SQL Server 2008
The DATETIME2 is a new data type in SQL Server 2008.
We all know of the existence of the DATETIME.
So, what is the difference between these two data types?
Let's try to compare them with some examples.
--Comparison 1: Notice the date - Both data types work fine
select cast('1753-01-01 18:00:00.123' as DATETIME) as [datetime]
Result (Success): 1753-01-01 18:00:00.123
select cast('1753-01-01 18:00:00.123' as DATETIME2) as [datetime2]
Result (Success): 1753-01-01 18:00:00.1230000
Comments: Please note the precision of the DATETIME2. It provides support up to nanoseconds!
--Comparison 2: Notice the time precision
select cast('1753-01-01 18:00:00.1234' as DATETIME) as [datetime]
Result (Error): Conversion failed when converting date and/or time from character string
select cast('1753-01-01 18:00:00.1234' as DATETIME2) as [datetime2]
Result (Success): 1753-01-01 18:00:00.1234000
Comments: DATΕTIME does not support time precision more than milliseconds and that's why the above conversion fails. Though, the DATETIME2 supports up to nanoseconds and the conversion works.
--Comparison 3: Notice the date values
select cast('1653-01-01 18:00:00.123' as DATETIME) as [datetime]
Result (Error): The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
select cast('1653-01-01 18:00:00.123' as DATETIME2) as [datetime]
Result (Success): 1653-01-01 18:00:00.1230000
Comments: DATΕTIME does not support date values before the year 1753 and that's why the conversion fails. Though, DATETIME2 supports until back to year 0001 and so the conversion works.
Conclusions: The DATETIME2 offers support for larger date ranges and larger time precision.
DATETIME: Date and Time Ranges
------------------------------------
The supported date range is:1753-01-01 through 9999-12-31 (January 1, 1753, AD through December 31, 9999 AD)
The supported time range is: 00:00:00 through 23:59:59.997
DATETIME2: Date and Time Ranges
-------------------------------------
The supported date range is: 0001-01-01 through 9999-12-31 (January 1,1 AD through December 31, 9999 AD)
The Supported time range is: 00:00:00 through 23:59:59.9999999
Read more on this article...
We all know of the existence of the DATETIME.
So, what is the difference between these two data types?
Let's try to compare them with some examples.
--Comparison 1: Notice the date - Both data types work fine
select cast('1753-01-01 18:00:00.123' as DATETIME) as [datetime]
Result (Success): 1753-01-01 18:00:00.123
select cast('1753-01-01 18:00:00.123' as DATETIME2) as [datetime2]
Result (Success): 1753-01-01 18:00:00.1230000
Comments: Please note the precision of the DATETIME2. It provides support up to nanoseconds!
--Comparison 2: Notice the time precision
select cast('1753-01-01 18:00:00.1234' as DATETIME) as [datetime]
Result (Error): Conversion failed when converting date and/or time from character string
select cast('1753-01-01 18:00:00.1234' as DATETIME2) as [datetime2]
Result (Success): 1753-01-01 18:00:00.1234000
Comments: DATΕTIME does not support time precision more than milliseconds and that's why the above conversion fails. Though, the DATETIME2 supports up to nanoseconds and the conversion works.
--Comparison 3: Notice the date values
select cast('1653-01-01 18:00:00.123' as DATETIME) as [datetime]
Result (Error): The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
select cast('1653-01-01 18:00:00.123' as DATETIME2) as [datetime]
Result (Success): 1653-01-01 18:00:00.1230000
Comments: DATΕTIME does not support date values before the year 1753 and that's why the conversion fails. Though, DATETIME2 supports until back to year 0001 and so the conversion works.
Conclusions: The DATETIME2 offers support for larger date ranges and larger time precision.
DATETIME: Date and Time Ranges
------------------------------------
The supported date range is:1753-01-01 through 9999-12-31 (January 1, 1753, AD through December 31, 9999 AD)
The supported time range is: 00:00:00 through 23:59:59.997
DATETIME2: Date and Time Ranges
-------------------------------------
The supported date range is: 0001-01-01 through 9999-12-31 (January 1,1 AD through December 31, 9999 AD)
The Supported time range is: 00:00:00 through 23:59:59.9999999
Read more on this article...
The TIME data type in SQL Server 2008
Similarly to my previous post, another new data type in SQL Server 2008 is the "TIME".
Again, without having to manipulate the current datetime for getting the time, you just have to cast it to the TIME data type. Here's the syntax:
select cast(SYSDATETIME() as TIME) as [current_time]
--or
select cast(getdate() as TIME) as [current_time]
Read more on this article...
Again, without having to manipulate the current datetime for getting the time, you just have to cast it to the TIME data type. Here's the syntax:
select cast(SYSDATETIME() as TIME) as [current_time]
--or
select cast(getdate() as TIME) as [current_time]
Read more on this article...
The DATE data type in SQL Server 2008
One of the new data types in SQL Server 2008 is the "DATE".
The DATE data type provides only the date without requiring from the database developers to extract the date from the datetime anymore.
To this end you can just get the date with a single line of code:
select cast(SYSDATETIME() as DATE) as [current_date]
or
select cast(getdate() as DATE) as [current_date]
Isn't that cool? :)
Read more on this article...
The DATE data type provides only the date without requiring from the database developers to extract the date from the datetime anymore.
To this end you can just get the date with a single line of code:
select cast(SYSDATETIME() as DATE) as [current_date]
or
select cast(getdate() as DATE) as [current_date]
Isn't that cool? :)
Read more on this article...
Monday, December 15, 2008
Machine Debug Manager - Windows Vista
Recently I have installed Service Pack 1 for Visual Studio 2008.
My machine runs Windows Vista SP1.
During the installation process I was prompt with a warning message that the Machine Debug Manager service was still running. For completing the installation process on the safe side I had to stop this service.
So, what is exactly the Machine Debug Manager? What is the purpose of it?
Machine Debug Manager supports local and remote debugging for Visual Studio and script debuggers. Among these, it also allows windows users to debug Internet Explorer scripting errors.
You can stop this service by opening Windows Services (start --> run --> services.msc) and explicitly stopping the service "Machine Debug Manager" (Service Name: MDM).
When the installation of the Service Pack completes, you can then start the service again if desirable.
Read more on this article...
My machine runs Windows Vista SP1.
During the installation process I was prompt with a warning message that the Machine Debug Manager service was still running. For completing the installation process on the safe side I had to stop this service.
So, what is exactly the Machine Debug Manager? What is the purpose of it?
Machine Debug Manager supports local and remote debugging for Visual Studio and script debuggers. Among these, it also allows windows users to debug Internet Explorer scripting errors.
You can stop this service by opening Windows Services (start --> run --> services.msc) and explicitly stopping the service "Machine Debug Manager" (Service Name: MDM).
When the installation of the Service Pack completes, you can then start the service again if desirable.
Read more on this article...
Subscribe to:
Posts (Atom)
Posts
