Challenging Search Engine Like Stored Porcedure Needed

(DML below)
First, thank you in advance for your help. Here's one for those of you that
like a little challenge, or perhaps this is a cake walk for you (I'm jealous
).
This DB is to hold information so that our helpdesk can search it for
support information. The front end will be a Visual Basic application
calling stored procedures.
Note "tblDocument.Document" and "tblSystem.Document" will hold an HTML
document. We will Full Text Indexed both of those columns so as to enable a
granular search. I'm guessing the "freetext" t-sql command is the tool for
this job to create a search engine like stored procedure.(if there's a bette
r
way, please suggest it).
The Front End VB application will have the following interface, so the
Stored Procedure will need to accomodate the following:
1) A text box to enter the search terms which searches the
tblDocument.document and tblSystem.Document columns for words that are
entered.
2) Dropdown box: Choice between a System Document (from tblSystem), or a
support document (from tblDocument). If System is choosen, it only searches
tblSystem. If Document is choosen, it only searches tblDocument. If left
blank will search both.
3) Dropdown box: Choice of a category to search (from tblcatagories), If a
category is choosen, the stored procedure will only return those documents
meeting that criteria. if left blank will search all categories.
4) Dropdown box: Choice of a SUBCategory to search (from tblsubcategories),
If Subcategory is choosen, the stored procedure will only return those
documents meeting that criteria. if left blank will search all SUBcategorie
s.
5) Dropdown box: Ability to choose whom submitted the document. If
SubmitterName is choosen, the stored procedure will only return thos documen
t
OR systems meeting that criteria. If left blank will search all systems AND
documents from all submitters.
The Stored Procedure will need to return results that look like this:
(if search terms hit a document) DocumentID, Title, SubmitterName
(if search terms hit a system) SystemID, SystemName, SubmitterName
Thanks again
Jeff
CREATE DATABASE [eDOC] ON (NAME = N'eDOC_Data', FILENAME =
N'C:\MSSQL7\Data\eDOC_Data.MDF' , SIZE = 2, FILEGROWTH = 10%) LOG ON (NAME =
N'eDOC_Log', FILENAME = N'C:\MSSQL7\Data\eDOC_Log.LDF' , SIZE = 1, FILEGROWT
H
= 10%)
GO
exec sp_dboption N'eDOC', N'autoclose', N'false'
GO
exec sp_dboption N'eDOC', N'bulkcopy', N'false'
GO
exec sp_dboption N'eDOC', N'trunc. log', N'false'
GO
exec sp_dboption N'eDOC', N'torn page detection', N'true'
GO
exec sp_dboption N'eDOC', N'read only', N'false'
GO
exec sp_dboption N'eDOC', N'dbo use', N'false'
GO
exec sp_dboption N'eDOC', N'single', N'false'
GO
exec sp_dboption N'eDOC', N'autoshrink', N'false'
GO
exec sp_dboption N'eDOC', N'ANSI null default', N'false'
GO
exec sp_dboption N'eDOC', N'recursive triggers', N'false'
GO
exec sp_dboption N'eDOC', N'ANSI nulls', N'false'
GO
exec sp_dboption N'eDOC', N'concat null yields null', N'false'
GO
exec sp_dboption N'eDOC', N'cursor close on commit', N'false'
GO
exec sp_dboption N'eDOC', N'default to local cursor', N'false'
GO
exec sp_dboption N'eDOC', N'quoted identifier', N'false'
GO
exec sp_dboption N'eDOC', N'ANSI warnings', N'false'
GO
exec sp_dboption N'eDOC', N'auto create statistics', N'true'
GO
exec sp_dboption N'eDOC', N'auto update statistics', N'true'
GO
use [eDOC]
GO
if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1
exec sp_fulltext_database N'enable'
GO
if not exists (select * from dbo.sysfulltextcatalogs where name =
N'DocumentFullText')
exec sp_fulltext_catalog N'DocumentFullText', N'create'
GO
CREATE TABLE [dbo].[tblCategories] (
[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryName] [char] (100) ,
[CreattionDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblDocument] (
[DocumentID] [int] IDENTITY (1000, 1) NOT NULL ,
[CategoryID] [int] NOT NULL ,
[SubCategoryID] [int] NOT NULL ,
[Title] [char] (100) ,
[KeyWords] [char] (100) ,
[SubmitterID] [int] NOT NULL ,
[CreationDate] [datetime] NULL ,
[RevisedBy] [int] NOT NULL ,
[RevisedDate] [datetime] NULL ,
[Document] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblSubCategories] (
[SubCategoyID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryID] [int] NOT NULL ,
[SubcategoryName] [char] (100) NOT NULL ,
[CreationDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblSubmitter] (
[SubmitterID] [int] IDENTITY (1, 1) NOT NULL ,
[SubmitterName] [char] (40) NOT NULL ,
[Username] [varchar] (20) NULL ,
[Password] [varchar] (20) NULL ,
[CreationDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblSystem] (
[SystemID] [int] IDENTITY (10000, 5) NOT NULL ,
[SubmitterID] [int] NOT NULL ,
[SystemName] [char] (100) NOT NULL ,
[RevisedDate] [datetime] NOT NULL ,
[RevisedBy] [char] (100) NOT NULL ,
[Document] [text] NOT NULL ,
[CreationDate] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCategories] WITH NOCHECK ADD
CONSTRAINT [PK_tblCategories] PRIMARY KEY CLUSTERED
(
[CategoryID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblDocument] WITH NOCHECK ADD
CONSTRAINT [PK_tblDocument] PRIMARY KEY CLUSTERED
(
[DocumentID]
) ON [PRIMARY]
GO
USE edoc
EXEC sp_fulltext_database 'enable'
go
if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1
exec sp_fulltext_database N'enable'
GO
if not exists (select * from dbo.sysfulltextcatalogs where name =
N'DocumentFullText')
exec sp_fulltext_catalog N'DocumentFullText', N'create'
GO
exec sp_fulltext_table N'[dbo].[tblDocument]', N'create',
N'DocumentFullText', N'PK_tblDocument'
GO
exec sp_fulltext_column N'[dbo].[tblDocument]', N'Document', N'add', 1033
GO
exec sp_fulltext_table N'[dbo].[tblDocument]', N'activate'
GO
ALTER TABLE [dbo].[tblSubCategories] WITH NOCHECK ADD
CONSTRAINT [PK_tblSubCategories] PRIMARY KEY CLUSTERED
(
[SubCategoyID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblSubmitter] WITH NOCHECK ADD
CONSTRAINT [PK_tblSubmitter] PRIMARY KEY CLUSTERED
(
[SubmitterID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblSystem] WITH NOCHECK ADD
CONSTRAINT [PK_tblSystem] PRIMARY KEY CLUSTERED
(
[SystemID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCategories] WITH NOCHECK ADD
CONSTRAINT [DF_tblCategories_CreattionDate] DEFAULT (getdate()) FOR
[CreattionDate]
GO
ALTER TABLE [dbo].[tblDocument] WITH NOCHECK ADD
CONSTRAINT [DF_tblDocument_CreationDate] DEFAULT (getdate()) FOR
[CreationDate]
GO
ALTER TABLE [dbo].[tblSubCategories] WITH NOCHECK ADD
CONSTRAINT [DF_tblSubCategories_CreationDate] DEFAULT (getdate()) FOR
[CreationDate]
GO
ALTER TABLE [dbo].[tblSubmitter] WITH NOCHECK ADD
CONSTRAINT [DF_tblSubmitter_CreationDate] DEFAULT (getdate()) FOR
[CreationDate]
GO
ALTER TABLE [dbo].[tblSystem] WITH NOCHECK ADD
CONSTRAINT [DF_tblSystem_RevisedDate] DEFAULT (getdate()) FOR [RevisedDate],
CONSTRAINT [DF_tblSystem_CreationDate] DEFAULT (getdate()) FOR [CreationDate]
GO
ALTER TABLE [dbo].[tblDocument] ADD
CONSTRAINT [FK_tblDocument_tblCategories] FOREIGN KEY
(
[CategoryID]
) REFERENCES [dbo].[tblCategories] (
[CategoryID]
),
CONSTRAINT [FK_tblDocument_tblSubCategories1] FOREIGN KEY
(
[SubCategoryID]
) REFERENCES [dbo].[tblSubCategories] (
[SubCategoyID]
),
CONSTRAINT [FK_tblDocument_tblSubmitter2] FOREIGN KEY
(
[SubmitterID]
) REFERENCES [dbo].[tblSubmitter] (
[SubmitterID]
)
GO
ALTER TABLE [dbo].[tblSubCategories] ADD
CONSTRAINT [FK_tblSubCategories_tblCategories] FOREIGN KEY
(
[CategoryID]
) REFERENCES [dbo].[tblCategories] (
[CategoryID]
)
GO
ALTER TABLE [dbo].[tblSystem] ADD
CONSTRAINT [FK_tblSystem_tblSubmitter1] FOREIGN KEY
(
[SubmitterID]
) REFERENCES [dbo].[tblSubmitter] (
[SubmitterID]
)
GO
use edoc
go
INSERT into tblcategories(CategoryName) VALUES ('hardware')
insert into tblcategories(CategoryName) values ('Software')
insert into tblsubcategories(categoryID,subcategoryn
ame) values ('1','Dell
Server')
insert into tblsubcategories(categoryID,subcategoryn
ame) values
('2','Outlook')
insert into tblsubmitter(submittername, username, [password]) values ('Joe
Williams', 'WilliamsJ', 'password')
insert into tblsubmitter(submittername, username, [password]) values ('Mike
Smith', 'SmithM', 'password')
insert into tblsystem(submitterID, SystemName, RevisedBy, Document) values
('1', 'SQL Server', '2', 'Long HTML document')
insert into tblsystem(submitterID, SystemName, RevisedBy, Document) values
('2', 'Exchange', '1', 'Very Long HTML document')
insert into tbldocument(categoryID, subcategoryID, Title, Keywords,
submitterID, Revisedby, document) values ('1', '2', 'Outlook Crashes',
'run32.dll abend', '1', '2', 'Outlook document goes here')
insert into tbldocument(categoryID, subcategoryID, Title, Keywords,
submitterID, Revisedby, document) values ('2', '1', 'Dell Post Error 151',
'0x8765432', '2', '1', 'Dell document goes here')
goOn Sat, 10 Sep 2005 11:47:08 -0700, Jeff wrote:

>(DML below)
Hi Jeff,
Thanks for supplying DDL and sample data!

>Note "tblDocument.Document" and "tblSystem.Document" will hold an HTML
>document.
Prefixing table names with "tbl" is considered a bad habit in the world
of relational databases. I'd rename these tables "Systems" and
"Documents" (since they store information about more than one system and
more than one document).

> We will Full Text Indexed both of those columns so as to enable a
>granular search. I'm guessing the "freetext" t-sql command is the tool for
>this job to create a search engine like stored procedure.(if there's a bett
er
>way, please suggest it).
I have no experience with full text indexing. From the sound of it, it's
probably the tool for the job, though.
Note that there is a special group where the full text experts prefer to
hang their hats: microsoft.public.sqlserver.fulltext.

>The Front End VB application will have the following interface, so the
>Stored Procedure will need to accomodate the following:
>1) A text box to enter the search terms which searches the
>tblDocument.document and tblSystem.Document columns for words that are
>entered.
That would be a job for full text search, I guess.

>2) Dropdown box: Choice between a System Document (from tblSystem), or a
>support document (from tblDocument). If System is choosen, it only searche
s
>tblSystem. If Document is choosen, it only searches tblDocument. If left
>blank will search both.
My choice would be to have two stored procedures. One searches the
Systems table, the other searches the Documents table. The front end
uses this dropdown box to decide whether to call the first, the second,
ot both and merge the results in the front end. (The added advantage is
that your front end has no trouble figuring out if a returned row refers
to a system or to a document).

>3) Dropdown box: Choice of a category to search (from tblcatagories), If a
>category is choosen, the stored procedure will only return those documents
>meeting that criteria. if left blank will search all categories.
>4) Dropdown box: Choice of a SUBCategory to search (from tblsubcategories)
,
>If Subcategory is choosen, the stored procedure will only return those
>documents meeting that criteria. if left blank will search all SUBcategori
es.
>5) Dropdown box: Ability to choose whom submitted the document. If
>SubmitterName is choosen, the stored procedure will only return thos docume
nt
>OR systems meeting that criteria. If left blank will search all systems AN
D
>documents from all submitters.
For all these three requirements: www.sommarskog.se/dyn-search.html.
(big snip)
>CREATE TABLE [dbo].[tblCategories] (
> [CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
> [CategoryName] [char] (100) ,
> [CreattionDate] [datetime] NOT NULL
> ) ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblCategories] WITH NOCHECK ADD
> CONSTRAINT [PK_tblCategories] PRIMARY KEY CLUSTERED
> (
> [CategoryID]
> ) ON [PRIMARY]
>GO
* If not all categories have names with a length of >90 characters,
change the datatype to varchar(100).
* An identity can never be the only key of a table. You'll need a
natural key as well, to prevent duplicates. Assuming that no two
categories share the same name, add a UNIQUE constraint on CategoryName.
* Oh, and fix the typo in CreattionDate before deploying your system. It
is still an easy fix now.
* Why is there no NOT NULL constraint for CategoryName?

>CREATE TABLE [dbo].[tblDocument] (
> [DocumentID] [int] IDENTITY (1000, 1) NOT NULL ,
> [CategoryID] [int] NOT NULL ,
> [SubCategoryID] [int] NOT NULL ,
> [Title] [char] (100) ,
> [KeyWords] [char] (100) ,
> [SubmitterID] [int] NOT NULL ,
> [CreationDate] [datetime] NULL ,
> [RevisedBy] [int] NOT NULL ,
> [RevisedDate] [datetime] NULL ,
> [Document] [text] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblDocument] WITH NOCHECK ADD
> CONSTRAINT [PK_tblDocument] PRIMARY KEY CLUSTERED
> (
> [DocumentID]
> ) ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblDocument] ADD
> CONSTRAINT [FK_tblDocument_tblCategories] FOREIGN KEY
> (
> [CategoryID]
> ) REFERENCES [dbo].[tblCategories] (
> [CategoryID]
> ),
> CONSTRAINT [FK_tblDocument_tblSubCategories1] FOREIGN KEY
> (
> [SubCategoryID]
> ) REFERENCES [dbo].[tblSubCategories] (
> [SubCategoyID]
> ),
> CONSTRAINT [FK_tblDocument_tblSubmitter2] FOREIGN KEY
> (
> [SubmitterID]
> ) REFERENCES [dbo].[tblSubmitter] (
> [SubmitterID]
> )
>GO
* Change char to varchar in this table as well (and in the remainging
tables of course - I won't repeat this advice)
* Since there are no tables that refer to this table, there's no need
for a surrogate key. You should remove the identity column.
* I don't know your data well enough to propose what set of columns make
up the business key in your business, but I'm sure you do. Maybe just
"Title"? Maybe the combination of Category, Subcategory and Title?
* If subcategory S1 belongs to Category C1, is it then possible for a
document to belong to subcategory S1, but to category C2? If not, then
the column CategoryID should not be in this table at all, unless you
have compelling reasons (probably performance-driven) to use a partly
denormalized design - and in that case, you should probably add some
extra constraints to make sure that you'll never get any data like the
example I gave. (Suggestion: use a foreign key on (Category,
Subcategory) - you'll have to create a "redundant" UNIQUE constraint in
the Subcategories table for that)
* Why is there no NOT NULL constraint for Title, Keywords, and Document?
* Why is there a NOT NULL constraint for RevisedBy, but none for
RevisedDate? I think RevisedBy shouyld be NULLable as well?
* Why is there no foreign key constraint for RevisedBy?

>CREATE TABLE [dbo].[tblSubCategories] (
> [SubCategoyID] [int] IDENTITY (1, 1) NOT NULL ,
> [CategoryID] [int] NOT NULL ,
> [SubcategoryName] [char] (100) NOT NULL ,
> [CreationDate] [datetime] NOT NULL
> ) ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblSubCategories] WITH NOCHECK ADD
> CONSTRAINT [PK_tblSubCategories] PRIMARY KEY CLUSTERED
> (
> [SubCategoyID]
> ) ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblSubCategories] ADD
> CONSTRAINT [FK_tblSubCategories_tblCategories] FOREIGN KEY
> (
> [CategoryID]
> ) REFERENCES [dbo].[tblCategories] (
> [CategoryID]
> )
>GO
* Assuming that two subcategories of the same category can't have the
same name, but two subcategories of different categories can, the real
key is (CategoryID, SubcategoryName). Use a UNIQUE constraint to prevent
duplicates.

>CREATE TABLE [dbo].[tblSubmitter] (
> [SubmitterID] [int] IDENTITY (1, 1) NOT NULL ,
> [SubmitterName] [char] (40) NOT NULL ,
> [Username] [varchar] (20) NULL ,
> [Password] [varchar] (20) NULL ,
> [CreationDate] [datetime] NOT NULL
> ) ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblSubmitter] WITH NOCHECK ADD
> CONSTRAINT [PK_tblSubmitter] PRIMARY KEY CLUSTERED
> (
> [SubmitterID]
> ) ON [PRIMARY]
>GO
* Storing passwords in a table is not really safe.
* The UNIQUE constraint should go on SubmitterName, I guess.
* Why is there no NOT NULL constraint for Username and Password?

>CREATE TABLE [dbo].[tblSystem] (
> [SystemID] [int] IDENTITY (10000, 5) NOT NULL ,
> [SubmitterID] [int] NOT NULL ,
> [SystemName] [char] (100) NOT NULL ,
> [RevisedDate] [datetime] NOT NULL ,
> [RevisedBy] [char] (100) NOT NULL ,
> [Document] [text] NOT NULL ,
> [CreationDate] [datetime] NOT NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblSystem] WITH NOCHECK ADD
> CONSTRAINT [PK_tblSystem] PRIMARY KEY CLUSTERED
> (
> [SystemID]
> ) ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblSystem] ADD
> CONSTRAINT [FK_tblSystem_tblSubmitter1] FOREIGN KEY
> (
> [SubmitterID]
> ) REFERENCES [dbo].[tblSubmitter] (
> [SubmitterID]
> )
>GO
* Most of the comments that relate to the Documents table relate to this
table as well. I won;t repeat them here.
* Why the non-standard seed and increment for the IDENTITY property? Is
this table part of some distributed replication setup?
* Why did you define RevisedBy as int in the Documents table, but as
char(100) in this table? Having two columns with the sme name but with
different datatypes (and different contents) will cause you a wealthy
supply of bugs!
Best, Hugo
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I really appreciate your input. In short to your comments, some were errors
,
some are on my to-do list, like adding the constraints, some are due to the
evoluation of the DB and we need to go back and fix, and the rest are things
that I aparently need to further learn.
And I especially appreciate the advice on the best way to handle the stored
procedure which I where I'm stuck since DBs are not my strong point as you
can tell. I'm not a programmer, I'm a newtorking guy that only dabbles in
this.
But where I'm really at a loss is the stored procedures themselves. So how
can I turn the below basic procedure into one that will accept the various
options and return results. Obviously, as it stands now, I can't leave a
variable blank...
@.searchterm must have something entere in it
@.Category must accept either a 1, 2, or be left blank to return both 1 & 2.
@.Subcategory same as above
@.Submitter same as above
Thanks again in advance for all your help!
CREATE PROCEDURE SearchDocument
(
@.SearchTerm char (200),
@.Category int,
@.Subcategory int,
@.Submitter int
)
as
SELECT dbo.tblDocument.DocumentID,
dbo.tblDocument.Title,
dbo.tblSubmitter.SubmitterName
FROM dbo.tblDocument join dbo.tblSubmitter
on (tbldocument.submitterid = tblsubmitter.submitterid)
where freetext (document, @.searchterm) and
dbo.tblDocument.SubCategoryID = @.subcategory AND
dbo.tblDocument.CategoryID = @.category AND
dbo.tblDocument.SubmitterID = @.submitter
"Hugo Kornelis" wrote:

> On Sat, 10 Sep 2005 11:47:08 -0700, Jeff wrote:
>
> Hi Jeff,
> Thanks for supplying DDL and sample data!
>
> Prefixing table names with "tbl" is considered a bad habit in the world
> of relational databases. I'd rename these tables "Systems" and
> "Documents" (since they store information about more than one system and
> more than one document).
>
> I have no experience with full text indexing. From the sound of it, it's
> probably the tool for the job, though.
> Note that there is a special group where the full text experts prefer to
> hang their hats: microsoft.public.sqlserver.fulltext.
>
> That would be a job for full text search, I guess.
>
> My choice would be to have two stored procedures. One searches the
> Systems table, the other searches the Documents table. The front end
> uses this dropdown box to decide whether to call the first, the second,
> ot both and merge the results in the front end. (The added advantage is
> that your front end has no trouble figuring out if a returned row refers
> to a system or to a document).
>
> For all these three requirements: www.sommarskog.se/dyn-search.html.
> (big snip)
> (...)
> * If not all categories have names with a length of >90 characters,
> change the datatype to varchar(100).
> * An identity can never be the only key of a table. You'll need a
> natural key as well, to prevent duplicates. Assuming that no two
> categories share the same name, add a UNIQUE constraint on CategoryName.
> * Oh, and fix the typo in CreattionDate before deploying your system. It
> is still an easy fix now.
> * Why is there no NOT NULL constraint for CategoryName?
>
> (...)
> (...)
> * Change char to varchar in this table as well (and in the remainging
> tables of course - I won't repeat this advice)
> * Since there are no tables that refer to this table, there's no need
> for a surrogate key. You should remove the identity column.
> * I don't know your data well enough to propose what set of columns make
> up the business key in your business, but I'm sure you do. Maybe just
> "Title"? Maybe the combination of Category, Subcategory and Title?
> * If subcategory S1 belongs to Category C1, is it then possible for a
> document to belong to subcategory S1, but to category C2? If not, then
> the column CategoryID should not be in this table at all, unless you
> have compelling reasons (probably performance-driven) to use a partly
> denormalized design - and in that case, you should probably add some
> extra constraints to make sure that you'll never get any data like the
> example I gave. (Suggestion: use a foreign key on (Category,
> Subcategory) - you'll have to create a "redundant" UNIQUE constraint in
> the Subcategories table for that)
> * Why is there no NOT NULL constraint for Title, Keywords, and Document?
> * Why is there a NOT NULL constraint for RevisedBy, but none for
> RevisedDate? I think RevisedBy shouyld be NULLable as well?
> * Why is there no foreign key constraint for RevisedBy?
>
> (...)
> (...)
> * Assuming that two subcategories of the same category can't have the
> same name, but two subcategories of different categories can, the real
> key is (CategoryID, SubcategoryName). Use a UNIQUE constraint to prevent
> duplicates.
>
> (...)
> * Storing passwords in a table is not really safe.
> * The UNIQUE constraint should go on SubmitterName, I guess.
> * Why is there no NOT NULL constraint for Username and Password?
>
> (...)
> (...)
> * Most of the comments that relate to the Documents table relate to this
> table as well. I won;t repeat them here.
> * Why the non-standard seed and increment for the IDENTITY property? Is
> this table part of some distributed replication setup?
> * Why did you define RevisedBy as int in the Documents table, but as
> char(100) in this table? Having two columns with the sme name but with
> different datatypes (and different contents) will cause you a wealthy
> supply of bugs!
> Best, Hugo
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||Jeff (Jeff@.discussions.microsoft.com) writes:
> But where I'm really at a loss is the stored procedures themselves. So
> how can I turn the below basic procedure into one that will accept the
> various options and return results. Obviously, as it stands now, I
> can't leave a variable blank...
> @.searchterm must have something entere in it
> @.Category must accept either a 1, 2, or be left blank to return both 1 &
> 2.
> @.Subcategory same as above
> @.Submitter same as above
You cannot leave it blank, but you can set it to NULL, and you can even make
that a default value:
@.SearchTerm char (200),
@.Category int = NULL,
@.Subcategory int = NULL,
@.Submitter int = ULL

> where freetext (document, @.searchterm) and
> dbo.tblDocument.SubCategoryID = @.subcategory AND
> dbo.tblDocument.CategoryID = @.category AND
> dbo.tblDocument.SubmitterID = @.submitter
Then you can say
AND (dbo.tblDocument.SubCategoryID = @.subcategory OR @.subcategory IS NULL
Note that if any of the columns are indexed, those indexes will not be
used. If you want an index on, say, CategoryID to be used, you will
need to use IF statements that leads to different SELECT statements,
or use dynamic SQL.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Oh yes! I remember that trick of setting the variables to null now...
Thanks!!
"Jeff" wrote:

> (DML below)
> First, thank you in advance for your help. Here's one for those of you th
at
> like a little challenge, or perhaps this is a cake walk for you (I'm jealo
us).
> This DB is to hold information so that our helpdesk can search it for
> support information. The front end will be a Visual Basic application
> calling stored procedures.
> Note "tblDocument.Document" and "tblSystem.Document" will hold an HTML
> document. We will Full Text Indexed both of those columns so as to enable
a
> granular search. I'm guessing the "freetext" t-sql command is the tool fo
r
> this job to create a search engine like stored procedure.(if there's a bet
ter
> way, please suggest it).
> The Front End VB application will have the following interface, so the
> Stored Procedure will need to accomodate the following:
> 1) A text box to enter the search terms which searches the
> tblDocument.document and tblSystem.Document columns for words that are
> entered.
> 2) Dropdown box: Choice between a System Document (from tblSystem), or a
> support document (from tblDocument). If System is choosen, it only search
es
> tblSystem. If Document is choosen, it only searches tblDocument. If left
> blank will search both.
> 3) Dropdown box: Choice of a category to search (from tblcatagories), If
a
> category is choosen, the stored procedure will only return those documents
> meeting that criteria. if left blank will search all categories.
> 4) Dropdown box: Choice of a SUBCategory to search (from tblsubcategories
),
> If Subcategory is choosen, the stored procedure will only return those
> documents meeting that criteria. if left blank will search all SUBcategor
ies.
> 5) Dropdown box: Ability to choose whom submitted the document. If
> SubmitterName is choosen, the stored procedure will only return thos docum
ent
> OR systems meeting that criteria. If left blank will search all systems A
ND
> documents from all submitters.
>
> The Stored Procedure will need to return results that look like this:
> (if search terms hit a document) DocumentID, Title, SubmitterName
> (if search terms hit a system) SystemID, SystemName, SubmitterName
>
> Thanks again
> Jeff
>
> CREATE DATABASE [eDOC] ON (NAME = N'eDOC_Data', FILENAME =
> N'C:\MSSQL7\Data\eDOC_Data.MDF' , SIZE = 2, FILEGROWTH = 10%) LOG ON (NAME
=
> N'eDOC_Log', FILENAME = N'C:\MSSQL7\Data\eDOC_Log.LDF' , SIZE = 1, FILEGRO
WTH
> = 10%)
> GO
> exec sp_dboption N'eDOC', N'autoclose', N'false'
> GO
> exec sp_dboption N'eDOC', N'bulkcopy', N'false'
> GO
> exec sp_dboption N'eDOC', N'trunc. log', N'false'
> GO
> exec sp_dboption N'eDOC', N'torn page detection', N'true'
> GO
> exec sp_dboption N'eDOC', N'read only', N'false'
> GO
> exec sp_dboption N'eDOC', N'dbo use', N'false'
> GO
> exec sp_dboption N'eDOC', N'single', N'false'
> GO
> exec sp_dboption N'eDOC', N'autoshrink', N'false'
> GO
> exec sp_dboption N'eDOC', N'ANSI null default', N'false'
> GO
> exec sp_dboption N'eDOC', N'recursive triggers', N'false'
> GO
> exec sp_dboption N'eDOC', N'ANSI nulls', N'false'
> GO
> exec sp_dboption N'eDOC', N'concat null yields null', N'false'
> GO
> exec sp_dboption N'eDOC', N'cursor close on commit', N'false'
> GO
> exec sp_dboption N'eDOC', N'default to local cursor', N'false'
> GO
> exec sp_dboption N'eDOC', N'quoted identifier', N'false'
> GO
> exec sp_dboption N'eDOC', N'ANSI warnings', N'false'
> GO
> exec sp_dboption N'eDOC', N'auto create statistics', N'true'
> GO
> exec sp_dboption N'eDOC', N'auto update statistics', N'true'
> GO
> use [eDOC]
> GO
> if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1
> exec sp_fulltext_database N'enable'
> GO
> if not exists (select * from dbo.sysfulltextcatalogs where name =
> N'DocumentFullText')
> exec sp_fulltext_catalog N'DocumentFullText', N'create'
> GO
> CREATE TABLE [dbo].[tblCategories] (
> [CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
> [CategoryName] [char] (100) ,
> [CreattionDate] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblDocument] (
> [DocumentID] [int] IDENTITY (1000, 1) NOT NULL ,
> [CategoryID] [int] NOT NULL ,
> [SubCategoryID] [int] NOT NULL ,
> [Title] [char] (100) ,
> [KeyWords] [char] (100) ,
> [SubmitterID] [int] NOT NULL ,
> [CreationDate] [datetime] NULL ,
> [RevisedBy] [int] NOT NULL ,
> [RevisedDate] [datetime] NULL ,
> [Document] [text] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblSubCategories] (
> [SubCategoyID] [int] IDENTITY (1, 1) NOT NULL ,
> [CategoryID] [int] NOT NULL ,
> [SubcategoryName] [char] (100) NOT NULL ,
> [CreationDate] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblSubmitter] (
> [SubmitterID] [int] IDENTITY (1, 1) NOT NULL ,
> [SubmitterName] [char] (40) NOT NULL ,
> [Username] [varchar] (20) NULL ,
> [Password] [varchar] (20) NULL ,
> [CreationDate] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblSystem] (
> [SystemID] [int] IDENTITY (10000, 5) NOT NULL ,
> [SubmitterID] [int] NOT NULL ,
> [SystemName] [char] (100) NOT NULL ,
> [RevisedDate] [datetime] NOT NULL ,
> [RevisedBy] [char] (100) NOT NULL ,
> [Document] [text] NOT NULL ,
> [CreationDate] [datetime] NOT NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblCategories] WITH NOCHECK ADD
> CONSTRAINT [PK_tblCategories] PRIMARY KEY CLUSTERED
> (
> [CategoryID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblDocument] WITH NOCHECK ADD
> CONSTRAINT [PK_tblDocument] PRIMARY KEY CLUSTERED
> (
> [DocumentID]
> ) ON [PRIMARY]
> GO
>
> USE edoc
> EXEC sp_fulltext_database 'enable'
> go
> if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1
> exec sp_fulltext_database N'enable'
> GO
> if not exists (select * from dbo.sysfulltextcatalogs where name =
> N'DocumentFullText')
> exec sp_fulltext_catalog N'DocumentFullText', N'create'
> GO
> exec sp_fulltext_table N'[dbo].[tblDocument]', N'create',
> N'DocumentFullText', N'PK_tblDocument'
> GO
> exec sp_fulltext_column N'[dbo].[tblDocument]', N'Document', N'add', 1033
> GO
> exec sp_fulltext_table N'[dbo].[tblDocument]', N'activate'
> GO
> ALTER TABLE [dbo].[tblSubCategories] WITH NOCHECK ADD
> CONSTRAINT [PK_tblSubCategories] PRIMARY KEY CLUSTERED
> (
> [SubCategoyID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblSubmitter] WITH NOCHECK ADD
> CONSTRAINT [PK_tblSubmitter] PRIMARY KEY CLUSTERED
> (
> [SubmitterID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblSystem] WITH NOCHECK ADD
> CONSTRAINT [PK_tblSystem] PRIMARY KEY CLUSTERED
> (
> [SystemID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblCategories] WITH NOCHECK ADD
> CONSTRAINT [DF_tblCategories_CreattionDate] DEFAULT (getdate()) FOR
> [CreattionDate]
> GO
> ALTER TABLE [dbo].[tblDocument] WITH NOCHECK ADD
> CONSTRAINT [DF_tblDocument_CreationDate] DEFAULT (getdate()) FOR
> [CreationDate]
> GO
> ALTER TABLE [dbo].[tblSubCategories] WITH NOCHECK ADD
> CONSTRAINT [DF_tblSubCategories_CreationDate] DEFAULT (getdate()) FOR
> [CreationDate]
> GO
> ALTER TABLE [dbo].[tblSubmitter] WITH NOCHECK ADD
> CONSTRAINT [DF_tblSubmitter_CreationDate] DEFAULT (getdate()) FOR
> [CreationDate]
> GO
> ALTER TABLE [dbo].[tblSystem] WITH NOCHECK ADD
> CONSTRAINT [DF_tblSystem_RevisedDate] DEFAULT (getdate()) FOR [RevisedDate],
> CONSTRAINT [DF_tblSystem_CreationDate] DEFAULT (getdate()) FOR [CreationDate]
> GO
> ALTER TABLE [dbo].[tblDocument] ADD
> CONSTRAINT [FK_tblDocument_tblCategories] FOREIGN KEY
> (
> [CategoryID]
> ) REFERENCES [dbo].[tblCategories] (
> [CategoryID]
> ),
> CONSTRAINT [FK_tblDocument_tblSubCategories1] FOREIGN KEY
> (
> [SubCategoryID]
> ) REFERENCES [dbo].[tblSubCategories] (
> [SubCategoyID]
> ),
> CONSTRAINT [FK_tblDocument_tblSubmitter2] FOREIGN KEY
> (
> [SubmitterID]
> ) REFERENCES [dbo].[tblSubmitter] (
> [SubmitterID]
> )
> GO
> ALTER TABLE [dbo].[tblSubCategories] ADD
> CONSTRAINT [FK_tblSubCategories_tblCategories] FOREIGN KEY
> (
> [CategoryID]
> ) REFERENCES [dbo].[tblCategories] (
> [CategoryID]
> )
> GO
> ALTER TABLE [dbo].[tblSystem] ADD
> CONSTRAINT [FK_tblSystem_tblSubmitter1] FOREIGN KEY
> (
> [SubmitterID]
> ) REFERENCES [dbo].[tblSubmitter] (
> [SubmitterID]
> )|||Jeff,
> But where I'm really at a loss is the stored procedures themselves...
So, the column document "freetext (document, @.searchterm)" is the only
column that is FT-enabled. Correct?
Could you provide more details on the SQL Server version (@.@.version) as well
as the OS platform, and language of text in the document column? As all of
these variables are important for functionality as is the number of rows in
your FT-enabled table.
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:BDC416B4-331A-46C4-A6C3-85F9844DC90C@.microsoft.com...
> Oh yes! I remember that trick of setting the variables to null now...
> Thanks!!
> "Jeff" wrote:
>|||On Sun, 11 Sep 2005 11:22:04 -0700, Jeff wrote:
(snip)
>But where I'm really at a loss is the stored procedures themselves. So how
>can I turn the below basic procedure into one that will accept the various
>options and return results. Obviously, as it stands now, I can't leave a
>variable blank...
>@.searchterm must have something entere in it
>@.Category must accept either a 1, 2, or be left blank to return both 1 & 2.
>@.Subcategory same as above
>@.Submitter same as above
(snip)
Hi Jeff,
I attempted to answer that question by providing a link to Erland's page
about this: http://www.sommarskog.se/dyn-search.html. Sure, it's a long
read, but (IMO) well worth the time you spend reading it. You'll find
that Erland presents many ways to achieve what you are trying to do
(searching with optional search arguments), and elaborates on the pros
and cons of all the various methods.
I see that Erland now has also posted a solution taht uses one of the
method's described on his page. I trust that this was enough to get you
going (but feel free to ask again if it's not!).
But I still recommend that you take the time to read the complete
article - as I said: time well spent!!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> This DB is to hold information so that our helpdesk can search it for sup
port information. <<
Have you looked into a Help Desk package instead of writing your own?

Challenge: nested FOR XML EXPLICIT query with data not in target t

I am trying to create a nested FOR XML EXPLICIT query where the majority of the data used in the query does not come from the target table being referenced.
Actually, only a few fields will come from the target table and when those fields appear in the data I want the FOR XML EXPLICIT query to duplicate the entire document structure for each time this happens only changing those elements to which the data cor
responds to (since all of the other elements are static anyway).
What I want is this:
<docs>
<doc><settings><doctype>Letter_w_Attachments</doctype><language>ENGLISH</language><type>CoverLetter</type><branch>Mailroom</branch><printer>PRT1</printer></settings><standard><ITEM>6282</ITEM></standard><multiused><item><AttachmentID>?</AttachmentID><Comm
ent>?</Comment><DocName>?</DocName><NbrOfPages>?</NbrOfPages></item></multiused>
</doc>
<doc><settings><doctype>Letter_w_Attachments</doctype><language>ENGLISH</language><type>CoverLetter</type><branch>Mailroom</branch><printer>PRT1</printer></settings><standard><ITEM>6283</ITEM></standard><multiused><item><AttachmentID>?</AttachmentID><Comm
ent>?</Comment><DocName>?</DocName><NbrOfPages>?</NbrOfPages></item></multiused>
</doc>
</docs>
However what I am getting is this:
<docs>
<doc><settings><doctype>Letter_w_Attachments</doctype><language>ENGLISH</language><type>CoverLetter</type><branch>Mailroom</branch><printer>PRT1</printer></settings><standard><ITEM>6282</ITEM></standard><standard><ITEM>6283</ITEM></standard><standard><ITE
M>6284</ITEM></standard><multiused><item><AttachmentID>?</AttachmentID><Comment>?</Comment><DocName>?</DocName><NbrOfPages>?</NbrOfPages></item></multiused>
</doc>
</docs>
The <standard> tag is repeating multiple times in a single document where I want only 1 <standard> tag to appear a document. Additional <standard> tags should trigger additional documents to be generated.
Here is the FOR XML EXPLICIT query I am currently using:
SET NOCOUNT ON
SELECT
1 AS Tag
,NULL AS Parent
,NULL AS [docs!1]
,NULL AS [doc!2!ordering!hide]
,NULL AS [doc!2]
,NULL AS [settings!3!ordering!hide]
,NULL AS [settings!3!doctype!element]
,NULL AS [settings!3!language!element]
,NULL AS [settings!3!type!element]
,NULL AS [settings!3!branch!element]
,NULL AS [settings!3!printer!element]
,NULL AS [standard!4!ordering!hide]
,NULL AS [standard!4!ITEM!element]
,NULL AS [multiused!5!ordering!hide]
,NULL AS [multiused!5]
,NULL AS [item!6!ordering!hide]
,NULL AS [item!6!AttachmentID!element]
,NULL AS [item!6!Comment!element]
,NULL AS [item!6!DocName!element]
,NULL AS [item!6!NbrOfPages!element]
UNION ALL
SELECT
2 AS Tag
,1 AS Parent
--docs
,NULL
--order doc
,NULL
--doc
,NULL
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--order standard
,NULL
--standard
,NULL
--order multiused
,NULL
--multiused
,NULL
--order item
,NULL
--item
,NULL
,NULL
,NULL
,NULL
UNION ALL
SELECT
3 AS Tag
,2 AS Parent
--docs
,NULL
--order doc
,NULL
--doc
,NULL
--order settings
,NULL
--settings
,'Letter_w_Attachments'
,'ENGLISH'
,'CoverLetter'
,'Mailroom'
,'PRT1'
--order standard
,NULL
--standard
,NULL
--order multiused
,NULL
--multiused
,NULL
--order item
,NULL
--item
,NULL
,NULL
,NULL
,NULL
UNION ALL
SELECT
4 AS Tag
,2 AS Parent
--docs
,NULL
--order doc
,NULL
--doc
,NULL
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--order standard
,NULL
--standard
,ID
--order multiused
,NULL
--multiused
,NULL
--order item
,NULL
--item
,NULL
,NULL
,NULL
,NULL
FROM vConsolidationPrinting
UNION ALL
SELECT
5 AS Tag
,2 AS Parent
--docs
,NULL
--order doc
,NULL
--doc
,NULL
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--order standard
,NULL
--standard
,NULL
--order multiused
,NULL
--multiused
,NULL
--order item
,NULL
--item
,NULL
,NULL
,NULL
,NULL
UNION ALL
SELECT
6 AS Tag
,5 AS Parent
--docs
,NULL
--order doc
,NULL
--doc
,NULL
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--order standard
,NULL
--standard
,NULL
--order multiused
,NULL
--multiused
,NULL
--order item
,NULL
--item
,'?'
,'?'
,'?'
,'?'
UNION ALL
SELECT
5 AS Tag
,2 AS Parent
--docs
,NULL
--order doc
,NULL
--doc
,NULL
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--order standard
,NULL
--standard
,NULL
--order multiused
,NULL
--multiused
,NULL
--order item
,NULL
--item
,NULL
,NULL
,NULL
,NULL
ORDER BY
[doc!2!ordering!hide]
,[settings!3!ordering!hide]
,[standard!4!ordering!hide]
,[multiused!5!ordering!hide]
,[item!6!ordering!hide]
FOR XML EXPLICIT
Any assistance would be most helpful.
Thank you.
The solution can be found by envisioning how the relation that is being
aggregated needs to look like:
1 row for every element. Thus if you want as many documents as <standard>
elements, you need to generate more than one and use some common id to group
them with their children.
Try for example (I simplified the query a bit without loss of
functionality):
SELECT
1 AS Tag
,NULL AS Parent
,ID AS [docs!1!id!hide]
,NULL AS [doc!2!ordering!hide]
,NULL AS [settings!3!ordering!hide]
,NULL AS [settings!3!doctype!element]
,NULL AS [settings!3!language!element]
,NULL AS [settings!3!type!element]
,NULL AS [settings!3!branch!element]
,NULL AS [settings!3!printer!element]
,NULL AS [standard!4!ITEM!element]
,NULL AS [multiused!5!ordering!hide]
--,NULL AS [item!6!ordering!hide]
,NULL AS [item!6!AttachmentID!element]
,NULL AS [item!6!Comment!element]
,NULL AS [item!6!DocName!element]
,NULL AS [item!6!NbrOfPages!element]
FROM vConsolidationPrinting
UNION ALL
SELECT
2 AS Tag
,1 AS Parent
--docs
,ID
--order doc
,ID
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--standard
,NULL
--multiused
,NULL
--item
,NULL
,NULL
,NULL
,NULL
FROM vConsolidationPrinting
UNION ALL
SELECT
3 AS Tag
,2 AS Parent
--docs
,ID
--order doc
,ID
--order settings
,ID
--settings
,'Letter_w_Attachments'
,'ENGLISH'
,'CoverLetter'
,'Mailroom'
,'PRT1'
--standard
,NULL
--multiused
,NULL
--item
,NULL
,NULL
,NULL
,NULL
FROM vConsolidationPrinting
UNION ALL
SELECT
4 AS Tag
,2 AS Parent
--docs
,ID
--order doc
,ID
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--standard
,ID
--multiused
,NULL
--item
,NULL
,NULL
,NULL
,NULL
FROM vConsolidationPrinting
UNION ALL
SELECT
5 AS Tag
,2 AS Parent
--docs
,ID
--order doc
,ID
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--standard
,NULL
--multiused
,ID
--item
,NULL
,NULL
,NULL
,NULL
FROM vConsolidationPrinting
UNION ALL
SELECT
6 AS Tag
,5 AS Parent
--docs
,ID
--order doc
,ID
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--standard
,NULL
--multiused
,ID
--item
,'?'
,'?'
,'?'
,'?'
FROM vConsolidationPrinting
/*
UNION ALL
SELECT
5 AS Tag
,2 AS Parent
--docs
,NULL
--order doc
,NULL
--doc
,NULL
--order settings
,NULL
--settings
,NULL
,NULL
,NULL
,NULL
,NULL
--order standard
,NULL
--standard
,NULL
--order multiused
,NULL
--multiused
,NULL
--order item
,NULL
--item
,NULL
,NULL
,NULL
,NULL
*/
ORDER BY
[docs!1!id!hide],[doc!2!ordering!hide],Tag,[settings!3!ordering!hide]
,[standard!4!ITEM!element]
,[multiused!5!ordering!hide]
, [item!6!AttachmentID!element]
FOR XML EXPLICIT
HTH
Michael
PS: BTW, this can be done much easier in SQL Server 2005 with the new PATH
mode. Here it is:
SELECT
'Letter_w_Attachments' as "settings/doctype",
'ENGLISH' as "settings/language",
'CoverLetter' as "settings/type",
'Mailroom' as "settings/branch",
'PRT1' as "settings/printer",
ID as "standard/ITEM",
'?' as "multiused/item/AttachmentID",
'?' as "multiused/item/Comment",
'?' as "multiused/item/DocName",
'?' as "multiused/item/NbrOfPages"
FROM vConsolidationPrinting
FOR XML Path('doc'), ROOT('docs')
"JRutberg" <JRutberg@.discussions.microsoft.com> wrote in message
news:303784BC-7CEC-4AA2-A1AD-1CAF38C1CE1D@.microsoft.com...
>I am trying to create a nested FOR XML EXPLICIT query where the majority of
>the data used in the query does not come from the target table being
>referenced.
> Actually, only a few fields will come from the target table and when those
> fields appear in the data I want the FOR XML EXPLICIT query to duplicate
> the entire document structure for each time this happens only changing
> those elements to which the data corresponds to (since all of the other
> elements are static anyway).
> What I want is this:
> <docs>
> <doc><settings><doctype>Letter_w_Attachments</doctype><language>ENGLISH</language><type>CoverLetter</type><branch>Mailroom</branch><printer>PRT1</printer></settings><standard><ITEM>6282</ITEM></standard><multiused><item><AttachmentID>?</AttachmentID><Co
mment>?</Comment><DocName>?</DocName><NbrOfPages>?</NbrOfPages></item></multiused>
> </doc>
> <doc><settings><doctype>Letter_w_Attachments</doctype><language>ENGLISH</language><type>CoverLetter</type><branch>Mailroom</branch><printer>PRT1</printer></settings><standard><ITEM>6283</ITEM></standard><multiused><item><AttachmentID>?</AttachmentID><Co
mment>?</Comment><DocName>?</DocName><NbrOfPages>?</NbrOfPages></item></multiused>
> </doc>
> </docs>
> However what I am getting is this:
> <docs>
> <doc><settings><doctype>Letter_w_Attachments</doctype><language>ENGLISH</language><type>CoverLetter</type><branch>Mailroom</branch><printer>PRT1</printer></settings><standard><ITEM>6282</ITEM></standard><standard><ITEM>6283</ITEM></standard><standard><I
TEM>6284</ITEM></standard><multiused><item><AttachmentID>?</AttachmentID><Comment>?</Comment><DocName>?</DocName><NbrOfPages>?</NbrOfPages></item></multiused>
> </doc>
> </docs>
> The <standard> tag is repeating multiple times in a single document where
> I want only 1 <standard> tag to appear a document. Additional <standard>
> tags should trigger additional documents to be generated.
> Here is the FOR XML EXPLICIT query I am currently using:
>
> SET NOCOUNT ON
> SELECT
> 1 AS Tag
> ,NULL AS Parent
> ,NULL AS [docs!1]
> ,NULL AS [doc!2!ordering!hide]
> ,NULL AS [doc!2]
> ,NULL AS [settings!3!ordering!hide]
> ,NULL AS [settings!3!doctype!element]
> ,NULL AS [settings!3!language!element]
> ,NULL AS [settings!3!type!element]
> ,NULL AS [settings!3!branch!element]
> ,NULL AS [settings!3!printer!element]
> ,NULL AS [standard!4!ordering!hide]
> ,NULL AS [standard!4!ITEM!element]
> ,NULL AS [multiused!5!ordering!hide]
> ,NULL AS [multiused!5]
> ,NULL AS [item!6!ordering!hide]
> ,NULL AS [item!6!AttachmentID!element]
> ,NULL AS [item!6!Comment!element]
> ,NULL AS [item!6!DocName!element]
> ,NULL AS [item!6!NbrOfPages!element]
> UNION ALL
> SELECT
> 2 AS Tag
> ,1 AS Parent
> --docs
> ,NULL
> --order doc
> ,NULL
> --doc
> ,NULL
> --order settings
> ,NULL
> --settings
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> --order standard
> ,NULL
> --standard
> ,NULL
> --order multiused
> ,NULL
> --multiused
> ,NULL
> --order item
> ,NULL
> --item
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> UNION ALL
> SELECT
> 3 AS Tag
> ,2 AS Parent
> --docs
> ,NULL
> --order doc
> ,NULL
> --doc
> ,NULL
> --order settings
> ,NULL
> --settings
> ,'Letter_w_Attachments'
> ,'ENGLISH'
> ,'CoverLetter'
> ,'Mailroom'
> ,'PRT1'
> --order standard
> ,NULL
> --standard
> ,NULL
> --order multiused
> ,NULL
> --multiused
> ,NULL
> --order item
> ,NULL
> --item
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> UNION ALL
> SELECT
> 4 AS Tag
> ,2 AS Parent
> --docs
> ,NULL
> --order doc
> ,NULL
> --doc
> ,NULL
> --order settings
> ,NULL
> --settings
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> --order standard
> ,NULL
> --standard
> ,ID
> --order multiused
> ,NULL
> --multiused
> ,NULL
> --order item
> ,NULL
> --item
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> FROM vConsolidationPrinting
> UNION ALL
> SELECT
> 5 AS Tag
> ,2 AS Parent
> --docs
> ,NULL
> --order doc
> ,NULL
> --doc
> ,NULL
> --order settings
> ,NULL
> --settings
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> --order standard
> ,NULL
> --standard
> ,NULL
> --order multiused
> ,NULL
> --multiused
> ,NULL
> --order item
> ,NULL
> --item
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> UNION ALL
> SELECT
> 6 AS Tag
> ,5 AS Parent
> --docs
> ,NULL
> --order doc
> ,NULL
> --doc
> ,NULL
> --order settings
> ,NULL
> --settings
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> --order standard
> ,NULL
> --standard
> ,NULL
> --order multiused
> ,NULL
> --multiused
> ,NULL
> --order item
> ,NULL
> --item
> ,'?'
> ,'?'
> ,'?'
> ,'?'
> UNION ALL
> SELECT
> 5 AS Tag
> ,2 AS Parent
> --docs
> ,NULL
> --order doc
> ,NULL
> --doc
> ,NULL
> --order settings
> ,NULL
> --settings
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> --order standard
> ,NULL
> --standard
> ,NULL
> --order multiused
> ,NULL
> --multiused
> ,NULL
> --order item
> ,NULL
> --item
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ORDER BY
> [doc!2!ordering!hide]
> ,[settings!3!ordering!hide]
> ,[standard!4!ordering!hide]
> ,[multiused!5!ordering!hide]
> ,[item!6!ordering!hide]
> FOR XML EXPLICIT
>
> Any assistance would be most helpful.
> Thank you.
>

Challenge: Make SQL Server run as bad as Oracle

NEVER let anyone tell you that Oracle is faster than SQL Server:
http://www.dbforums.com/showthread.php?p=6265757#post6265757
So here is my challenge. What would you have to do to SQL Server to make it take 10 minutes to return a count from an empty table? Other than coding a loop that waits 10 minutes, of course.
I'm not sure it could be done.Apparently the issue I have encountered is due to something called the High Water Mark or HWM in Oracle. Naturally, this concept is not even mentioned in any of the Oracle programming and administration books that I have.
Oracle retains and reserves the maximum number of pages ever used by a table, even after the records in those pages have been deleted. After that, even when performing a simple COUNT of records on the table, Oracl scans all the previously used pages as if they still had data in them.
Amazing.|||come on, pal, don't hold it in, let it out, you'll feel a lot better

you've just given reason #421 for why i am ~so~ not a DBA

:)|||I have heard of the HWM in reference to attempting to resize (shrink) files, but not on tables. I would expect analyzing the table will shorten the response, as well. Remember, Oracle does not have auto-update stats. And there have been some cases in SQL Server, where I have thought seriously about disabling it on a few tables.|||Apparently the issue I have encountered is due to something called the High Water Mark or HWM in Oracle. Naturally, this concept is not even mentioned in any of the Oracle programming and administration books that I have.
Oracle retains and reserves the maximum number of pages ever used by a table, even after the records in those pages have been deleted. After that, even when performing a simple COUNT of records on the table, Oracl scans all the previously used pages as if they still had data in them.
Amazing.

ALTER TABLE [table_name] DEALLOCATE UNUSED;

Go on, ask me how I know. Go on ask...please!

'Cause I just (today) passed 1Z0-033 (9i Tuning and Performance). That was actually one of the test questions. And you thought those tests never had any practical value!

Regards,

hmscott|||Congrats on the test, hmscott. But won't the DEALLOCATE UNUSED merely deallocate extents above the HWM? I have no good test system to try that on.|||Congrats on the test, hmscott. But won't the DEALLOCATE UNUSED merely deallocate extents above the HWM? I have no good test system to try that on.

MCrowley,

From my study guide...

The HWM is significant because a User's Server Process reads all the segment block's up to the HWM when performing a full table scan...

...Since the HWM does not move when rows are deleted from a segment, many empty blocks may end up being scanned...

...This unused space can be released back to the tablespace using the ALTER TABLE [tablename] DEALLOCATE UNUSED; command.

So far so good (maybe). This stuff gets me confused and I did not pass the test by a wide margin...

...reading just a page further on in the guide...

There are two techniques for moving a table's HWM to the appropriate level:
- Export; then drop or truncate the original and re-import
- Use the ALTER TABLE ... MOVE and specify a new TABLESPACE to store the table.

Like I said, I was marginal on the test. I thought I was better prepared, but I need to hit the books yet once again. It looks like my original statement was wrong and that you are correct. DEALLOCATE UNUSED will only clear the stuff above the HWM. You'll need to use one of the latter two techniques to fix things properly. From experience on 8i, I know that if you use the ALTER TABLE ... MOVE command, you will also have to remember to rebuild associated indexes.

Regards,

hmscott|||Garr. I would have expected analyze to help you somewhat, but I guess not, eh?|||Garr. I would have expected analyze to help you somewhat, but I guess not, eh?

Mayhap it would. Here's the thing that kills me about Oracle...let me try to run down my thoughts on this particular scenario...

1. The server just executed the query, so the blocks are in the db buffer, no?

2. Maybe not, it was a full table scan and therefore the blocks went immediately to the LRU (least recently used) end of the LRU "conveyor" which means they got flushed out of cache almost immediately, right?

3. Maybe not, was there other activity affecting the db cache at the time?

4. Was the execution plan cached in the shared pool? Should be. But would that make a difference (if the execution plan still says that he has to scan every block up to the HWM)?

Like I said, this is what kills me about Oracle; the more I learn about it, the less confident/comfortable I feel about it. There are hundreds of undocumented startup parameters (in addition to the 200 or so documented ones). The stack of publications that I have for Oracle (on file and on paper) and the list of web sites that I have bookmarked for help trouble shooting Oracle issues absolutely dwarfs the stuff that I have for SQL.

For SQL, I know that if I have an issue, I can come here and get an answer quick!

The pay for an experienced Oracle DBA on the other hand...:D

Regards,

hmscott|||The pay for an experienced Oracle DBA on the other hand...:D

but is it worth it?

my 2 cents: nothing could be more boring in my mind, to become one of the world's experts on 200 undocumented startup parameters. It's like the SAP devs that become experts on some table named ZJSJ__3338dj. So what if they make more. That kind of knowledge is so arbitrary, and has nothing to do with real logic puzzles and problem solving which is what makes programming fun (to me anyway). Just learning a bunch of arbitrary configuration so you know which knob to tweak when things go south? Yuck. Let someone else do it and they can have the fatter paycheck.

I guess that's why I'm not a dba :)|||That's probably why, as a rule, all Oracle documentation sucks. If it took you years to find out about an obscure command, and that knowledge was that source of your salary, would you post it somewhere where the next person could google it in five minutes?|||I think the causal chain goes the other way: salaries are higher for oracle dbas because they have a skill that's harder to acquire than the same skill for sql server. and that skill is harder to acquire because the docs are poor, and because the product is more complicated to boot.

If oracle wanted to sell more licenses, they should make their product easier to use and administer. this would be good for the people that buy their licenses, and bad for the average oracle dba -- because their guild would be easier to join, causing their salaries to go down.

I don't see any real incentive for oracle to keep their docs poor, unless they are getting kickbacks from the dba guild :)

Challenge: Can you optimize this?

This code is attempting to find records that have a RegJrnID that does
not occur more than one time in the table.

The reason that I want to find records with non-duplicated RegJrnID
values is to create "reversal" records for these such that the reversal
record has identical values for every column except the TaxableAmount
which will contain a negative amount. (see: example data below).

/* Set up */

CREATE TABLE t1(RegJrnID INTEGER, InvoiceDate VARCHAR(8), InvoiceNumber
VARCHAR(20), TaxableAmount DECIMAL(32,8))

/* Example data */

INSERT INTO t1 VALUES (1, '20060101', '2321323', 100.00)
INSERT INTO t1 VALUES (9, '20060213', '2130009', 40.01)
INSERT INTO t1 VALUES (3, '20060101', '9402293', 512.44)
INSERT INTO t1 VALUES (1, '20060104', '2321323', -100.00)
INSERT INTO t1 VALUES (4, '20060105', '9302221', 612.12)
INSERT INTO t1 VALUES (5, '20060105', '0003235', 18.11)
INSERT INTO t1 VALUES (6, '20060111', '5953432', 2101.21)
INSERT INTO t1 VALUES (3, '20060111', '9402293', -512.44)
INSERT INTO t1 VALUES (7, '20060115', '4234444', 44.52)
INSERT INTO t1 VALUES (8, '20060115', '0342222', 95.21)
INSERT INTO t1 VALUES (6, '20060119', '5953432', -2101.21)
INSERT INTO t1 VALUES (2, '20060101', '5440033', 231.01)

/* Show what's in the table - just because */

SELECT * FROM t1 ORDER BY RegJrnID, InvoiceDate

/* Query for records to reverse */

SELECT *
FROM t1 a

/* Ignore records that have already been reversed */

WHERE a.RegJrnID != ALL

/* This subselect finds reversed records (i.e. those that have a
duplicate RegJrnID) */

(
SELECT b.RegJrnID
FROM t1 b
GROUP BY b.RegJrnID
HAVING COUNT(*) > 1
)

/* User selection criteria are appended here */

/* AND InvoiceNumber >= '5000000' AND InvoiceNumber <= '7500000' */

/* Make the results look pretty (optional) */

ORDER BY RegJrnID

/* Housekeeping */

DROP TABLE t1There are many ways to accomplish that. I would start with something
this (untested):

select pos.RegJrnID
from(
select * from t1 where TaxableAmount >0
) pos
left outer join
from(
select * from t1 where TaxableAmount <0
) neg
on pos.RegJrnID = neg.RegJrnID
where neg.RegJrnID is null|||Here's the tested (and slightly modified) version of your code...

SELECT pos.*

FROM
(
SELECT * FROM t1 WHERE TaxableAmount > 0
) pos
LEFT OUTER JOIN
(
SELECT * FROM t1 WHERE TaxableAmount < 0
) neg
ON pos.RegJrnID = neg.RegJrnID

WHERE neg.RegJrnID IS NULL

/* Make the results look pretty (optional) */

ORDER BY pos.RegJrnID|||According to the SQL Query analyzer your query is better going head to
head with the original representing 43.43% of the batch and the
original representing 56.57% of the batch.

A 13% improvement!

Thanks!|||On 6 Jun 2006 13:24:54 -0700, octangle wrote:

>This code is attempting to find records that have a RegJrnID that does
>not occur more than one time in the table.
>The reason that I want to find records with non-duplicated RegJrnID
>values is to create "reversal" records for these such that the reversal
>record has identical values for every column except the TaxableAmount
>which will contain a negative amount. (see: example data below).

Hi octangle,

Thanks for providing CREATE TABLE and INSERT statements. This made it
very easy to set up a test DB and fun to find an answer.

What worries me is that there's no primary key in your table. I hope
that you just forgot to include it in the script and that your real
table does have a key!

Here's a much quicker way. Running both your version and my version with
execution plan displayed, yours took 72% and mine 28%. Removing the
ORDER BY changed this to 64% / 36%. Still a nice gain.

SELECT RegJrnID, MAX(InvoiceDate),
MAX(InvoiceNumber), MAX(TaxableAmount)
FROM t1
GROUP BY RegJrnID
HAVING COUNT(*) = 1
--ORDER BY RegJrnID

And here's another one, but it's correctness depends on some assumptions
I had to make because you forgot to include the primary key. With ORDER
BY, it's slightly more expensive than the previous version. With the
ORDER BY commented out, it only costs half as much!

SELECT RegJrnID, InvoiceDate,
InvoiceNumber, TaxableAmount
FROM t1 AS a
WHERE NOT EXISTS
(SELECT *
FROM t1 AS b
WHERE a.RegJrnID = b.RegJrnID
AND a.InvoiceDate <> b.InvoiceDate)
--ORDER BY RegJrnID

(Note - I have compared these queries using the sample data you provided
on a SQL Server 2005 database on my computer. Results will probably vary
on yoour database, especially if your table has indexes, your data
distribution is not like the sample data, and/or you are running another
version of SQL Server. I recommend that you test out the various
suggestions yourself before deciding.)

--
Hugo Kornelis, SQL Server MVP|||Upon further review...

As written in the previous post, records with a TaxableAmount of 0 will
not be found to be reversed... so in an attempt to remedy this I
modified the LEFT OUTER JOIN as follows:

FROM
(
SELECT * FROM t1 WHERE TaxableAmount >= 0
) pos
LEFT OUTER JOIN
(
SELECT * FROM t1 WHERE TaxableAmount < 0
) neg
ON pos.RegJrnID = neg.RegJrnID

This succeeds at finding the 0 TaxableAmount records... but once a
companion reversal record is inserted into the database both records
(the original and a the reversal) are found on subsequent queries using
this technique... since these records are retrieved by the query as
records to reverse, these get reversed again (thus making a total of 4
instances of the original record - instead of 2 which is all are
needed). And if we repeat the process a 0 TaxableAmount record will
redouble it instances every time the process is run...

Now the questions are...

Can the above LEFT OUTER JOIN be fixed?

OR

Should 0 TaxableAmounts be processed in their own pass with their own
query (yuck)?

OR

Is the original query really better because it works for all
TaxableAmounts despite the fact that its 13% slower...

OR

Is there another option??|||one more question: what if there is only one row with negative amount?

INSERT INTO t1 VALUES (11, '20060101', '2321323', -100.00)

and there is no corresponding row with positive amount? Nothing in the
posted DDL prevents you from that. In fact, originally I was
considering the query posted by Hugo, but realized it would return that
single row with negative amount and assumed it incorrect. It looks like
there might be no base for my assumption.|||Alexander Kuznetsov (AK_TIREDOFSPAM@.hotmail.COM) writes:
> one more question: what if there is only one row with negative amount?
> INSERT INTO t1 VALUES (11, '20060101', '2321323', -100.00)
> and there is no corresponding row with positive amount? Nothing in the
> posted DDL prevents you from that. In fact, originally I was
> considering the query posted by Hugo, but realized it would return that
> single row with negative amount and assumed it incorrect. It looks like
> there might be no base for my assumption.

Having watched the thread from aside, I think the real problem is that
the data model needs improvement. I would add a bit column "isbalanceentry"
or some such. And of course add a primary key. (InvoiceNumber,
isbalanceentry) looks like a candidate.

Better get the data model in order, before looking at smart queries.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 6 Jun 2006 17:45:58 -0700, Alexander Kuznetsov wrote:

>one more question: what if there is only one row with negative amount?
>INSERT INTO t1 VALUES (11, '20060101', '2321323', -100.00)
>and there is no corresponding row with positive amount? Nothing in the
>posted DDL prevents you from that. In fact, originally I was
>considering the query posted by Hugo, but realized it would return that
>single row with negative amount and assumed it incorrect. It looks like
>there might be no base for my assumption.

Hi Alexander,

I've seenn nothing in the original post that justifies special treatment
of negative amounts. If these should be excluded, then my version can
still be used - just add AND MAX(TaxableAmount) >= 0 to the HAVING
clause.

However, I agree with Erland that a redesign might be a better choice if
something like that is the case.

--
Hugo Kornelis, SQL Server MVP|||Erland Sommarskog wrote:
> Having watched the thread from aside, I think the real problem is that
> the data model needs improvement. I would add a bit column "isbalanceentry"
> or some such. And of course add a primary key. (InvoiceNumber,
> isbalanceentry) looks like a candidate.

I concur that the schema might need some work. I was thinking that a
single nullable column negated_date might be sufficient, so that
instead of inserting one more row one just needs to update negated_date.|||Below is an aggregate script that includes everyone's suggested queries
so far...

Based upon feedback I have beefed up the test records to more
accurately reflect all of the potential scenarios that need to be
handled by this quey.

The original query (Query attempt #1 (Octangle)) generates the desired
result set and therefore is the benchmark of correctness for my
purposes.

MWMWMWMWMWMWMWMWMWMWMWMWMWMWMWMWMW

/* Set up */

CREATE TABLE t1(RegJrnID INTEGER, InvoiceDate VARCHAR(8), InvoiceNumber
VARCHAR(20), TaxableAmount DECIMAL(32,8))

INSERT INTO t1 VALUES (0, '20060120', '0000033', 0.00)
INSERT INTO t1 VALUES (1, '20060101', '2321323', 100.00)
INSERT INTO t1 VALUES (9, '20060213', '2130009', 40.01)
INSERT INTO t1 VALUES (11, '20060324', '3321110', -1200.16)
INSERT INTO t1 VALUES (3, '20060101', '9402293', 512.44)
INSERT INTO t1 VALUES (1, '20060104', '2321323', -100.00)
INSERT INTO t1 VALUES (13, '20051127', '1034501', -77.50)
INSERT INTO t1 VALUES (4, '20060105', '9302221', 612.12)
INSERT INTO t1 VALUES (5, '20060105', '0003235', 18.11)
INSERT INTO t1 VALUES (10, '20060421', '0000033', 0.00)
INSERT INTO t1 VALUES (6, '20060111', '5953432', 2101.21)
INSERT INTO t1 VALUES (3, '20060111', '9402293', -512.44)
INSERT INTO t1 VALUES (12, '20060606', '0000001', 4431.55)
INSERT INTO t1 VALUES (7, '20060115', '4234444', 44.52)
INSERT INTO t1 VALUES (8, '20060115', '0342222', 95.21)
INSERT INTO t1 VALUES (6, '20060119', '5953432', -2101.21)
INSERT INTO t1 VALUES (2, '20060101', '5440033', 231.01)
INSERT INTO t1 VALUES (10, '20060517', '0000033', 0.00)
INSERT INTO t1 VALUES (11, '20060324', '3321110', 1200.16)
INSERT INTO t1 VALUES (12, '20060606', '0000001', -4431.55)

/* Show what's in the table */

SELECT * FROM t1 ORDER BY RegJrnID, InvoiceDate

/* Query for records to reverse */

/* Query attempt #1 (Octangle) */

/* Pros: correct */
/* Cons: slow */

SELECT *
FROM t1 a

/* Ignore records that have already been reversed */

WHERE a.RegJrnID != ALL

/* This subselect finds reversed records (i.e. those that have a
duplicate RegJrnID) */

(
SELECT b.RegJrnID
FROM t1 b
GROUP BY b.RegJrnID
HAVING COUNT(*) > 1
)

/* User selection criteria are appended here */

/* AND InvoiceNumber >= '5000000' AND InvoiceNumber <= '7500000' */

/*ORDER BY RegJrnID; * Make the results look pretty (optional) */

/* Query attempt #2 (Alexander) */

/* Pros: faster */
/* Cons: misses 0 TaxableAmounts */

SELECT pos.*
FROM
(
SELECT * FROM t1 WHERE TaxableAmount > 0
) pos
LEFT OUTER JOIN
(
SELECT * FROM t1 WHERE TaxableAmount < 0
) neg
ON pos.RegJrnID = neg.RegJrnID
WHERE neg.RegJrnID IS NULL
/*ORDER BY pos.RegJrnID * Make the results look pretty (optional) */

/* Query attempt #3 (Alexander - tweaked by Octangle) */

/* Pros: faster */
/* Cons: finds too many 0 TaxableAmounts */

SELECT pos.*
FROM
(
SELECT * FROM t1 WHERE TaxableAmount >= 0
) pos
LEFT OUTER JOIN
(
SELECT * FROM t1 WHERE TaxableAmount < 0
) neg
ON pos.RegJrnID = neg.RegJrnID
WHERE neg.RegJrnID IS NULL
/*ORDER BY pos.RegJrnID * Make the results look pretty (optional) */

/* Query attempt #4 (Hugo) */

/* Pros: correct , fastest, returns results in RegJrnID order with
ORDER BY clause */

SELECT RegJrnID, MAX(InvoiceDate) as "InvoiceDate",
MAX(InvoiceNumber) as "InvoiceNumber", MAX(TaxableAmount) as
"TaxableAmount"
FROM t1
GROUP BY RegJrnID
HAVING COUNT(*) = 1

/* Query attempt #5 (Hugo) */

/* Pros: fast */
/* Cons: not correct */

SELECT RegJrnID, InvoiceDate, InvoiceNumber, TaxableAmount
FROM t1 AS a
WHERE NOT EXISTS
(
SELECT *
FROM t1 AS b
WHERE a.RegJrnID = b.RegJrnID
AND a.InvoiceDate <> b.InvoiceDate
)
/*ORDER BY RegJrnID * Make the results look pretty (optional) */

/* Housekeeping */

DROP TABLE t1

MWMWMWMWMWMWMWMWMWMWMWMWMWMWMWMWMW

Queries as percent of batch (when just executing the queries in the
above script)

Query #1: 22.66% - Correct
Query #2: 19.77%
Query #3: 20.27%
Query #4: 12.63% - Correct
Query #5: 20.67%

Queries as percent when compared to only the original query (Query #1)

Query #1: 50.00% - Correct
Query #2: 42.58%
Query #3: 43.19%
Query #4: 32.14% - Correct
Query #5: 43.67%

At this point it looks like the clear winner is Query #4 by Hugo!

MWMWMWMWMWMWMWMWMWMWMWMWMWMWMWMWMW

To address some of the observations/comments:

1. Negative transactions are possible - I augmented the test data to
include this case.
2. This is for a commercial product that has numerous existing
customers, I inherited the data model that this table is based upon...
my coding constraits are:
- I cannot add any columns (due to how we version a column change would
force this release to be considered a major release and not a minor
release as desired)
- I should not add any indexes/primary keys/uniqueness constriants for
performance reasons (see below)

The purpose of this table to store processed transaction results. It
needs to be as efficient as possible for insertions, so as to not slow
down the transaction processing engine. Reporting (and reversing groups
of transactions) are secondary concerns and it is acceptable for these
functions to be slower.

I sincerely want to thank everyone who chipped in a comment or
suggestion on this...|||1. If negative transactions are possible, than my query is incorrect.
2. You probably need a much larger set of test data to test different
approaches against. Also I would say there are at lest 2 possible
situations:
- most transactions are already negated.
- most transactions have not been negated yet.
In some cases in different situations different queries are the best. I
would try both and see if one and the same query is the best.

Good luck!|||Alexander Kuznetsov wrote:
> 1. If negative transactions are possible, than my query is incorrect.
> 2. You probably need a much larger set of test data to test different
> approaches against. Also I would say there are at lest 2 possible
> situations:
> - most transactions are already negated.
> - most transactions have not been negated yet.
> In some cases in different situations different queries are the best. I
> would try both and see if one and the same query is the best.
> Good luck!

FYI

The normal situation would be that most transactions are not negated in
this table. Negation would only occur if the billing system was
un-doing a billing run for some technical or business reason...
Therefore negating transactions would be rare, theoretically...

I appreciate the notion of getting better test data - this will
naturally accrue as I implement and test this soultion. I will make
sure to compare performance as this project matures...

Thanks again...!|||/* Query attempt #4 (Hugo) */

SELECT RegJrnID, MAX(InvoiceDate) as "InvoiceDate", MAX(InvoiceNumber)
as "InvoiceNumber", MAX(TaxableAmount) as "TaxableAmount"
FROM t1
GROUP BY RegJrnID
HAVING COUNT(*) = 1

I talked to a few folks around the office and none of us had ever
though to use MAX() to force values out of a query using a GROUP BY
clause...

e.g. if the query were changed to look like this:

SELECT *
FROM t1
GROUP BY RegJrnID
HAVING COUNT(*) = 1

The following error occurs for each column not mentioned in the GROUP
BY clause: "Column 't1.InvoiceDate' is invalid in the select list
because it is not contained in either an aggregate function or the
GROUP BY clause." So MAX() forces these values to participate in the
result set generated by this query...

My question with this is, "Is this technique safe for all major DBs
(Oracle, SQL Server, DB2 and MySQL) and will it work with all column
types?"|||octangle (idea.vortex@.gmail.com) writes:
> /* Query attempt #4 (Hugo) */
> SELECT RegJrnID, MAX(InvoiceDate) as "InvoiceDate", MAX(InvoiceNumber)
> as "InvoiceNumber", MAX(TaxableAmount) as "TaxableAmount"
> FROM t1
> GROUP BY RegJrnID
> HAVING COUNT(*) = 1
> I talked to a few folks around the office and none of us had ever
> though to use MAX() to force values out of a query using a GROUP BY
> clause...
>...
> My question with this is, "Is this technique safe for all major DBs
> (Oracle, SQL Server, DB2 and MySQL) and will it work with all column
> types?"

Yes, it should on any RDBMS worth the name, as it is very plain standard
SQL.

Then again, MySQL has so many funny quirks, I suspect that one should
never take anything for granted with that engine.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||octangle (idea.vortex@.gmail.com) writes:
> - I should not add any indexes/primary keys/uniqueness constriants for
> performance reasons (see below)
> The purpose of this table to store processed transaction results. It
> needs to be as efficient as possible for insertions, so as to not slow
> down the transaction processing engine. Reporting (and reversing groups
> of transactions) are secondary concerns and it is acceptable for these
> functions to be slower.

There are good changes that a well-considered clustered index can improve
the performance. Not the least, because you can handle fragmentation better.

In any case, having a table without a primary key in order to save some
cycles on insertion is about criminal in my opinion. What to you when
the same data gets inserted twice? (Don't tell me that it never happens!).
And why InvoiceDate as varchar(8)? That's 10 bytes per date, instead of
8 with datetime or 4 with smalldatetime. Here's is a second risk for
errors. Wonder how many entries for 20060230 you have...

As for the actual challenge, I prefer to stay out. I don't really want
to contribute to something which is obviously flawed.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
> There are good changes that a well-considered clustered index can improve
> the performance. Not the least, because you can handle fragmentation better.
> In any case, having a table without a primary key in order to save some
> cycles on insertion is about criminal in my opinion. What to you when
> the same data gets inserted twice? (Don't tell me that it never happens!).
> And why InvoiceDate as varchar(8)? That's 10 bytes per date, instead of
> 8 with datetime or 4 with smalldatetime. Here's is a second risk for
> errors. Wonder how many entries for 20060230 you have...
> As for the actual challenge, I prefer to stay out. I don't really want
> to contribute to something which is obviously flawed.

Thanks for the tip on clustered index usage.

Criminal? A little severe - I'd call it a trade-off - a trade-off made
based upon the project requirements. Our process is a small part in a
larger customer billing cycle and everything we can do to be as small a
percentage of the overall work effort in the processing of every bill
is critical...

As it turns out the same RegJrnID must not be inserted twice. We have
programmatic control over this and if it were to occur there would be a
bug in the software or a serious proceedural issue on the part of the
user. In either case these represent bigger problems than the structure
of this table.

OK, OK - putting a date in a varchar is kludgy... again I simply
inherited this design... I think the big issue here was compatibility
with other RDBMS... We need to support DB2, Oracle, SQL Server and
MySQL with the same code base... So they all support character data
roughly the same... chalk it up as a rookie mistake... but oddly your
example that the table will allow Feb 30th could be seen as a feature
(i.e. we are fault tolerant of bad dates) - ultimately the generation
of the invoice date is controlled be the external application calling
our API - so allowing a bad date to float through to the DB isn't a big
deal to us...

Flawed? Again trade-offs have been made by SQL beginners... Funny thing
is that it seems to work very well for our customers and they seem
happy, so I'd say that flaws are in the eye of the beholder...

Thanks for holding us to a higher standard...

:-)|||octangle (idea.vortex@.gmail.com) writes:
> Criminal? A little severe - I'd call it a trade-off - a trade-off made
> based upon the project requirements.

I would not call it trade-off, only off.

Then again, I'm the coward kind of guy that always wear a safety belt
when I'm driving(*) and all that.

(*) OK, so it happened once I didn't put it on. That was when I was
drive up for my driving license!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 7 Jun 2006 10:54:44 -0700, octangle wrote:

>/* Query attempt #4 (Hugo) */
>/* Pros: correct , fastest, returns results in RegJrnID order with
>ORDER BY clause */
>SELECT RegJrnID, MAX(InvoiceDate) as "InvoiceDate",
>MAX(InvoiceNumber) as "InvoiceNumber", MAX(TaxableAmount) as
>"TaxableAmount"
>FROM t1
>GROUP BY RegJrnID
>HAVING COUNT(*) = 1

Hi octangle,

I don't know what the remark about returning results in RegJrnID order
with ORDER BY means - all queries will return results in that order if
yoou include an ORDER BY. And without the ORDER BY, some of the queries
might return the results in that order some of the time, maybe even
every time during testing, but there's no guarantee that it will remain
so in production. In shhort - if you need a specific order, use an ORDER
BY - always!

>/* Query attempt #5 (Hugo) */
>/* Pros: fast */
>/* Cons: not correct */

Only because the data you originally provided was not enough to show
what column oor combination of columns makes a row unique. And your
reply still doesn't show it, so my next attempt might well be wrong
again :-((

SELECT RegJrnID, InvoiceDate, InvoiceNumber, TaxableAmount
FROM t1 AS a
WHERE NOT EXISTS
(
SELECT *
FROM t1 AS b
WHERE a.RegJrnID = b.RegJrnID
AND ( a.TaxableAmount <> b.TaxableAmount
OR a.InvoiceDate <> b.InvoiceDate )
)

Without the ORDER BY, this is significantly faster than attempt #4 when
tested with your test data. But with a much larger set of test data,
attempt #4 is faster (though this might be different with your data, as
it might be distributed differently).

>2. This is for a commercial product that has numerous existing
>customers, I inherited the data model that this table is based upon...
>my coding constraits are:
>- I cannot add any columns (due to how we version a column change would
>force this release to be considered a major release and not a minor
>release as desired)
>- I should not add any indexes/primary keys/uniqueness constriants for
>performance reasons (see below)

I concur with everything Erlland says about this. And I'd still like to
know which (combination of) column(s) you can use to uniquely identify a
single row.

--
Hugo Kornelis, SQL Server MVP|||Hi There,
If the following is your requirement.
"
This code is attempting to find records that have a RegJrnID that does
not occur more than one time in the table.
"
Then

Select * from Yourtable where ID in (Select ID from group by ID having
count(1)=1)
-- Give some order by etc as per your requirement
Output:--
9, '20060213', '2130009', 40.01
4, '20060105', '9302221', 612.12
5, '20060105', '0003235', 18.11
7, '20060115', '4234444', 44.52
8, '20060115', '0342222', 95.21
2, '20060101', '5440033', 231.01

With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com

octangle wrote:
> This code is attempting to find records that have a RegJrnID that does
> not occur more than one time in the table.
> The reason that I want to find records with non-duplicated RegJrnID
> values is to create "reversal" records for these such that the reversal
> record has identical values for every column except the TaxableAmount
> which will contain a negative amount. (see: example data below).
> /* Set up */
> CREATE TABLE t1(RegJrnID INTEGER, InvoiceDate VARCHAR(8), InvoiceNumber
> VARCHAR(20), TaxableAmount DECIMAL(32,8))
> /* Example data */
> INSERT INTO t1 VALUES (1, '20060101', '2321323', 100.00)
> INSERT INTO t1 VALUES (9, '20060213', '2130009', 40.01)
> INSERT INTO t1 VALUES (3, '20060101', '9402293', 512.44)
> INSERT INTO t1 VALUES (1, '20060104', '2321323', -100.00)
> INSERT INTO t1 VALUES (4, '20060105', '9302221', 612.12)
> INSERT INTO t1 VALUES (5, '20060105', '0003235', 18.11)
> INSERT INTO t1 VALUES (6, '20060111', '5953432', 2101.21)
> INSERT INTO t1 VALUES (3, '20060111', '9402293', -512.44)
> INSERT INTO t1 VALUES (7, '20060115', '4234444', 44.52)
> INSERT INTO t1 VALUES (8, '20060115', '0342222', 95.21)
> INSERT INTO t1 VALUES (6, '20060119', '5953432', -2101.21)
> INSERT INTO t1 VALUES (2, '20060101', '5440033', 231.01)
> /* Show what's in the table - just because */
> SELECT * FROM t1 ORDER BY RegJrnID, InvoiceDate
> /* Query for records to reverse */
> SELECT *
> FROM t1 a
> /* Ignore records that have already been reversed */
> WHERE a.RegJrnID != ALL
> /* This subselect finds reversed records (i.e. those that have a
> duplicate RegJrnID) */
> (
> SELECT b.RegJrnID
> FROM t1 b
> GROUP BY b.RegJrnID
> HAVING COUNT(*) > 1
> )
> /* User selection criteria are appended here */
> /* AND InvoiceNumber >= '5000000' AND InvoiceNumber <= '7500000' */
> /* Make the results look pretty (optional) */
> ORDER BY RegJrnID
> /* Housekeeping */
> DROP TABLE t1

challenge...

How can i programmatically via Tsql change the datatype of all the columns of a table to varchar(1000)?

Like I have a table employee

Employee

(

colA int

colB int

colC varchar

)

If i run the tsql..

it should give me

Employee

(

colA varchar

colB varchar

colC varchar

)

Is the table empty? If not, what is the disposition of the data that is already in the table?

|||

Well you could try using a change script generated by Enterprise Manager, like this one:

Code Snippet

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Employee
(
ColA varchar(1000) NULL,
ColB varchar(1000) NULL,
ColC varchar(1000) NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Employee)
EXEC('INSERT INTO dbo.Tmp_Employee (ColA, ColB, ColC)
SELECT CONVERT(varchar(1000), ColA), CONVERT(varchar(1000), ColB), ColC FROM dbo.Employee (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.Employee
GO
EXECUTE sp_rename N'dbo.Tmp_Employee', N'Employee', 'OBJECT'
GO
COMMIT

|||

Please don't use the graphical tools to make schema changes. It can generate scripts that are inefficient and unnecessary. You can just use ALTER TABLE to change the column from int to varchar in this case. If you do use the tools then please make sure to review the scripts because there is lot of things that can be simplified or improved. For example, in above case there is no reason to do CREATE TABLE and INSERT. You can do SELECT...INTO - this can perform minimally logged operations & can run magnitudes of time faster.

Challenge with SQL Express Installation

I am having a problem with the installation of SQL Express. the uninstall program says that part of the Beta is still installed. then the uninstall program crashes with a syntax error saying that &nbsp; in wrong place. found it deleted it and then the program will not continue. i cant find any remnants of the beta but SQL Server express will not install!! Please help.

Check out the Setup log, it will give specific information about what components are still installed. If those components are not listed in Add/Remove Programs, you can uninstall them using:

msiexec -x <product ID>

Product ID will be listed in the setup log. This problem is discussed in detail in several other posts in this forum and in the Setup forum if you need more information.

Mike

|||where would i find the setup log?

Challenge to all DBAs

I have a challenge for all DBAs. I am getting INCONSISTENT TIMINGS for Querry Results from a SQL Server 2000 Standard Edition on a HP Proliant 2 CPU Server with 4 GB RAM and SCSI DrivAFAe.

Our Database is 72 million records and have 8 columns. Most of them are indexed, which are used in the "Where…" clause. In addition to independent indexes, we also have a covering index for 3 most frequently searched fields.

My challenge is that out of the 7000 odd queries that hit the server with various search conditions in a Week, 5% of the queries return result in less than a minute.

The same query at a different Time or with a different Value, returns results inconsistently. For e.g. searching FirstName = "Anna"; LastName = "Williams" returns result in 0.01 sec. Searching for FirstName "Benjamin"; LastName = "Watson" returns in 5 minutes.

Any kind of help is welcome and will be highly appreciated.

- Santy

san.rely@.gmail.com
Note: Edited to fix white on white font.

Run the queries in query analyzer, have it show the execution plan, and look and see what is different, if anything.

Challenge revisisted

Hi,
I'm trying to port som old access code to asp.net and the access solution ha
s a couple of queries to sql server that I'm not sure work as expected. I t
hink the idea of the query is to identify if the same event (slaugher of rei
ndeer) is registered in two
different places (field 5 in my last challenge), but two duplicate rows diff
ering only by place does not get selected by the query.
The query is as follows:
SELECT ImportID FROM Temp t WHERE ( EXISTS (
SELECT Year, Code1, Code2, Date FROM (
SELECT DISTINCT Year, Code1, Code2, Date , Place FROM Temp ) SUBQUERY
WHERE ( t.Year = SUBQUERY.Year )
AND ( t.Code1 = SUBQUERY.Code1 )
AND ( t.Code2 = SUBQUERY.Code2 )
AND ( t.Date = SUBQUERY.Date)
GROUP BY Year, Code1, Code2, Date
HAVING (COUNT(*) > 1) ))
Can anyone see if this query does anything useful?, or perhaps give me a que
ry that selects rows with same Year,Code1,Code2,Date, but with different Pla
ce.
The other query is essentially the same, using Zone instead of Place
Morten WennevikOn Mon, 14 Nov 2005 08:51:43 +0100, Morten Wennevik wrote:

>Hi,
>I'm trying to port som old access code to asp.net and the access solution has a cou
ple of queries to sql server that I'm not sure work as expected. I think the idea o
f the query is to identify if the same event (slaugher of reindeer) is registered in
tw
o different places (field 5 in my last challenge), but two duplicate rows differing only by
place does not get selected by the query.
>The query is as follows:
>SELECT ImportID FROM Temp t WHERE ( EXISTS (
> SELECT Year, Code1, Code2, Date FROM (
> SELECT DISTINCT Year, Code1, Code2, Date , Place FROM Temp ) SUBQUERY
> WHERE ( t.Year = SUBQUERY.Year )
> AND ( t.Code1 = SUBQUERY.Code1 )
> AND ( t.Code2 = SUBQUERY.Code2 )
> AND ( t.Date = SUBQUERY.Date)
> GROUP BY Year, Code1, Code2, Date
> HAVING (COUNT(*) > 1) ))
>Can anyone see if this query does anything useful?, or perhaps give me a qu
ery that selects rows with same Year,Code1,Code2,Date, but with different Pl
ace.
>The other query is essentially the same, using Zone instead of Place
>Morten Wennevik
Hi Morten,
I'm not sure what you want eactly. If you want to find year / code1 /
code2 / date combinations for which more than one row exist, use:
SELECT Year, Code1, Code2, Date, COUNT(*)
FROM Temp
GROUP BY Year, Code1, Code2, Date
HAVING COUNT(*) > 1
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)