My general question is whether there is anything to be gained by having 50 tables in one database versus 5 tables each in 10 databases.
I have a number of different databases running on a server (SQL Server 2k). The different databases represent different functional groups, for instance car maintenance, cab reservation/dispatch, cab accounting, limo reservation/dispatch, limo accounting, etc. There is some crossover, for instance the cab dispatch system would look to car maintenance to validate the car number entered.
A friend who happens to be IT Director at the local university suggested that the server would run more efficiently if there was only one database, rather than the roughly 12 I have now. His belief is that each separate database carries a certain amount of overhead, and combining them into one would be advantageous.
Is he all wet, or would there be gains to be made?
TIAThere would be gains. It is just a matter of whether you would notice them. I have never tested the scenario, so I have no actual numbers. Personally, I would favor the single database approach, for permissions administration reasons. A user only needs permissions on a stored procedure and not the underlying tables, IF all of the tables it accesses are in the same database. Same for views.
Now, suppose you want to restore the 10 databases back to a point in time just before your temp deleted a pile of data in "some tables". Do you want to do 10 separate restores, one?
I am sure some of the other folks here can come up with other examples, if they tried.|||He's right for the wrong reasons.
I don't think you would get a performance boost from combining the databases. You could make arguments for either increased efficiency or decreased efficiency either way.
But...from a data management standpoint it makes administrative sense to combine the databases if they reference eachother for lookup values or cross-database queries. From you limited description of the situation, I would recommend combining them.|||Thanks for your thoughts. I guess I'll look at going through the effort.|||I read the first line and fell off my barstoo...um office chair
My general question is whether there is anything to be gained by having 50 tables in one database versus 5 tables each in 10 databases.
Are you kidding? Ever hear of maintenance?
I have a number of different databases running on a server (SQL Server 2k). The different databases represent different functional groups, for instance car maintenance, cab reservation/dispatch, cab accounting, limo reservation/dispatch, limo accounting, etc. There is some crossover, for instance the cab dispatch system would look to car maintenance to validate the car number entered.
A friend who happens to be IT Director at the local university suggested that the server would run more efficiently if there was only one database, rather than the roughly 12 I have now. His belief is that each separate database carries a certain amount of overhead, and combining them into one would be advantageous.
Is he all wet, or would there be gains to be made?
TIA
I like the cut of his gib...are all the tables named differently? Also, what about the apps? Would they be hard to port?|||Putting objects in different databases will allow you more flexibility in terms of allocating data and log files. (IE if you have to use primary for select into reasons, you can more easily manage it with multiple databases). Also, depending on your backup requirements you may be able to set some databases to simple mode, some to full, only do trn backups for certain databases and the list goes on. I think it's more a management thing then a performance thing.
-kilka|||Putting objects in different databases will allow you more flexibility in terms of allocating data and log files. (IE if you have to use primary for select into reasons, you can more easily manage it with multiple databases). Also, depending on your backup requirements you may be able to set some databases to simple mode, some to full, only do trn backups for certain databases and the list goes on. I think it's more a management thing then a performance thing.
-kilka
Nope ... and thank you for playing (at least for the first statement). You can create as many segments as you desire for a single database for both data and log files ... non-clustered indexes on multiple disks, split log files, etc., etc., etc.
Not to mention that referential integrity rules can only be enforced with database, not across database.
Also, IMHO, cross database joins requires the engine to drill down thru the metadata of the other databases to access the index and page structures of the "foreign" database since SQLServer "cooks" it database space when it is allocated.
That being said, it would be interesting to see the results of an empirical test. If Paul Randal is still hanging around, maybe he can comment on this topic!|||What do you mean by "cooks" space when it's allocated?
Showing posts with label versus. Show all posts
Showing posts with label versus. Show all posts
Tuesday, March 27, 2012
Monday, March 12, 2012
function versus stored proc
I have to repost this, so I apologize if anyone responded. For some reason
the copy I sent is not being refreshed in my newsreader Inbox.
Is there a benefit to using one over the other?
Both give me the same result. Should I go with the function or the stored
proc?
ALTER FUNCTION f_PhraseCount
(
@.Phrase VARCHAR(512)
)
RETURNS INT
AS
BEGIN
Declare @.PhraseCount INT
SELECT @.PhraseCount = COUNT(*) from SearchHistory WHERE Phrase = @.Phrase
RETURN @.PhraseCount
END
or
ALTER PROCEDURE dbo.spr_PhraseCount
(
@.Phrase VARCHAR(512)
)
AS
SELECT COUNT(*) from SearchHistory WHERE Phrase= @.PhraseIt comes down to what kind of interface you want your clients to have to use
in order to get the data from the database. A stored procedure, to me,
"feels" more like a solid interface (from a client perspective) than does a
T-SQL function. Compare:
EXEC dbo.spr_PhraseCount @.Phrase='x'
to:
SELECT dbo.f_PhraseCount('x')
By using the function, you're exposing a little piece of SQL to the client
(i.e., the SELECT statement), whereas the stored procedure completely
encapsulates it. This is a very small issue in this case, but it might
illustrate the direction that you want to take the entire application in
from a data access point of view. Ultimately, you need to decide how much
you want to couple your client (the application tier) to the database.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"mrmagoo" <-> wrote in message news:e$szWgPZGHA.1200@.TK2MSFTNGP03.phx.gbl...
>I have to repost this, so I apologize if anyone responded. For some reason
> the copy I sent is not being refreshed in my newsreader Inbox.
> Is there a benefit to using one over the other?
> Both give me the same result. Should I go with the function or the stored
> proc?
> ALTER FUNCTION f_PhraseCount
> (
> @.Phrase VARCHAR(512)
> )
> RETURNS INT
> AS
> BEGIN
> Declare @.PhraseCount INT
> SELECT @.PhraseCount = COUNT(*) from SearchHistory WHERE Phrase = @.Phrase
> RETURN @.PhraseCount
> END
>
> or
>
> ALTER PROCEDURE dbo.spr_PhraseCount
> (
> @.Phrase VARCHAR(512)
> )
> AS
> SELECT COUNT(*) from SearchHistory WHERE Phrase= @.Phrase
>|||Currently they give you the same result, but if you wanted to change things
slightly stored procedures have more flexibility regarding returning
recordsets
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"mrmagoo" <-> wrote in message news:e$szWgPZGHA.1200@.TK2MSFTNGP03.phx.gbl...
> I have to repost this, so I apologize if anyone responded. For some reason
> the copy I sent is not being refreshed in my newsreader Inbox.
> Is there a benefit to using one over the other?
> Both give me the same result. Should I go with the function or the stored
> proc?
> ALTER FUNCTION f_PhraseCount
> (
> @.Phrase VARCHAR(512)
> )
> RETURNS INT
> AS
> BEGIN
> Declare @.PhraseCount INT
> SELECT @.PhraseCount = COUNT(*) from SearchHistory WHERE Phrase = @.Phrase
> RETURN @.PhraseCount
> END
>
> or
>
> ALTER PROCEDURE dbo.spr_PhraseCount
> (
> @.Phrase VARCHAR(512)
> )
> AS
> SELECT COUNT(*) from SearchHistory WHERE Phrase= @.Phrase
>|||Thanks Adam for your insightful response.
I had originally posted this but lost the thread in my newsreader. However,
I performed a groups.google.com search and found it. I had received a
response from someone named Tibor Karaszi, a SQL Server MVP, who answered my
exact same post with the very brief and useless reply of "IT depends on how
you wish to use it.". That's all he said, so I thank you for your
elaboration. I have participated in the MS newsgrouips for a long time, and
I wish MS would award the MVP status to people like you who take the time to
give people like me information I can use. Whereas Tibor gave me such
pathetically little information that I can do nothing with, and I wonder why
he even bothered to spend what must have been 10 seconds replying to such a
meaningless question to him (it seems), you have provided me information
that I can use.
Thank you.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uAjzxGRZGHA.1196@.TK2MSFTNGP03.phx.gbl...
> It comes down to what kind of interface you want your clients to have to
use
> in order to get the data from the database. A stored procedure, to me,
> "feels" more like a solid interface (from a client perspective) than does
a
> T-SQL function. Compare:
> EXEC dbo.spr_PhraseCount @.Phrase='x'
> to:
> SELECT dbo.f_PhraseCount('x')
> By using the function, you're exposing a little piece of SQL to the client
> (i.e., the SELECT statement), whereas the stored procedure completely
> encapsulates it. This is a very small issue in this case, but it might
> illustrate the direction that you want to take the entire application in
> from a data access point of view. Ultimately, you need to decide how much
> you want to couple your client (the application tier) to the database.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "mrmagoo" <-> wrote in message
news:e$szWgPZGHA.1200@.TK2MSFTNGP03.phx.gbl...
reason
stored
@.Phrase
>|||Thank you.
"Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> wrote in message
news:mbCdnaLFI9MmDtXZRVny1A@.bt.com...
> Currently they give you the same result, but if you wanted to change
things
> slightly stored procedures have more flexibility regarding returning
> recordsets
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> "mrmagoo" <-> wrote in message
news:e$szWgPZGHA.1200@.TK2MSFTNGP03.phx.gbl...
reason
stored
@.Phrase
>|||I'm sorry to hear that you weren't pleased with Tibor's reply -- he is
generally a great source of knowledge on all things SQL Server related, and
he's helped me many times in these groups and elsewhere.
As for MS awarding MVP to people like me, it does happen -- I've been an MVP
since 2004 ;-)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"mrmagoo" <-> wrote in message
news:%23B59gVWZGHA.4248@.TK2MSFTNGP05.phx.gbl...
> Thanks Adam for your insightful response.
> I had originally posted this but lost the thread in my newsreader.
> However,
> I performed a groups.google.com search and found it. I had received a
> response from someone named Tibor Karaszi, a SQL Server MVP, who answered
> my
> exact same post with the very brief and useless reply of "IT depends on
> how
> you wish to use it.". That's all he said, so I thank you for your
> elaboration. I have participated in the MS newsgrouips for a long time,
> and
> I wish MS would award the MVP status to people like you who take the time
> to
> give people like me information I can use. Whereas Tibor gave me such
> pathetically little information that I can do nothing with, and I wonder
> why
> he even bothered to spend what must have been 10 seconds replying to such
> a
> meaningless question to him (it seems), you have provided me information
> that I can use.
> Thank you.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uAjzxGRZGHA.1196@.TK2MSFTNGP03.phx.gbl...
> use
> a
> news:e$szWgPZGHA.1200@.TK2MSFTNGP03.phx.gbl...
> reason
> stored
> @.Phrase
>|||mrmagoo wrote:
> Thanks Adam for your insightful response.
> I had originally posted this but lost the thread in my newsreader. However
,
> I performed a groups.google.com search and found it. I had received a
> response from someone named Tibor Karaszi, a SQL Server MVP, who answered
my
> exact same post with the very brief and useless reply of "IT depends on ho
w
> you wish to use it.". That's all he said, so I thank you for your
> elaboration. I have participated in the MS newsgrouips for a long time, an
d
> I wish MS would award the MVP status to people like you who take the time
to
> give people like me information I can use. Whereas Tibor gave me such
> pathetically little information that I can do nothing with, and I wonder w
hy
> he even bothered to spend what must have been 10 seconds replying to such
a
> meaningless question to him (it seems), you have provided me information
> that I can use.
>
There are very few people here who are more helpful than Tibor. I
expect his reply was intended to prompt you for more information.
Please remember that any help you do get is for free. If you want a
guaranteed level of service you'd better go hire someone. :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I am aware that people here contribute for "free", however MVPs get MSDN,
don't they?, so contributions are not without remuneration. Non-MVPs are the
only ones who truly work for free, and I doubt that an MVP wannabe would get
to be an MVP by answering posts that way.
Your point is well taken, however. I did not mean to offend Tibor, but I did
intend to point out that the reply was not helpful.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1145642545.679167.247350@.t31g2000cwb.googlegroups.com...
> mrmagoo wrote:
However,
answered my
how
and
time to
why
such a
> There are very few people here who are more helpful than Tibor. I
> expect his reply was intended to prompt you for more information.
> Please remember that any help you do get is for free. If you want a
> guaranteed level of service you'd better go hire someone. :-)
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
the copy I sent is not being refreshed in my newsreader Inbox.
Is there a benefit to using one over the other?
Both give me the same result. Should I go with the function or the stored
proc?
ALTER FUNCTION f_PhraseCount
(
@.Phrase VARCHAR(512)
)
RETURNS INT
AS
BEGIN
Declare @.PhraseCount INT
SELECT @.PhraseCount = COUNT(*) from SearchHistory WHERE Phrase = @.Phrase
RETURN @.PhraseCount
END
or
ALTER PROCEDURE dbo.spr_PhraseCount
(
@.Phrase VARCHAR(512)
)
AS
SELECT COUNT(*) from SearchHistory WHERE Phrase= @.PhraseIt comes down to what kind of interface you want your clients to have to use
in order to get the data from the database. A stored procedure, to me,
"feels" more like a solid interface (from a client perspective) than does a
T-SQL function. Compare:
EXEC dbo.spr_PhraseCount @.Phrase='x'
to:
SELECT dbo.f_PhraseCount('x')
By using the function, you're exposing a little piece of SQL to the client
(i.e., the SELECT statement), whereas the stored procedure completely
encapsulates it. This is a very small issue in this case, but it might
illustrate the direction that you want to take the entire application in
from a data access point of view. Ultimately, you need to decide how much
you want to couple your client (the application tier) to the database.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"mrmagoo" <-> wrote in message news:e$szWgPZGHA.1200@.TK2MSFTNGP03.phx.gbl...
>I have to repost this, so I apologize if anyone responded. For some reason
> the copy I sent is not being refreshed in my newsreader Inbox.
> Is there a benefit to using one over the other?
> Both give me the same result. Should I go with the function or the stored
> proc?
> ALTER FUNCTION f_PhraseCount
> (
> @.Phrase VARCHAR(512)
> )
> RETURNS INT
> AS
> BEGIN
> Declare @.PhraseCount INT
> SELECT @.PhraseCount = COUNT(*) from SearchHistory WHERE Phrase = @.Phrase
> RETURN @.PhraseCount
> END
>
> or
>
> ALTER PROCEDURE dbo.spr_PhraseCount
> (
> @.Phrase VARCHAR(512)
> )
> AS
> SELECT COUNT(*) from SearchHistory WHERE Phrase= @.Phrase
>|||Currently they give you the same result, but if you wanted to change things
slightly stored procedures have more flexibility regarding returning
recordsets
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"mrmagoo" <-> wrote in message news:e$szWgPZGHA.1200@.TK2MSFTNGP03.phx.gbl...
> I have to repost this, so I apologize if anyone responded. For some reason
> the copy I sent is not being refreshed in my newsreader Inbox.
> Is there a benefit to using one over the other?
> Both give me the same result. Should I go with the function or the stored
> proc?
> ALTER FUNCTION f_PhraseCount
> (
> @.Phrase VARCHAR(512)
> )
> RETURNS INT
> AS
> BEGIN
> Declare @.PhraseCount INT
> SELECT @.PhraseCount = COUNT(*) from SearchHistory WHERE Phrase = @.Phrase
> RETURN @.PhraseCount
> END
>
> or
>
> ALTER PROCEDURE dbo.spr_PhraseCount
> (
> @.Phrase VARCHAR(512)
> )
> AS
> SELECT COUNT(*) from SearchHistory WHERE Phrase= @.Phrase
>|||Thanks Adam for your insightful response.
I had originally posted this but lost the thread in my newsreader. However,
I performed a groups.google.com search and found it. I had received a
response from someone named Tibor Karaszi, a SQL Server MVP, who answered my
exact same post with the very brief and useless reply of "IT depends on how
you wish to use it.". That's all he said, so I thank you for your
elaboration. I have participated in the MS newsgrouips for a long time, and
I wish MS would award the MVP status to people like you who take the time to
give people like me information I can use. Whereas Tibor gave me such
pathetically little information that I can do nothing with, and I wonder why
he even bothered to spend what must have been 10 seconds replying to such a
meaningless question to him (it seems), you have provided me information
that I can use.
Thank you.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uAjzxGRZGHA.1196@.TK2MSFTNGP03.phx.gbl...
> It comes down to what kind of interface you want your clients to have to
use
> in order to get the data from the database. A stored procedure, to me,
> "feels" more like a solid interface (from a client perspective) than does
a
> T-SQL function. Compare:
> EXEC dbo.spr_PhraseCount @.Phrase='x'
> to:
> SELECT dbo.f_PhraseCount('x')
> By using the function, you're exposing a little piece of SQL to the client
> (i.e., the SELECT statement), whereas the stored procedure completely
> encapsulates it. This is a very small issue in this case, but it might
> illustrate the direction that you want to take the entire application in
> from a data access point of view. Ultimately, you need to decide how much
> you want to couple your client (the application tier) to the database.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "mrmagoo" <-> wrote in message
news:e$szWgPZGHA.1200@.TK2MSFTNGP03.phx.gbl...
reason
stored
@.Phrase
>|||Thank you.
"Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> wrote in message
news:mbCdnaLFI9MmDtXZRVny1A@.bt.com...
> Currently they give you the same result, but if you wanted to change
things
> slightly stored procedures have more flexibility regarding returning
> recordsets
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> "mrmagoo" <-> wrote in message
news:e$szWgPZGHA.1200@.TK2MSFTNGP03.phx.gbl...
reason
stored
@.Phrase
>|||I'm sorry to hear that you weren't pleased with Tibor's reply -- he is
generally a great source of knowledge on all things SQL Server related, and
he's helped me many times in these groups and elsewhere.
As for MS awarding MVP to people like me, it does happen -- I've been an MVP
since 2004 ;-)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"mrmagoo" <-> wrote in message
news:%23B59gVWZGHA.4248@.TK2MSFTNGP05.phx.gbl...
> Thanks Adam for your insightful response.
> I had originally posted this but lost the thread in my newsreader.
> However,
> I performed a groups.google.com search and found it. I had received a
> response from someone named Tibor Karaszi, a SQL Server MVP, who answered
> my
> exact same post with the very brief and useless reply of "IT depends on
> how
> you wish to use it.". That's all he said, so I thank you for your
> elaboration. I have participated in the MS newsgrouips for a long time,
> and
> I wish MS would award the MVP status to people like you who take the time
> to
> give people like me information I can use. Whereas Tibor gave me such
> pathetically little information that I can do nothing with, and I wonder
> why
> he even bothered to spend what must have been 10 seconds replying to such
> a
> meaningless question to him (it seems), you have provided me information
> that I can use.
> Thank you.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uAjzxGRZGHA.1196@.TK2MSFTNGP03.phx.gbl...
> use
> a
> news:e$szWgPZGHA.1200@.TK2MSFTNGP03.phx.gbl...
> reason
> stored
> @.Phrase
>|||mrmagoo wrote:
> Thanks Adam for your insightful response.
> I had originally posted this but lost the thread in my newsreader. However
,
> I performed a groups.google.com search and found it. I had received a
> response from someone named Tibor Karaszi, a SQL Server MVP, who answered
my
> exact same post with the very brief and useless reply of "IT depends on ho
w
> you wish to use it.". That's all he said, so I thank you for your
> elaboration. I have participated in the MS newsgrouips for a long time, an
d
> I wish MS would award the MVP status to people like you who take the time
to
> give people like me information I can use. Whereas Tibor gave me such
> pathetically little information that I can do nothing with, and I wonder w
hy
> he even bothered to spend what must have been 10 seconds replying to such
a
> meaningless question to him (it seems), you have provided me information
> that I can use.
>
There are very few people here who are more helpful than Tibor. I
expect his reply was intended to prompt you for more information.
Please remember that any help you do get is for free. If you want a
guaranteed level of service you'd better go hire someone. :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I am aware that people here contribute for "free", however MVPs get MSDN,
don't they?, so contributions are not without remuneration. Non-MVPs are the
only ones who truly work for free, and I doubt that an MVP wannabe would get
to be an MVP by answering posts that way.
Your point is well taken, however. I did not mean to offend Tibor, but I did
intend to point out that the reply was not helpful.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1145642545.679167.247350@.t31g2000cwb.googlegroups.com...
> mrmagoo wrote:
However,
answered my
how
and
time to
why
such a
> There are very few people here who are more helpful than Tibor. I
> expect his reply was intended to prompt you for more information.
> Please remember that any help you do get is for free. If you want a
> guaranteed level of service you'd better go hire someone. :-)
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
Subscribe to:
Posts (Atom)