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 Ibisonsql

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 Point Time Recovery

Hello All,
I have questions about point in time.
I took database backups and transaction log like the following.
Database backup at 1:00 pm on July 20
Transaction backup at 2:00 pm on July 20
Database backup at 1:00 pm on July 21
Transaction backup at 2:00 pm on July 21
Can I restore the database to 3:00 pm on July 20?
Can I restore the database to only 2:00 pm on July 20?
Would you please explain it?
Are there any good documents or web site about point time recovery?
If point in time restore button is grey, I cannot restore a database to point
in time? Is it right?
Thanks in advance,
Do.
Message posted via http://www.droptable.com
Hi,
POINT-IN-TIME recovery can be made only if your database is FULL recovery
model. BULK_LOGGED recovery model will not allow
POINT-IN_TIME recovery.
Based on your backups below you could do a seperate point in time recovery
till 2:00 pm on July 20 or 3:00 pm on July 20
(Provided your database recovery is FULL)
-- For 2:00 pm on July 20
RESTORE DATABASE DBNAME
FROM disk='c:\backup\dbfullbackup_20_july.bak'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_20july_02PM.TRN'
WITH RECOVERY, STOPAT = 'Jul 20, 2005 02:00 PM'
GO
-- For 3:00 pm on July 20
RESTORE DATABASE DBNAME
FROM disk='c:\backup\dbfullbackup_20_july.bak'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_20july_02PM.TRN'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_21july_02PM.TRN'
WITH RECOVERY, STOPAT = 'Jul 20, 2005 03:00 PM'
GO
Are there any good documents or web site about point time recovery?
See POINT IN TIME in books online
If point in time restore button is grey, I cannot restore a database to
point in time? Is it right?
"Do P via droptable.com" <forum@.droptable.com> wrote in message
news:51A93BABEA594@.droptable.com...
> Hello All,
> I have questions about point in time.
> I took database backups and transaction log like the following.
> Database backup at 1:00 pm on July 20
> Transaction backup at 2:00 pm on July 20
> Database backup at 1:00 pm on July 21
> Transaction backup at 2:00 pm on July 21
> Can I restore the database to 3:00 pm on July 20?
> Can I restore the database to only 2:00 pm on July 20?
> Would you please explain it?
> Are there any good documents or web site about point time recovery?
> If point in time restore button is grey, I cannot restore a database to
> point
> in time? Is it right?
>
> Thanks in advance,
> Do.
>
> --
> Message posted via http://www.droptable.com
|||On Thu, 21 Jul 2005 17:27:51 GMT, Do P via droptable.com wrote:

>Hello All,
>I have questions about point in time.
>I took database backups and transaction log like the following.
>Database backup at 1:00 pm on July 20
>Transaction backup at 2:00 pm on July 20
>Database backup at 1:00 pm on July 21
>Transaction backup at 2:00 pm on July 21
>Can I restore the database to 3:00 pm on July 20?
Hi Do,
Yes. First restore from the full backup, with the WITH NORECOVERY
option. Next, restore the transaction log backup of July 20 2:00 PM,
again with the WITH NORECOVERY option. Finally, restore from the second
transaction log, this time without WITH NORECOVERY, but with a STOPAT
parameter.

>Are there any good documents or web site about point time recovery?
Books Online is a great start.

>If point in time restore button is grey, I cannot restore a database to point
>in time? Is it right?
Either that, or you didn't supply all the needed information yet, or you
have to restore some other backup first before you can restore to the
desired point in time. I recommend that you start using Query Analyzer
and typing the RESTORE DATABASE and RESTORE LOG commands. This gives you
much more control over what will happen.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hello Hugo
Thanks for your update.
If I use the ''Point in time restore'' option, at the end of the restore, my
database is ALWAYS in ''Loading'' state even if I checked the ''Leave
database operational''. Would you please let me know how to restore a
database to point in time in Enterprise Manager? How can I avoid this from
happening?
Best Regards,
Do.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200507/1
|||Perhaps a refresh issue? Try closing down Enterprise Manager and start it again.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Do Park via droptable.com" <forum@.droptable.com> wrote in message
news:51B72295E6DA2@.droptable.com...
> Hello Hugo
> Thanks for your update.
> If I use the ''Point in time restore'' option, at the end of the restore, my
> database is ALWAYS in ''Loading'' state even if I checked the ''Leave
> database operational''. Would you please let me know how to restore a
> database to point in time in Enterprise Manager? How can I avoid this from
> happening?
> Best Regards,
> Do.
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200507/1

Question about Point Time Recovery

