example of finding percent restored

select percent_complete

from sys.dm_exec_requests

where session_id = 58

Continue

DECLARE @int varchar(10)  

DECLARE curs_int  CURSOR FOR

SELECT '1'

union SELECT'2'

union SELECT'3'

union SELECT'4'

union SELECT'5'

order by 1

OPEN curs_int

FETCH NEXT FROM curs_int

INTO @int

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT @int

FETCH NEXT FROM curs_int

      INTO @int

    if @int = '3' continue

print @int      

END

CLOSE curs_int

DEALLOCATE curs_int

returns
1
2
2
3
4
4
5
5
5

a unsuccessful insert will not change @@identity

create table #dsfdsf(a varchar(1000),b int identity)

insert into #dsfdsf(a)

select 'gfdfg' df

select  @@identity

insert into #dsfdsf(a)

select 'gfdfg' df

where 1=5  

select  @@identity

drop table #dsfdsf

the select returns 1 in both cases

Default inserts

when the only column has a default

create table x (i int default 1)

insert into x values (default)

 

isolation level cheatsheet

DBCC USEROPTIONS will show default settings

Set Option        Value

isolation level    read committed

to avoid locks where the data is does not have to be perfect

SET TRANSACTION ISOLATION LEVEL

    { READ UNCOMMITTED

    | READ COMMITTED

    | REPEATABLE READ

    | SNAPSHOT

    | SERIALIZABLE

    }

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Select etc …

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Or 

Select * from trans with (nolock)

Nulls in avg and count

select     

    sum(a) / count(a)  [homemade average - count doesnt count nulls],

      avg(isnull(a,0))  [average with isnull],

      avg(a)  [regular average]

from

(

select null a

union select 0

union select 5

union select 15

) main

Turning of the firewall in XP

Here's how

Attrib

I have , over the past years, contracted nasty viruses from my kids media. One particular one would hide all the files on a drive and create files with the same name that were links to the lord knows where.

I removed them and ran for e.g.

C:\>attrib -h -r -s u:\investments

note:

You must remove the h attribute for the other attributes to be removed

Running TSQL with "Go" in ado.net

did the following:
added these references

and ran this code

     using (SqlConnection con = new SqlConnection (connstr))

     {

         con.Open();

         Server server = new Server(new ServerConnection(con));

         server.ConnectionContext.ExecuteNonQuery(grants);

      }

Thanx        

Crystal reports check box

Great solution

Big O

Great article.

Implicit Conversions In SQL Server

Implicit Conversions occur based on precedence. To get the desired result you dont need to cast the numerator, the denominator will also work:

Example of Lambda in javascript

var sq = function (x) {return x * x};

Example of reflection getting the value of a private field

namespace sss

{

    class Program

    {

        static void Main(string[] args)

        {

            testReflection t = new testReflection();

            Type typ = Assembly.GetExecutingAssembly().GetType("sss.testReflection");

             int value = (int)typ.InvokeMember("i",

     BindingFlags.GetField | BindingFlags.Instance | BindingFlags.NonPublic,

     null, t, null);

        }

    }

    public class testReflection

    {

        private int i = 78;

    }

Abstract types should not have public constructors

Microsoft's heading is a bit imprecise. Public will work , though. Private wont (though that has nothing to do with being abstract):

   class Program

    {

        static void Main(string[] args)

        {

            test2 t = new test2(); 

        }

    }

    public abstract class test

    {

       private  test()

        {

        }

    }

    public class test2 : test

    {

        public test2()

        {

        }

    }

When @@identity fails

create table t (x int identity(1,1))

go

create table t2 (x int identity(10,1))

go

create trigger t_trig on t

after insert

as

begin

insert t2  DEFAULT VALUES

end

go

insert t  DEFAULT VALUES

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];

SELECT @@IDENTITY AS [@@IDENTITY];

select ident_current('t') as 'ident_current'

XML in SQL Server cheat sheet

CREATE TABLE [dbo].[customers](

      [id] [int] IDENTITY(1,1) NOT NULL,

      [name] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

      [Preferences] [xml] NULL,

 CONSTRAINT [PK_customers] PRIMARY KEY CLUSTERED

(

      [id] ASC

)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

insert into customers values(1,'herby','<Preferences />')

insert into customers values(2,'herby2','<Preferences />')

insert into customers values(3,'herby24','<Preferences />')

insert into customers values(4,'herby84','<Preferences />')

--manipulate and selects on xml data column

select id,Preferences.value('(/preferences/preference/@color)[2]','nvarchar(max)' )

from customers

where Preferences.exist('/preferences/preference[@color="blue" and @number ="2"]')=1

update customers

set preferences.modify('insert <preference  color ="blue" number ="2"  /> into (/preferences)[1]')

--data to xml conversions FOR XML

SELECT * FROM customers FOR XML AUTO, TYPE,root('doc')

SELECT * FROM customers FOR XML AUTO, TYPE,root('doc'), elements

SELECT

1 as tag,

null as parent,

name as [Person!1!name]--,

--id as [person!1!id!ELEMENT]

FROM customers FOR XML explicit, TYPE,root('doc')

--same thing using XQuery

SELECT (SELECT * FROM customers FOR XML AUTO, TYPE).query(

'<doc>{

   for $c in /customers

   return

     <Person name="{data($c/@name)}"/>

 }</doc>')

--xml text to data --attribute style

declare @text varchar(max)

declare @pointer int

set @text = '<doc>

  <Person name="herby" />

  <Person name="herby2" />

  <Person name="herby24" />

  <Person name="herby84" />

</doc>'

exec sp_xml_preparedocument @pointer out, @text

select name from openxml(@pointer,'doc/Person',0)

with ( name varchar(max)  '@name' )

exec sp_xml_removedocument @pointer

--xml text to data --element style

declare @text varchar(max)

declare @pointer int

set @text = '<doc>

  <Person><name>herby</name></Person>

  <Person><name>herby2</name></Person>

  <Person><name>herby24</name></Person>

  <Person><name>herby84</name></Person>

</doc>'

exec sp_xml_preparedocument @pointer out, @text

select * from openxml(@pointer,'doc/Person',1)

with ( name varchar(max)  'name' )

exec sp_xml_removedocument @pointer

--openxml works on xml data types also

declare @text xml

declare @pointer int

SELECT @text =

 (SELECT * FROM customers FOR XML AUTO, TYPE).query(

'<doc>{

   for $c in /customers

   return

     <Person name="{data($c/@name)}"/>

 }</doc>')

exec sp_xml_preparedocument @pointer out, @text

select name from openxml(@pointer,'doc/Person',0)

with ( name varchar(max)  '@name' )

exec sp_xml_removedocument @pointer

index fragmentation

select object_name(object_id) ,index_id,index_type_desc,

avg_fragmentation_in_percent

from sys.dm_db_index_physical_stats(db_id('test'),null,null, null,'LIMITED')

--index usage

select i.name,user_seeks,user_scans,last_user_seek,last_user_scan

from sys.dm_db_index_usage_stats s inner join

sys.indexes i on

s.object_id = i.object_id

and s.index_id = i.index_id

where database_id =  db_id('test')

thanx again sql server recipe book

statistics cheat sheet

create statistics customers_stats on customers(name)  with fullscan

update statistics customers with fullscan

--checking statistics on tables

dbcc show_statistics ('customers','customers_stats')

sp_createstats

sp_updatestats

checking statistics on cached query plans

select s.text,

st.total_logical_reads,

st.total_physical_reads,

st.total_elapsed_time/1000000 secs,

st.total_logical_writes

from sys.dm_exec_query_stats st

cross apply  sys.dm_exec_sql_text(st.sql_handle) s

(thank you sql recipe book)

Another great usage of cross apply - all running queries

select r.session_id, r.status,r.start_time,r.command,s.text

from sys.dm_exec_requests r

cross apply  sys.dm_exec_sql_text(r.sql_handle) s

where r.status ='running'

statistics IO

Set statistics io on doesn’t work in SSMS

- You have to click the graphical button statistics io on

How to find all open transactions

dbcc opentran('database')

code to replace deprecated sp_lock

select  request_session_id,

resource_database_id,

resource_associated_entity_id,

--object_name(resource_associated_entity_id),

o.name,

request_mode,

request_status

from sys.dm_tran_locks l left join sys.objects o

on l.resource_associated_entity_id = o.object_id

Code to replace deprecated sp_who

select blocking_session_id, wait_duration_ms, session_id

from sys.dm_os_waiting_tasks

where blocking_session_id is not null

or

SELECT

        t1.resource_type,

        t1.resource_database_id,

        t1.resource_associated_entity_id,

        t1.request_mode,

        t1.request_session_id,

        t2.blocking_session_id

FROM sys.dm_tran_locks AS t1

INNER JOIN sys.dm_os_waiting_tasks AS t2

    ON t1.lock_owner_address = t2.resource_address;