DATETIME AS PrimaryKey? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

DATETIME AS PrimaryKey?

Author

Topic

majidbhutta
Starting Member

13 Posts

Posted - 2005-12-01 : 10:17:17

DATETIME AS PrimaryKey?
Hi Dears!
declare @dtpk smalldatetime
select @dtpk=getdate()
print @dtpk

in the above, i m using @dtpk as a primary key value in a
table to keep a track of some events that are occuring with
a difference of one second. the above method gives me value
to store in the Primary key Filed (EventTime smalldatetime)
in the form "Dec 1 2005 7:57PM" where my requirement is
to save it in the form "Dec 1 2005 7:57:12.03PM" so as to
make it unique for the primary key field(EventTime
smalldatetime). how to get required datetime format with
the help of above statements to store as primary key in
EventTime field of type smalldatetime.

THNX IN ADAVNCE

Kristen
Test

22859 Posts

Posted - 2005-12-01 : 10:26:21

Its storing it in the format of a "smalldatetime" datatype.

"Dec 1 2005 7:57PM" is just the format that is being used to display it back to you.

"keep a track of some events that are occurring with a difference of one second"

Smalldatetime only resolves to the minute, so you will need to use "datetime" instead to resolve to a second (actually datetime resolves to "one three-hundredth of a second")

Keep it in a datetime datatype, and don't try to store it as Character/String data, and you should be fine.

Kristen

majidbhutta
Starting Member

13 Posts

Posted - 2005-12-01 : 10:35:29

Hi Dear!

i m still having results in the previous format on my system
with ur suggestion i used
declare @dtpk datetime
select @dtpk=getdate()
print @dtpk

out put is "Dec 1 2005 8:26PM"
where as required ouput format is
"Dec 1 2005 8:26:32.12PM"

Kristen
Test

22859 Posts

Posted - 2005-12-01 : 10:39:14

As I said, the "human format" that the date is displayed is not how it is stored. A "datetime" datatype will store the date+time to the nearest 3.33 milliseconds

Make sure the PK column in your table is also datetime datatype (NOT smalldatetime and NOT any sort of character/string) and you should be "good to go" !

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2005-12-01 : 11:13:19

datetime as a PK? Just try and visualize what that's gonna do....

Can you tell us what other attributes your Entity Contains?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-12-01 : 11:52:38

quote:
datetime as a PK? Just try and visualize what that's gonna do....

Why am I being reminded of a particularly unpleasant piece of code?


-- of remaining ones, move earlier-transmitted results with same
-- HOSPITAL_NO, TEST_DATE, OBR, OBX back by successive seconds
UPDATE #HL7OBXContext
SET TEST_TIME = tt
FROM (
    SELECT A.ln AS ln, DATEADD(s, -COUNT(*), A.TEST_TIME) AS tt
    FROM #HL7OBXContext AS A
    INNER JOIN #HL7OBXContext AS B
    ON A.HOSPITAL_NO = B.HOSPITAL_NO AND A.TEST_TIME = B.TEST_TIME
      AND A.OBR_NAME = B.OBR_NAME AND A.OBX_NAME = B.OBX_NAME
      AND (A.SYS_DATE < B.SYS_DATE OR (A.SYS_DATE = B.SYS_DATE AND A.ln < B.ln))
    GROUP BY A.ln, A.TEST_TIME
  ) AS a
WHERE #HL7OBXContext.ln = a.ln

And before you ask, it was a conversion of an even more disgusting piece of Access VB code that had to be bug-compatible.

majidbhutta
Starting Member

13 Posts

Posted - 2005-12-01 : 12:30:35

its as whole a composite primary key
field are
EMP_Id int foreignkey
Shift_Id int foreignkey
dtpk datetime primarykey

all these three attribute taking part to make a primary key

X002548
Not Just a Number

15586 Posts

Posted - 2005-12-01 : 12:33:27

I'm still lost on the question. Please read the hint link in my sig and post some more details

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2005-12-01 : 12:37:13

"as a primary key value in a table to keep a track of some events that are occuring with a difference of one second"

Is that too awful Brett?

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2005-12-01 : 12:49:28

If it's like this:


CREATE TABLE myTable99(
	  EMP_Id      int
	, Shift_Id    int
	, dtpk        datetime
	, PRIMARY KEY (EMP_ID, Shift_Id, dtpk))

BUT!, if it's like this


CREATE TABLE myTable99(
	  EMP_Id      int
	, Shift_Id    int
	, dtpk        datetime
	, PRIMARY KEY (dtpk, EMP_ID, Shift_Id))

Then yes, that would be bad. All of your INSERT Will all be slamming on the same page. Your SELECTs, UPDATE and DELETES, if you are access the data by date range, will obtain lock escalation very rapidly...depending on the range of dates, I would imagine a table lock could easily be obtained

Now, If you would kindly read the hint link in my sig and post the details we would need to assist you...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2005-12-01 : 12:53:56

"All of your INSERT Will all be slamming on the same page"

In the interested of learned debate

... at "one per second" will anything really care?

You're making me really nervous about my logging table with is averaging 2.5 PER datetime resolution - that's 2.5 INSERTs PER 33ms ...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources