Tomaz.tsql

viewing files on disk

2013-01-01T18:41:00Z

From time to time, it is very usefull to see the files on disk (folder, sub-folder, etc.) simple sql query returns the results: EXEC Xp_dirtree 'c:\asp', 1, 1 where "c:\asp" is a folder named "asp" on drive c:\ it will return all files under root folder asp....(read more)

happy new year 2013

2012-12-21T18:44:00Z

with a special new year's card at: http://www.simple-talk.com/blogs/2012/12/21/rudolph-the-red-nosed-tester-and-other-festive-arrangements/ see you in 2013...(read more)

Delete trigger does not catch table truncation

2012-08-30T07:37:00Z

Sample shows table truncation will not fire delete trigger. USE AdventureWorks; GO -- STAGING IF EXISTS (SELECT * FROM sys.objects WHERE name = 'est_del_trigger_log' AND type = 'U') DROP TABLE test_del_trigger_log; GO IF EXISTS (SELECT * FROM sys.objects WHERE name = 'est_del_trigger' AND type = 'U') DROP TABLE test_del_trigger; GO CREATE TABLE test_del_trigger (id INT IDENTITY(1,1) ,tkt VARCHAR(10) CONSTRAINT pk_test_del_trigger PRIMARY KEY (id) ); GO INSERT INTO...(read more)

simple word randomization based on dictionary

2012-01-01T16:00:00Z

Based on list of words, one can randomize the letters in the word using simple cross apply. ;with dictionary as ( select 'soundgarden' as word union all select 'impossible' as word union all select 'apple' as word union all select 'microsoft' as word union all select 'mices' as word union all select 'ITCrowds' as word ) select t1.word ,x.n from dictionary as t1 cross apply ( select substring(t1.word,sv.number,1) from master..spt_values as sv where sv...(read more)

CTP3

2011-07-22T05:25:00Z

I must admit that CTP3 Denaliwill bring some usefull T-SQL features. which i'm looking forward very much and just can't wait for the final release....(read more)

Reoccuring visits

2011-07-05T17:23:00Z

Itzik Ben-Gan posted this game on SQL magazine previous week, stating: The challenge is to write a query that works with two input arguments @from and @to holding the start and end dates of a date range, and calculates, for each day, various statistics in respect to the previous day. Specifically, how many visitors visited the site that day, how many new visitors were added compared to the previous day, how many visitors were removed compared to the previous day, and how many remained. For the given...(read more)

DateTime Tally Table

2011-07-04T20:15:00Z

Five ways to generate Date Tally Table: -- 1. way ;create table tc..dt (id int identity(1,1) ,dt datetime) insert into dt(dt) select a.dt+b.number as dt from (select cast('2010/12/31' as datetime) as dt ) as a cross join master..spt_values as b where type = 'P' and number between 1 and 365 -- 2. way ;create table tc..dt (id int identity(1,1) ,dt datetime) ;with tab as ( select cast('2011-1-1' as datetime) as dt, 1 as rn union all select dateadd(day,1,dt) as dt,rn + 1 as rn...(read more)

SQL Bits VII

2011-02-20T08:40:00Z

I was very happy to dive in into SQL Bits VII. Several interesting presentations, interesting concepts, advices and most of all interesting people. Some things actually disturb me. 1. the outlook of all presentations. I would recommend to have standardized outlook of all presentations for each of the presenter. Meaning, to have a general rules of PPT design (fonts, views, etc.), general diagrams, pictures that can be used by everyone (something like techEd introduced as well as SQL PASS). 2. access...(read more)

T-SQL Jokes

2011-01-30T21:59:00Z

SQL Table walks to a psychiatrist dr. Index Table: "Doctor, I have a problem" Dr: "what kind a problem?" Table: "I'm a mess. I have things all over the place, i always look for my stuff" Dr. "No problem. I will get you in order". Index and table are reading a book "index-sutra" Table: Oh, baby tonight we can try a clustered position" Index: "yeah baby, we can also try covered position" Table: "or maybe multiple clustered position"...(read more)

insert into script

2011-01-18T22:03:00Z

My contribution to community http://www.sqlmag.com/article/sql-server/Transfer-small-amount-of-data-by-generating-the-INSERT-INTO-statement.aspx Enjoy!...(read more)