Chosen datatype for Primary key field and performance questions?

Hi there,

I have been hired for a couple of weeks to investigate the performance of a sql server 2000 system.

One of the things that strikes me is that all the Primary key (identity field) fileds uses an decimal(18,0) as it's datatype.

An decimal with a precision of 18,0 takes 9 bytes for each column, while an int takes only 4 bytes and and bigint 8 bytes.

Many tables aren't that big, so the values will fit in an int datatype.

1. Is iot a good option to change the decimals columns to an int column ?

2. Many of these columns are indexed by a clustered index. Can the decimal datatype be a performance issue ?

3. sometimes they have deadlocks due page splits. Can this by reduced by changing the data types, while more data fit's into an page?

Thanks in advance,

Greetz,

Patrick de Jong

To me decimal (18,0) does not make sense at all... if at all u need to store larger number u could have gone for BigInt. Ofcourse the index size increases. To reduce the page split the change of datatype may not be sufficient. you many need to re-look your fillfactor for the index.

Madhu

|||

Using int for an indentity column is the most common solution, but just be aware of the roughly 2.1 billion upper limit for that data type. If 2.1 billion is not large enough, most people use bigint.

Changing your fillfactor can help minimize page splits, at the cost of making your index larger. Your deadlocks are probably not caused by page splits. Its more likely that they are caused by lack of, or improper indexes. You might try running this query to see if you are seeing any blocking:

-- Detect blocking

SELECT blocked_query.session_id AS blocked_session_id,

blocking_query.session_id AS blocking_session_id,

sql_text.text AS blocked_text,

sql_btext.text AS blocking_text,

waits.wait_type AS blocking_resource

FROM sys.dm_exec_requests AS blocked_query

INNER JOIN sys.dm_exec_requests AS blocking_query

ON blocked_query.blocking_session_id = blocking_query.session_id

CROSS APPLY

(SELECT *

FROM sys.dm_exec_sql_text(blocking_query.sql_handle)

) sql_btext

CROSS APPLY

(SELECT *

FROM sys.dm_exec_sql_text(blocked_query.sql_handle)

) sql_text

INNER JOIN sys.dm_os_waiting_tasks AS waits

ON waits.session_id = blocking_query.session_id

choosing the right datatype

It was hard to tell if this was the correct forum, but here goes:
My browser-driven Intranet App has 3 fields where users can type in a very
long description. It has a size of 5000, and is a varchar. People will
typically type in alphanumeric characters in it. (For the web-savvy, the
users types into a Textarea).
A co-worker tells me I should change the datatype from varchar to text. I
had it at varchar specifically because I understood that varchar will only
take up what disc space that it needs to. I asked him for an explanation of
why to go to text. He replied:
"There is an 8k limitation on a row when you use standard datatypes. You
have 3 columns with 5000 characters which pretty much doubles the amount of
allowed storage in a row. I'm surprised you haven't run into any problems.
Text datatypes are stored separately and allow up to 2GB."
Is this correct? Under my described scenario, which would be best?I would stick with Varchar. Varchar can hold up to 8,000 characters.
You're not storing more than 8,000 characters, so I don't see the point of
switching to a Text field. Also, if you use a text field, unless you
specify that you're storing the data "in row", it will only store a pointer
to the separate page or pages to access your string. You also can't
directly reference a text column in a WHERE clause. Unless you're storing
unusually large amount of characters (defined as 8,001+ characters) per row
in this column there's no real point to going with text.
"middletree" <middletree@.htomail.com> wrote in message
news:eDwvTxXKFHA.2688@.TK2MSFTNGP15.phx.gbl...
> It was hard to tell if this was the correct forum, but here goes:
> My browser-driven Intranet App has 3 fields where users can type in a very
> long description. It has a size of 5000, and is a varchar. People will
> typically type in alphanumeric characters in it. (For the web-savvy, the
> users types into a Textarea).
> A co-worker tells me I should change the datatype from varchar to text. I
> had it at varchar specifically because I understood that varchar will only
> take up what disc space that it needs to. I asked him for an explanation
> of
> why to go to text. He replied:
> "There is an 8k limitation on a row when you use standard datatypes. You
> have 3 columns with 5000 characters which pretty much doubles the amount
> of
> allowed storage in a row. I'm surprised you haven't run into any
> problems.
> Text datatypes are stored separately and allow up to 2GB."
> Is this correct? Under my described scenario, which would be best?
>|||He is right, but the limit for a row is 8060 and not 8k.
Example:
use northwind
go
create table t (
colA varchar(8000),
colB varchar(8000),
colC varchar(8000)
)
go
insert into t values(replicate('a', 8000), replicate('b', 8000),
replicate('c', 8000))
go
drop table t
go
Result:
Server: Msg 511, Level 16, State 1, Line 2
Cannot create a row of size 24015 which is greater than the allowable
maximum of 8060.
The statement has been terminated.
AMB
"middletree" wrote:

> It was hard to tell if this was the correct forum, but here goes:
> My browser-driven Intranet App has 3 fields where users can type in a very
> long description. It has a size of 5000, and is a varchar. People will
> typically type in alphanumeric characters in it. (For the web-savvy, the
> users types into a Textarea).
> A co-worker tells me I should change the datatype from varchar to text. I
> had it at varchar specifically because I understood that varchar will only
> take up what disc space that it needs to. I asked him for an explanation o
f
> why to go to text. He replied:
> "There is an 8k limitation on a row when you use standard datatypes. You
> have 3 columns with 5000 characters which pretty much doubles the amount o
f
> allowed storage in a row. I'm surprised you haven't run into any problems
.
> Text datatypes are stored separately and allow up to 2GB."
> Is this correct? Under my described scenario, which would be best?
>
>|||It is, generally, correct. However, there are perfomance and usage penaltie
s
for using Text datatype, that make it worthwhile t oavoid them if possible..
.
What are the three fields used for? An Alternative which MAY be worth
investigating, is putting these three fields in another table...
Assuming your existing Table has a PK called PKID,
Create Table Comments
(PKID Integer Not Null
WhichField TinyInt Not Null,
Description VarChar(5000),
CONSTRAINT Comment_PK PRIMARY KEY (PKId, WhichField)
)
-- (The WHichField column identifies which one of the web page's Description
fields this is for... 1,2, or 3)
"middletree" wrote:

> It was hard to tell if this was the correct forum, but here goes:
> My browser-driven Intranet App has 3 fields where users can type in a very
> long description. It has a size of 5000, and is a varchar. People will
> typically type in alphanumeric characters in it. (For the web-savvy, the
> users types into a Textarea).
> A co-worker tells me I should change the datatype from varchar to text. I
> had it at varchar specifically because I understood that varchar will only
> take up what disc space that it needs to. I asked him for an explanation o
f
> why to go to text. He replied:
> "There is an 8k limitation on a row when you use standard datatypes. You
> have 3 columns with 5000 characters which pretty much doubles the amount o
f
> allowed storage in a row. I'm surprised you haven't run into any problems
.
> Text datatypes are stored separately and allow up to 2GB."
> Is this correct? Under my described scenario, which would be best?
>
>|||Hey, 'tree. If it matters, "text" columns can be a pain to deal with in DW,
depending on what you're trying to do of course. The default recordset
options don't always work, you've got to watch your field order, etc.
I know it's not strictly database relevant, but I know you use DW, so it
might be relevant for you.
"middletree" <middletree@.htomail.com> wrote in message
news:eDwvTxXKFHA.2688@.TK2MSFTNGP15.phx.gbl...
> It was hard to tell if this was the correct forum, but here goes:
> My browser-driven Intranet App has 3 fields where users can type in a very
> long description. It has a size of 5000, and is a varchar. People will
> typically type in alphanumeric characters in it. (For the web-savvy, the
> users types into a Textarea).
> A co-worker tells me I should change the datatype from varchar to text. I
> had it at varchar specifically because I understood that varchar will only
> take up what disc space that it needs to. I asked him for an explanation
of
> why to go to text. He replied:
> "There is an 8k limitation on a row when you use standard datatypes. You
> have 3 columns with 5000 characters which pretty much doubles the amount
of
> allowed storage in a row. I'm surprised you haven't run into any
problems.
> Text datatypes are stored separately and allow up to 2GB."
> Is this correct? Under my described scenario, which would be best?
>|||Actually, I own DW, but tend to hand-code about 95% of my stuff. I hang out
at the DW forum to ask and answer questions about code, not DW. And to
discuss various theological and political topics ;)
Hope you haven't left the DW forums for good.
"CMBergin" <NoHarvestForYou@.NoSpam.org> wrote in message
news:OX2lEUYKFHA.436@.TK2MSFTNGP09.phx.gbl...
> Hey, 'tree. If it matters, "text" columns can be a pain to deal with in
DW,
> depending on what you're trying to do of course. The default recordset
> options don't always work, you've got to watch your field order, etc.
> I know it's not strictly database relevant, but I know you use DW, so it
> might be relevant for you.
> "middletree" <middletree@.htomail.com> wrote in message
> news:eDwvTxXKFHA.2688@.TK2MSFTNGP15.phx.gbl...
very
I
only
> of
You
> of
> problems.
>|||For a while, yes. Probably not for good though.
I'd elaborate, but the "on-topic" rules here seem quite a bit stricter.
"middletree" <middletree@.htomail.com> wrote in message
news:%23cpX5hZKFHA.572@.tk2msftngp13.phx.gbl...
> Actually, I own DW, but tend to hand-code about 95% of my stuff. I hang
out
> at the DW forum to ask and answer questions about code, not DW. And to
> discuss various theological and political topics ;)
> Hope you haven't left the DW forums for good.
>
> "CMBergin" <NoHarvestForYou@.NoSpam.org> wrote in message
> news:OX2lEUYKFHA.436@.TK2MSFTNGP09.phx.gbl...
> DW,
> very
the
text.
> I
> only
explanation
> You
amount
>

Choosing Data Type

Hi ... I have question on datatype on SQL Server 2005 EE

What is a good data type for email, password, Phone Number and ISBN number?

Thanks!I'd use varchar for all of those.|||Thanks for the reply. How about phone number? isn't that suppose to beNumeric? Or Numeric just for something that is calculateable?|||

iloveny:

Thanks for the reply. How about phone number? isn't that suppose to beNumeric? Or Numeric just for something that is calculateable?

You've hit the nail on the head. Only use numeric data types for something that is calculateable. Phone numbers, ZIP codes, etc. should definitely be one of the string data types (I suggest varchar).

Checking the datatype of a columns

Hello,

I have the following sql statement

Code Snippet

UPDATE OtherCall SET [Date] = CONVERT(NVARCHAR(50),CONVERT(DATETIME,[Date],103),111)

ALTER TABLE OtherCall ALTER COLUMN [Date] DATETIME

I am converting a nvarchar to a datetime on one of the columns in the table. However, I don't want to execute this if the conversion has already executed.

I was thinking of having a if statement that if the column is not a datetime then alter the column.

I am unsure how to write the if statement to check for the data type of that column.

Many thanks for any help,

Steve

If you're intention is to ultimately convert the datatype, what's preventing you from initially setting the datatype as datetime?

I know this doesn't answer your question, but I'm curious.

Adamus

|||Hello,

The table was created initially with a nvarchar. This is a live database and the dates that have already been entered have to be formated in order for the alter column will work.

If the customer runs this script more than once, I don't want to have to execute the alter statement again.

Many thanks,

Steve
|||

There's really not a problem if that statement executes EVEN if the datatype has been previously changed.

However, if you need to:

Code Snippet

IF NOT EXISTS
( SELECT DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE ( TABLE_NAME = 'OtherCall'
AND COLUMN_NAME = 'Date'
AND DATA_TYPE = 'datetime'
)
)
BEGIN
UPDATE OtherCall

SET [Date] = convert( nvarchar(50), convert( datetime, [Date], 103), 111)

ALTER TABLE OtherCall ALTER COLUMN [Date] datetime

END

|||

Try the below SQL Statement, this is my version of solution there may be other ways to

' Returns row if the column is already converted to the required data type

IF EXISTS (select c.name, c.xtype from sysobjects o, syscolumns c where o.id = c.id and o.name = tablename and c.xtype= 61(xtype value of datatime datatype, can be found in systypes table in master database) and c.name =column name)
BEGIN
// The column is already converted to datatime format. In your case this step will be blank
END
ELSE

BEGIN

// The column is not in datatime datatype so we need to convert it. Below is your code for conversion

UPDATE OtherCall SET [Date] = CONVERT(NVARCHAR(50),CONVERT(DATETIME,[Date],103),111)

ALTER TABLE OtherCall ALTER COLUMN [Date] DATETIME

END

Checking datatypes of a field accoss multiple tables

I have a tables called subsid that I need to change the datatype from
text to int.

I think I got them all but is there a query I can run that will check
all fields call subsid accross all tables that are of type text.Try:

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
COLUMN_NAME = 'subsid' AND
DATA_TYPE = 'text'

--
Hope this helps.

Dan Guzman
SQL Server MVP

<tdmailbox@.yahoo.com> wrote in message
news:1113780531.744940.153260@.l41g2000cwc.googlegr oups.com...
>I have a tables called subsid that I need to change the datatype from
> text to int.
> I think I got them all but is there a query I can run that will check
> all fields call subsid accross all tables that are of type text.