Tomaz.tsql

SQL Joke

What does table say to a trigger: "Hey, stop it, i'm full".

Stored procedure and how to script them

 Imagine having  hundred of stored procedures in your database and you want to move them to another database. There are several ways to move them.

1) In MMSM you can right-click each procedure and select "script stored procedure as -> create to -> new query window" but this might be tidious and everlasting

2) You can use information_schema.routines:

select routine_definition from information_schema.routines
where routine_type = 'PROCEDURE'

 There is only one limitation - the size of routine_definition. Try this with procedure longed that 8000 characters. Failed

3) One can also use catalog view syscomments :

select
     c.text
    ,c.colid
    ,o.name
from syscomments as c
join sysobjects as o
    on c.id = o.id
where
    o.xtype = 'P'

again. there is limitation to field text of syscomments to only 4000 characters. due to this limitation one get field called colid denoting the part of procedure so you can either later concenate it. but stil tidious job.Failed

4) and finally, using catalog view sys.sql_modules:

select
s.definition
,s.object_id
,o.name
from sys.sql_modules as s
join sys.objects as o
on o.object_id = s.object_id
where
o.type_desc = 'SQL_STORED_PROCEDURE'

Finally catalog view in SQL Server 2008 with field definition set as nvarchar(max) is much more promising that any other catalog views (in previous versions as well as more promising as information_schema views).

 Only sys.sql_modules is SQL Server 2008 code, everything else can be used in 2000, 2005 or 2008. 

And additional hint. Set your results in SSMS to "Results to text". And you will do all the magic.

Happy scripting

DBCC USEROPTIONS

this is something every DBA should know by heart. I mean, everyone. 

running query: 

[DBCC USEROPTIONS]

Returns following options each DBA can set on current database that will be affected globaly:

1.  textsize
2.  language
3.  dateformat
4.  datefirst
5.  lock_timeout
6.  quoted_identifier
7.  arithabort
8.  ansi_null

9.  ansi_warnings
10. ansi_padding
11. concat_null_yields_null
12. isolation level

 each of the options can be re- set using:

set [name_of_the_option] [new_value];

go

e.g.:

SET TEXTSIZE 1024;

GO

Short remarks on options:

1. set the textsize is set to: 2147483647, which is 2Gb. you can downsize the lenght of text datatype. This setting also effects @@TEXTSIZE function

2. set language to your local language. list of all languages can be retrieved with following query: select name from sys.syslanguages. Setting the lanuage directly all the date settings as well (but not the money currencies, time-zones, day-savings, etc.)  Once you set your local language, this setting will override also next two options: DATEFORMAT and DATEFIRST.

3. set dateformat is to change your dateformat datatypes in your favorite order. YYYY/MM/DD to DD/MM/YYYY or any other way. YDM indicates order as Year-Day-Month, DMY as Day-Month-Year etc.

4. set datefirst indicates the beginning of the week. Language us_english is set to start counting week from Sunday, Monday,...etc. setting datefirst to 1 it will mean that you can start counting week from Monday, Tuesday, etc.  I'm sure you had several occasions where you needed to query your data with weeks and you were annoyed that you need to sum or substract one day to get the non-us week starting from monday, tuesday, etc. This change also affects @@DATEFIRST function.

5. set lock_timeout is cruical to set it to 0 or 10000 (expressed in milliseconds), so if there are locks against the tables, query initiating the lock will be stopped. It is especially handy when working on production SQL Servers. If set to 0 query will stop immediatelly when lock accours. If you leave it to default value: -1, query will never stop.

6. set quoted_identifier on|off ; if set to ON all double quotations must be delimited with single quotations. Otherwise it will not be interpreted as object identifier. Very handy if you are working a lot with text and strings.

7. set arithabort on|off; if set to ON, query will override any division-by-zero errors.   Very handy if you are doing any calculations and you never know if there will occour diving with zero that will terminate your query.

8. set ansi_nulls on|off: if set to ON, query working in WHERE clause with any columns that are NULL will not be counted. e.g.: select count(*) from MyTable where MyColumn = NULL will return zero even if there are any nonnull values in MyColumn

9. set ansi_warnings on|off; if set to ON warning will be displayed that aggregated functions (e.g.: min, max, avg, sum, count, rank, var, varp, st_dev, etc.) are operating with null values that can certainly alter the results.

10. set ansi_padding on|off; this option only takes affect when creating new table or new column and only works with datatype char, varchar, binary and varbinary. if set to ON blanks at the end of the strings will be treated as blank, respectively.

11. set transaction isolation level read committed|read uncommitted|repeatable read|serializable affects how your data are read from the table in cases of locks.

read committed - is a shared lock is held against the table from where data are being read in order to avoid dirty reads.

read uncommitted - implementes a dirty read against the table you are quering, meaining no shared and no exclusive locks are held. one can run select against the table when the other query is inserting into the table and the select query might not get the same results is this option is set to "read committed". this option has the same effect if you use query option "with (nolock)" on each from and each join statement.

repeatable read - locks are placed on all the tables used by running query and preventing and data change. but new rows can be inserted but will not be included in results of running query.

serialized - most restrictive of all four locks. it sets locks on all the tables running query is working on and releases the locks when query is finished.

Happy tweaking.

Team Foundation Server 2010

 Started working intensitively on Team Foundation Server 2010 as a built-in server with Visual Studio 2010 Ultimate version and i'm loving it.

