Newbie Question on SQL DB

Hello All!
I know I can do this, but not sure of the best way. I have a users table,
with a userID as key. I also have a profile table. When a user logs in,
they can add to thier profile if they want. My question is, What is the bes
t
way to link that user ID so the user ID is filled in on the userID col in th
e
profile table, and the user profile sticks to that ID. Even if the user
comes back to add to it later.
I think I would have to return the value of the user ID, pass that forward
to the profile table, but I'm not sure.
TIA!!!
RudyRudy wrote:
> Hello All!
> I know I can do this, but not sure of the best way. I have a users
> table, with a userID as key. I also have a profile table. When a
> user logs in, they can add to thier profile if they want. My
> question is, What is the best way to link that user ID so the user ID
> is filled in on the userID col in the profile table, and the user
> profile sticks to that ID. Even if the user comes back to add to it
> later.
> I think I would have to return the value of the user ID, pass that
> forward to the profile table, but I'm not sure.
> TIA!!!
>
> Rudy
Create Table MyUsers (
UserID INT IDENTITY NOT NULL PRIMARY KEY,
UserName NVARCHAR(50))
Create Table UserProfiler (
UserID INT NOT NULL REFERENCES MyUsers(UserID),
OptionID INT NOT NULL REFERENCES ProfileOptions(OptionID),
OptionValue NVARCHAR(30) NOT NULL,
PRIMARY KEY CLUSTERED (UserID, OptionID) )
Not sure of your design, but assuming you had a relationship like the
above, you need to physically insert the UserID into the UserProfile
table. There is not way for SQL Server to know what UserID you want
interted, unless you're talking about a login name (are you?).
For a login name you could use suser_sname() and have it as the default
on the table:
Create Table UserProfile (
UserID NVARCHAR(128) NOT NULL DEFAULT SUSER_SNAME(),
OptionID INT NOT NULL REFERENCES ProfileOptions(OptionID),
OptionValue NVARCHAR(30) NOT NULL,
PRIMARY KEY CLUSTERED (UserID, OptionID) )
and then use:
Insert UserProfile (
OptionID, OptionValue)
Values (
50, N'Profiler Data')
if the user id is something your database stores separately from the
login name, you would need to send the value to SQL Server. So you might
grab the UserID when the user logs into the application and pass it to
the insert statement or pass it to a stored procedure to be inserted
into the profile table.
David Gugick
Imceda Software
www.imceda.com|||Hi David!
Thank you for the quick reply. So I am talking about a login name, and the
user ID is on the same table as the user name.
For a login name you could use suser_sname() and have it as the default
> on the table:
> Create Table UserProfile (
> UserID NVARCHAR(128) NOT NULL DEFAULT SUSER_SNAME(),
> OptionID INT NOT NULL REFERENCES ProfileOptions(OptionID),
> OptionValue NVARCHAR(30) NOT NULL,
> PRIMARY KEY CLUSTERED (UserID, OptionID) )
I don't understand what you mean by the suser_sname as the default. It's
been awhile since I had to work with SQL, and was just learning at that time
.
Now that I need to use SQL a little bit more indepth than just making simple
tables, and passing values back and forth, I'm kinda in the weeds if you kno
w
what I mean. LOL
So now that I have set you up for my ignorance, how does one refrence? I
know about relationships and stuff, sorta. And I know I can use views to hav
e
data update automaticly from other tables. And views can be used just like
tables, right? Would I create a FK between the two tables using the userID?
But that doesn't update or keep the information of the userID the same, does
it?
I though if I could just return a value to what user was logged on, and then
that userID would link with the profile table, andthen the info can be
update. Maybe it would be easier if I had a table for just users who are
logged on?
Am I way off base or what?
Thank you for your time David!
Rudy
"David Gugick" wrote:

> Rudy wrote:
> Create Table MyUsers (
> UserID INT IDENTITY NOT NULL PRIMARY KEY,
> UserName NVARCHAR(50))
> Create Table UserProfiler (
> UserID INT NOT NULL REFERENCES MyUsers(UserID),
> OptionID INT NOT NULL REFERENCES ProfileOptions(OptionID),
> OptionValue NVARCHAR(30) NOT NULL,
> PRIMARY KEY CLUSTERED (UserID, OptionID) )
>
> Not sure of your design, but assuming you had a relationship like the
> above, you need to physically insert the UserID into the UserProfile
> table. There is not way for SQL Server to know what UserID you want
> interted, unless you're talking about a login name (are you?).
> For a login name you could use suser_sname() and have it as the default
> on the table:
> Create Table UserProfile (
> UserID NVARCHAR(128) NOT NULL DEFAULT SUSER_SNAME(),
> OptionID INT NOT NULL REFERENCES ProfileOptions(OptionID),
> OptionValue NVARCHAR(30) NOT NULL,
> PRIMARY KEY CLUSTERED (UserID, OptionID) )
> and then use:
> Insert UserProfile (
> OptionID, OptionValue)
> Values (
> 50, N'Profiler Data')
>
> if the user id is something your database stores separately from the
> login name, you would need to send the value to SQL Server. So you might
> grab the UserID when the user logs into the application and pass it to
> the insert statement or pass it to a stored procedure to be inserted
> into the profile table.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Rudy wrote:
> I don't understand what you mean by the suser_sname as the default.
suser_sname() is a function that returns the logged in user name.
My example showed a PK/FK reference. And as I mentioned, the FK value
does not update automatically, it just enforces values based on the
available PK values in the referenced table.
I think you need to spell out in a clear and concise way exactly what
you are trying to do, what all the data means, etc.
David Gugick
Imceda Software
www.imceda.com

Newbie question on SQL code best practice

Team
I wrote following code
Create PROCEDURE asp_nykl_Full_Update_605ProcStat
--@.sku_barcode varchar(12)
AS
--declare @.Err1 int
--begin transaction
UPDATE pix_tran
SET proc_stat_code = 90
FROM ITEM_MASTER
WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
--AND sku_brcd=@.sku_barcode
AND TRAN_TYPE = '605'
AND proc_stat_code = 10
I have been advised that I must put
1. begin and end transaction
2. Must have SELECT ... (UPDLOCK) before update statement
3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
Is it always advisable to do so?
Thanks
D Goyal (goyald@.gmail.com) writes:
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
As long as you only have a single update statement, that's a bit
of overkill - as long as you can be dead sure that the code is running
with implicit_transactions off. This setting is indeed off by default,
but if the procedure is invoked remotely, this is not so. So BEGIN/END
would make it a little safer.

> 2. Must have SELECT ... (UPDLOCK) before update statement
I don't really see the point with this here.

> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
SET NOCOUNT ON is indeed recommendable, as without setting SQL Server
produces a rowcount about affected rows which clients more often does
not care about than they do. In fact, our load tool automatically inserts
a SET NOCOUNT ON in all our stored procedures.
SET LOCK_TIMEOUT I can't really comment on, as this is more tied to
business rules. It prevents the procedure from being locked forever,
but then again what should you do if you time out?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||D Goyal wrote:
> Team
> I wrote following code
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
You can, but it's not always necessary. SQL Server will run that single
statement in a transaction for you if you leave off the begin
tran/commit. Autocommit mode is the default, but if you have standards
in place, you can start a transaction and check @.@.ERROR after each DML
statement and subsequently commit or rollback. It will save you some
headaches should you add a second DML statement to the procedure. In
autocommit mode (without a begin tran) if the first succeeds and the
second statement fails, the first statement still commits.

> 2. Must have SELECT ... (UPDLOCK) before update statement
It's not needed. But if I look at the next item for LOCK_TIMEOUT, I
think I see why it might have been proposed. If you set a lock timeout
to say 5 seconds and try and select the rows with an escalated lock
(would need to be in a transaction), and other processes have locks on
the required pages, the SELECT will abort. But you can do the same
without the SELECT and just leave it to the UPDATE to time out if there
is lock contention.

> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
SET NOCOUNT ON is a highly advisable addition to every stored procedure
(first line). I don't generally use a lock timeout unless I'm running
something that I need to make sure doesn't sit there forever in the case
of someone hold extended locks on the required pages.

> Is it always advisable to do so?
> Thanks
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||(1) Single statment like this does not require explicit transactions.
(2) If you are planning for any updates after a read and want to insure that
the data did not change between reads, then you need to add UPDLOCK hint in
your SELECT. If not, I do not see any need.
(3) SET NOCOUNT ON does not have any performance impact. No matter how you
set this option, the @.@.ROWCOUNT value will be affected. It simply does not
send the count as part of the result to the client.
(4) Unless you know how much time you want to wait for a blocked resource, I
would not recommend to change LOCK_TIMEOUT. Remember that this setting change
is for your connection.
"D Goyal" wrote:

> Team
> I wrote following code
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
> 2. Must have SELECT ... (UPDLOCK) before update statement
> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
> Is it always advisable to do so?
> Thanks
>
|||satheeshks wrote:
> (3) SET NOCOUNT ON does not have any performance impact. No matter
> how you set this option, the @.@.ROWCOUNT value will be affected. It
> simply does not send the count as part of the result to the client.
I have disagree with # 3.
Using SET NOCOUNT ON can cause a improvement in some queries (batches)
as well as prevent some ADO issues caused by the rowcount information
being returned to the client.
On a test I just performed that inserts 1000 rows into a table in a
loop, the CPU and Reads were the same, but the Duration dropped from an
average of 550ms to 450ms (a 19% improvement in speed).
This test was on local SQL Server box using Query Analyzer. Results
might vary when running on a network or when ignoring the row count
results (which are displayed on screen in QA).
But I would urge the OP to set NOCOUNT ON at the very top of every
stored procedure and also as the first command after connecting should
any embedded SQL be executed from the app.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Newbie question on SQL code best practice

Team
I wrote following code
Create PROCEDURE asp_nykl_Full_Update_605ProcStat
--@.sku_barcode varchar(12)
AS
--declare @.Err1 int
--begin transaction
UPDATE pix_tran
SET proc_stat_code = 90
FROM ITEM_MASTER
WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
--AND sku_brcd=@.sku_barcode
AND TRAN_TYPE = '605'
AND proc_stat_code = 10
I have been advised that I must put
1. begin and end transaction
2. Must have SELECT ... (UPDLOCK) before update statement
3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
Is it always advisable to do so?
ThanksD Goyal (goyald@.gmail.com) writes:
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
As long as you only have a single update statement, that's a bit
of overkill - as long as you can be dead sure that the code is running
with implicit_transactions off. This setting is indeed off by default,
but if the procedure is invoked remotely, this is not so. So BEGIN/END
would make it a little safer.
> 2. Must have SELECT ... (UPDLOCK) before update statement
I don't really see the point with this here.
> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
SET NOCOUNT ON is indeed recommendable, as without setting SQL Server
produces a rowcount about affected rows which clients more often does
not care about than they do. In fact, our load tool automatically inserts
a SET NOCOUNT ON in all our stored procedures.
SET LOCK_TIMEOUT I can't really comment on, as this is more tied to
business rules. It prevents the procedure from being locked forever,
but then again what should you do if you time out?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||D Goyal wrote:
> Team
> I wrote following code
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
You can, but it's not always necessary. SQL Server will run that single
statement in a transaction for you if you leave off the begin
tran/commit. Autocommit mode is the default, but if you have standards
in place, you can start a transaction and check @.@.ERROR after each DML
statement and subsequently commit or rollback. It will save you some
headaches should you add a second DML statement to the procedure. In
autocommit mode (without a begin tran) if the first succeeds and the
second statement fails, the first statement still commits.
> 2. Must have SELECT ... (UPDLOCK) before update statement
It's not needed. But if I look at the next item for LOCK_TIMEOUT, I
think I see why it might have been proposed. If you set a lock timeout
to say 5 seconds and try and select the rows with an escalated lock
(would need to be in a transaction), and other processes have locks on
the required pages, the SELECT will abort. But you can do the same
without the SELECT and just leave it to the UPDATE to time out if there
is lock contention.
> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
SET NOCOUNT ON is a highly advisable addition to every stored procedure
(first line). I don't generally use a lock timeout unless I'm running
something that I need to make sure doesn't sit there forever in the case
of someone hold extended locks on the required pages.
> Is it always advisable to do so?
> Thanks
David Gugick
Quest Software
www.imceda.com
www.quest.com|||(1) Single statment like this does not require explicit transactions.
(2) If you are planning for any updates after a read and want to insure that
the data did not change between reads, then you need to add UPDLOCK hint in
your SELECT. If not, I do not see any need.
(3) SET NOCOUNT ON does not have any performance impact. No matter how you
set this option, the @.@.ROWCOUNT value will be affected. It simply does not
send the count as part of the result to the client.
(4) Unless you know how much time you want to wait for a blocked resource, I
would not recommend to change LOCK_TIMEOUT. Remember that this setting change
is for your connection.
"D Goyal" wrote:
> Team
> I wrote following code
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
> 2. Must have SELECT ... (UPDLOCK) before update statement
> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
> Is it always advisable to do so?
> Thanks
>|||satheeshks wrote:
> (3) SET NOCOUNT ON does not have any performance impact. No matter
> how you set this option, the @.@.ROWCOUNT value will be affected. It
> simply does not send the count as part of the result to the client.
I have disagree with # 3.
Using SET NOCOUNT ON can cause a improvement in some queries (batches)
as well as prevent some ADO issues caused by the rowcount information
being returned to the client.
On a test I just performed that inserts 1000 rows into a table in a
loop, the CPU and Reads were the same, but the Duration dropped from an
average of 550ms to 450ms (a 19% improvement in speed).
This test was on local SQL Server box using Query Analyzer. Results
might vary when running on a network or when ignoring the row count
results (which are displayed on screen in QA).
But I would urge the OP to set NOCOUNT ON at the very top of every
stored procedure and also as the first command after connecting should
any embedded SQL be executed from the app.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Newbie question on SQL code best practice

