Tomaz.tsql

viewing files on disk

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.

Posted: Jan 01 2013, 07:41 PM by Tomaz.tsql | with no comments
Filed under: ,
happy new year 2013

 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

Delete trigger does not catch table truncation

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 test_del_trigger
SELECT 'la la l1' UNION ALL
SELECT 'la la l2' UNION ALL
SELECT 'la la l3' UNION ALL
SELECT 'la la l4';
GO 10


CREATE TABLE test_del_trigger_log
(id INT IDENTITY(1,1)
,deleteTime SMALLDATETIME DEFAULT(GETDATE())
,nof_rows INT
CONSTRAINT pk_test_del_trigger_log PRIMARY KEY (id)
);
GO


CREATE TRIGGER test_del_trigger_Dtrig ON test_del_trigger
FOR DELETE
AS
-- SIMPLE LOG
INSERT INTO test_del_trigger_log (nof_rows)
SELECT @@rowcount  AS nof_rows
GO


-- INITIAL DATA
SELECT * FROM test_del_trigger
SELECT * FROM test_del_trigger_log
GO



-- DELETE 1 ROW;
-- DATA IS LOGGED IN LOG TABLE
DELETE FROM test_del_trigger
WHERE id = 1;

SELECT * FROM test_del_trigger
SELECT * FROM test_del_trigger_log
GO


-- DELETE 11 (and higher) ROWS;
-- DATA IS LOGGED IN LOG TABLE
DELETE FROM test_del_trigger
WHERE id > 10


SELECT * FROM test_del_trigger
SELECT * FROM test_del_trigger_log
GO


-- TABLE TRUNCATION
-- NO LOG
TRUNCATE TABLE test_del_trigger

SELECT * FROM test_del_trigger
SELECT * FROM test_del_trigger_log
GO

simple word randomization based on dictionary

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.type = 'P'
                        and sv.number between 1 and 30 -- max word length
                        order by (select (abs(checksum(newid())))%1000) desc
                        for xml path('')
                ) as x(n)

 

CTP3

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.

Reoccuring visits

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 sample data, the desired
result should look like this:

dt         numvisits   added       removed     remained
---------- ----------- ----------- ----------- -----------
2011-06-01 3           3           0           0
2011-06-02 2           0           1           2
2011-06-03 2           1           1           1
2011-06-04 0           0           2           0
2011-06-05 0           0           0           0
2011-06-06 0           0           0           0
2011-06-07 2           2           0           0
2011-06-08 3           2           1           1

Observe that there’s a key defined on dt and visitor, meaning that you store only one
occurrence at most per visitor and day. Also note that there is a possibility that
during some days there would be no visitors.

 

-- DDL and sample data for DailyVisits table
SET NOCOUNT ON;
USE TC;
IF OBJECT_ID('dbo.DailyVisits', 'U') IS NOT NULL
  DROP TABLE dbo.DailyVisits;
GO

CREATE TABLE dbo.DailyVisits
(
  dt      DATEtime        NOT NULL,
  visitor VARCHAR(10) NOT NULL,
  CONSTRAINT PK_DailyVisits PRIMARY KEY(dt, visitor)
);



INSERT INTO dbo.DailyVisits(dt, visitor) VALUES
     ('20110601', 'A'),
     ('20110601', 'B'),
     ('20110601', 'C'),
     ('20110602', 'A'),
     ('20110602', 'C'),
     ('20110603', 'A'),
     ('20110603', 'D'),
     ('20110607', 'A'),
     ('20110607', 'D'),
     ('20110608', 'D'),
     ('20110608', 'E'),
     ('20110608', 'F');

 

two simple solutions:


-- SOLUTION #1


;with dt_cte as
-- insert empty/missing dates
(select
    min(dt) as dt_
    ,max(dt) as dt_m
from dailyvisits
union all
select
     dt_+1 as dt_
    ,dt_m
from dt_cte
where
    dt_+1 <= dt_m)
-- calculating added, removed and remained users
select
date_.dt_ as date
,count(distinct dv.visitor) as numvisits
,count(distinct dv.visitor)-(sum( case when (dv1.visitor = dv.visitor and dv.visitor is not null and dv1.visitor is not null) then 1 else 0 end)) as added
,count(distinct dv1.visitor) - sum(case when dv.visitor = dv1.visitor then 1 else 0 end) as removed
,sum(case when dv.visitor = dv1.visitor then 1 else 0 end) as remained