Building the whole solution within C# with all supported SQL server neccessities and having options for versioning, file managing, comments, etc. in big enterpreus is trully worth using.

Setting Customer Order based on their purchase

Setting the Customer Order based on their purchase over a specific channel was suppose to be relatively straightforward task,  but it turned out slightly different.

Especially when customer's order sorting must be applied.

Sample data is following:

--CREATE SAMPLE TABLE
CREATE TABLE purchase
(partnerID int
,OrderIDOrder int
,saleschannel varchar(20)
)
 
--GET SOME DATA IN TABLE
INSERT INTO purchase
           SELECT '90001266',1,'OUTBOUND'
union ALL  SELECT '90001266',2,'OUTBOUND'
union ALL  SELECT '90001266',3,'BACKEND'
union ALL  SELECT '90001250',1,'TELEVISION'
union ALL  SELECT '90001250',2,'TELEVISION'
union ALL  SELECT '90001250',3,'OUTBOUND'
union ALL  SELECT '90001250',4,'PRINTED MEDIA'
union ALL  SELECT '90001250',5,'OUTBOUND'
union ALL  SELECT '90001249',1,'OUTBOUND'
union ALL  SELECT '90001249',2,'BACKEND'
union ALL  SELECT '90001249',3,'BACKEND'
union ALL  SELECT '90001176',1,'OUTBOUND'
union ALL  SELECT '90001176',2,'OUTBOUND'
union ALL  SELECT '90001176',3,'TELEVISION'
union ALL  SELECT '90001176',4,'BACKEND'
union ALL  SELECT '90001154',1,'OUTBOUND'
union ALL  SELECT '90001154',2,'BACKEND'
union ALL  SELECT '90001154',3,'INTERNET'
union ALL  SELECT '90001154',4,'INTERNET'
union ALL  SELECT '10001154',1,'INTERNET'
union ALL  SELECT '10001154',2,'INTERNET'
union ALL  SELECT '10001151',1,'INTERNET'
union ALL  SELECT '10001151',2,'BACKEND'
union ALL  SELECT '10001152',1,'INTERNET'
union ALL  SELECT '10001153',1,'INTERNET'
union ALL  SELECT '10001153',2,'INTERNET'
union ALL  SELECT '10001153',3,'TELEVISION'
--(27 row(s) affected)

What i wanted to achieve was following customer classification based on ther purchases over channels. Customer gets assigned:

1 - for first time purchase

2 - for any sequential purchase over same channel

3 - for any sequential purchase over different channel.

e.g.: My first purchase was done over Television -> assigned 1, second was done over Internet -> assigned 3, third purchase was done again over Television -> still assigned 3.

or e.g.: My first purchase was done over Television -> assigned 1, second was done again over Television -> assigned 2 and third over Outbound -> assigned 3.

Solution was done for SQL2005 or higher.

--SOLUTION
;with cte_purchase (partnerID, orderIDorder, Saleschannel, neworder)
as
( 
      select
             p0.partnerID
            ,p0.orderIDorder
            ,p0.Saleschannel
            ,1 as neworder
      from purchase p0
      where p0.orderIDorder = 1

      union all
    
      select
            p1.partnerID
            ,p1.orderIDorder
            ,p1.Saleschannel
            ,case when p1.Saleschannel = cte_p.Saleschannel then 2 else 3 end as neworder
      from purchase as p1
            join cte_purchase as cte_p
            on p1.partnerID = cte_p.partnerID
            where
                  p1.orderIDorder > cte_p.orderIDorder
)
select
       c.partnerID
      ,c.orderIDorder
      ,c.Saleschannel
      ,max(c.neworder) as neworder
             from cte_purchase  as c
 
group by
       partnerID
      ,orderIDorder
      ,Saleschannel
order by partnerID, orderIDorder
---(27 row(s) affected)

Determing the index size on table

Getting all the relevant information needed to see your indexes on the table:

select

ps.database_id

,ps.object_id

,o.name

,o.type_desc

,ps.index_id

,b.name as index_name

,ps.avg_fragmentation_in_percent

,ps.avg_page_space_used_in_percent

,ps.avg_fragment_size_in_pages

,ps.fragment_count

,ps.page_count

,ps.record_count

,ps.index_type_desc

,ps.min_record_size_in_bytes

,ps.max_record_size_in_bytes

,ps.avg_record_size_in_bytes --in bytes

,ps.record_count*ps.avg_record_size_in_bytes as index_size_in_bytes

,(ps.record_count*ps.avg_record_size_in_bytes)/1024 as index_size_in_KiloBytes

,(ps.record_count*ps.avg_record_size_in_bytes)/1048576 as index_size_in_MegaBytes

,(ps.record_count*ps.avg_record_size_in_bytes)/1073741824 as index_size_in_GigaBytes

,ps.alloc_unit_type_desc

,ps.partition_number

,ps.index_level

,ps.index_depth

,b.fill_factor

,b.allow_row_locks

,b.allow_page_locks

sys.dm_db_index_physical_stats (16, 926626344, null, null, 'SAMPLED') as ps -- (databaseID, ObjectID,null,null,'SAMPLED')

inner join sys.indexes as b

on ps.object_id = b.object_id

and ps.index_id = b.index_id

inner join sys.all_objects as o

on o.object_id = ps.object_id

where ps.database_id = 16 --db_id(N'') --DatabaseID

and b.object_id = 926626344 --ObjectID

order by ps.object_id