question about primary key

how can I set my primary key to star again from 1' for example, if I had a
table with some rows in it and I deleted all that rows, next time when I
insert a new row in that table, primary key starts from last primary key of
previous rows, but I want it to start again from 1.
is that possible'
thanks!Bernard,
presumably you mean that your primary key has an identity attribute? In this
case if you truncate the table the seed is reset, or you can use DBCC
CHECKIDENT :
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED)
GO
to get the original seed value (usually 1)
or
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 30)
GO
to set the initial value to be 30.
HTH,
Paul Ibison

question about primary key

how can I set my primary key to star again from 1? for example, if I had a
table with some rows in it and I deleted all that rows, next time when I
insert a new row in that table, primary key starts from last primary key of
previous rows, but I want it to start again from 1.
is that possible?
thanks!
Bernard,
presumably you mean that your primary key has an identity attribute? In this
case if you truncate the table the seed is reset, or you can use DBCC
CHECKIDENT :
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED)
GO
to get the original seed value (usually 1)
or
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 30)
GO
to set the initial value to be 30.
HTH,
Paul Ibison

Question about Group By

Hi All,
I am having a problem with a group of results being returned by a
query with a group by. Basically the data is being populated into a
table (BulkLoad) from a legacy system in a predetermined order. When I
run a query on that data (without any order bys) the data is comming
out in different order then in the order in the table. Does Group by
implicitly order results?
This is an issue because order by wouldn't put the rows in the
correct order, it's ordered by other attributes. Is there any way to
stop it from doing that?
the unordered data looks like (correct)
Q0001Y
Q0001N
Q0002Y
Q0003N
and the group by makes it into (incorrect)
Q0001N
Q0001Y
Q0002Y
Q0003NOrder of rows in a table is never guaranteed (even with a clustered
index ;-) , if you need to order output use ORDER BY
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Red2 wrote:
> Hi All,
> I am having a problem with a group of results being returned by a
> query with a group by. Basically the data is being populated into a
> table (BulkLoad) from a legacy system in a predetermined order. When I
> run a query on that data (without any order bys) the data is comming
> out in different order then in the order in the table. Does Group by
> implicitly order results?
> This is an issue because order by wouldn't put the rows in the
> correct order, it's ordered by other attributes. Is there any way to
> stop it from doing that?
> the unordered data looks like (correct)
> Q0001Y
> Q0001N
> Q0002Y
> Q0003N
> and the group by makes it into (incorrect)
> Q0001N
> Q0001Y
> Q0002Y
> Q0003N|||This is probably one of the most important things to understand about
SQL--the logical aspects of the language vs. the storage of the data and the
physical processing of the query by the database platform you are working
with.
SQL deals with sets, and one of the most fundamental aspects of a set is
that it is UNORDERED.
As far as the language is concerned, a query with no ORDER BY clause does
not guarantee any particular order of the result set (with or without a
GROUP BY).
Therefore, if you need the result to be returned in a particular order, the
only way to guarantee this is by explicitly specifying an ORDER BY clause;
period.
In terms of data storage, even when you create an index on the table, it
"logically sorts the data"; there's no guarantee that the data will be
physically organized in index order in the data files.
In terms of physical processing, even if the version of SQL Server you are
working with supports only certain access methods that process a given
language element (e.g., GROUP BY) in a certain order, you should never rely
on this behavior because future versions or even service packs can change
the behavior as long as the language requirements in terms of the logical
correctness result set are met.
For example, prior to SQL Server 7.0 the only algorithm available to the
optimizer to perform GROUP BY was a sort-based algorithm, which always
resulted in the output being sorted by the GROUP BY list. SQL Server 7.0
introduced hash-based grouping, which does not guarantee that the output
will be sorted by the GROUP BY list.
In short, to save you grief, specify ORDER BY if you need the data sorted.
Otherwise, don't have any expectations in terms of the order of the output.
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Red2" <sdibello@.gmail.com> wrote in message
news:1149682973.983254.90760@.i40g2000cwc.googlegroups.com...
> Hi All,
> I am having a problem with a group of results being returned by a
> query with a group by. Basically the data is being populated into a
> table (BulkLoad) from a legacy system in a predetermined order. When I
> run a query on that data (without any order bys) the data is comming
> out in different order then in the order in the table. Does Group by
> implicitly order results?
> This is an issue because order by wouldn't put the rows in the
> correct order, it's ordered by other attributes. Is there any way to
> stop it from doing that?
> the unordered data looks like (correct)
> Q0001Y
> Q0001N
> Q0002Y
> Q0003N
> and the group by makes it into (incorrect)
> Q0001N
> Q0001Y
> Q0002Y
> Q0003N
>|||Red2 wrote:
> Hi All,
> I am having a problem with a group of results being returned by a
> query with a group by. Basically the data is being populated into a
> table (BulkLoad) from a legacy system in a predetermined order. When I
> run a query on that data (without any order bys) the data is comming
> out in different order then in the order in the table. Does Group by
> implicitly order results?
> This is an issue because order by wouldn't put the rows in the
> correct order, it's ordered by other attributes. Is there any way to
> stop it from doing that?
> the unordered data looks like (correct)
> Q0001Y
> Q0001N
> Q0002Y
> Q0003N
> and the group by makes it into (incorrect)
> Q0001N
> Q0001Y
> Q0002Y
> Q0003N
Tables aren't ordered. The only way to order query results is to use
ORDER BY. If you don't populate the table with the necessary data on
which to sort then you don't have enough information to solve your
problem.
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
--|||Thanks for all the help.
So from one table to another table, with identical data, group by my do
different things
got it...
:)

Question about database size and performance...

I have a project where I will be loading ~ 242 Million Rows of data to a
table and then adding to this on a monthly basis. Maybe 3 million a month
added. This will be used mainly for reporting purposes.
Is there a limit as to how many rows a table in SQL can have? SQL Server
2000 is what I am using?
As for performance what recommendations would you have? Is there a website
out there that discussses this in length?
Thanks,
RogRoger wrote:
> I have a project where I will be loading ~ 242 Million Rows of data
> to a table and then adding to this on a monthly basis. Maybe 3
> million a month added. This will be used mainly for reporting
> purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL
> Server 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
> website out there that discussses this in length?
> Thanks,
> Rog
What will you be doing with the data once it's loaded in the table?
In general, proper index design so queries run as efficiently as
possible is very important on large tables. Just as table design is
important. For example, if you have large varchar or char columns that
are NULLable and many rows might contain NULL, it may be better to
create another table with a 1:1 relationship to store the large data.
Keeping row size as small as possible is better.
SQL Server has no trouble handling tables of this size. Or dishing out
data from a query. It will give you correct results whether the queries
are tuned or not or whether the table is well-designed. The question is,
how long are your users willing to wait for a response.
If you have the DDL for the table, consider posting it here for
comments. And then tune your queries.
David G.|||As David said, attention to table and index design details are important
with large tables. The maximum rows per table is limited only by available
storage in SQL 2000. We have tables with billions of rows that perform
quite well on appropriately sized hardware.
You also need to consider administration, maintenance and your availability
requirements. For example, partitioning financial data into separate tables
by quarter can reduce the time it takes to load data, purge data, rebuild
indexes, etc. Multiple filegroups can provide more granular backup/recovery
options. The downside is additional administration and schema complexity.
This may or may not affect performance, depending on how data are accessed.
You might check out the chapter RDBMS Performance Tuning Guide for Data
Warehousing in the SQL 2000 Resource Kit:
2061.mspx" target="_blank">http://www.microsoft.com/resources/...r />
2061.mspx
Hope this helps.
Dan Guzman
SQL Server MVP
"Roger" <rogoflap@.gmail.com> wrote in message
news:Oex4dY7iEHA.536@.TK2MSFTNGP11.phx.gbl...
> I have a project where I will be loading ~ 242 Million Rows of data to a
> table and then adding to this on a monthly basis. Maybe 3 million a month
> added. This will be used mainly for reporting purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL Server
> 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
website
> out there that discussses this in length?
> Thanks,
> Rog
>|||Thanks for the insite from all of your responses..... I will post my
schema here in a short while... Days to see what suggestions you might
have.
Thanks,
Rog
"Roger" <rogoflap@.gmail.com> wrote in message
news:Oex4dY7iEHA.536@.TK2MSFTNGP11.phx.gbl...
> I have a project where I will be loading ~ 242 Million Rows of data to a
> table and then adding to this on a monthly basis. Maybe 3 million a month
> added. This will be used mainly for reporting purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL Server
> 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
website
> out there that discussses this in length?
> Thanks,
> Rog
>

Question about database size and performance...

I have a project where I will be loading ~ 242 Million Rows of data to a
table and then adding to this on a monthly basis. Maybe 3 million a month
added. This will be used mainly for reporting purposes.
Is there a limit as to how many rows a table in SQL can have? SQL Server
2000 is what I am using?
As for performance what recommendations would you have? Is there a website
out there that discussses this in length?
Thanks,
Rog
Roger wrote:
> I have a project where I will be loading ~ 242 Million Rows of data
> to a table and then adding to this on a monthly basis. Maybe 3
> million a month added. This will be used mainly for reporting
> purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL
> Server 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
> website out there that discussses this in length?
> Thanks,
> Rog
What will you be doing with the data once it's loaded in the table?
In general, proper index design so queries run as efficiently as
possible is very important on large tables. Just as table design is
important. For example, if you have large varchar or char columns that
are NULLable and many rows might contain NULL, it may be better to
create another table with a 1:1 relationship to store the large data.
Keeping row size as small as possible is better.
SQL Server has no trouble handling tables of this size. Or dishing out
data from a query. It will give you correct results whether the queries
are tuned or not or whether the table is well-designed. The question is,
how long are your users willing to wait for a response.
If you have the DDL for the table, consider posting it here for
comments. And then tune your queries.
David G.
|||As David said, attention to table and index design details are important
with large tables. The maximum rows per table is limited only by available
storage in SQL 2000. We have tables with billions of rows that perform
quite well on appropriately sized hardware.
You also need to consider administration, maintenance and your availability
requirements. For example, partitioning financial data into separate tables
by quarter can reduce the time it takes to load data, purge data, rebuild
indexes, etc. Multiple filegroups can provide more granular backup/recovery
options. The downside is additional administration and schema complexity.
This may or may not affect performance, depending on how data are accessed.
You might check out the chapter RDBMS Performance Tuning Guide for Data
Warehousing in the SQL 2000 Resource Kit:
http://www.microsoft.com/resources/d...rt5/c2061.mspx
Hope this helps.
Dan Guzman
SQL Server MVP
"Roger" <rogoflap@.gmail.com> wrote in message
news:Oex4dY7iEHA.536@.TK2MSFTNGP11.phx.gbl...
> I have a project where I will be loading ~ 242 Million Rows of data to a
> table and then adding to this on a monthly basis. Maybe 3 million a month
> added. This will be used mainly for reporting purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL Server
> 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
website
> out there that discussses this in length?
> Thanks,
> Rog
>
|||Thanks for the insite from all of your responses..... I will post my
schema here in a short while... Days to see what suggestions you might
have.
Thanks,
Rog
"Roger" <rogoflap@.gmail.com> wrote in message
news:Oex4dY7iEHA.536@.TK2MSFTNGP11.phx.gbl...
> I have a project where I will be loading ~ 242 Million Rows of data to a
> table and then adding to this on a monthly basis. Maybe 3 million a month
> added. This will be used mainly for reporting purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL Server
> 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
website
> out there that discussses this in length?
> Thanks,
> Rog
>

Question about best practices..

Given the need to update data, is it better to have 500 procs that update a
table as needed for a specific situation or given a table like:
create table dbo.People
(
ID int identity(1,1) not null,
LastAccessed datetime null,
FirstName varchar(20) not null,
LastName varchar(30) not null,
Address1 varchar(50) not null,
Address2 varchar(50) not null,
City varchar(50) not null,
State char(2) not null
)
to have a proc like this that can be used for any update to this table:
create procedure UpdatePeople
@.ID int,
@.LastAccessed datetime=null,
@.FirstName varchar(20)=null,
@.LastName varchar(30)=null,
@.Address1 varchar(50)=null,
@.Address2 varchar(50)=nul,
@.City varchar(50)=null,
@.State char(2)=null
as
set nocount on;
update dbo.People
set LastAccessed=isnull(@.LastAccessed,LastAc
cessed),
FirstName=isnull(@.FirstName, FirstName),
LastName=isnull(@.LastName, LastName),
Address1=isnull(@.Address1, Address1),
Address2=isnull(@.Address2, Address2),
City=isnull(@.City, City),
State=isnull(@.State,State)
where ID=@.ID
return @.@.error
GO
Thanks !! Inquiring minds want to know (mine!) and I'm tired of seeing
developers throwing hundreds of procs at me when it seems unecessary!Personally, I would prefer to have a CRUD interface that takes all the
values out, and then updates all of the values when saving.
The problem with trying to preserve a few bytes like you are doing, is that
now you can't overwrite an existing value with NULL. Yes, you can pass
Address1 = '' but is that really the same thing as NULL? I don't think so,
but it really depends on overall requirements.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%23NHluITgHHA.5052@.TK2MSFTNGP05.phx.gbl...
> Given the need to update data, is it better to have 500 procs that update
> a table as needed for a specific situation or given a table like:
> create table dbo.People
> (
> ID int identity(1,1) not null,
> LastAccessed datetime null,
> FirstName varchar(20) not null,
> LastName varchar(30) not null,
> Address1 varchar(50) not null,
> Address2 varchar(50) not null,
> City varchar(50) not null,
> State char(2) not null
> )
>
> to have a proc like this that can be used for any update to this table:
> create procedure UpdatePeople
> @.ID int,
> @.LastAccessed datetime=null,
> @.FirstName varchar(20)=null,
> @.LastName varchar(30)=null,
> @.Address1 varchar(50)=null,
> @.Address2 varchar(50)=nul,
> @.City varchar(50)=null,
> @.State char(2)=null
> as
> set nocount on;
> update dbo.People
> set LastAccessed=isnull(@.LastAccessed,LastAc
cessed),
> FirstName=isnull(@.FirstName, FirstName),
> LastName=isnull(@.LastName, LastName),
> Address1=isnull(@.Address1, Address1),
> Address2=isnull(@.Address2, Address2),
> City=isnull(@.City, City),
> State=isnull(@.State,State)
> where ID=@.ID
> return @.@.error
> GO
>
> Thanks !! Inquiring minds want to know (mine!) and I'm tired of seeing
> developers throwing hundreds of procs at me when it seems unecessary!
>
>|||Also, typically if you are updating a person's info, you're not updating a
single value. e.g. if someone moves, you need to update address1, address2,
city, state, zip, etc.
Do you really want to manage this set of procedures, and call them all
individually? I wouldn't:
dbo.Person_UpdateAddress1
dbo.Person_UpdateAddress2
dbo.Person_UpdateCity
dbo.Person_UpdateState
dbo.Person_UpdateZip
dbo.Person_UpdatePhone
dbo.Person_UpdateFax
...
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||On 17 Apr, 21:51, "Tim Greenwood" <tim_greenwood AT yahoo DOT com>
wrote:
> Given the need to update data, is it better to have 500 procs that update
a
> table as needed for a specific situation or given a table like:
> create table dbo.People
> (
> ID int identity(1,1) not null,
> LastAccessed datetime null,
> FirstName varchar(20) not null,
> LastName varchar(30) not null,
> Address1 varchar(50) not null,
> Address2 varchar(50) not null,
> City varchar(50) not null,
> State char(2) not null
> )
> to have a proc like this that can be used for any update to this table:
> create procedure UpdatePeople
> @.ID int,
> @.LastAccessed datetime=null,
> @.FirstName varchar(20)=null,
> @.LastName varchar(30)=null,
> @.Address1 varchar(50)=null,
> @.Address2 varchar(50)=nul,
> @.City varchar(50)=null,
> @.State char(2)=null
> as
> set nocount on;
> update dbo.People
> set LastAccessed=isnull(@.LastAccessed,LastAc
cessed),
> FirstName=isnull(@.FirstName, FirstName),
> LastName=isnull(@.LastName, LastName),
> Address1=isnull(@.Address1, Address1),
> Address2=isnull(@.Address2, Address2),
> City=isnull(@.City, City),
> State=isnull(@.State,State)
> where ID=@.ID
> return @.@.error
> GO
> Thanks !! Inquiring minds want to know (mine!) and I'm tired of seeing
> developers throwing hundreds of procs at me when it seems unecessary!
If you regularly need to update some small subset of columns then it
may be worth creating a separate proc for such a case. On grounds of
maintainability I would question the value of creating 500 such procs
unless it's essential to squeeze every last ounce of performance from
the database.
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
--|||That's exactly my point...no I wouldn't want to but that is exactly what I
keep getting from developers. I'd prefer CRUD interface as well but I'm
lacking in support right now. Unfortunately all developers have to pass
their code through me for verification as dba before it goes into final QA
environment.
So other than the idea of supporting only modified values the basic idea
here seems right on? IOW get rid of the whole isnull() on the updates?
The only problem we run into with that is if they've used a custom view or
proc that only queries a subset of data from multiple tables then they don't
have all the values to supply to a CRUD proc for updating...I know these
are extremely basic issues here, I'm just playing devils advocate. I have
some authority to leverage in the enforcement of these things but want to be
sure I'm coming from a best (or at least accepted) practices.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:%23RO3DPTgHHA.1220@.TK2MSFTNGP03.phx.gbl...
> Also, typically if you are updating a person's info, you're not updating a
> single value. e.g. if someone moves, you need to update address1,
> address2, city, state, zip, etc.
> Do you really want to manage this set of procedures, and call them all
> individually? I wouldn't:
> dbo.Person_UpdateAddress1
> dbo.Person_UpdateAddress2
> dbo.Person_UpdateCity
> dbo.Person_UpdateState
> dbo.Person_UpdateZip
> dbo.Person_UpdatePhone
> dbo.Person_UpdateFax
> ...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>|||> So other than the idea of supporting only modified values the basic idea
> here seems right on? IOW get rid of the whole isnull() on the updates?
> The only problem we run into with that is if they've used a custom view or
> proc that only queries a subset of data from multiple tables then they
> don't have all the values to supply to a CRUD proc for updating...I know
> these are extremely basic issues here, I'm just playing devils advocate.
> I have some authority to leverage in the enforcement of these things but
> want to be sure I'm coming from a best (or at least accepted) practices.
We enforce that they get all the details from a generic _GetDetails
procedure if their intention is to update even only one of the values.
If they are just getting the data for display, then yes I could see why they
might argue that they only want a subset of the data.
But with the memory on servers these days, there is no reason why the app
can't store the whole row in memory. Or, in cases like a two-column report,
that segment of the code can just ignore the other columns.
The trade-off here is performance vs. maintenance. You need to make that
decision... we can't tell you what's best because we don't know what it will
take to convince your developers to do it your way, and we don't know what
the performance threshold is (e.g. when does pulling/updating a subset
really change the way the app behaves).
A|||I have developed a WhichFieldsUsed mechanism that enables a single sproc to
only update the field or fields in a table that the caller wishes to have
updated, and then only if they are different from the existing values. Drop
me an email if you are interested. kgboles a t earth link d o t net.
TheSQLGuru
President
Indicium Resources, Inc.|||I already know *how* to do that. My question wasn't so much how to do it as
what is the most accepted practice.
Thanks!
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:%23eMdKFfgHHA.5044@.TK2MSFTNGP05.phx.gbl...
>I have developed a WhichFieldsUsed mechanism that enables a single sproc to
>only update the field or fields in a table that the caller wishes to have
>updated, and then only if they are different from the existing values.
>Drop me an email if you are interested. kgboles a t earth link d o t net.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
>
>

Question about best practices..

Given the need to update data, is it better to have 500 procs that update a
table as needed for a specific situation or given a table like:
create table dbo.People
(
ID int identity(1,1) not null,
LastAccessed datetime null,
FirstName varchar(20) not null,
LastName varchar(30) not null,
Address1 varchar(50) not null,
Address2 varchar(50) not null,
City varchar(50) not null,
State char(2) not null
)
to have a proc like this that can be used for any update to this table:
create procedure UpdatePeople
@.ID int,
@.LastAccessed datetime=null,
@.FirstName varchar(20)=null,
@.LastName varchar(30)=null,
@.Address1 varchar(50)=null,
@.Address2 varchar(50)=nul,
@.City varchar(50)=null,
@.State char(2)=null
as
set nocount on;
update dbo.People
set LastAccessed=isnull(@.LastAccessed,LastAccessed),
FirstName=isnull(@.FirstName, FirstName),
LastName=isnull(@.LastName, LastName),
Address1=isnull(@.Address1, Address1),
Address2=isnull(@.Address2, Address2),
City=isnull(@.City, City),
State=isnull(@.State,State)
where ID=@.ID
return @.@.error
GO
Thanks !! Inquiring minds want to know (mine!) and I'm tired of seeing
developers throwing hundreds of procs at me when it seems unecessary!
Personally, I would prefer to have a CRUD interface that takes all the
values out, and then updates all of the values when saving.
The problem with trying to preserve a few bytes like you are doing, is that
now you can't overwrite an existing value with NULL. Yes, you can pass
Address1 = '' but is that really the same thing as NULL? I don't think so,
but it really depends on overall requirements.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%23NHluITgHHA.5052@.TK2MSFTNGP05.phx.gbl...
> Given the need to update data, is it better to have 500 procs that update
> a table as needed for a specific situation or given a table like:
> create table dbo.People
> (
> ID int identity(1,1) not null,
> LastAccessed datetime null,
> FirstName varchar(20) not null,
> LastName varchar(30) not null,
> Address1 varchar(50) not null,
> Address2 varchar(50) not null,
> City varchar(50) not null,
> State char(2) not null
> )
>
> to have a proc like this that can be used for any update to this table:
> create procedure UpdatePeople
> @.ID int,
> @.LastAccessed datetime=null,
> @.FirstName varchar(20)=null,
> @.LastName varchar(30)=null,
> @.Address1 varchar(50)=null,
> @.Address2 varchar(50)=nul,
> @.City varchar(50)=null,
> @.State char(2)=null
> as
> set nocount on;
> update dbo.People
> set LastAccessed=isnull(@.LastAccessed,LastAccessed),
> FirstName=isnull(@.FirstName, FirstName),
> LastName=isnull(@.LastName, LastName),
> Address1=isnull(@.Address1, Address1),
> Address2=isnull(@.Address2, Address2),
> City=isnull(@.City, City),
> State=isnull(@.State,State)
> where ID=@.ID
> return @.@.error
> GO
>
> Thanks !! Inquiring minds want to know (mine!) and I'm tired of seeing
> developers throwing hundreds of procs at me when it seems unecessary!
>
>
|||Also, typically if you are updating a person's info, you're not updating a
single value. e.g. if someone moves, you need to update address1, address2,
city, state, zip, etc.
Do you really want to manage this set of procedures, and call them all
individually? I wouldn't:
dbo.Person_UpdateAddress1
dbo.Person_UpdateAddress2
dbo.Person_UpdateCity
dbo.Person_UpdateState
dbo.Person_UpdateZip
dbo.Person_UpdatePhone
dbo.Person_UpdateFax
...
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
|||On 17 Apr, 21:51, "Tim Greenwood" <tim_greenwood AT yahoo DOT com>
wrote:
> Given the need to update data, is it better to have 500 procs that update a
> table as needed for a specific situation or given a table like:
> create table dbo.People
> (
> ID int identity(1,1) not null,
> LastAccessed datetime null,
> FirstName varchar(20) not null,
> LastName varchar(30) not null,
> Address1 varchar(50) not null,
> Address2 varchar(50) not null,
> City varchar(50) not null,
> State char(2) not null
> )
> to have a proc like this that can be used for any update to this table:
> create procedure UpdatePeople
> @.ID int,
> @.LastAccessed datetime=null,
> @.FirstName varchar(20)=null,
> @.LastName varchar(30)=null,
> @.Address1 varchar(50)=null,
> @.Address2 varchar(50)=nul,
> @.City varchar(50)=null,
> @.State char(2)=null
> as
> set nocount on;
> update dbo.People
> set LastAccessed=isnull(@.LastAccessed,LastAccessed),
> FirstName=isnull(@.FirstName, FirstName),
> LastName=isnull(@.LastName, LastName),
> Address1=isnull(@.Address1, Address1),
> Address2=isnull(@.Address2, Address2),
> City=isnull(@.City, City),
> State=isnull(@.State,State)
> where ID=@.ID
> return @.@.error
> GO
> Thanks !! Inquiring minds want to know (mine!) and I'm tired of seeing
> developers throwing hundreds of procs at me when it seems unecessary!
If you regularly need to update some small subset of columns then it
may be worth creating a separate proc for such a case. On grounds of
maintainability I would question the value of creating 500 such procs
unless it's essential to squeeze every last ounce of performance from
the database.
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
|||That's exactly my point...no I wouldn't want to but that is exactly what I
keep getting from developers. I'd prefer CRUD interface as well but I'm
lacking in support right now. Unfortunately all developers have to pass
their code through me for verification as dba before it goes into final QA
environment.
So other than the idea of supporting only modified values the basic idea
here seems right on? IOW get rid of the whole isnull() on the updates?
The only problem we run into with that is if they've used a custom view or
proc that only queries a subset of data from multiple tables then they don't
have all the values to supply to a CRUD proc for updating...I know these
are extremely basic issues here, I'm just playing devils advocate. I have
some authority to leverage in the enforcement of these things but want to be
sure I'm coming from a best (or at least accepted) practices.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23RO3DPTgHHA.1220@.TK2MSFTNGP03.phx.gbl...
> Also, typically if you are updating a person's info, you're not updating a
> single value. e.g. if someone moves, you need to update address1,
> address2, city, state, zip, etc.
> Do you really want to manage this set of procedures, and call them all
> individually? I wouldn't:
> dbo.Person_UpdateAddress1
> dbo.Person_UpdateAddress2
> dbo.Person_UpdateCity
> dbo.Person_UpdateState
> dbo.Person_UpdateZip
> dbo.Person_UpdatePhone
> dbo.Person_UpdateFax
> ...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
|||> So other than the idea of supporting only modified values the basic idea
> here seems right on? IOW get rid of the whole isnull() on the updates?
> The only problem we run into with that is if they've used a custom view or
> proc that only queries a subset of data from multiple tables then they
> don't have all the values to supply to a CRUD proc for updating...I know
> these are extremely basic issues here, I'm just playing devils advocate.
> I have some authority to leverage in the enforcement of these things but
> want to be sure I'm coming from a best (or at least accepted) practices.
We enforce that they get all the details from a generic _GetDetails
procedure if their intention is to update even only one of the values.
If they are just getting the data for display, then yes I could see why they
might argue that they only want a subset of the data.
But with the memory on servers these days, there is no reason why the app
can't store the whole row in memory. Or, in cases like a two-column report,
that segment of the code can just ignore the other columns.
The trade-off here is performance vs. maintenance. You need to make that
decision... we can't tell you what's best because we don't know what it will
take to convince your developers to do it your way, and we don't know what
the performance threshold is (e.g. when does pulling/updating a subset
really change the way the app behaves).
A
|||I have developed a WhichFieldsUsed mechanism that enables a single sproc to
only update the field or fields in a table that the caller wishes to have
updated, and then only if they are different from the existing values. Drop
me an email if you are interested. kgboles a t earth link d o t net.
TheSQLGuru
President
Indicium Resources, Inc.
|||I already know *how* to do that. My question wasn't so much how to do it as
what is the most accepted practice.
Thanks!
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:%23eMdKFfgHHA.5044@.TK2MSFTNGP05.phx.gbl...
>I have developed a WhichFieldsUsed mechanism that enables a single sproc to
>only update the field or fields in a table that the caller wishes to have
>updated, and then only if they are different from the existing values.
>Drop me an email if you are interested. kgboles a t earth link d o t net.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
>
>

question abot primary key

how can I set my primary key to star again from 1?? for example, if I had a
table with some rows in it and I deleted all that rows, next time when I
insert a new row in that table, primary key starts from last primary key of
previous rows, but I want it to start again from 1.

is that possible??

thanks!I think you are talking about an IDENTITY column, which is not the same as a
Primary Key (although IDENTITY is used as a surrogate key).

DBCC CHECKIDENT ('TableName', RESEED,0)

--
David Portas
SQL Server MVP
--|||>> how can I set my primary key to start again from 1? <<

Please read a book on RDBMS; you have absolutely no idea what you are
doing.

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access
or ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless.

A primary key **BY DEFINITION** is a subset of attributes (columns).
Keys do not "start" anywhere. Each entity in the data model has a
unique primary key by its nature; it is not assigned by the PHYSICAL
storage used to hold the PHYSICAL representation of the DBMS.

If you had a 1950's magnetic tape file, then your question would make
sense. They start at record #1 each time you hang a new magnetic tape
on the drive, which is obviously your mental model. This is the
difference between a VIN number and a parking space number.