from
dt_cte as date_
left hash join dailyvisits as dv
on date_.dt_ = dv.dt
left hash join dailyvisits as dv1
on date_.dt_ = dateadd(day,1,dv1.dt)
group by date_.dt_



-- SOLUTION #2

-- calculating added, removed and remained users
select
date_.dt_ as date
,count(distinct dv.visitor) as numvisits
,count(distinct dv.visitor)-(sum( case when (dv1.visitor = dv.visitor and dv.visitor is not null and dv1.visitor is not null) then 1 else 0 end)) as added
,count(distinct dv1.visitor)-sum(case when dv.visitor = dv1.visitor then 1 else 0 end) as removed
,sum(case when dv.visitor = dv1.visitor then 1 else 0 end) as remained

from
-- insert empty/missing dates
(
    select
         (select min(dt) from dailyvisits)+number as dt_
    from master..spt_values as b with (nolock readuncommitted)
    where
        [type] = 'P'
    and number between 1 and (select datediff(day,(select min(dt) from dailyvisits), (select max(dt) from dailyvisits)))
) as date_
left hash join dailyvisits as dv
on date_.dt_ = dv.dt
left hash join dailyvisits as dv1
on date_.dt_ = dateadd(day,1,dv1.dt)
group by date_.dt_
 

 

Posted: Jul 05 2011, 07:23 PM by Tomaz.tsql | with no comments
Filed under: , , ,
DateTime Tally Table

 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
from tab
where rn < 365
)
insert into dt(dt)
select dt
from tab
option (maxrecursion 365)


-- 3. way
;create table tc..dt
(id int identity(1,1)
,dt datetime)

insert into dt(dt)
;select
    cast('2010/12/31' as datetime)+number as dt
from master..spt_values as b
where
 type = 'P' and number between 1 and 365


-- 4. way
;create table tc..dt
(id int identity(1,1)
,dt datetime)

insert into dt(dt)
select
dateadd(day, isnull((select scope_identity()),2),'2010/12/31')
go 365


-- 5. way
;create table tc..dt
(id int identity(1,1)
,dt datetime)

while  isnull(@@identity,1) <= 365
begin
    insert into dt(dt)
    select
        dateadd(day, isnull(@@identity,1),'2010/12/31') as dt
end

 

SQL Bits VII

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 to presentations and code / samples online (if presenters agree with this as well as SQL Bits agree with this)

3. try to avoid personalized computers settings (online skype messages, email messages, windows settings, windows sizes,  etc.). Some of best presenters and presentations by experts (Simon Sabin, Ramesh Meyyappan, Chriss Webb) were having from what  i've seen some problems with that. :)

4. maybe some additional reviewing would be welcome.

5. all presenters use same database (when possible). It can be bl***y AdventureWorks2008 or it can be SQLBitsWorks :) 

6. I like interactive presentation. one things, for the video or the track, it would be nice when listeners in audiance ask questions, to either (1) to pass them mic or (2) repeat the question for the video.

7. nice polo shirts. would love to have one :) 

 

Otherwise i hope i will be able to  prepare something from my side for next  SqlBits to see it in reality. I trully love SqlBits, it's really down-to-earth, case-study presentations and everything is based on experience, milage and long hours of code typing.

 

Can't wait for SqlBits VIII.


 

Posted: Feb 20 2011, 09:40 AM by Tomaz.tsql | with no comments
Filed under:
T-SQL Jokes

 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"
 Index: "baby, yes, that's the one. i'm just gonna call my friends"

 Indexianina philosophy on Fill factor 50 = "Half empty or Half full?"

 What does an execution plan say to t-sql query? "Go f**k yourself, if you are not happy with me"

 Execution plan to t-sql query is like alter-ego to self.

 

Posted: Jan 30 2011, 10:59 PM by Tomaz.tsql | with no comments
Filed under:
insert into script

My contribution to community Smile

http://www.sqlmag.com/article/sql-server/Transfer-small-amount-of-data-by-generating-the-INSERT-INTO-statement.aspx

 

Enjoy!

More Posts Next page »