Why does the following fail the parser ?
CREATE PROC [dbo].[GetQuestionAnswers]
@.QuestionID int
AS
DECLARE @.TotalCount int
SELECT @.TotalCount = Sum(AnswerCount)
FROM Answer
WHERE QuestionID = @.QuestionID
IF (@.TotalCount = 0)
BEGIN
SET @.TotalCount = 1
END
SELECT AnswerID, AnswerText, CAST(AnswerCount / @.TotalCount AS double) As
AnswerFraction
FROM Answer
WHERE QuestionID = @.QuestionID
ORDER BY Rank
GODouble is not a Transact-SQL system data type. Use float or real for
approximate values or decimal for exact numeric values.
Hope this helps.
Dan Guzman
SQL Server MVP
"John Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:ecBae3oLFHA.4028@.tk2msftngp13.phx.gbl...
> Why does the following fail the parser ?
> CREATE PROC [dbo].[GetQuestionAnswers]
> @.QuestionID int
> AS
> DECLARE @.TotalCount int
> SELECT @.TotalCount = Sum(AnswerCount)
> FROM Answer
> WHERE QuestionID = @.QuestionID
> IF (@.TotalCount = 0)
> BEGIN
> SET @.TotalCount = 1
> END
> SELECT AnswerID, AnswerText, CAST(AnswerCount / @.TotalCount AS double) As
> AnswerFraction
> FROM Answer
> WHERE QuestionID = @.QuestionID
> ORDER BY Rank
> GO
>|||Hi Dan, and thanks for the response.
I should have phrased my question differently (because I quickly found out
that Double is not SQLDataType).
How to write SQL that creates a float or real or decimal psuedocolumn equal
to the quotient of two integer values (columns of integer type, or
otherwise) -- I do not want truncation to the nearest integer.
Thanks.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23gnckLpLFHA.1308@.tk2msftngp13.phx.gbl...
> Double is not a Transact-SQL system data type. Use float or real for
> approximate values or decimal for exact numeric values.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
> news:ecBae3oLFHA.4028@.tk2msftngp13.phx.gbl...
>|||Use
CAST(AnswerCount / @.TotalCount AS Decimal(12,2))
Madhivanan|||John,
It will not work to cast after dividing. You need to cast or
force implicit conversion before dividing.
Won't work:
cast(AnswerCount / @.TotalCount as double precision)
Either of these will work:
cast(AnswerCount as double precision) / @.TotalCount
(1e0*AnswerCount)/@.TotalCount
Steve Kass
Drew University
John A Grandy wrote:
>Hi Dan, and thanks for the response.
>I should have phrased my question differently (because I quickly found out
>that Double is not SQLDataType).
>How to write SQL that creates a float or real or decimal psuedocolumn equal
>to the quotient of two integer values (columns of integer type, or
>otherwise) -- I do not want truncation to the nearest integer.
>Thanks.
>"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>news:%23gnckLpLFHA.1308@.tk2msftngp13.phx.gbl...
>
>
>|||CREATE PROCEDURE GetQuestionAnswers ( @.my_question_id INTEGER)
AS
SELECT A1.answer_id, A1.answer_txt,
(A1.answer_count
/ (SELECT CASE WHEN SUM(A2.answer_count) = 0
THEN CAST (1.00 AS REAL)
ELSE SUM(A2.answer_count) END)
FROM Answers AS A2
WHERE A2.question_id = A1.question_id))
AS answer_fraction
FROM Answers AS A1
WHERE A1.question_id = @.my_question_id;
Untested; the CASE expression will resolve to the highest data type in
the THEN or ELSE clauses. This saves a local variable and lets the
optimizer do its thing.
Showing posts with label dbo. Show all posts
Showing posts with label dbo. Show all posts
Sunday, March 25, 2012
Monday, March 19, 2012
composite indexes - a subtle question
I have a table like so
CREATE TABLE [dbo].[account] (
[pty_id] [int] NOT NULL ,
[sort_code] [int] NOT NULL ,
[account_no] [int] NOT NULL ,
[account_open_dt] [datetime] NULL ,
[account_close_dt] [datetime] NULL ,
[account_nm] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[market_sector] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
[market_segment] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
[category_code] [smallint] NULL ,
[prime] [char] (1) COLLATE Latin1_General_CI_AS NULL
CONSTRAINT PK_ACCOUNT PRIMARY KEY CLUSTERED (sort_code, account_no)
) ON [PRIMARY]
as you can see there's a primary key clustered index on account_no and
sort_code
I can link it up to any other table efficiently which also has sort_code and
account_no as part of a single index (primary or otherwise)
ON (a.sort_code= b.sortCode) ANd (a.account_no = b.account_no)
etc...
and that will work fine...
Here's my question
if account_no is part or the sort_code/Account_no composite key (and
therefore index)
could I link another table to only the account_no column and still get some
indexing benefit
i.e can you join on a single column of a multiple column index and still get
some help from that composite index or does that composite index only work a
s
a single entity and in fact you need to create an extra nonclustered index
for that column on top of the other index it's participating in to get the
benefit of indexing on that column...
phew
I hope this makes sense
any help would be great appreciated
Regards and thanks in advance,
CharlesACharles,
When joining to the first column of a multi-column index, some
of the same optimizations are available--for example, a merge
join (if the other table also has a supporting index) or nested loop
joins with index s
s. Depending on the exact query, the two-column
index may be just as useful as a separate one-column index or a
little less useful (typically because the two-column index takes up
more data pages because of duplications in the first column and the
presence of the second column even when duplicates are few), but it
should still help in most cases. The only real way to be sure is
to create the additional index and measure the performance.
Steve Kass
Drew University
CharlesA wrote:
>I have a table like so
>CREATE TABLE [dbo].[account] (
> [pty_id] [int] NOT NULL ,
> [sort_code] [int] NOT NULL ,
> [account_no] [int] NOT NULL ,
> [account_open_dt] [datetime] NULL ,
> [account_close_dt] [datetime] NULL ,
> [account_nm] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [market_sector] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
> [market_segment] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
> [category_code] [smallint] NULL ,
> [prime] [char] (1) COLLATE Latin1_General_CI_AS NULL
> CONSTRAINT PK_ACCOUNT PRIMARY KEY CLUSTERED (sort_code, account_no)
> ) ON [PRIMARY]
>
>
>as you can see there's a primary key clustered index on account_no and
>sort_code
>I can link it up to any other table efficiently which also has sort_code an
d
>account_no as part of a single index (primary or otherwise)
>ON (a.sort_code= b.sortCode) ANd (a.account_no = b.account_no)
>etc...
>and that will work fine...
>Here's my question
>if account_no is part or the sort_code/Account_no composite key (and
>therefore index)
>could I link another table to only the account_no column and still get some
>indexing benefit
>i.e can you join on a single column of a multiple column index and still ge
t
>some help from that composite index or does that composite index only work
as
>a single entity and in fact you need to create an extra nonclustered index
>for that column on top of the other index it's participating in to get the
>benefit of indexing on that column...
>phew
>I hope this makes sense
>any help would be great appreciated
>Regards and thanks in advance,
>CharlesA
>|||Charles,
Can you recreate your PK as (account_no, sort_code)? What are the
cardinalities of these 2 columns?|||Charles,
I know you'd like an immediate answer, but it might really help to
watch Kimberly Tripp's (SQL Server MVP) presentation at the following
website. I'm in the process of designing indexes and it was incredibly
helpful to watch the presentation. She keeps it simple but I finally
got it...
http://www.microsoft.com/uk/technet...aspx?videoid=29
Other links:
http://msevents.microsoft.com/CUI/E...e=en-U
S
http://www.sqlskills.com/blogs/kimb...
3-a58ba7b1265e
(The presentation took me a while to download and it is an hour long
but totally worth it.)|||Hi,
Whether you get performance benifit or not depends on the location of the
column in the order of the index key.
You want to join Account_no from tbl1 with Account no in tbl2 with
sort_code/Account_no as an index.
If the left most column in the index is sort_code i.e
Index(sort_code,Account_no) , you don't find any use of the index, it will
still go for an idex scan.
But if your index had been Index(Account_no,sort_code)
then this index is sufficient for joining Account_no alone or Account_no and
sort_code.
Hope I made sense :)|||Thanks everyone for your answers, much appreciated...
I will download that presentation when I'm at home, I've been dying to
understand indexing properly for ages now
as I say, thanks All
Regards,
CharlesA
CREATE TABLE [dbo].[account] (
[pty_id] [int] NOT NULL ,
[sort_code] [int] NOT NULL ,
[account_no] [int] NOT NULL ,
[account_open_dt] [datetime] NULL ,
[account_close_dt] [datetime] NULL ,
[account_nm] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[market_sector] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
[market_segment] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
[category_code] [smallint] NULL ,
[prime] [char] (1) COLLATE Latin1_General_CI_AS NULL
CONSTRAINT PK_ACCOUNT PRIMARY KEY CLUSTERED (sort_code, account_no)
) ON [PRIMARY]
as you can see there's a primary key clustered index on account_no and
sort_code
I can link it up to any other table efficiently which also has sort_code and
account_no as part of a single index (primary or otherwise)
ON (a.sort_code= b.sortCode) ANd (a.account_no = b.account_no)
etc...
and that will work fine...
Here's my question
if account_no is part or the sort_code/Account_no composite key (and
therefore index)
could I link another table to only the account_no column and still get some
indexing benefit
i.e can you join on a single column of a multiple column index and still get
some help from that composite index or does that composite index only work a
s
a single entity and in fact you need to create an extra nonclustered index
for that column on top of the other index it's participating in to get the
benefit of indexing on that column...
phew
I hope this makes sense
any help would be great appreciated
Regards and thanks in advance,
CharlesACharles,
When joining to the first column of a multi-column index, some
of the same optimizations are available--for example, a merge
join (if the other table also has a supporting index) or nested loop
joins with index s
s. Depending on the exact query, the two-columnindex may be just as useful as a separate one-column index or a
little less useful (typically because the two-column index takes up
more data pages because of duplications in the first column and the
presence of the second column even when duplicates are few), but it
should still help in most cases. The only real way to be sure is
to create the additional index and measure the performance.
Steve Kass
Drew University
CharlesA wrote:
>I have a table like so
>CREATE TABLE [dbo].[account] (
> [pty_id] [int] NOT NULL ,
> [sort_code] [int] NOT NULL ,
> [account_no] [int] NOT NULL ,
> [account_open_dt] [datetime] NULL ,
> [account_close_dt] [datetime] NULL ,
> [account_nm] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [market_sector] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
> [market_segment] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
> [category_code] [smallint] NULL ,
> [prime] [char] (1) COLLATE Latin1_General_CI_AS NULL
> CONSTRAINT PK_ACCOUNT PRIMARY KEY CLUSTERED (sort_code, account_no)
> ) ON [PRIMARY]
>
>
>as you can see there's a primary key clustered index on account_no and
>sort_code
>I can link it up to any other table efficiently which also has sort_code an
d
>account_no as part of a single index (primary or otherwise)
>ON (a.sort_code= b.sortCode) ANd (a.account_no = b.account_no)
>etc...
>and that will work fine...
>Here's my question
>if account_no is part or the sort_code/Account_no composite key (and
>therefore index)
>could I link another table to only the account_no column and still get some
>indexing benefit
>i.e can you join on a single column of a multiple column index and still ge
t
>some help from that composite index or does that composite index only work
as
>a single entity and in fact you need to create an extra nonclustered index
>for that column on top of the other index it's participating in to get the
>benefit of indexing on that column...
>phew
>I hope this makes sense
>any help would be great appreciated
>Regards and thanks in advance,
>CharlesA
>|||Charles,
Can you recreate your PK as (account_no, sort_code)? What are the
cardinalities of these 2 columns?|||Charles,
I know you'd like an immediate answer, but it might really help to
watch Kimberly Tripp's (SQL Server MVP) presentation at the following
website. I'm in the process of designing indexes and it was incredibly
helpful to watch the presentation. She keeps it simple but I finally
got it...
http://www.microsoft.com/uk/technet...aspx?videoid=29
Other links:
http://msevents.microsoft.com/CUI/E...e=en-U
S
http://www.sqlskills.com/blogs/kimb...
3-a58ba7b1265e
(The presentation took me a while to download and it is an hour long
but totally worth it.)|||Hi,
Whether you get performance benifit or not depends on the location of the
column in the order of the index key.
You want to join Account_no from tbl1 with Account no in tbl2 with
sort_code/Account_no as an index.
If the left most column in the index is sort_code i.e
Index(sort_code,Account_no) , you don't find any use of the index, it will
still go for an idex scan.
But if your index had been Index(Account_no,sort_code)
then this index is sufficient for joining Account_no alone or Account_no and
sort_code.
Hope I made sense :)|||Thanks everyone for your answers, much appreciated...
I will download that presentation when I'm at home, I've been dying to
understand indexing properly for ages now
as I say, thanks All
Regards,
CharlesA
Thursday, March 8, 2012
Complex UPDATE
Given the 2 tables Sponsor and SponsorEvent with SponsorID on SponsorEvent
as a FK
CREATE TABLE [dbo].[Sponsor] (
[SponsorID] [int] NOT NULL ,
[SponsorCode] [char] (15) NOT NULL ,
[BrandID] [int] NOT NULL ,
[FirstDate] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Sponsor] WITH NOCHECK ADD
CONSTRAINT [PK_Sponsor] PRIMARY KEY CLUSTERED
(
[SponsorID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE TABLE [dbo].[SponsorEvent] (
[Date] [int] NOT NULL ,
[BarbChanID] [int] NOT NULL ,
[StartTime] [int] NOT NULL ,
[AreaFlags] [smallint] NOT NULL ,
[PlatformFlags] [tinyint] NOT NULL ,
[EndTime] [int] NOT NULL ,
[SponsorID] [int] NOT NULL ,
[SponsorICodeID] [int] NOT NULL ,
[SponsorINameID] [int] NOT NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[SponsorEvent] WITH NOCHECK ADD
CONSTRAINT [PK_SponsorEvent] PRIMARY KEY CLUSTERED
(
[Date],
[BarbChanID],
[StartTime],
[AreaFlags],
[PlatformFlags]
) WITH FILLFACTOR = 90 ON [PRIMARY]
what is wrong with this syntax
update Sponsor set FirstDate = att.[Date]
from (
SELECT SponsorID,MIN([DATE]) From SponsorEvent GROUP by SponsorID)
att,Sponsor s
where s.SponsorID = att.SponsorID
Query Analyser complains of
"No column was specified for column 2 of 'att'."
I am trying to group by SponsorID on SponsorEvent, work out what the minimum
date is on SponsorEvent and update [Date] on Sponsor with this where
SponsorID is in common.
Thanks
Stephen Howe> Query Analyser complains of
> "No column was specified for column 2 of 'att'."
Isn't it "No column NAME was specified...?"
This happens when you apply a calculation on a column and don't include an
alias. The outer query doesn't see a column named "Date", it only sees the
expression MIN([Date]), which is not the same thing.
How about :
UPDATE Sponsor
SET FirstDate = att.FirstDate
FROM Sponsor
INNER JOIN
(
SELECT SponsorID, [Date] = MIN([DATE])
FROM SponsorEvent
GROUP by SponsorID
) Att
ON s.SponsorID = att.SponsorID
Or, better yet, instead of having to run this update every single time the
SponsorEvent changes, drop the column firstdate from the sponsor table.
There is no reason to store this redundant information when you can always
get it directly from SponsorEvent.
As an aside, I recommend renaming the column [Date]. It is never a good
idea to use reserved words as column names.
A|||Sorry, forgot the s:
> FROM Sponsor
Should be
FROM Sponsor s|||Thanks for response
> Isn't it "No column NAME was specified...?"
No. Definitely not. What I wrote was copy and pasted out of QA.
There is a message in colour red just before. The full message is
Server: Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 'att'.
> How about :
> UPDATE Sponsor
> SET FirstDate = att.FirstDate
> FROM Sponsor
> INNER JOIN
> (
> SELECT SponsorID, [Date] = MIN([DATE])
> FROM SponsorEvent
> GROUP by SponsorID
> ) Att
> ON s.SponsorID = att.SponsorID
Does not work. I changed text to
SET FirstDate = att.[Date]
FROM Sponsor s
and now I get
"Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'att' does not match with a table name or alias name used
in the query."
> Or, better yet, instead of having to run this update every single time the
> SponsorEvent changes, drop the column firstdate from the sponsor table.
> There is no reason to store this redundant information when you can always
> get it directly from SponsorEvent.
Yes you are right and I would like to.
But the reason why is that we have a analogous situation for tables where
Sponsor and SponsorEvent which have SponsorID in common
is reproduced with
SpotFilm and Spot which have SpotFilmID in common
SpotFilm and Sponsor have identical columns apart from the ID fields. It is
also such that if you concatenated them, the ID's are unique, no row in
common.
I say "somewhat symmetrical" because the difference is that data in
SponsorEvent is split between Spot and Slot.
Spot as a table has over 100 million rows (and gets larger per w).
Working out MIN([Date]) for each SpotFilmID on Spot is a triple-join between
Spot,Slot and SpotFilm, and we need to export that every Thursday. FirstDate
just happens to be a compromise. I know what you are saying, and I agree in
principal. At some point this year the server will be upgraded from SQL
Server 7 to 2000 and maybe the optimisation might be better.
> As an aside, I recommend renaming the column [Date]. It is never a good
> idea to use reserved words as column names.
I know. Noted. We have a table called [Break] which I am loathe to change.
Granted it is a keyword, but in the industry I am in "Break" is the most
descriptive word there is for the data it contains. They really are called
"Commercial Breaks". But I will change [Date] :-)
Stephen Howe|||What, do you have a case sensitive collation? Try using att everywhere and
no Att vs. att?
This works fine for me. You might want to provide similar DDL in the future
to make it easier for others to reproduce your scenario and test their
results. (See http://www.aspfaq.com/5006)
USE Tempdb
GO
CREATE TABLE Sponsor
(
SponsorID INT,
FirstDate SMALLDATETIME
)
GO
CREATE TABLE SponsorEvent
(
SponsorID INT,
[Date] SMALLDATETIME
)
GO
SET NOCOUNT ON
INSERT Sponsor SELECT 1, NULL
INSERT Sponsor SELECT 2, NULL
INSERT Sponsor SELECT 3, NULL
INSERT Sponsor SELECT 4, NULL
INSERT SponsorEvent SELECT 1, '20010501'
INSERT SponsorEvent SELECT 1, '20010601'
INSERT SponsorEvent SELECT 1, '20040801'
INSERT SponsorEvent SELECT 3, '20040701'
INSERT SponsorEvent SELECT 3, '20010601'
INSERT SponsorEvent SELECT 4, '20030801'
GO
UPDATE Sponsor
SET FirstDate = att.FirstDate
FROM Sponsor s
INNER JOIN
(
SELECT SponsorID, FirstDate = MIN([Date])
FROM SponsorEvent
GROUP BY SponsorID
) att
ON s.SponsorID = att.SponsorID
GO
SELECT * FROM Sponsor
GO
DROP TABLE SponsorEvent, Sponsor
GO
On 3/12/05 12:57 PM, in article ORy87zyJFHA.2716@.TK2MSFTNGP15.phx.gbl,
"Stephen Howe" <stephenPOINThoweATtns-globalPOINTcom> wrote:
> Thanks for response
>
> No. Definitely not. What I wrote was copy and pasted out of QA.
> There is a message in colour red just before. The full message is
> Server: Msg 8155, Level 16, State 2, Line 1
> No column was specified for column 2 of 'att'.
>
> Does not work. I changed text to
> SET FirstDate = att.[Date]
> FROM Sponsor s
> and now I get
> "Server: Msg 107, Level 16, State 2, Line 1
> The column prefix 'att' does not match with a table name or alias name use
d
> in the query."
>
> Yes you are right and I would like to.
> But the reason why is that we have a analogous situation for tables where
> Sponsor and SponsorEvent which have SponsorID in common
> is reproduced with
> SpotFilm and Spot which have SpotFilmID in common
> SpotFilm and Sponsor have identical columns apart from the ID fields. It i
s
> also such that if you concatenated them, the ID's are unique, no row in
> common.
> I say "somewhat symmetrical" because the difference is that data in
> SponsorEvent is split between Spot and Slot.
> Spot as a table has over 100 million rows (and gets larger per w).
> Working out MIN([Date]) for each SpotFilmID on Spot is a triple-join between
> Spot,Slot and SpotFilm, and we need to export that every Thursday. FirstDa
te
> just happens to be a compromise. I know what you are saying, and I agree i
n
> principal. At some point this year the server will be upgraded from SQL
> Server 7 to 2000 and maybe the optimisation might be better.
>
> I know. Noted. We have a table called [Break] which I am loathe to change.
> Granted it is a keyword, but in the industry I am in "Break" is the most
> descriptive word there is for the data it contains. They really are called
> "Commercial Breaks". But I will change [Date] :-)
> Stephen Howe
>
>
>|||> Sorry, forgot the s:
>
> Should be
> FROM Sponsor s
Thanks Aaaron, done it
UPDATE Sponsor
SET FirstDate = att.[Date]
FROM
(SELECT SponsorID, [Date] = MIN([DATE])
FROM SponsorEvent
GROUP by SponsorID
) Att INNER JOIN Sponsor s ON Att.SponsorID=s.SPonsorID|||> What, do you have a case sensitive collation? Try using att everywhere
and
> no Att vs. att?
I don't think we do. Anything to do with SQL Server 7 not recognising the
syntax?
Perhaps SQL Server 2000 is "improved" in that order does not matter
I changed the order putting Sponsor s last, (after the SELECT .. GROUP BY)
and at that point it recognised it.
Strange
But thanks
Stephen|||Date is not just a reserved word, it is too vague to be a valid data
element name -- date of what' Is there only one sponsor, as you
showed with a singular name? If you were writing SQL instead of a
strange dialect, would this be what you meant? It is a bad practice to
name relationship tables by concatenating names together -- always ask
if the relationship has its own name. Endorsements, sponsorships, etc.
UPDATE Sponsors
SET firstdate -- of what'
= (SELECT MIN(foobar_date)
FROM Endorsements AS E
WHERE E.sponsor_id = Sponsors.sponsor_id)
You might want to read a book on SQL and see what the standard UPDATE
syntax is.
And get a book on data modeling. Names like "SponsorICodeID" make
absolutley no sense. A code is not an identifier; it is a scalar value
on a nominal scale. Most of the rest of your DDL looks like you are
writing SQL with flags, etc. -- all the classic mistakes of someone who
does not know how to make a schema.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1110772877.481289.236930@.l41g2000cwc.googlegroups.com...
> It is a bad practice to name relationship tables by concatenating
names together
Mr. Celko,
Really? I've been doing a lot of that lately (sometimes M-to-M
relationships are stumpers). Is that a part of a standard I can take
a look at? Perhaps you could provide a weblink to an article or other
work with an extensive description of why it is bad, and the process
of doing better.
Sincerely,
Chris O.|||>> [name relationship tables by concatenating names together ] I've
been doing a lot of that lately (sometimes M-to-M relationships are
stumpers). <<
Not if you start from a data model. Relationships important enough to
be modeled tend to have names -- "Marriages" instead "ManWoman' or even
worse "CivilUnions" instead of "ManMan_WomanWoman_ManWomen". A
relationship name invites the attributes that go with the relationship;
Marriages implies a wedding date, license number, etc.
Google up ISO-11179; the principle is to name a thing for what it *is*,
not for what it *does* in a particular situation, not for hopw you
build it-- i.e. this is an "automobile", not a "TiresFrameMotor"; the
whole not the parts.
an extensive description of why it is bad, and the process of doing
better. <<
Look for an entire book on SQL style about the middle of this year from
me.
as a FK
CREATE TABLE [dbo].[Sponsor] (
[SponsorID] [int] NOT NULL ,
[SponsorCode] [char] (15) NOT NULL ,
[BrandID] [int] NOT NULL ,
[FirstDate] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Sponsor] WITH NOCHECK ADD
CONSTRAINT [PK_Sponsor] PRIMARY KEY CLUSTERED
(
[SponsorID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE TABLE [dbo].[SponsorEvent] (
[Date] [int] NOT NULL ,
[BarbChanID] [int] NOT NULL ,
[StartTime] [int] NOT NULL ,
[AreaFlags] [smallint] NOT NULL ,
[PlatformFlags] [tinyint] NOT NULL ,
[EndTime] [int] NOT NULL ,
[SponsorID] [int] NOT NULL ,
[SponsorICodeID] [int] NOT NULL ,
[SponsorINameID] [int] NOT NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[SponsorEvent] WITH NOCHECK ADD
CONSTRAINT [PK_SponsorEvent] PRIMARY KEY CLUSTERED
(
[Date],
[BarbChanID],
[StartTime],
[AreaFlags],
[PlatformFlags]
) WITH FILLFACTOR = 90 ON [PRIMARY]
what is wrong with this syntax
update Sponsor set FirstDate = att.[Date]
from (
SELECT SponsorID,MIN([DATE]) From SponsorEvent GROUP by SponsorID)
att,Sponsor s
where s.SponsorID = att.SponsorID
Query Analyser complains of
"No column was specified for column 2 of 'att'."
I am trying to group by SponsorID on SponsorEvent, work out what the minimum
date is on SponsorEvent and update [Date] on Sponsor with this where
SponsorID is in common.
Thanks
Stephen Howe> Query Analyser complains of
> "No column was specified for column 2 of 'att'."
Isn't it "No column NAME was specified...?"
This happens when you apply a calculation on a column and don't include an
alias. The outer query doesn't see a column named "Date", it only sees the
expression MIN([Date]), which is not the same thing.
How about :
UPDATE Sponsor
SET FirstDate = att.FirstDate
FROM Sponsor
INNER JOIN
(
SELECT SponsorID, [Date] = MIN([DATE])
FROM SponsorEvent
GROUP by SponsorID
) Att
ON s.SponsorID = att.SponsorID
Or, better yet, instead of having to run this update every single time the
SponsorEvent changes, drop the column firstdate from the sponsor table.
There is no reason to store this redundant information when you can always
get it directly from SponsorEvent.
As an aside, I recommend renaming the column [Date]. It is never a good
idea to use reserved words as column names.
A|||Sorry, forgot the s:
> FROM Sponsor
Should be
FROM Sponsor s|||Thanks for response
> Isn't it "No column NAME was specified...?"
No. Definitely not. What I wrote was copy and pasted out of QA.
There is a message in colour red just before. The full message is
Server: Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 'att'.
> How about :
> UPDATE Sponsor
> SET FirstDate = att.FirstDate
> FROM Sponsor
> INNER JOIN
> (
> SELECT SponsorID, [Date] = MIN([DATE])
> FROM SponsorEvent
> GROUP by SponsorID
> ) Att
> ON s.SponsorID = att.SponsorID
Does not work. I changed text to
SET FirstDate = att.[Date]
FROM Sponsor s
and now I get
"Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'att' does not match with a table name or alias name used
in the query."
> Or, better yet, instead of having to run this update every single time the
> SponsorEvent changes, drop the column firstdate from the sponsor table.
> There is no reason to store this redundant information when you can always
> get it directly from SponsorEvent.
Yes you are right and I would like to.
But the reason why is that we have a analogous situation for tables where
Sponsor and SponsorEvent which have SponsorID in common
is reproduced with
SpotFilm and Spot which have SpotFilmID in common
SpotFilm and Sponsor have identical columns apart from the ID fields. It is
also such that if you concatenated them, the ID's are unique, no row in
common.
I say "somewhat symmetrical" because the difference is that data in
SponsorEvent is split between Spot and Slot.
Spot as a table has over 100 million rows (and gets larger per w
).Working out MIN([Date]) for each SpotFilmID on Spot is a triple-join between
Spot,Slot and SpotFilm, and we need to export that every Thursday. FirstDate
just happens to be a compromise. I know what you are saying, and I agree in
principal. At some point this year the server will be upgraded from SQL
Server 7 to 2000 and maybe the optimisation might be better.
> As an aside, I recommend renaming the column [Date]. It is never a good
> idea to use reserved words as column names.
I know. Noted. We have a table called [Break] which I am loathe to change.
Granted it is a keyword, but in the industry I am in "Break" is the most
descriptive word there is for the data it contains. They really are called
"Commercial Breaks". But I will change [Date] :-)
Stephen Howe|||What, do you have a case sensitive collation? Try using att everywhere and
no Att vs. att?
This works fine for me. You might want to provide similar DDL in the future
to make it easier for others to reproduce your scenario and test their
results. (See http://www.aspfaq.com/5006)
USE Tempdb
GO
CREATE TABLE Sponsor
(
SponsorID INT,
FirstDate SMALLDATETIME
)
GO
CREATE TABLE SponsorEvent
(
SponsorID INT,
[Date] SMALLDATETIME
)
GO
SET NOCOUNT ON
INSERT Sponsor SELECT 1, NULL
INSERT Sponsor SELECT 2, NULL
INSERT Sponsor SELECT 3, NULL
INSERT Sponsor SELECT 4, NULL
INSERT SponsorEvent SELECT 1, '20010501'
INSERT SponsorEvent SELECT 1, '20010601'
INSERT SponsorEvent SELECT 1, '20040801'
INSERT SponsorEvent SELECT 3, '20040701'
INSERT SponsorEvent SELECT 3, '20010601'
INSERT SponsorEvent SELECT 4, '20030801'
GO
UPDATE Sponsor
SET FirstDate = att.FirstDate
FROM Sponsor s
INNER JOIN
(
SELECT SponsorID, FirstDate = MIN([Date])
FROM SponsorEvent
GROUP BY SponsorID
) att
ON s.SponsorID = att.SponsorID
GO
SELECT * FROM Sponsor
GO
DROP TABLE SponsorEvent, Sponsor
GO
On 3/12/05 12:57 PM, in article ORy87zyJFHA.2716@.TK2MSFTNGP15.phx.gbl,
"Stephen Howe" <stephenPOINThoweATtns-globalPOINTcom> wrote:
> Thanks for response
>
> No. Definitely not. What I wrote was copy and pasted out of QA.
> There is a message in colour red just before. The full message is
> Server: Msg 8155, Level 16, State 2, Line 1
> No column was specified for column 2 of 'att'.
>
> Does not work. I changed text to
> SET FirstDate = att.[Date]
> FROM Sponsor s
> and now I get
> "Server: Msg 107, Level 16, State 2, Line 1
> The column prefix 'att' does not match with a table name or alias name use
d
> in the query."
>
> Yes you are right and I would like to.
> But the reason why is that we have a analogous situation for tables where
> Sponsor and SponsorEvent which have SponsorID in common
> is reproduced with
> SpotFilm and Spot which have SpotFilmID in common
> SpotFilm and Sponsor have identical columns apart from the ID fields. It i
s
> also such that if you concatenated them, the ID's are unique, no row in
> common.
> I say "somewhat symmetrical" because the difference is that data in
> SponsorEvent is split between Spot and Slot.
> Spot as a table has over 100 million rows (and gets larger per w
).> Working out MIN([Date]) for each SpotFilmID on Spot is a triple-join between
> Spot,Slot and SpotFilm, and we need to export that every Thursday. FirstDa
te
> just happens to be a compromise. I know what you are saying, and I agree i
n
> principal. At some point this year the server will be upgraded from SQL
> Server 7 to 2000 and maybe the optimisation might be better.
>
> I know. Noted. We have a table called [Break] which I am loathe to change.
> Granted it is a keyword, but in the industry I am in "Break" is the most
> descriptive word there is for the data it contains. They really are called
> "Commercial Breaks". But I will change [Date] :-)
> Stephen Howe
>
>
>|||> Sorry, forgot the s:
>
> Should be
> FROM Sponsor s
Thanks Aaaron, done it
UPDATE Sponsor
SET FirstDate = att.[Date]
FROM
(SELECT SponsorID, [Date] = MIN([DATE])
FROM SponsorEvent
GROUP by SponsorID
) Att INNER JOIN Sponsor s ON Att.SponsorID=s.SPonsorID|||> What, do you have a case sensitive collation? Try using att everywhere
and
> no Att vs. att?
I don't think we do. Anything to do with SQL Server 7 not recognising the
syntax?
Perhaps SQL Server 2000 is "improved" in that order does not matter
I changed the order putting Sponsor s last, (after the SELECT .. GROUP BY)
and at that point it recognised it.
Strange
But thanks
Stephen|||Date is not just a reserved word, it is too vague to be a valid data
element name -- date of what' Is there only one sponsor, as you
showed with a singular name? If you were writing SQL instead of a
strange dialect, would this be what you meant? It is a bad practice to
name relationship tables by concatenating names together -- always ask
if the relationship has its own name. Endorsements, sponsorships, etc.
UPDATE Sponsors
SET firstdate -- of what'
= (SELECT MIN(foobar_date)
FROM Endorsements AS E
WHERE E.sponsor_id = Sponsors.sponsor_id)
You might want to read a book on SQL and see what the standard UPDATE
syntax is.
And get a book on data modeling. Names like "SponsorICodeID" make
absolutley no sense. A code is not an identifier; it is a scalar value
on a nominal scale. Most of the rest of your DDL looks like you are
writing SQL with flags, etc. -- all the classic mistakes of someone who
does not know how to make a schema.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1110772877.481289.236930@.l41g2000cwc.googlegroups.com...
> It is a bad practice to name relationship tables by concatenating
names together
Mr. Celko,
Really? I've been doing a lot of that lately (sometimes M-to-M
relationships are stumpers). Is that a part of a standard I can take
a look at? Perhaps you could provide a weblink to an article or other
work with an extensive description of why it is bad, and the process
of doing better.
Sincerely,
Chris O.|||>> [name relationship tables by concatenating names together ] I've
been doing a lot of that lately (sometimes M-to-M relationships are
stumpers). <<
Not if you start from a data model. Relationships important enough to
be modeled tend to have names -- "Marriages" instead "ManWoman' or even
worse "CivilUnions" instead of "ManMan_WomanWoman_ManWomen". A
relationship name invites the attributes that go with the relationship;
Marriages implies a wedding date, license number, etc.
Google up ISO-11179; the principle is to name a thing for what it *is*,
not for what it *does* in a particular situation, not for hopw you
build it-- i.e. this is an "automobile", not a "TiresFrameMotor"; the
whole not the parts.
an extensive description of why it is bad, and the process of doing
better. <<
Look for an entire book on SQL style about the middle of this year from
me.
Wednesday, March 7, 2012
complex SQL (for me)
Hello,
Lets look at this table :
CREATE TABLE [dbo].[TableHisto](
[Id] [int] NOT NULL,
[Week] [nvarchar](50) COLLATE French_CI_AS NULL,
[Project] [int] NOT NULL
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'Identifiant d''enregistrement' ,@.level0type=N'SCHEMA', @.level0name=N'dbo', @.level1type=N'TABLE', @.level1name=N'TableHisto', @.level2type=N'COLUMN', @.level2name=N'Id'
GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'Date de l''enregistrement' ,@.level0type=N'SCHEMA', @.level0name=N'dbo', @.level1type=N'TABLE', @.level1name=N'TableHisto', @.level2type=N'COLUMN', @.level2name=N'Week'
GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'Projet de rfrence' ,@.level0type=N'SCHEMA', @.level0name=N'dbo', @.level1type=N'TABLE', @.level1name=N'TableHisto', @.level2type=N'COLUMN', @.level2name=N'Project'
It is a table where i store projects week reports.
I want to make a request to display a table with project ID in Row, Weeks in columns and either TableHisto.id or Null value in cell.
I use SQL 2005. Thanks for any help
You can use a Matrix Report type that supported by many report products
like Crystal report ,
it is easy to use by a power full wizard
|||I cant use it becouse i need to use it as a navigation tool on web site
|||See if this set of posts helps you:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326847&SiteID=1
We did a rotation of a set like this (more generic in nature) in these posts.
|||Your sample is very nice but i don't know how to use it.
Lets me show what I want :
set nocount on
create table Histo
(
Id varchar(10) primary key,
Week varchar(10),
Project varchar(10)
)
insert into Histo (Id, Week, Project)
select '47','2006-12','Internet'
union all
select '48','2006-12','Internet'
union all
select '49','2006-13','Intranet'
go
select *
from Histo
go
it make this table :
Id | Week | Project
47 | 2006-12 | Internet
48 | 2006-12 | Internet
49 | 2006-13 | Intranet
And I Want to get something like that :
Project | 2006-12 | 2006-13
Internet | 47 | 49
Intranet | 48 | null
I work on it for days and dont find the solution.
Thanks a lot for any help
|||Well, this is the perfect example of the use of the PIVOT keyword in SQL Server 2005. If you're using SQL 2005 then use PIVOT. If you're using SQL 2000 then you'll have to use a case statement to accomplish this and it can be a bit cumbersome.|||Please demonstrate. I don't quite know how you will achieve what is being asked.|||nobody can help me ?|||I found the solution :
set nocount on
create table Histo
(
Id varchar(10)primary key,
Week varchar(10),
Project varchar(10)
)
insert into Histo (Id, Week, Project)
select '47','2006-12','Internet'
union all
select '48','2006-13','Internet'
union all
select '49','2006-12','Intranet'
go
select*
from Histo
go
SELECT Project,
[2006-12],
[2006-13]
FROM
(SELECT Project, week, id
FROM histo) s
PIVOT
(
max(id)
FOR Week IN ([2006-12],[2006-13])
) p
ORDER BY [Project]
go
drop table histo
Tuesday, February 14, 2012
Compatibility of SQL 2000
I find that the "Create Table" script generated by SQL Server 2005 is in format:
CREATE TABLE [dbo].[City](
[CityID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED
(
[CityID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Can this appliable in SQL Server 2000 or MSDE? If not, how should I change it to make it work in both SQL 2000 and 2005?
Thanks
Thats not working in SQL 2000. You will have to create it in compat. mode 2k.HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Would you please show me a sample code?
Thanks
|||Hi,the equivalent would be:
CREATE TABLE [dbo].[City](
[CityID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED
(
[CityID] ASC
) ON [PRIMARY]
)
GO
CREATE UNIQUE INDEX [SomenewIndex] ON [dbo].[City]([CityID])
WITH IGNORE_DUP_KEY ON [PRIMARY]
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||What did you use to generate the script above? Did you use SMO or Tools? Did you select the correct compatibility level for the script? If you cannot get the correct output for the script then you should just create it by hand since it is easier that way and avoids all the other unnecessary default options. For example, the default for IGNORE_DUP_KEY is OFF for PRIMARY/UNIQUE key constraints and you should probably avoid using it anyway. So you can write DDL like:
CREATE TABLE [dbo].[City](
[CityID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED
(
[CityID] ASC
)
) ON [PRIMARY]
Btw, if you cannot get the tools or SMO to work correctly then please file a bug using http://connect.microsoft.com/sqlserver or create a thread in the SQL Server Tools forum.
|||Thanks for the reply.
I generated the script using SMO in the following steps:
1. In SMO, right-click on the table City, choose Modify.
2. Highlight CityID, click the "Set Primary Key" button and save the change
3. Right-click on table City, choose "Script table as", choose "Create to"....
Thanks.
|||Hi
u can't use ignore_dup_key with constraint. u need to create index key.
CREATE TABLE [dbo].[test] (
[id] [int] NOT NULL ,
[name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[test] WITH NOCHECK ADD
CONSTRAINT [pk_id] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [ind_id] ON [dbo].[test]([id] DESC ) WITH IGNORE_DUP_KEY ON [PRIMARY]
GO
|||
I upgraded my SQL Server 2005 to SP1.
Then this line : WITH (IGNORE_DUP_KEY = OFF) is removed from generated script.
Problem solved!!
Compatibility of SQL 2000
I find that the "Create Table" script generated by SQL Server 2005 is in format:
CREATE TABLE [dbo].[City](
[CityID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED
(
[CityID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Can this appliable in SQL Server 2000 or MSDE? If not, how should I change it to make it work in both SQL 2000 and 2005?
Thanks
Thats not working in SQL 2000. You will have to create it in compat. mode 2k.HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Would you please show me a sample code?
Thanks
|||Hi,the equivalent would be:
CREATE TABLE [dbo].[City](
[CityID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED
(
[CityID] ASC
) ON [PRIMARY]
)
GO
CREATE UNIQUE INDEX [SomenewIndex] ON [dbo].[City]([CityID])
WITH IGNORE_DUP_KEY ON [PRIMARY]
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||What did you use to generate the script above? Did you use SMO or Tools? Did you select the correct compatibility level for the script? If you cannot get the correct output for the script then you should just create it by hand since it is easier that way and avoids all the other unnecessary default options. For example, the default for IGNORE_DUP_KEY is OFF for PRIMARY/UNIQUE key constraints and you should probably avoid using it anyway. So you can write DDL like:
CREATE TABLE [dbo].[City](
[CityID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED
(
[CityID] ASC
)
) ON [PRIMARY]
Btw, if you cannot get the tools or SMO to work correctly then please file a bug using http://connect.microsoft.com/sqlserver or create a thread in the SQL Server Tools forum.
|||Thanks for the reply.
I generated the script using SMO in the following steps:
1. In SMO, right-click on the table City, choose Modify.
2. Highlight CityID, click the "Set Primary Key" button and save the change
3. Right-click on table City, choose "Script table as", choose "Create to"....
Thanks.
|||Hi
u can't use ignore_dup_key with constraint. u need to create index key.
CREATE TABLE [dbo].[test] (
[id] [int] NOT NULL ,
[name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[test] WITH NOCHECK ADD
CONSTRAINT [pk_id] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [ind_id] ON [dbo].[test]([id] DESC ) WITH IGNORE_DUP_KEY ON [PRIMARY]
GO
|||
I upgraded my SQL Server 2005 to SP1.
Then this line : WITH (IGNORE_DUP_KEY = OFF) is removed from generated script.
Problem solved!!
Compatibility of SQL 2000
I find that the "Create Table" script generated by SQL Server 2005 is in format:
CREATE TABLE [dbo].[City](
[CityID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED
(
[CityID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Can this appliable in SQL Server 2000 or MSDE? If not, how should I change it to make it work in both SQL 2000 and 2005?
Thanks
Drop the With( IGNORE_DUP_KEY = OFF ) clause. That is not supported in the Create statement in 2000. It refers to how the index created to support the constraint should react to duplicate keys. This option can be specified if the index is created separately. OFF is the default setting, so removing it will not change the behavior.
Subscribe to:
Posts (Atom)