Team
I wrote following code
Create PROCEDURE asp_nykl_Full_Update_605ProcStat
--@.sku_barcode varchar(12)
AS
--declare @.Err1 int
--begin transaction
UPDATE pix_tran
SET proc_stat_code = 90
FROM ITEM_MASTER
WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
--AND sku_brcd=@.sku_barcode
AND TRAN_TYPE = '605'
AND proc_stat_code = 10
I have been advised that I must put
1. begin and end transaction
2. Must have SELECT ... (UPDLOCK) before update statement
3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
Is it always advisable to do so?
ThanksD Goyal (goyald@.gmail.com) writes:
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
As long as you only have a single update statement, that's a bit
of overkill - as long as you can be dead sure that the code is running
with implicit_transactions off. This setting is indeed off by default,
but if the procedure is invoked remotely, this is not so. So BEGIN/END
would make it a little safer.

> 2. Must have SELECT ... (UPDLOCK) before update statement
I don't really see the point with this here.

> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
SET NOCOUNT ON is indeed recommendable, as without setting SQL Server
produces a rowcount about affected rows which clients more often does
not care about than they do. In fact, our load tool automatically inserts
a SET NOCOUNT ON in all our stored procedures.
SET LOCK_TIMEOUT I can't really comment on, as this is more tied to
business rules. It prevents the procedure from being locked forever,
but then again what should you do if you time out?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||D Goyal wrote:
> Team
> I wrote following code
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
You can, but it's not always necessary. SQL Server will run that single
statement in a transaction for you if you leave off the begin
tran/commit. Autocommit mode is the default, but if you have standards
in place, you can start a transaction and check @.@.ERROR after each DML
statement and subsequently commit or rollback. It will save you some
headaches should you add a second DML statement to the procedure. In
autocommit mode (without a begin tran) if the first succeeds and the
second statement fails, the first statement still commits.

> 2. Must have SELECT ... (UPDLOCK) before update statement
It's not needed. But if I look at the next item for LOCK_TIMEOUT, I
think I see why it might have been proposed. If you set a lock timeout
to say 5 seconds and try and select the rows with an escalated lock
(would need to be in a transaction), and other processes have locks on
the required pages, the SELECT will abort. But you can do the same
without the SELECT and just leave it to the UPDATE to time out if there
is lock contention.

> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
SET NOCOUNT ON is a highly advisable addition to every stored procedure
(first line). I don't generally use a lock timeout unless I'm running
something that I need to make sure doesn't sit there forever in the case
of someone hold extended locks on the required pages.

> Is it always advisable to do so?
> Thanks
David Gugick
Quest Software
www.imceda.com
www.quest.com|||(1) Single statment like this does not require explicit transactions.
(2) If you are planning for any updates after a read and want to insure that
the data did not change between reads, then you need to add UPDLOCK hint in
your SELECT. If not, I do not see any need.
(3) SET NOCOUNT ON does not have any performance impact. No matter how you
set this option, the @.@.ROWCOUNT value will be affected. It simply does not
send the count as part of the result to the client.
(4) Unless you know how much time you want to wait for a blocked resource, I
would not recommend to change LOCK_TIMEOUT. Remember that this setting chang
e
is for your connection.
"D Goyal" wrote:

