computed columns or UDFs

Hi,

What is the difference between a computed column and a UDF?
Is a computed column the same as the "Formula" field under Design Table in Enterprise Manager?
Also, what is the proper syntax for the Formula field? Can I use regular SQL on it or is there more to it?

thanks,
Frankjust look up books online they have a better xplanation than anyone here can give you in 1-2 lines.

hth

Compressed Drive

I have been given the task of fixing a design problem. We have a 2 GB database (and growing) on a compressed drive. If I decompress the drive what impact if any will there be on the Database or data?This should increase your performance. As far as database concerns, you should not see any issues. However, backup or copy the database to another drive before you proceed. Also, run dbcc checkdb to check the database for any issues - run this before and after the operation. And make sure that you have enough disk space for the decompressed version of the database.

Composite index design question

I have converted a database to support multiple clients by adding a ClientID
(int) to most of the 200 tables. This is to enable an application to use a
single database for multiple clients, separating the data via the ClientID
Where the primary key was OrderID, or ProductID, it is now ( ClientID,
OrderID) and (ClientID, ProductID) respectively.
The majority of clustered indexes are composite and have ClientID as the
first column. This of course means most of the foreign key indexes have
ClientID as the first column in the index. I even added ClientID to most
other nonclustered indexes since I know that ClientID will be in every join
and specified in the WHERE clause of every query.
It occurred to me recently that this database may have hundreds of thousands
of Orders and Products, but will likely only have 30-50 unique values of
ClientID.
This being the case, I would assume I will get better performance if the
indexes were rearranged to be ( ProductID, ClientID) and ( OrderID,
ClientID) ? The database tables are not sufficiently large at this point to
make a difference, but with 30 clients they will hit the 100K mark in a shor
t
period of time.Hi
When you create a composite index on columns SQL Server keeps statistics on
only one (first) column. So make sure that this column is selective enough.
http://www.sql-server-performance.c...ite_indexes.asp
"mikenz" <mikewnz@.newsgroups.nospam> wrote in message
news:BEA8FDD5-CD9C-4EA6-9942-DD6D4D2A6331@.microsoft.com...
>I have converted a database to support multiple clients by adding a
>ClientID
> (int) to most of the 200 tables. This is to enable an application to use
> a
> single database for multiple clients, separating the data via the ClientID
> Where the primary key was OrderID, or ProductID, it is now ( ClientID,
> OrderID) and (ClientID, ProductID) respectively.
> The majority of clustered indexes are composite and have ClientID as the
> first column. This of course means most of the foreign key indexes have
> ClientID as the first column in the index. I even added ClientID to most
> other nonclustered indexes since I know that ClientID will be in every
> join
> and specified in the WHERE clause of every query.
> It occurred to me recently that this database may have hundreds of
> thousands
> of Orders and Products, but will likely only have 30-50 unique values of
> ClientID.
> This being the case, I would assume I will get better performance if the
> indexes were rearranged to be ( ProductID, ClientID) and ( OrderID,
> ClientID) ? The database tables are not sufficiently large at this point
> to
> make a difference, but with 30 clients they will hit the 100K mark in a
> short
> period of time.
>|||Consider seperate non-composite indexes for ClientID and ProductID, since
this will provide more flexibility when generating an execution plan. Also,
consider not clustering on ClientID. With 50 unique clients using the
database, clustering will result in additional I/O and page fragmentation.
I generally avoid using composite columns except on clustered indexes, and I
generally avoid clustered indexes except on data warehouse tables where
there is a large amount of fairly static data that is frequently grouped or
sorted in mass for reporting purposes. There is little advantage to
clustering indexes in an OLTP database, unless it is on a bookmarked id
column like Product.ProductID.
"mikenz" <mikewnz@.newsgroups.nospam> wrote in message
news:BEA8FDD5-CD9C-4EA6-9942-DD6D4D2A6331@.microsoft.com...
>I have converted a database to support multiple clients by adding a
>ClientID
> (int) to most of the 200 tables. This is to enable an application to use
> a
> single database for multiple clients, separating the data via the ClientID
> Where the primary key was OrderID, or ProductID, it is now ( ClientID,
> OrderID) and (ClientID, ProductID) respectively.
> The majority of clustered indexes are composite and have ClientID as the
> first column. This of course means most of the foreign key indexes have
> ClientID as the first column in the index. I even added ClientID to most
> other nonclustered indexes since I know that ClientID will be in every
> join
> and specified in the WHERE clause of every query.
> It occurred to me recently that this database may have hundreds of
> thousands
> of Orders and Products, but will likely only have 30-50 unique values of
> ClientID.
> This being the case, I would assume I will get better performance if the
> indexes were rearranged to be ( ProductID, ClientID) and ( OrderID,
> ClientID) ? The database tables are not sufficiently large at this point
> to
> make a difference, but with 30 clients they will hit the 100K mark in a
> short
> period of time.
>

complicated use of select and count.

