How to find list of indexes in a database ?

Found very good options at
http://stackoverflow.com/questions/765867/list-of-all-index-index-columns-in-sql-server-db

The ones which I liked most are :


1.
DECLARE @IndexInfo  TABLE (index_name         varchar(250)
                          ,index_description  varchar(250)
                          ,index_keys         varchar(250))
INSERT INTO @IndexInfo exec sp_msforeachtable 'sp_helpindex ''?'''
select * from @IndexInfo

This does not return table name, but if you are sure you can makeout table name from
index name then this is sufficient, short and sweet.

NOTICE this sp : sp_msforeachtable

2. Following one is also good, but uses a loop and less readable than above.
But it returns table name also.


DECLARE @IndexInfoTemp TABLE (index_name varchar(250)
,index_description varchar(250)
,index_keys varchar(250)
)
DECLARE @IndexInfo TABLE (table_name sysname
,index_name varchar(250)
,index_description varchar(250)
,index_keys varchar(250)
)
DECLARE @Tables Table (RowID int not null identity(1,1)
,TableName sysname
)
DECLARE @MaxRow int
DECLARE @CurrentRow int
DECLARE @CurrentTable sysname
INSERT INTO @Tables
SELECT
DISTINCT t.name
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE i.Name IS NOT NULL
SELECT @MaxRow=@@ROWCOUNT,@CurrentRow=1
WHILE @CurrentRow<=@MaxRow
BEGIN
SELECT @CurrentTable=TableName FROM @Tables WHERE RowID=@CurrentRow
INSERT INTO @IndexInfoTemp
exec sp_helpindex @CurrentTable
INSERT INTO @IndexInfo
(table_name , index_name , index_description , index_keys)
SELECT
@CurrentTable , index_name , index_description , index_keys
FROM @IndexInfoTemp
DELETE FROM @IndexInfoTemp
SET @CurrentRow=@CurrentRow+1
END --WHILE
SELECT * from @IndexInfo

SQL Server - Which are the database recovery modes ?

full
 simple
 bulk-logged

How to clear SQL Server Cache ?

CONCLUSION :
1. Normally, use following two when you do not want query compilation also to
come into picture. 
CHECKPOINT
DBCC DROPCLEANBUFFERS
2. When you want query compilation also to come into picture,  free the proccache also :

CHECKPOINT
DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

3. The most comprehensive cleaning strategy would be :

DBCC FREESYSTEMCACHE(All)
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
DBCC FLUSHPROCINDB( db_id )
CHECKPOINT
DBCC DROPCLEANBUFFERS

=============================================================



From : http://sqlserverpedia.com/wiki/SELECT_DISTINCT

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

NOTE: Do not run the aforementioned DBCC commands on a production server

MSDN : DBCC DROPCLEANBUFFERS (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms187762.aspx

Removes ALL CLEAN BUFFERS from the buffer pool.

MSDN recommends to use CHECKPOINT before DROPCLEANBUFFERS.  AS you can see, DROPCLEANBUFFERS will drop only clean buffers, what about dirty buffers ?

clean buffers  - memory and disk data is matching

dirty buffers  - updated data present in memory, not written to disk

CHECKPOINT forces data of dirty pages to be written to disk, and makes it clean.

REMEMBER that CHECKPOINT only works on CURRENT DATABASE.

So next question : What is the difference between DROPCLEANBUFFERS and

FREEPROCCACHE ?

See http://mssqlonline.blogspot.com/2007/03/stored-procedure-clean-cache-and-clean.html for answer.

It says "DBCC FREEPROCCACHE will invalidate all stored procedure plans that the optimizer has cached in memory and force SQL Server to compile new plans the next time those procedures are run."

FREEPROCCACHE clears cached execution plans of SPs.

MSDN says http://msdn.microsoft.com/en-us/library/ms174283.aspx

Use DBCC FREEPROCCACHE to clear the plan cache carefully. Freeing the plan cache causes, for example, a stored procedure to be recompiled instead of reused from the cache.

Now another question : What are clean , dirty and cold buffer caches ?

CLEAN and DIRTY are well explained above. Not much clear about cold,

refer here for some explanation :

http://blogs.msdn.com/b/psssql/archive/2009/03/17/sql-server-what-is-a-cold-dirty-or-clean-buffer.aspx

It says :

The cold buffer cache has a couple of variants within SQL Server.    Typically it refers to the buffer pool right after restart.   The data cache is not loaded (cold) and requires physical reads to populate the cache.   This is often termed the RAMPUP phase.     Internally it can also refer to the free list activity.   SQL Server understands the time of last access to buffers and when appropriate takes this into account when handling the free buffer lists.   Buffers on the free list that have some age to them may be termed cold because the age increases the chance that they accessible with a near memory transfer from a location such as CPU cache.

As per Pinal Dave http://blog.sqlauthority.com/2008/07/22/sql-server-clear-sql-server-memory-caches/

The following commands clean only the plan cache:
DBCC FREESYSTEMCACHE(All | pool_name)
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
DBCC FLUSHPROCINDB( db_id )
To clear the data buffer use the following command:
DBCC DROPCLEANBUFFERS
For more information visit to : http://blog.sqlauthority.com/2007/03/23/sql-server-stored-procedure-clean-cache-and-clean-buffer/

How to disable WCF security ?

In the host web.config, include a binding like following and assign it to the service :



Note the "CustomBinding" below, and note the  "None"s : security, proxyCredential, clientcredential. Also note that the binding is assigned using the bindingConfiguration attribute.

  <system.serviceModel>
    <bindings>
      <wsHttpBinding >
        <binding name="CustomBinding" >
          <security mode="None">
            <transport proxyCredentialType="None" clientCredentialType="None"></transport>
          </security>
        </binding>
      </wsHttpBinding>
    </bindings>
    <services>
    
      <service name="BusinessServiceLayer.LoginService">
        <endpoint address="" binding="wsHttpBinding" contract="BusinessServiceLayer.ILoginService"  bindingConfiguration="CustomBinding" >
        </endpoint>
        <endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange" />
        <host>
          <baseAddresses>
            <add baseAddress="http://RelayTrack_Services.retser.com/BusinessServiceLayer/LoginService/" />
          </baseAddresses>
        </host>
      </service>
    </services>

How to Host a WCF Class Library Project ?

When we create a WCF class library project, it is not ready to be hosted on IIS directly.
It needs web.config and .svc file.
Notice that the WCF class library project has a app.config file, but no web.config or .svc file.

There are two steps :
  Create web.cofig
  Create .svc file

1. To create web.config, add a blank text file to the class library project. Rename it to web.config.
Add the following to it  and save it:
 <?xml version="1.0" encoding="UTF-8"?>
  <configuration>
  </configuration>

Then copy the entire <system.serviceModel>  tag from the existing app.config file of the class library peoject.

2. Next add a blank file to the wcf class library project and name it as a .svc file, say xyz.svc.
In the svc file add two statements :
  <%@ ServiceHost Service="BusinessServiceLayer.LoginService" %>
  <%@ Assembly Name="BusinessServiceLayer" %>

 Note that the service value in the ServiceHost directive  is the name appearing in the <service> tag in app.config/web.cofig

Assembly name is the name of the dll. By default this dll is created in bin\Debug of bin\Release folder of the
 class library project. This must be copied out of the \Debug or \Release folder into bin folder for it to
 be accessible to the IIS.

The last step is to set .svc as the default page in the IIS web site settings.

How to Store output of SQL Server EXEC into a table

http://it.toolbox.com/wiki/index.php/Store_output_of_SQL_Server_EXEC_into_a_table

how to produce 0 without using any number in sql server ?

1.
select $
2.
select £
3.
Declare @num int
Set @num = ''
Select @num

this is more useful. see what happens when a set an integer to null

Note that the following does NOT produce 0, it will produce NULL

Declare
Select @num int @num