Hello All,
I have questions about point in time.
I took database backups and transaction log like the following.
Database backup at 1:00 pm on July 20
Transaction backup at 2:00 pm on July 20
Database backup at 1:00 pm on July 21
Transaction backup at 2:00 pm on July 21
Can I restore the database to 3:00 pm on July 20?
Can I restore the database to only 2:00 pm on July 20?
Would you please explain it?
Are there any good documents or web site about point time recovery?
If point in time restore button is grey, I cannot restore a database to point
in time? Is it right?
Thanks in advance,
Do.
--
Message posted via http://www.sqlmonster.comHi,
POINT-IN-TIME recovery can be made only if your database is FULL recovery
model. BULK_LOGGED recovery model will not allow
POINT-IN_TIME recovery.
Based on your backups below you could do a seperate point in time recovery
till 2:00 pm on July 20 or 3:00 pm on July 20
(Provided your database recovery is FULL)
-- For 2:00 pm on July 20
RESTORE DATABASE DBNAME
FROM disk='c:\backup\dbfullbackup_20_july.bak'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_20july_02PM.TRN'
WITH RECOVERY, STOPAT = 'Jul 20, 2005 02:00 PM'
GO
-- For 3:00 pm on July 20
RESTORE DATABASE DBNAME
FROM disk='c:\backup\dbfullbackup_20_july.bak'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_20july_02PM.TRN'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_21july_02PM.TRN'
WITH RECOVERY, STOPAT = 'Jul 20, 2005 03:00 PM'
GO
Are there any good documents or web site about point time recovery?
See POINT IN TIME in books online
If point in time restore button is grey, I cannot restore a database to
point in time? Is it right?
"Do P via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:51A93BABEA594@.SQLMonster.com...
> Hello All,
> I have questions about point in time.
> I took database backups and transaction log like the following.
> Database backup at 1:00 pm on July 20
> Transaction backup at 2:00 pm on July 20
> Database backup at 1:00 pm on July 21
> Transaction backup at 2:00 pm on July 21
> Can I restore the database to 3:00 pm on July 20?
> Can I restore the database to only 2:00 pm on July 20?
> Would you please explain it?
> Are there any good documents or web site about point time recovery?
> If point in time restore button is grey, I cannot restore a database to
> point
> in time? Is it right?
>
> Thanks in advance,
> Do.
>
> --
> Message posted via http://www.sqlmonster.com|||On Thu, 21 Jul 2005 17:27:51 GMT, Do P via SQLMonster.com wrote:
>Hello All,
>I have questions about point in time.
>I took database backups and transaction log like the following.
>Database backup at 1:00 pm on July 20
>Transaction backup at 2:00 pm on July 20
>Database backup at 1:00 pm on July 21
>Transaction backup at 2:00 pm on July 21
>Can I restore the database to 3:00 pm on July 20?
Hi Do,
Yes. First restore from the full backup, with the WITH NORECOVERY
option. Next, restore the transaction log backup of July 20 2:00 PM,
again with the WITH NORECOVERY option. Finally, restore from the second
transaction log, this time without WITH NORECOVERY, but with a STOPAT
parameter.
>Are there any good documents or web site about point time recovery?
Books Online is a great start.
>If point in time restore button is grey, I cannot restore a database to point
>in time? Is it right?
Either that, or you didn't supply all the needed information yet, or you
have to restore some other backup first before you can restore to the
desired point in time. I recommend that you start using Query Analyzer
and typing the RESTORE DATABASE and RESTORE LOG commands. This gives you
much more control over what will happen.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo
Thanks for your update.
If I use the ''Point in time restore'' option, at the end of the restore, my
database is ALWAYS in ''Loading'' state even if I checked the ''Leave
database operational''. Would you please let me know how to restore a
database to point in time in Enterprise Manager? How can I avoid this from
happening?
Best Regards,
Do.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1|||Perhaps a refresh issue? Try closing down Enterprise Manager and start it again.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Do Park via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:51B72295E6DA2@.SQLMonster.com...
> Hello Hugo
> Thanks for your update.
> If I use the ''Point in time restore'' option, at the end of the restore, my
> database is ALWAYS in ''Loading'' state even if I checked the ''Leave
> database operational''. Would you please let me know how to restore a
> database to point in time in Enterprise Manager? How can I avoid this from
> happening?
> Best Regards,
> Do.
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1

Question about Point Time Recovery

Hello All,
I have questions about point in time.
I took database backups and transaction log like the following.
Database backup at 1:00 pm on July 20
Transaction backup at 2:00 pm on July 20
Database backup at 1:00 pm on July 21
Transaction backup at 2:00 pm on July 21
Can I restore the database to 3:00 pm on July 20?
Can I restore the database to only 2:00 pm on July 20?
Would you please explain it?
Are there any good documents or web site about point time recovery?
If point in time restore button is grey, I cannot restore a database to poin
t
in time? Is it right?
Thanks in advance,
Do.
Message posted via http://www.droptable.comHi,
POINT-IN-TIME recovery can be made only if your database is FULL recovery
model. BULK_LOGGED recovery model will not allow
POINT-IN_TIME recovery.
Based on your backups below you could do a seperate point in time recovery
till 2:00 pm on July 20 or 3:00 pm on July 20
(Provided your database recovery is FULL)
-- For 2:00 pm on July 20
RESTORE DATABASE DBNAME
FROM disk='c:\backup\dbfullbackup_20_july.bak'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_20july_02PM.TRN'
WITH RECOVERY, STOPAT = 'Jul 20, 2005 02:00 PM'
GO
-- For 3:00 pm on July 20
RESTORE DATABASE DBNAME
FROM disk='c:\backup\dbfullbackup_20_july.bak'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_20july_02PM.TRN'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_21july_02PM.TRN'
WITH RECOVERY, STOPAT = 'Jul 20, 2005 03:00 PM'
GO
Are there any good documents or web site about point time recovery?
See POINT IN TIME in books online
If point in time restore button is grey, I cannot restore a database to
point in time? Is it right?
"Do P via droptable.com" <forum@.droptable.com> wrote in message
news:51A93BABEA594@.droptable.com...
> Hello All,
> I have questions about point in time.
> I took database backups and transaction log like the following.
> Database backup at 1:00 pm on July 20
> Transaction backup at 2:00 pm on July 20
> Database backup at 1:00 pm on July 21
> Transaction backup at 2:00 pm on July 21
> Can I restore the database to 3:00 pm on July 20?
> Can I restore the database to only 2:00 pm on July 20?
> Would you please explain it?
> Are there any good documents or web site about point time recovery?
> If point in time restore button is grey, I cannot restore a database to
> point
> in time? Is it right?
>
> Thanks in advance,
> Do.
>
> --
> Message posted via http://www.droptable.com|||On Thu, 21 Jul 2005 17:27:51 GMT, Do P via droptable.com wrote:

>Hello All,
>I have questions about point in time.
>I took database backups and transaction log like the following.
>Database backup at 1:00 pm on July 20
>Transaction backup at 2:00 pm on July 20
>Database backup at 1:00 pm on July 21
>Transaction backup at 2:00 pm on July 21
>Can I restore the database to 3:00 pm on July 20?
Hi Do,
Yes. First restore from the full backup, with the WITH NORECOVERY
option. Next, restore the transaction log backup of July 20 2:00 PM,
again with the WITH NORECOVERY option. Finally, restore from the second
transaction log, this time without WITH NORECOVERY, but with a STOPAT
parameter.

>Are there any good documents or web site about point time recovery?
Books Online is a great start.

>If point in time restore button is grey, I cannot restore a database to poi
nt
>in time? Is it right?
Either that, or you didn't supply all the needed information yet, or you
have to restore some other backup first before you can restore to the
desired point in time. I recommend that you start using Query Analyzer
and typing the RESTORE DATABASE and RESTORE LOG commands. This gives you
much more control over what will happen.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo
Thanks for your update.
If I use the ''Point in time restore'' option, at the end of the restore, my
database is ALWAYS in ''Loading'' state even if I checked the ''Leave
database operational''. Would you please let me know how to restore a
database to point in time in Enterprise Manager? How can I avoid this from
happening?
Best Regards,
Do.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200507/1|||Perhaps a refresh issue? Try closing down Enterprise Manager and start it ag
ain.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Do Park via droptable.com" <forum@.droptable.com> wrote in message
news:51B72295E6DA2@.droptable.com...
> Hello Hugo
> Thanks for your update.
> If I use the ''Point in time restore'' option, at the end of the restore,
my
> database is ALWAYS in ''Loading'' state even if I checked the ''Leave
> database operational''. Would you please let me know how to restore a
> database to point in time in Enterprise Manager? How can I avoid this from
> happening?
> Best Regards,
> Do.
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200507/1

Question about parallel operations of SQLServer

Our system will support about 200 concurrent users during work time. But
some heavy queries are executed in parallel sometimes. When this is
happening, the server is really stressed and even logon to the server will
take long time. We have tried our best to optimize these heavy queries and
we can not totally eliminate the parallel process. What should we do? should
we turn off the parallel option? How does your server configured?
Thanks for any response.
Lijunreduce the setting "max degree of parallelism" to something less than the
total number of processors on the machine.
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Lijun Zhang" <nospam@.nospam.nospam> wrote in message
news:#KXBHLKqDHA.3320@.tk2msftngp13.phx.gbl...
> Our system will support about 200 concurrent users during work time. But
> some heavy queries are executed in parallel sometimes. When this is
> happening, the server is really stressed and even logon to the server will
> take long time. We have tried our best to optimize these heavy queries and
> we can not totally eliminate the parallel process. What should we do?
should
> we turn off the parallel option? How does your server configured?
> Thanks for any response.
> Lijun
>|||Hi Lijun,
Thanks for your post. Generally, the bottleneck of the query performance
might be on many factors (mainly divided into four parts) such as the
Number and Type of Processor, Memory Size and Usage, Physical Disk I/O,
Multiple threads (Hardware and Operating System Limitations); Provider to
connect server, Network Communication Speed, Numbers of connection
(Connection Limitation); Execution Plan, Statistics, Index, Data Volume,
Returned result sets, locks/deadlocks, long-run transactions/store
procedure (SQL Server Specific Issues); Cursor Location, Coding consistent,
application designing issue (VB Specific Issues). Therefore, the
configuration on the server side is not the only thing we need to concern
for the performance of SQL Server.
In this case, how did the 200 concurrent users connect to SQL Server during
work time, using Query Analyzer or in Application?
You wrote "the server is really stressed and even logon to the server will
take long time. What do you mean by the "long time"? How many seconds do
you spend on query and logon at the worst situation?
What does the hardware configuration on our side? How many processors do
you have?
Did some jobs worked on the background inducing the server busy?
The above things that affect the query performance are all need to be
considered on your side. For detailed reference on how to perform the
configuration on the server side, please read the following article
seriously and perform some configurations which meet your needs. 319942 HOW
TO: Determine Proper SQL Server Configuration Settings
http://support.microsoft.com/?id=319942
For additional information regarding this issue please refer to the
following article on SQL Server Books Online.
Topic: "max degree of parallelism Option"
Topic: "Degree of Parallelism"
For how to troubleshoot SQL Server performance issues step-by-step, please
reference the following Microsoft technical article which accurately
determine the source of a performance issue.
224587.KB.EN-US INF: Troubleshooting Application Performance with SQL Server
http://support.microsoft.com/default.aspx?scid=KB;EN-US;224587
For detailed information on how to perform QUERY TUNING, please follow the
directions of the following article which specifies accurate
troubleshooting unexpectedly long-lasting queries and updates and provides
very effective method to deal with the poorly performing queries:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/od
p_tun_1_536v.asp
Also, due to the complexity of this issue, it would be best to contact
Microsoft Product Support Services via telephone so that a dedicated
Support Professional can assist with your request. To obtain the phone
numbers for specific technology request please take a look at the web site
listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
Thanks for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Sorry if I sound silly
Can you make sure there are not too many "Select * INTO..." statements in your SQL these can also HOLD locks on system tables, that can make logging into sql server slow.

