How to enable and configure Filestream in SQL SERVER

Filestream was introduced in Sql Server 2008 for the storage and management of unstructured data.
Follow the below steps to enable this filestream.

To enable filestream through SQL Server configuration manager:

1.Open SQL Server configuration manager.Open SQL Server services
2.Select the instance for which you want to enable Filestream.Right click the instance->properties.
3.In the SQL Server Properties dialog box, click the Filestream tab.
4.Select the Enable Filestream for Transact-SQL access.
5.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.
6.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.
7.Click Apply.


Enable Filestream access level server configuration option:
In SQL Server Management Studio

[0 -Disables FILESTREAM,
1 -Enables FILESTREAM for T-SQL,
2 -Enables FILESTREAM for T-SQL and Win32 streaming access]

Syntax:
EXEC sp_configure filestream_access_level,2
RECONFIGURE with override

Create filestream enabled database:

1. We can enable file stream while creating the database  (or) If the database is already created we can enable filestream using alter database.
   

To create file stream enable database you can use below query

CREATE DATABASE DBname
ON
PRIMARY ( NAME = test1,
    FILENAME = ‘c:\data\testdat1.mdf’),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = test3,
    FILENAME = ‘c:\data\test1′)
LOG ON  ( NAME = testlog1,
    FILENAME = ‘c:\data\test1.ldf’)
GO

Enable filestream on existing database:
To enable file stream on existing database you can use alter database  command with  SET FILESTREAM similar to the example below or SSMS 
ALTER DATABASE [DBNAME] SET FILESTREAM( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N’Directoryname’ ) WITH NO_WAIT
GO
 
Enable filestream for database using SQL Server 2008 Management Studio:
 
1. Connect to SQL Server Instance using SQL Server Management Studio
2. In the Object Explorer, right click the instance and select Properties.
3. On the left panel click on the Advanced tab, then click on the drop down list of Filestream Access Level and select Full access enabled option.


4. Click Ok to save the changes.

sqlchallenges

A Table DIVISION (divid, divname ) have many units, UNIT (unitid, unitname, divid[fk] ) have many locations, LOCATION ( locid, locname, unitid[fk]) have many criminals, CRIMINAL (criminalid, fname, lname, locid[fk]) Can anyone give me a SQL QUERY . I need 2 fields only one is DIVISION and other is VAL(with values the sum of each criminal in a particular division). Division count ---------------- Div1 5 Div2 10 Answer: SELECT d.divname, count( d.divid ) FROM division d JOIN unit u ON ( d.divid = u.divid ) JOIN location l ON ( l.unitid = u.unitid ) JOIN CRIMINAL c ON ( c.locid = l.locid ) GROUP BY divname

SQL Server consistency check

How can you be sure everything is OK with your Sql server‎ database?

Run a consistency check to check the database objects logical and physical integrity

- DBCC CHECKDB - checks the entire database. Executes CHECKALLOC, CHECKCATALOG and CHECKTABLE for every table and view
- DBCC CHECKALLOC - checks the consistency of disk space allocation for a specific database
- DBCC CHECKCATALOG - checks catalog consistency for a specific online database
- DBCC CHECKTABLE - checks the integrity of all pages and structures in a specific table/indexed view
- DBCC CHECKFILEGROUP - executes CHECKALLOC and CHECKTABLE for every table in the filegroup you specified

Solve Query...

I have One table Like

Table1:
======

ID      Name           Date             Value
--------------------------------------------------
1        naimish        10/4/12          50
2        jugal             12/4/12        150
3        vimal            15/4/12        300
4        mohit            20/4/12        450

Display Output Like Below (using Query Only)

ID      Name            Date              Value          Date_Modified        New_Value
------------------------------------------------------------------------------------------------
1        naimish         10/4/12          50              12/4/12                  100
2        jugal              12/4/12         150             15/4/12                  150
3        vimal             15/4/12         300             20/4/12                  150
4        mohit             20/4/12         450             Null                        Null

Ans:

SELECT t1.id, t1.name,t1.date,t1.value , t2.date Date_Modified , t2.value - t1.value New_Value FROM `temp` t1
join (SELECT id - 1 as id ,name,date,value FROM `temp`) as t2
on (t1.id = t2.id)
union all
select id, name ,date ,  value , null ,null from temp
where id in(select max(id) from temp)

Cache Memory Information

To See What type of information is stored in cache memory

SELECT COUNT(*) AS cached_pages_count,
name AS BaseTableName, IndexName,
IndexTypeDesc
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT s_obj.name, s_obj.index_id,
s_obj.allocation_unit_id, s_obj.OBJECT_ID,
i.name IndexName, i.type_desc IndexTypeDesc
FROM
(
SELECT OBJECT_NAME(OBJECT_ID) AS name,
index_id ,allocation_unit_id, OBJECT_ID
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT OBJECT_NAME(OBJECT_ID) AS name,
index_id, allocation_unit_id, OBJECT_ID
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id
AND au.type = 2
) AS s_obj
LEFT JOIN sys.indexes i ON i.index_id = s_obj.index_id
AND i.OBJECT_ID = s_obj.OBJECT_ID ) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = DB_ID()
GROUP BY name, index_id, IndexName, IndexTypeDesc
ORDER BY cached_pages_count DESC;
GO

To clean Buffer Use utility.

DBCC DROPCLEANBUFFERS

 

Query Optimization Tips

Before optimize any query clean buffer first after we can see what's exactly going on

  - DBCC dropcleanbuffer
  - DBCC FreeProcCatch

For See Statistsics Also See In Execution Plan

  - Set Statistics IO ON
  - Set Statistics Time ON

For Optimization We are Concentrate on 3 Most Statistic

1) NO of logical reads
2) CPU Time
3) Query Cost (QC)

Both 1st and 2nd You will Get From Upper Commands
But For QC Use Execution Plan

See and Note All Statistic and Then Apply This Instruction For optimization that Given Below:

1) Limit no of colums return from query
2) Create Primary Key
3) Create index which are using in workhours
4) Limit the no of rows by using top
5) Working with join have index on the column in the join
6) If you use multiple columns in where or order by clause then create compound index
7) If you are trying to fetch unique values use group by instead of distinct
8) Use exists in behalf of in().
9) set nocount on

Query Optimization OR and Union

When we execute query with OR clause,filtering the records from two 
different field. Oracle Database can not use index to filter the that.

1) create Index Idx_empno on emp(empno)

2) create Index Idx_salary on emp(salary)

Select * from emp
where empno = 7319 or salary > 15000

In this query Both the field have an index then also you can see
that it takes to much time because of "or" indexing field not work because of OR

so use Union to overcome this,

Select * from emp where empno = 7319
Union
Select * from emp where salary > 15000