Mark Cowne

Forum Replies Created

Viewing 15 posts - 1,351 through 1,365 (of 1,439 total)

RE: Equivalent of 'DECODE' in sql

Use
CASE ... WHEN ... THEN ... ELSE ... END
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

March 27, 2008 at 5:55 am

#794524
RE: set char field stud_id to unique

Use an indexed view
http://www.sqlservercentral.com/Forums/FindPost471408.aspx
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

March 25, 2008 at 9:57 am

#793609
RE: Removing a Duplicate Record

You can remove duplicates using this
delete from mytable
where exists (select * from mytable t2
where t2.UserName=mytable.UserName
...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

March 24, 2008 at 3:00 pm

#793229
RE: Tracking a stored proc that is already running

I find this really useful
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

March 20, 2008 at 4:52 am

#791913
RE: unique index with multiple nulls

Try using an indexed view
create view dbo.myview
with schemabinding
as
select TvsFormNo
from dbo.TvsRecords
where TvsFormNo is not null
go
create unique clustered index uix_myview on dbo.myview (TvsFormNo)
Also...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

March 19, 2008 at 1:32 am

#791307
RE: Finding a sequence of rows that match another sequence of rows

Maybe this ?
select r.SessionID
from Requests r
inner join PageFlow p on p.MatchValue=r.PageUrl
group by r.SessionID
having count(distinct r.PageUrl)=(select count(distinct MatchValue) from PageFlow)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

March 13, 2008 at 8:02 am

#789059
RE: XML Workshop XIV - Generating an XML Tree

Personally I avoid FOR XML EXPLICIT in SQL Server 2005 and use FOR XML PATH instead
CREATE FUNCTION dbo.GetSubTree(@id int)
RETURNS XML
BEGIN RETURN
(SELECT id AS "@id",
...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

February 29, 2008 at 3:49 am

#783658
RE: Group by where MAX can be the same

Using a different approach
select X.h_id, X.l_id, X.ton
from #t1 X
where not exists (select * from #t1 Y
...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

February 28, 2008 at 10:15 am

#783346
RE: Complex Search Query - PLEASE HELP!

Sample data would help...
select g.ID,g.Name
from Groups g
where exists (select 1
from Search s
...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

February 28, 2008 at 8:15 am

#783199
RE: Select records that dont exist based off of max and min date ranges.

Maybe this?
select T1.client_id,T1.services_rendered,T1.date
from T1
where not exists (select T2.client_id
from T2
...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

February 28, 2008 at 6:26 am

#783126
RE: retrieving duplicate consecutive records.

Try this. Probably not very efficient on large data sets though
WITH CTE AS
(SELECT checkInOutID,
employeeID,
isCheckInOut,
...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

February 26, 2008 at 9:45 am

#782076
RE: Exporting data to XML

Easier to use FOR XML PATH
select b.externalid as "@externalid",
b.ititid as "@ititid",
b.containstype as "@containstype",
...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

February 26, 2008 at 4:19 am

#781909
RE: Help with grouping non-contiguous ranges

See if this helps
create table missingdates(person varchar(10),division varchar(10), dt datetime)
insert into missingdates(person,division,dt)
select 'John','Div1','20080711' union all
select 'John','Div1','20080712' union all
select 'John','Div1','20080713' union all
select 'John','Div1','20080805' union all
select 'John','Div2','20081211' union all
select 'John','Div2','20081212'
select lbound.person,
...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

February 15, 2008 at 4:16 pm

#779040
RE: Order table items in a "TreeView" way. How?

create table #Teams (Id int, ParentId int, name varchar(100), Position int)
insert into #Teams(Id, ParentId, Name,Position)
select 1, 0, 'Items', 1 union all
select 2, 0, 'Standings', 3 union all
select 3, 0,...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

February 14, 2008 at 3:23 am

#778253
RE: Generating Missing Dates and Numbers

Really don't know. It works on the all of the 2005 machines I've tried and fails on the 2000 machine with the exact same error.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

February 11, 2008 at 10:30 am

#776991

Viewing 15 posts - 1,351 through 1,365 (of 1,439 total)