Question about functions

Hi,
I need to call a function in a sql query in a stored procedure to
calculate time differences between various dates. I have a function
that uses a cursor to sum up the totals of these numbers, but it runs
very, very slowly. I can accomplish the same results without a cursor
by using a temporary table and several queries, but when I try to put
this in a stored procedure and call the stored procedure from the
function, I get the following error:
Only functions and extended stored procedures can be executed from
within a function.
Any suggestions?
Thanks,
Amy Bolden>> Any suggestions?
The message clearly states what you can do with a function. So you will have
to find an alternative to calling procedures from functions. Either make the
calling routine a procedure or make the called routine a function.
Anith|||You must supply more information about that, did you try to use
datediff in a correlated query (don=B4t know where you get the data from
?).
Jens Suessmeyer.|||Sorry, I thought maybe an explanation would be enough.
Here is the top query in the stored procedure. The @.StartDate and
@.EndDate are
parameters that are supplied by a web application:
SELECT DISTINCT(CONVERT(VARCHAR(10), TT.DateTS, 101)) AS ActivityDate,
POSUM.UserName,
TT.UserId,
dbo.fnTimeInTruckInMinutes(TT.UserId,TT.DateTS) AS TimeInTruck
FROM vw_TTLH TT INNER JOIN
UserMaster POSUM ON TT.UserID = POSUM.UserNum
WHERE DateTS BETWEEN @.StartDate AND @.EndDate)
GROUP BY POSUM.UserName, TT.UserID, POSUM.UserNum,
CONVERT(VARCHAR(10), TT.DateTS, 101), TT.DateTS
Here is the function that calculates all the time spent in a truck for a
particular day:
CREATE FUNCTION dbo.fnTimeInTruckInMinutes (@.UserID int = NULL,
@.ActivityDate DateTime = NULL)
RETURNS int
AS
BEGIN
DECLARE @.TotalTimeInTruck INT
Exec spGetTotalTimeInTruck @.UserID, @.ActivityDate, @.TotalTimeInTruck
RETURN @.TotalTimeInTruck
END
Here is the stored procedure that I am trying to call from the function
to get the total time
spent in a truck on a single day:
CREATE PROCEDURE spGetTotalTimeInTruck
@.UserID INT,
@.ActivityDate VARCHAR(10),
@.TotalTimeInTruck INT OUTPUT
AS
DECLARE @.ActivityDatePlusOne VARCHAR(10)
SET @.ActivityDatePlusOne = DATEADD(d, 1, @.ActivityDate)
CREATE TABLE #tempTruck
(
SeqNum int NULL,
InTruck datetime NULL,
OutTruck datetime NULL
)
INSERT INTO #tempTruck
(OrderNumber, InTruck)
SELECT OrderNumber, DateTS
FROM vw_TTLH
WHERE USerID = @.UserID
AND DateTimeStamp >= @.ActivityDate
AND DateTimeStamp <= @.ActivityDatePlusOne
AND ActID in (200)
UPDATE #tempTruck
SET OutTruck = (
SELECT DateTS
FROM vw_TTLH
WHERE USerID = @.UserID
AND DateTimeStamp >= @.ActivityDate
AND DateTimeStamp <= @.ActivityDatePlusOne
AND OrderNumber = #tempTruck.OrderNumber + 1)
SELECT SUM(DateDiff(n, InTruck, OutTruck)) FROM #tempTruck
DROP TABLE #tempTruck
RETURN @.TotalTimeInTruck
Thanks,
Amy Bolden
*** Sent via Developersdex http://www.examnotes.net ***|||Are you talking about something similar to this?
http://www.eggheadcafe.com/articles/20030626.asp
Robbe Morris - 2004/2005 Microsoft MVP C#
Free Source Code for ADO.NET Object Mapper To DataBase Tables And Stored
Procedures
http://www.eggheadcafe.com/articles...e_generator.asp
"Amy" <abolden@.eastridge.net> wrote in message
news:1127400856.741551.164370@.z14g2000cwz.googlegroups.com...
> Hi,
> I need to call a function in a sql query in a stored procedure to
> calculate time differences between various dates. I have a function
> that uses a cursor to sum up the totals of these numbers, but it runs
> very, very slowly. I can accomplish the same results without a cursor
> by using a temporary table and several queries, but when I try to put
> this in a stored procedure and call the stored procedure from the
> function, I get the following error:
> Only functions and extended stored procedures can be executed from
> within a function.
> Any suggestions?
> Thanks,
> Amy Bolden
>|||You might try eliminating the temp table and using a derived table in
its place. Something like this: (COMPLETELY UNTESTED)
CREATE PROCEDURE spGetTotalTimeInTruck @.UserID INT, @.ActivityDate
VARCHAR(10), @.TotalTimeInTruck INT OUTPUT
AS
SELECT -- Should "@.TotalTimeInTruck = " go here'
SUM(DateDiff(n, InTruck, OutTruck)) AS
FROM ( SELECT OrderNumber, DateTS AS InTruck,
( SELECT DateTS
FROM vw_TTLH ttlh2
WHERE USerID = @.UserID
AND DateTimeStamp >= @.ActivityDate
AND DateTimeStamp <= DATEADD(d, 1, @.ActivityDate)
AND OrderNumber = ttlh1.OrderNumber + 1) AS OutTruck
FROM vw_TTLH ttlh1
WHERE USerID = @.UserID
AND DateTimeStamp >= @.ActivityDate
AND DateTimeStamp <= DATEADD(d, 1, @.ActivityDate)
AND ActID in (200)
) ttlh_d
RETURN @.TotalTimeInTruck
You might then consider making the function an inline function (put the
select inline - "return select ..."). The optimizer seems to like that
better than procedural code.
Good luck.
Payson
Amy Bolden wrote:
> Sorry, I thought maybe an explanation would be enough.
> Here is the top query in the stored procedure. The @.StartDate and
> @.EndDate are
> parameters that are supplied by a web application:
> SELECT DISTINCT(CONVERT(VARCHAR(10), TT.DateTS, 101)) AS ActivityDate,
> POSUM.UserName,
> TT.UserId,
> dbo.fnTimeInTruckInMinutes(TT.UserId,TT.DateTS) AS TimeInTruck
> FROM vw_TTLH TT INNER JOIN
> UserMaster POSUM ON TT.UserID = POSUM.UserNum
> WHERE DateTS BETWEEN @.StartDate AND @.EndDate)
> GROUP BY POSUM.UserName, TT.UserID, POSUM.UserNum,
> CONVERT(VARCHAR(10), TT.DateTS, 101), TT.DateTS
> Here is the function that calculates all the time spent in a truck for a
> particular day:
> CREATE FUNCTION dbo.fnTimeInTruckInMinutes (@.UserID int = NULL,
> @.ActivityDate DateTime = NULL)
> RETURNS int
> AS
> BEGIN
> DECLARE @.TotalTimeInTruck INT
>
> Exec spGetTotalTimeInTruck @.UserID, @.ActivityDate, @.TotalTimeInTruck
> RETURN @.TotalTimeInTruck
> END
> Here is the stored procedure that I am trying to call from the function
> to get the total time
> spent in a truck on a single day:
> CREATE PROCEDURE spGetTotalTimeInTruck
> @.UserID INT,
> @.ActivityDate VARCHAR(10),
> @.TotalTimeInTruck INT OUTPUT
> AS
> DECLARE @.ActivityDatePlusOne VARCHAR(10)
> SET @.ActivityDatePlusOne = DATEADD(d, 1, @.ActivityDate)
> CREATE TABLE #tempTruck
> (
> SeqNum int NULL,
> InTruck datetime NULL,
> OutTruck datetime NULL
> )
> INSERT INTO #tempTruck
> (OrderNumber, InTruck)
> SELECT OrderNumber, DateTS
> FROM vw_TTLH
> WHERE USerID = @.UserID
> AND DateTimeStamp >= @.ActivityDate
> AND DateTimeStamp <= @.ActivityDatePlusOne
> AND ActID in (200)
> UPDATE #tempTruck
> SET OutTruck = (
> SELECT DateTS
> FROM vw_TTLH
> WHERE USerID = @.UserID
> AND DateTimeStamp >= @.ActivityDate
> AND DateTimeStamp <= @.ActivityDatePlusOne
> AND OrderNumber = #tempTruck.OrderNumber + 1)
> SELECT SUM(DateDiff(n, InTruck, OutTruck)) FROM #tempTruck
> DROP TABLE #tempTruck
> RETURN @.TotalTimeInTruck
> Thanks,
> Amy Bolden
> *** Sent via Developersdex http://www.examnotes.net ***|||You might try eliminating the temp table and using a derived table in
its place. Something like this: (COMPLETELY UNTESTED)
CREATE PROCEDURE spGetTotalTimeInTruck @.UserID INT, @.ActivityDate
VARCHAR(10), @.TotalTimeInTruck INT OUTPUT
AS
SELECT -- Should "@.TotalTimeInTruck = " go here'
SUM(DateDiff(n, InTruck, OutTruck)) AS
FROM ( SELECT OrderNumber, DateTS AS InTruck,
( SELECT DateTS
FROM vw_TTLH ttlh2
WHERE USerID = @.UserID
AND DateTimeStamp >= @.ActivityDate
AND DateTimeStamp <= DATEADD(d, 1, @.ActivityDate)
AND OrderNumber = ttlh1.OrderNumber + 1) AS OutTruck
FROM vw_TTLH ttlh1
WHERE USerID = @.UserID
AND DateTimeStamp >= @.ActivityDate
AND DateTimeStamp <= DATEADD(d, 1, @.ActivityDate)
AND ActID in (200)
) ttlh_d
RETURN @.TotalTimeInTruck
You might then consider making the function an inline function (put the
select inline - "return select ..."). The optimizer seems to like that
better than procedural code.
Good luck.
Payson
Amy Bolden wrote:
> Sorry, I thought maybe an explanation would be enough.
> Here is the top query in the stored procedure. The @.StartDate and
> @.EndDate are
> parameters that are supplied by a web application:
> SELECT DISTINCT(CONVERT(VARCHAR(10), TT.DateTS, 101)) AS ActivityDate,
> POSUM.UserName,
> TT.UserId,
> dbo.fnTimeInTruckInMinutes(TT.UserId,TT.DateTS) AS TimeInTruck
> FROM vw_TTLH TT INNER JOIN
> UserMaster POSUM ON TT.UserID = POSUM.UserNum
> WHERE DateTS BETWEEN @.StartDate AND @.EndDate)
> GROUP BY POSUM.UserName, TT.UserID, POSUM.UserNum,
> CONVERT(VARCHAR(10), TT.DateTS, 101), TT.DateTS
> Here is the function that calculates all the time spent in a truck for a
> particular day:
> CREATE FUNCTION dbo.fnTimeInTruckInMinutes (@.UserID int = NULL,
> @.ActivityDate DateTime = NULL)
> RETURNS int
> AS
> BEGIN
> DECLARE @.TotalTimeInTruck INT
>
> Exec spGetTotalTimeInTruck @.UserID, @.ActivityDate, @.TotalTimeInTruck
> RETURN @.TotalTimeInTruck
> END
> Here is the stored procedure that I am trying to call from the function
> to get the total time
> spent in a truck on a single day:
> CREATE PROCEDURE spGetTotalTimeInTruck
> @.UserID INT,
> @.ActivityDate VARCHAR(10),
> @.TotalTimeInTruck INT OUTPUT
> AS
> DECLARE @.ActivityDatePlusOne VARCHAR(10)
> SET @.ActivityDatePlusOne = DATEADD(d, 1, @.ActivityDate)
> CREATE TABLE #tempTruck
> (
> SeqNum int NULL,
> InTruck datetime NULL,
> OutTruck datetime NULL
> )
> INSERT INTO #tempTruck
> (OrderNumber, InTruck)
> SELECT OrderNumber, DateTS
> FROM vw_TTLH
> WHERE USerID = @.UserID
> AND DateTimeStamp >= @.ActivityDate
> AND DateTimeStamp <= @.ActivityDatePlusOne
> AND ActID in (200)
> UPDATE #tempTruck
> SET OutTruck = (
> SELECT DateTS
> FROM vw_TTLH
> WHERE USerID = @.UserID
> AND DateTimeStamp >= @.ActivityDate
> AND DateTimeStamp <= @.ActivityDatePlusOne
> AND OrderNumber = #tempTruck.OrderNumber + 1)
> SELECT SUM(DateDiff(n, InTruck, OutTruck)) FROM #tempTruck
> DROP TABLE #tempTruck
> RETURN @.TotalTimeInTruck
> Thanks,
> Amy Bolden
> *** Sent via Developersdex http://www.examnotes.net ***|||Hi
I think if you omitt using of CURSUR in your Function then it will be some
more faster. If you are using cursor for itration purpose than I have an Ide
a
that may help you.
There is no concept of Arrays in SQL Server I think so, but we can create
our own Psedu Arrays, and we can itrate in these arrays.
Define a local variable as varchar and insert the Primary key in the varable
COMMA seprated.
And then by a while loop get the ID and select the field(s) you want from
the orignal table and colculate it.
Run This code It may open your Mind
Declare @.var varchar(100)
SET @.var = '120,20,23,32,23234,,3,5,6,'
WHILE @.var <> ''
BEGIN
Declare @.id int
SET @.id = CAST(SUBSTRING(@.var,0, CharIndex(',', @.var,0)) as int)
SET @.var = SUBSTRING(@.var, CharIndex(',', @.var,0)+1, LEN(@.var))
PRINT @.id
END
________________________________________
__________________
"Amy" wrote:

> Hi,
> I need to call a function in a sql query in a stored procedure to
> calculate time differences between various dates. I have a function
> that uses a cursor to sum up the totals of these numbers, but it runs
> very, very slowly. I can accomplish the same results without a cursor
> by using a temporary table and several queries, but when I try to put
> this in a stored procedure and call the stored procedure from the
> function, I get the following error:
> Only functions and extended stored procedures can be executed from
> within a function.
> Any suggestions?
> Thanks,
> Amy Bolden
>

Question about datetime field

Dear all,
I am quite confused when dealing with the date and time in SQL tables.
e.g. I have a table myTable and there is a field myDate. The type of myDate
is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
in SQL Query Analyzer.
However, if I want to choose the records by the following SQL statement:
select * from myTable where myDate < getdate() - 1
Current date is Oct 25, therefore getdate() - 1 should retrieve the records
with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But i
t
also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know th
e
time factor also counts. But I don't want this to be counted. I am quite
confused. Any suggestion to get rid of the time factor?
Thanks a million.
IvanOne method to remove the time portion is with CONVERT. For example:
SELECT *
FROM myTable
WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <Ivan@.discussions.microsoft.com> wrote in message
news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
> Dear all,
> I am quite confused when dealing with the date and time in SQL tables.
> e.g. I have a table myTable and there is a field myDate. The type of
> myDate
> is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
> in SQL Query Analyzer.
> However, if I want to choose the records by the following SQL statement:
> select * from myTable where myDate < getdate() - 1
> Current date is Oct 25, therefore getdate() - 1 should retrieve the
> records
> with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But
> it
> also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know
> the
> time factor also counts. But I don't want this to be counted. I am quite
> confused. Any suggestion to get rid of the time factor?
> Thanks a million.
> Ivan|||Dear Dan,
Thanks for your help! It's very helpful.
Ivan
"Dan Guzman" wrote:

> One method to remove the time portion is with CONVERT. For example:
> SELECT *
> FROM myTable
> WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <Ivan@.discussions.microsoft.com> wrote in message
> news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
>
>

Question about datetime field

Dear all,
I am quite confused when dealing with the date and time in SQL tables.
e.g. I have a table myTable and there is a field myDate. The type of myDate
is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
in SQL Query Analyzer.
However, if I want to choose the records by the following SQL statement:
select * from myTable where myDate < getdate() - 1
Current date is Oct 25, therefore getdate() - 1 should retrieve the records
with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But it
also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know the
time factor also counts. But I don't want this to be counted. I am quite
confused. Any suggestion to get rid of the time factor?
Thanks a million.
IvanOne method to remove the time portion is with CONVERT. For example:
SELECT *
FROM myTable
WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <Ivan@.discussions.microsoft.com> wrote in message
news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
> Dear all,
> I am quite confused when dealing with the date and time in SQL tables.
> e.g. I have a table myTable and there is a field myDate. The type of
> myDate
> is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
> in SQL Query Analyzer.
> However, if I want to choose the records by the following SQL statement:
> select * from myTable where myDate < getdate() - 1
> Current date is Oct 25, therefore getdate() - 1 should retrieve the
> records
> with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But
> it
> also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know
> the
> time factor also counts. But I don't want this to be counted. I am quite
> confused. Any suggestion to get rid of the time factor?
> Thanks a million.
> Ivan|||Dear Dan,
Thanks for your help! It's very helpful.
Ivan
"Dan Guzman" wrote:
> One method to remove the time portion is with CONVERT. For example:
> SELECT *
> FROM myTable
> WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <Ivan@.discussions.microsoft.com> wrote in message
> news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
> > Dear all,
> >
> > I am quite confused when dealing with the date and time in SQL tables.
> >
> > e.g. I have a table myTable and there is a field myDate. The type of
> > myDate
> > is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
> > in SQL Query Analyzer.
> > However, if I want to choose the records by the following SQL statement:
> >
> > select * from myTable where myDate < getdate() - 1
> >
> > Current date is Oct 25, therefore getdate() - 1 should retrieve the
> > records
> > with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But
> > it
> > also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know
> > the
> > time factor also counts. But I don't want this to be counted. I am quite
> > confused. Any suggestion to get rid of the time factor?
> > Thanks a million.
> >
> > Ivan
>
>

Question about datetime field

Dear all,
I am quite confused when dealing with the date and time in SQL tables.
e.g. I have a table myTable and there is a field myDate. The type of myDate
is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
in SQL Query Analyzer.
However, if I want to choose the records by the following SQL statement:
select * from myTable where myDate < getdate() - 1
Current date is Oct 25, therefore getdate() - 1 should retrieve the records
with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But it
also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know the
time factor also counts. But I don't want this to be counted. I am quite
confused. Any suggestion to get rid of the time factor?
Thanks a million.
Ivan
One method to remove the time portion is with CONVERT. For example:
SELECT *
FROM myTable
WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <Ivan@.discussions.microsoft.com> wrote in message
news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
> Dear all,
> I am quite confused when dealing with the date and time in SQL tables.
> e.g. I have a table myTable and there is a field myDate. The type of
> myDate
> is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
> in SQL Query Analyzer.
> However, if I want to choose the records by the following SQL statement:
> select * from myTable where myDate < getdate() - 1
> Current date is Oct 25, therefore getdate() - 1 should retrieve the
> records
> with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But
> it
> also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know
> the
> time factor also counts. But I don't want this to be counted. I am quite
> confused. Any suggestion to get rid of the time factor?
> Thanks a million.
> Ivan
|||Dear Dan,
Thanks for your help! It's very helpful.
Ivan
"Dan Guzman" wrote:

> One method to remove the time portion is with CONVERT. For example:
> SELECT *
> FROM myTable
> WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <Ivan@.discussions.microsoft.com> wrote in message
> news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
>
>

Question about converting bigint field to int field

We made a poor decision a long time ago when designing our database
structure. We used bigint data types as the identity keys for many of
our base tables. For many reasons I would like to change these fields
to int at the largest. The largest data in these fields is around
200,000. I know that int can easily store this.

What should I be worried about when changing these fields from bigint
to int? If anything. Your help is appreciated. I did several
searches without much luck.I think you've covered the one biggy - make sure your existing data will
fit!

Others...

a) Make sure anything you join with are the same type, basically make
sure you change it everywhere including your foreign keys table.
b) Remember to do the stored procedures, udfs, triggers that may use them
as parameter.
c) You'll need to drop any constraints on your column definied with the
identity property, see example problem...