Hi,
I have 2 tables, with a 1-m relationship between them and I am trying
to design a query that seems to beyond what sql can do.
Lets say Table A, has the 1 one side of the relationship and table B
the many. Basically I want a query that returns a list of some or all
entries in Table A (thats the easy bit) and a count of the total
number of records in Table B that meet certain criteria. It seems like
what I want is a where clause for each count clause, rather than a
single where clause for the whole query
TableA has a PrimaryKey of MasterID and TableB has a foreign key to it
also called MasterID
Something like Select TableA.MasterID, COUNT( TableB.* where
TableB.Foo = bar) as 'BarFooCount', COUNT(TableB.* where
TableB.Category = 'Sample') as 'SampleCount', COUNT(TableB.* where
TableB.Category = 'Macro' or TableB.Category = 'Micro') as
'MacroOrMicroCount'
FROM TableA, TableB,
Inner Join blah blah blah;
Basically to get a query back that lists each TableA item, along with
summary information about each TableAs TableB records. It doesnt seem
like I can put where clauses in count clauses so can this be done in
SQL another way?Here's on option, based on Adventureworks database:
SELECT
soh.SalesOrderID
,soh.OrderDate
,(SELECT COUNT(*) FROM Sales.SalesOrderDetail AS sod
WHERE sod.SalesOrderID = soh.SalesOrderID) AS NoOrders
FROM Sales.SalesOrderHeader AS soh
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Oolis Kraprin" <Oolis.Kraprin@.googlemail.com> wrote in message
news:bc3c3456-7d89-4533-8171-91926ada7bf4@.m34g2000hsb.googlegroups.com...
> Hi,
> I have 2 tables, with a 1-m relationship between them and I am trying
> to design a query that seems to beyond what sql can do.
> Lets say Table A, has the 1 one side of the relationship and table B
> the many. Basically I want a query that returns a list of some or all
> entries in Table A (thats the easy bit) and a count of the total
> number of records in Table B that meet certain criteria. It seems like
> what I want is a where clause for each count clause, rather than a
> single where clause for the whole query
> TableA has a PrimaryKey of MasterID and TableB has a foreign key to it
> also called MasterID
> Something like Select TableA.MasterID, COUNT( TableB.* where
> TableB.Foo = bar) as 'BarFooCount', COUNT(TableB.* where
> TableB.Category = 'Sample') as 'SampleCount', COUNT(TableB.* where
> TableB.Category = 'Macro' or TableB.Category = 'Micro') as
> 'MacroOrMicroCount'
> FROM TableA, TableB,
> Inner Join blah blah blah;
> Basically to get a query back that lists each TableA item, along with
> summary information about each TableAs TableB records. It doesnt seem
> like I can put where clauses in count clauses so can this be done in
> SQL another way?|||Excellent, that put me in exactly the right direction!
Thank you very much!
On Jan 28, 1:09 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Here's on option, based on Adventureworks database:
> SELECT
> soh.SalesOrderID
> ,soh.OrderDate
> ,(SELECT COUNT(*) FROM Sales.SalesOrderDetail AS sod
> WHERE sod.SalesOrderID = soh.SalesOrderID) AS NoOrders
> FROM Sales.SalesOrderHeader AS soh
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Oolis Kraprin" <Oolis.Krap...@.googlemail.com> wrote in message
> news:bc3c3456-7d89-4533-8171-91926ada7bf4@.m34g2000hsb.googlegroups.com...
> > Hi,
> > I have 2 tables, with a 1-m relationship between them and I am trying
> > to design a query that seems to beyond what sql can do.
> > Lets say Table A, has the 1 one side of the relationship and table B
> > the many. Basically I want a query that returns a list of some or all
> > entries in Table A (thats the easy bit) and a count of the total
> > number of records in Table B that meet certain criteria. It seems like
> > what I want is a where clause for each count clause, rather than a
> > single where clause for the whole query
> > TableA has a PrimaryKey of MasterID and TableB has a foreign key to it
> > also called MasterID
> > Something like Select TableA.MasterID, COUNT( TableB.* where
> > TableB.Foo = bar) as 'BarFooCount', COUNT(TableB.* where
> > TableB.Category = 'Sample') as 'SampleCount', COUNT(TableB.* where
> > TableB.Category = 'Macro' or TableB.Category = 'Micro') as
> > 'MacroOrMicroCount'
> > FROM TableA, TableB,
> > Inner Join blah blah blah;
> > Basically to get a query back that lists each TableA item, along with
> > summary information about each TableAs TableB records. It doesnt seem
> > like I can put where clauses in count clauses so can this be done in
> > SQL another way?|||On Mon, 28 Jan 2008 01:39:51 -0800 (PST), Oolis Kraprin wrote:
>Hi,
>I have 2 tables, with a 1-m relationship between them and I am trying
>to design a query that seems to beyond what sql can do.
>Lets say Table A, has the 1 one side of the relationship and table B
>the many. Basically I want a query that returns a list of some or all
>entries in Table A (thats the easy bit) and a count of the total
>number of records in Table B that meet certain criteria. It seems like
>what I want is a where clause for each count clause, rather than a
>single where clause for the whole query
>TableA has a PrimaryKey of MasterID and TableB has a foreign key to it
>also called MasterID
>Something like Select TableA.MasterID, COUNT( TableB.* where
>TableB.Foo = bar) as 'BarFooCount', COUNT(TableB.* where
>TableB.Category = 'Sample') as 'SampleCount', COUNT(TableB.* where
>TableB.Category = 'Macro' or TableB.Category = 'Micro') as
>'MacroOrMicroCount'
>FROM TableA, TableB,
>Inner Join blah blah blah;
>Basically to get a query back that lists each TableA item, along with
>summary information about each TableAs TableB records. It doesnt seem
>like I can put where clauses in count clauses so can this be done in
>SQL another way?
Hi Oolis,
Here's a basic outline of what you could use:
SELECT A.MasterID,
SUM(CASE WHEN B.Foo = 'Bar' THEN 1 ELSE 0 END) AS FooCount,
SUM(CASE WHEN B.Foo = 'Sample' THEN 1 ELSE 0 END) AS
SampleCount
FROM TableA AS A
INNER JOIN TableB AS B
ON B.MasterID = A.MasterID
GROUP BY A.MasterID;
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis