Create a FILESTREAM-Enabled Database

If you want to use FILESTREAM data storage features in database you must create FILESTREAM enabled database.
Must specify the CONTAINS FILESTREAM clause for at least one filegroup.

Here are the sample script to create FILESTREAM-Enabled Database :

CREATE DATABASE AccountSystem
ON
PRIMARY ( NAME = accountsystem1,
    FILENAME = 'c:\data\accountsystemdat1.mdf'),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = accountsystem3,
    FILENAME = 'c:\data\filestream1')
LOG ON  ( NAME = Archlog1,
    FILENAME = 'c:\data\accountsystemlog1.ldf')
GO

Here this script Create Database name "AccountSystem".
This database contains three filegroups PRIMARY ,  accountsystem1 AND FileStreamGroup1.
PRIMARY  and accountsystem1 are regular file groups that cannot contain FILESTREAM data.
FileStreamGroup1 is the FILESTREAM filegroup.

For a FILESTREAM filegroup, FILENAME refers to a path. The path up to the last folder must exist, and the last folder must not exist.
In this example, c:\data must exist. However, the filestream1 subfolder cannot exist when you execute the CREATE DATABASE statement.

After you run this script, a filestream.hdr file and an $FSLOG folder appears in the c:\Data\filestream1 folder.
The filestream.hdr file is a header file for the FILESTREAM container.

Important

The filestream.hdr file is an important system file. It contains FILESTREAM header information. Do not remove or modify this file.

You can use the ALTER DATABASE statement to add a FILESTREAM filegroup for an exsiting database.

New FILESTREAM Data Storage Features in SQL Server 2008 : Part 1

FILESTREAM storage is use to store unstructured data, such as documents and images, on the file system.
Before we can start to use FILESTREAM, you must enable FILESTREAM on the instance of the SQL Server Database Engine.
Here are describes how to enable FILESTREAM by using SQL Server Configuration Manager.

Important Note :

You cannot enable FILESTREAM on a 32-bit version of SQL Server running on a 64-bit operating system.

Step enable and change FILESTREAM settings

1. Open SQL Server Configuration Manager from Microsoft SQL Server 2008 R2 's  Configuration Tools Menu  .
2. In the list of services, right-click SQL Server Services, and then click Open.
3. In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.
4. Right-click the instance, and then click Properties.
5. In the SQL Server Properties dialog box, click the FILESTREAM tab.
6. Select the Enable FILESTREAM for Transact-SQL access check box.
7. If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.
8. If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.
9. Click Apply.
10. In SQL Server Management Studio, click New Query to display the Query Editor.
11. In Query Editor, enter the following Transact-SQL code:
    
    EXEC sp_configure filestream_access_level, 2
    RECONFIGURE
12. Click Execute.
13. Restart the SQL Server service.

Sql Server Beginners : Various functions to get system date and time in Sql Server 2008

Here are varios functions to get system date and time.

SELECT SYSDATETIME() AS 'SYSDATETIME'
Output :-

2012-03-09 12:01:17.25

SELECT SYSDATETIMEOFFSET() AS 'SYSDATETIMEOFFSET'
Output :-

2012-03-09 12:01:17.2580000 +00:00

SELECT SYSUTCDATETIME() AS 'SYSUTCDATETIME'
Output :-

2012-03-09 12:01:17.25

SELECT CURRENT_TIMESTAMP AS 'CURRENT_TIMESTAMP'
Output :-

2012-03-09 12:01:17.257

SELECT GETDATE() AS 'GETDATE'
Output :-

2012-03-09 12:01:17.257

SELECT GETUTCDATE() AS 'GETUTCDATE'
Output :-

2012-03-09 12:01:17.257

Sql Server Beginners : New Date and Time datatype in Sql Server 2008

Sql Serve 2008 introduce new Date and Time data types.
Here are the list of new datatypes:

SQL Server 2008 data type	Default string literal format
time	hh:mm:ss[.nnnnnnn]
date	YYYY-MM-DD
datetime2	YYYY-MM-DD hh:mm:ss[.nnnnnnn]
datetimeoffset	YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm

Here are Comparison between date and time datatypes.

Query :

SELECT
     CAST('2012-03-02 12:40:39. 1234567 +12:10' AS time(7)) AS 'time'
    ,CAST('2012-03-02 12:40:39. 1234567 +12:10' AS date) AS 'date'
    ,CAST('2012-03-02 12:40:39.123' AS smalldatetime) AS
        'smalldatetime'
    ,CAST('2012-03-02 12:40:39.123' AS datetime) AS 'datetime'
    ,CAST('2012-03-02 12:40:39. 1234567 +12:10' AS datetime2(7)) AS
        'datetime2'
    ,CAST('2012-03-02 12:40:39.1234567 +12:10' AS datetimeoffset(7)) AS
        'datetimeoffset';

Here are the output of the query:

SQL Server 2008 data type	Output
time	12:40:39.1234567
date	2012-03-02
smalldatetime	2012-03-02 12:41:00
datetime	2012-03-02 12:40:39.123
datetime2	2012-03-02 12:40:39.1234567
datetimeoffset	2012-03-02 12:40:39.1234567 +12:10