drop table t

create table t (
mycol bigint identity primary key,
t char(1) )

insert t ( t) values( 'a' )

alter table t alter column mycol int not null

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

<mamorgan1@.gmail.com> wrote in message
news:1137768775.133857.115810@.g44g2000cwa.googlegr oups.com...
> We made a poor decision a long time ago when designing our database
> structure. We used bigint data types as the identity keys for many of
> our base tables. For many reasons I would like to change these fields
> to int at the largest. The largest data in these fields is around
> 200,000. I know that int can easily store this.
> What should I be worried about when changing these fields from bigint
> to int? If anything. Your help is appreciated. I did several
> searches without much luck.|||Just curious... What problems are there with having bigint as an
identity column?|||There aren't any problems - it works just fine.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"pb648174" <google@.webpaul.net> wrote in message
news:1138052526.544803.80680@.z14g2000cwz.googlegro ups.com...
> Just curious... What problems are there with having bigint as an
> identity column?|||I think its because
Bigint takes 8 bytes storage and Int takes 4 bytes.
SQL Server will not automatically promote other integer data types
(tinyint, smallint, and int) to bigint.

Regards
Amish Shah|||> What problems are there with having bigint as an identity column?

extra storage space causing slower performance of everything

Question about "The inside of the 'UDF' function"