> Team
> I wrote following code
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
> 2. Must have SELECT ... (UPDLOCK) before update statement
> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
> Is it always advisable to do so?
> Thanks
>|||satheeshks wrote:
> (3) SET NOCOUNT ON does not have any performance impact. No matter
> how you set this option, the @.@.ROWCOUNT value will be affected. It
> simply does not send the count as part of the result to the client.
I have disagree with # 3.
Using SET NOCOUNT ON can cause a improvement in some queries (batches)
as well as prevent some ADO issues caused by the rowcount information
being returned to the client.
On a test I just performed that inserts 1000 rows into a table in a
loop, the CPU and Reads were the same, but the Duration dropped from an
average of 550ms to 450ms (a 19% improvement in speed).
This test was on local SQL Server box using Query Analyzer. Results
might vary when running on a network or when ignoring the row count
results (which are displayed on screen in QA).
But I would urge the OP to set NOCOUNT ON at the very top of every
stored procedure and also as the first command after connecting should
any embedded SQL be executed from the app.
David Gugick
Quest Software
www.imceda.com
www.quest.com

newbie question on SP, Databases,instances

I am sql server newbie who is trying to migrating a DB2 system(MF) to
SQLserver.
I have a few questions, it would be great if someone can help me out :
1. When I am porting the app to sqlserver, is it advisable to make the
queries to SP or keep it as a regular SQL ? Does SP perform any better
than SQL ? I have heard that lot of places write only SP, so the
front-end programmers can just call the SP and get the data.
2. I got multiple databases in DB2 (in mainframe ,databases are just
logical), is it a good idea to create different databases in SQLserver
or have it in one big database . Pro and cons ?
Could anybody please help
TIA
Roger
Comments Inline
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Perl rookie" <anytasks@.gmail.com> wrote in message
news:1107809732.055331.57880@.z14g2000cwz.googlegro ups.com...
> I am sql server newbie who is trying to migrating a DB2 system(MF) to
> SQLserver.
> I have a few questions, it would be great if someone can help me out :
> 1. When I am porting the app to sqlserver, is it advisable to make the
> queries to SP or keep it as a regular SQL ? Does SP perform any better
> than SQL ? I have heard that lot of places write only SP, so the
> front-end programmers can just call the SP and get the data.
Generally speaking, it is a good idea to write a stored procedure interface
to the database. Performance is usually better due to procedure plan
caching, plus it gives you a layer of abstraction so you can make changes
without haveing to make the code changes at the same time.

> 2. I got multiple databases in DB2 (in mainframe ,databases are just
> logical), is it a good idea to create different databases in SQLserver
> or have it in one big database . Pro and cons ?
>
I prefer to keep data together in a single database if it needs to be
transactionally synchronized for backup and restore, needs to have
database-level referential integrity constraints, and makes sense to do so.
There are other considerations such as whether there are data logs or work
queues involved that have specific performance and backup needs, but those
are the major reasons.
> Could anybody please help
> TIA
> Roger
>
|||When a query is processed by SQL Server, it requires miliseconds to compile
the execution plan. Whether this is a performance issue depends on the case
usage of the SP. If this SP is called 10,000 times per day, or several times
per second, then yes it can reduce the compile time and is probably more a
scalability issue. However, if this is a SP used for reporting purposes,
occasional data inserts (few times per hour), etc. calling as a SP will not
impact the runtime performance. In other words, a query that takes 2 minutes
to execute from the application will not be reduced to 2 seconds simply
because it has been re-written as a SP. Your time is better spent optimizing
the structure of the query itself.
However, from an architectural perspective, I believe that queries
(especially insert / update / delete queries) should be implemented as SPs.
This shifts business logic an data modification programming to the server
side where it can be better managed by the DBA. The application tier should
be as lightweight as possible and deal strictly with presentation, user
input, workflow, etc.; espcially if we are talking about a web application.
Whether your tables should be placed in one database or multiple databases
would require knowledge about the nature and relationship of the data. That
said, depending on the volume of data (say 10 GBs or larger), it can be
generally beneficial for performance reasons to archive rarely used
historical data to a seperate database.
"Perl rookie" <anytasks@.gmail.com> wrote in message
news:1107809732.055331.57880@.z14g2000cwz.googlegro ups.com...
> I am sql server newbie who is trying to migrating a DB2 system(MF) to
> SQLserver.
> I have a few questions, it would be great if someone can help me out :
> 1. When I am porting the app to sqlserver, is it advisable to make the
> queries to SP or keep it as a regular SQL ? Does SP perform any better
> than SQL ? I have heard that lot of places write only SP, so the
> front-end programmers can just call the SP and get the data.
> 2. I got multiple databases in DB2 (in mainframe ,databases are just
> logical), is it a good idea to create different databases in SQLserver
> or have it in one big database . Pro and cons ?
>
> Could anybody please help
> TIA
> Roger
>
|||Thanks a lot Geoff and Johnny . Appreciate ur reply
sql