Hi

I know that when i call the store procedure in the sql server 2000, it will take a long time.
But after, if i call the store procedure again, it will run faster than before.

As i know:
Stored procedures are more efficient in part because the procedure is stored in SQL Server when it is created. Therefore, the content in the procedure runs at the server when the stored procedure is executed. A complex Transact-SQL script contained in a stored procedure is called by a single Transact-SQL statement, rather than by sending hundreds of commands over the network.

Before a stored procedure is created, the command syntax is checked for accuracy. If no errors are returned, the procedure's name is stored in the SysObjects table and the procedure's text is stored in the SysComments table. The first time the stored procedure is run, an execution plan is created and the stored procedure is compiled. Subsequent processing of the compiled stored procedure is faster because SQL Server does not recheck command syntax, re-create an execution plan, or recompile the procedure. The cache is checked first for an execution plan before a new plan is created.

My question is that whether the UDF function is the same as the store procedure ?

Can you please narrow down your question a bit. What product are you trying to use? What version of the product?

Is this question for SQL Server Analysis Services?

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward Melomed:

sql 2000 any version

Not for Server Analysis Services

And i found the answer on www.sql-server-performance.com

That's the answer i think:

///////////////////////////////////////////////////////////////////////////////////////////////////////////////

Question
I work in a SQL Server 2000 environment where we have literally hundreds of stored procedures in one database that are utilized for an OLAP application, and likewise for an OLTP application.

I have seen in many instances where a stored procedure is used solely because one can pass parameters to it and use these parameters in the WHERE portion of a SELECT clause. These stored procedures do nothing more than return a result set.

I was also told that SQL Server has a FIFO queue where only a certain number of pre-compiled stored procedures reside. Furthermore, I was also told that I should consider using user-defined functions that does its SELECT from a view(s) and uses any parameters passed in as delimiters in the WHERE portion of the SELECT clause.

Since functions are not pre-compiled but stored procedures are, is there any performance gain from using user-defined functions with views as opposed to stored procedures?

Answer
Before SQL Server 2000, user-defined functions were unavailable. Because of this, stored procedures were often the only way to emulate what a user-defined function can do now. So the question becomes, are there any advantages of converting my current stored procedures to user-defined functions?

I want to break the answer for this question into two parts: performance and convenience issues. Let's start with performance first.

For the most part, rewriting stored procedures as functions will not give you any performance benefits, and quite possibly, they may cause a performance hit because of the extra overhead they incur as compared to stored procedures. The amount of the performance hit, if any, will depend on how the function is written and what it is doing. Keep in mind that user-defined functions are also pre-optimized and compiled similarly to stored procedures (unlike what you have been told). Even so, they have more overhead than corresponding stored procedures.

From a convenience standpoint, there are some reasons to rewrite some stored procedures as user-defined functions, assuming performance is not a major issue. Some of these include:

The ability for a user-defined function to act like a table gives developers the ability to break out complex logic into shorter code blocks. This will generally provides the additional benefit of making the code less complex, and easier to write and maintain.

If you want to be able to invoke a stored procedure directly from within a query, then rewriting a stored procedure as a user-defined function would be worthwhile.

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.