newbie question on SP, Databases,instances

I am sql server newbie who is trying to migrating a DB2 system(MF) to
SQLserver.
I have a few questions, it would be great if someone can help me out :
1. When I am porting the app to sqlserver, is it advisable to make the
queries to SP or keep it as a regular SQL ? Does SP perform any better
than SQL ? I have heard that lot of places write only SP, so the
front-end programmers can just call the SP and get the data.
2. I got multiple databases in DB2 (in mainframe ,databases are just
logical), is it a good idea to create different databases in SQLserver
or have it in one big database . Pro and cons ?
Could anybody please help
TIA
RogerComments Inline
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Perl rookie" <anytasks@.gmail.com> wrote in message
news:1107809732.055331.57880@.z14g2000cwz.googlegroups.com...
> I am sql server newbie who is trying to migrating a DB2 system(MF) to
> SQLserver.
> I have a few questions, it would be great if someone can help me out :
> 1. When I am porting the app to sqlserver, is it advisable to make the
> queries to SP or keep it as a regular SQL ? Does SP perform any better
> than SQL ? I have heard that lot of places write only SP, so the
> front-end programmers can just call the SP and get the data.
Generally speaking, it is a good idea to write a stored procedure interface
to the database. Performance is usually better due to procedure plan
caching, plus it gives you a layer of abstraction so you can make changes
without haveing to make the code changes at the same time.

> 2. I got multiple databases in DB2 (in mainframe ,databases are just
> logical), is it a good idea to create different databases in SQLserver
> or have it in one big database . Pro and cons ?
>
I prefer to keep data together in a single database if it needs to be
transactionally synchronized for backup and restore, needs to have
database-level referential integrity constraints, and makes sense to do so.
There are other considerations such as whether there are data logs or work
queues involved that have specific performance and backup needs, but those
are the major reasons.
> Could anybody please help
> TIA
> Roger
>|||When a query is processed by SQL Server, it requires miliseconds to compile
the execution plan. Whether this is a performance issue depends on the case
usage of the SP. If this SP is called 10,000 times per day, or several times
per second, then yes it can reduce the compile time and is probably more a
scalability issue. However, if this is a SP used for reporting purposes,
occasional data inserts (few times per hour), etc. calling as a SP will not
impact the runtime performance. In other words, a query that takes 2 minutes
to execute from the application will not be reduced to 2 seconds simply
because it has been re-written as a SP. Your time is better spent optimizing
the structure of the query itself.
However, from an architectural perspective, I believe that queries
(especially insert / update / delete queries) should be implemented as SPs.
This shifts business logic an data modification programming to the server
side where it can be better managed by the DBA. The application tier should
be as lightweight as possible and deal strictly with presentation, user
input, workflow, etc.; espcially if we are talking about a web application.
Whether your tables should be placed in one database or multiple databases
would require knowledge about the nature and relationship of the data. That
said, depending on the volume of data (say 10 GBs or larger), it can be
generally beneficial for performance reasons to archive rarely used
historical data to a seperate database.
"Perl rookie" <anytasks@.gmail.com> wrote in message
news:1107809732.055331.57880@.z14g2000cwz.googlegroups.com...
> I am sql server newbie who is trying to migrating a DB2 system(MF) to
> SQLserver.
> I have a few questions, it would be great if someone can help me out :
> 1. When I am porting the app to sqlserver, is it advisable to make the
> queries to SP or keep it as a regular SQL ? Does SP perform any better
> than SQL ? I have heard that lot of places write only SP, so the
> front-end programmers can just call the SP and get the data.
> 2. I got multiple databases in DB2 (in mainframe ,databases are just
> logical), is it a good idea to create different databases in SQLserver
> or have it in one big database . Pro and cons ?
>
> Could anybody please help
> TIA
> Roger
>|||Thanks a lot Geoff and Johnny . Appreciate ur reply

newbie question on SP, Databases,instances

I am sql server newbie who is trying to migrating a DB2 system(MF) to
SQLserver.
I have a few questions, it would be great if someone can help me out :
1. When I am porting the app to sqlserver, is it advisable to make the
queries to SP or keep it as a regular SQL ? Does SP perform any better
than SQL ? I have heard that lot of places write only SP, so the
front-end programmers can just call the SP and get the data.
2. I got multiple databases in DB2 (in mainframe ,databases are just
logical), is it a good idea to create different databases in SQLserver
or have it in one big database . Pro and cons ?
Could anybody please help
TIA
RogerComments Inline
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Perl rookie" <anytasks@.gmail.com> wrote in message
news:1107809732.055331.57880@.z14g2000cwz.googlegroups.com...
> I am sql server newbie who is trying to migrating a DB2 system(MF) to
> SQLserver.
> I have a few questions, it would be great if someone can help me out :
> 1. When I am porting the app to sqlserver, is it advisable to make the
> queries to SP or keep it as a regular SQL ? Does SP perform any better
> than SQL ? I have heard that lot of places write only SP, so the
> front-end programmers can just call the SP and get the data.
Generally speaking, it is a good idea to write a stored procedure interface
to the database. Performance is usually better due to procedure plan
caching, plus it gives you a layer of abstraction so you can make changes
without haveing to make the code changes at the same time.

> 2. I got multiple databases in DB2 (in mainframe ,databases are just
> logical), is it a good idea to create different databases in SQLserver
> or have it in one big database . Pro and cons ?
>
I prefer to keep data together in a single database if it needs to be
transactionally synchronized for backup and restore, needs to have
database-level referential integrity constraints, and makes sense to do so.
There are other considerations such as whether there are data logs or work
queues involved that have specific performance and backup needs, but those
are the major reasons.
> Could anybody please help
> TIA
> Roger
>|||When a query is processed by SQL Server, it requires miliseconds to compile
the execution plan. Whether this is a performance issue depends on the case
usage of the SP. If this SP is called 10,000 times per day, or several times
per second, then yes it can reduce the compile time and is probably more a
scalability issue. However, if this is a SP used for reporting purposes,
occasional data inserts (few times per hour), etc. calling as a SP will not
impact the runtime performance. In other words, a query that takes 2 minutes
to execute from the application will not be reduced to 2 seconds simply
because it has been re-written as a SP. Your time is better spent optimizing
the structure of the query itself.
However, from an architectural perspective, I believe that queries
(especially insert / update / delete queries) should be implemented as SPs.
This shifts business logic an data modification programming to the server
side where it can be better managed by the DBA. The application tier should
be as lightweight as possible and deal strictly with presentation, user
input, workflow, etc.; espcially if we are talking about a web application.
Whether your tables should be placed in one database or multiple databases
would require knowledge about the nature and relationship of the data. That
said, depending on the volume of data (say 10 GBs or larger), it can be
generally beneficial for performance reasons to archive rarely used
historical data to a seperate database.
"Perl rookie" <anytasks@.gmail.com> wrote in message
news:1107809732.055331.57880@.z14g2000cwz.googlegroups.com...
> I am sql server newbie who is trying to migrating a DB2 system(MF) to
> SQLserver.
> I have a few questions, it would be great if someone can help me out :
> 1. When I am porting the app to sqlserver, is it advisable to make the
> queries to SP or keep it as a regular SQL ? Does SP perform any better
> than SQL ? I have heard that lot of places write only SP, so the
> front-end programmers can just call the SP and get the data.
> 2. I got multiple databases in DB2 (in mainframe ,databases are just
> logical), is it a good idea to create different databases in SQLserver
> or have it in one big database . Pro and cons ?
>
> Could anybody please help
> TIA
> Roger
>|||Thanks a lot Geoff and Johnny . Appreciate ur reply