cidr, inet, and mac data types

I've tried searching around and haven't been able to determine if SQL Server 2005 supports cidr, inet, or mac address data types. I have a database in PostgreSQL that I'm working on moving to SQL Server 2005 due to business requirements and it uses these data types.

No. There isn't a native data for cidr. However, you can just use one of the native datatypes.

http://sqlserver2000.databases.aspfaq.com/how-should-i-store-an-ip-address-in-sql-server.html

Choosing the correct data types

In a typical "Order Item" table I need to store the selling price of
the item and the percentage discount applied. I'm thinking Numeric
for the price, and Float for the percentage.
Good design, or clueless?
Thanks
Edward
I would tend to opt for Numeric/Decimal for percentage as well. Why
introduce approximate numbers? Do you even need decimal places here?
(Typically discounts are 10%, 20% etc.)
<teddysnips@.hotmail.com> wrote in message
news:e4aa5937-58af-4be0-8db0-2b582c8fd7e3@.s36g2000prg.googlegroups.com...
> In a typical "Order Item" table I need to store the selling price of
> the item and the percentage discount applied. I'm thinking Numeric
> for the price, and Float for the percentage.
> Good design, or clueless?
> Thanks
> Edward
|||those will work. You could also consider using the money datatype for the
price.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
<teddysnips@.hotmail.com> wrote in message
news:e4aa5937-58af-4be0-8db0-2b582c8fd7e3@.s36g2000prg.googlegroups.com...
> In a typical "Order Item" table I need to store the selling price of
> the item and the percentage discount applied. I'm thinking Numeric
> for the price, and Float for the percentage.
> Good design, or clueless?
> Thanks
> Edward

Choosing the correct data types

In a typical "Order Item" table I need to store the selling price of
the item and the percentage discount applied. I'm thinking Numeric
for the price, and Float for the percentage.
Good design, or clueless?
Thanks
EdwardI would tend to opt for Numeric/Decimal for percentage as well. Why
introduce approximate numbers? Do you even need decimal places here?
(Typically discounts are 10%, 20% etc.)
<teddysnips@.hotmail.com> wrote in message
news:e4aa5937-58af-4be0-8db0-2b582c8fd7e3@.s36g2000prg.googlegroups.com...
> In a typical "Order Item" table I need to store the selling price of
> the item and the percentage discount applied. I'm thinking Numeric
> for the price, and Float for the percentage.
> Good design, or clueless?
> Thanks
> Edward|||those will work. You could also consider using the money datatype for the
price.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
<teddysnips@.hotmail.com> wrote in message
news:e4aa5937-58af-4be0-8db0-2b582c8fd7e3@.s36g2000prg.googlegroups.com...
> In a typical "Order Item" table I need to store the selling price of
> the item and the percentage discount applied. I'm thinking Numeric
> for the price, and Float for the percentage.
> Good design, or clueless?
> Thanks
> Edward

Choosing the correct data types

In a typical "Order Item" table I need to store the selling price of
the item and the percentage discount applied. I'm thinking Numeric
for the price, and Float for the percentage.
Good design, or clueless?
Thanks
EdwardI would tend to opt for Numeric/Decimal for percentage as well. Why
introduce approximate numbers? Do you even need decimal places here?
(Typically discounts are 10%, 20% etc.)
<teddysnips@.hotmail.com> wrote in message
news:e4aa5937-58af-4be0-8db0-2b582c8fd7e3@.s36g2000prg.googlegroups.com...
> In a typical "Order Item" table I need to store the selling price of
> the item and the percentage discount applied. I'm thinking Numeric
> for the price, and Float for the percentage.
> Good design, or clueless?
> Thanks
> Edward|||those will work. You could also consider using the money datatype for the
price.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
<teddysnips@.hotmail.com> wrote in message
news:e4aa5937-58af-4be0-8db0-2b582c8fd7e3@.s36g2000prg.googlegroups.com...
> In a typical "Order Item" table I need to store the selling price of
> the item and the percentage discount applied. I'm thinking Numeric
> for the price, and Float for the percentage.
> Good design, or clueless?
> Thanks
> Edward

choice of column for clustered index

I often founds 2 recommendations that sounds contradicting to me,
regarding what types of columns should be chosen for a clustered index
(in OLTP environment with lots of inserts):
1)avoid identity column, it will cause inserts to be slowed since they
will compete the same disk area at the end of the table (hot spot).
Instead, use a column whose new value can be at any part of the table
2)use sequential column (like identity one), so that new inserts only
happen at the end of the table and don't cause row migrations (when a
row inserted forces the next rows to move to a new page)
Can anyone give me a more sounding judgement of each of these 2
choices? I'm curious to know in which scenario, which choice is better
than the other, and what the cures are. Correct me if I'm wrong, I feel
that "row migration" is more fearful than "hot spot"
thanks,
TamUnless you're writing a million new rows a day, I doubt the hot spot concern
is valid on today's hardware. Obviously there will be a threshold but for
most applications I can envision this should be a minimal concern.
I have witnessed cases where fragmentation and page splitting, on the other
hand, has caused abysmal performance.
I can't really think of a situation where you'd rather jab new data in the
middle of a page than tack it on the end, unless you were just stuffing
every single transaction on a very busy system into an audit table that you
purge regularly and that you're rarely going to query, in which case, who
cares which way you go.
A
"Tam Vu" <vuht2000@.yahoo.com> wrote in message
news:1125521936.674430.273880@.g49g2000cwa.googlegroups.com...
>I often founds 2 recommendations that sounds contradicting to me,
> regarding what types of columns should be chosen for a clustered index
> (in OLTP environment with lots of inserts):
> 1)avoid identity column, it will cause inserts to be slowed since they
> will compete the same disk area at the end of the table (hot spot).
> Instead, use a column whose new value can be at any part of the table
> 2)use sequential column (like identity one), so that new inserts only
> happen at the end of the table and don't cause row migrations (when a
> row inserted forces the next rows to move to a new page)
> Can anyone give me a more sounding judgement of each of these 2
> choices? I'm curious to know in which scenario, which choice is better
> than the other, and what the cures are. Correct me if I'm wrong, I feel
> that "row migration" is more fearful than "hot spot"
> thanks,
> Tam
>|||See if this helps:
Tips on Optimizing SQL Server Clustered Indexes
http://www.sql-server-performance.c...red_indexes.asp
AMB
"Tam Vu" wrote:

> I often founds 2 recommendations that sounds contradicting to me,
> regarding what types of columns should be chosen for a clustered index
> (in OLTP environment with lots of inserts):
> 1)avoid identity column, it will cause inserts to be slowed since they
> will compete the same disk area at the end of the table (hot spot).
> Instead, use a column whose new value can be at any part of the table
> 2)use sequential column (like identity one), so that new inserts only
> happen at the end of the table and don't cause row migrations (when a
> row inserted forces the next rows to move to a new page)
> Can anyone give me a more sounding judgement of each of these 2
> choices? I'm curious to know in which scenario, which choice is better
> than the other, and what the cures are. Correct me if I'm wrong, I feel
> that "row migration" is more fearful than "hot spot"
> thanks,
> Tam
>|||> Unless you're writing a million new rows a day,
Wow, did I really say "day" there? Eep. In my experience, a hot spot comes
at a far greater volume than that. Then again, I have been spoiled with
must faster hardware than I had access to in college. :-)

chinese characters with Unicode data types?

Hi,
I read that Unicode data types (like nchar or nvarchar)
support any characters of any language in the world. As we
need a database that supports many different languages, I
have tried to paste chinese characters into one table
field - although the field's data type is nvarchar, it
pastes only squares (instead of the chinese characters)
Can someone please help?
Thanks in advance,
BodoOne possibility is that the data is actually stored correctly in the
database, but the query tool you are using hasn't been configured to use a
font that defines glyphs for Chinese characters. Try using Query Analyzer
to both insert and view the data (don't use the "Show All Rows" view in
Enterprise Manager as the controls used in this view aren't Unicode aware).
Go to Tools --> Options --> Fonts tab, and select a font that supports
Chinese like "SimSun" or "Arial Unicode MS" for the "Results Text" and
"Results Grid" views.
Bart
--
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
Content-Class: urn:content-classes:message
From: "bodo" <bodobecker@.hotmail.com>
Sender: "bodo" <bodobecker@.hotmail.com>
Subject: chinese characters with Unicode data types?
Date: Sat, 13 Sep 2003 09:11:57 -0700
Lines: 14
Message-ID: <000a01c37a11$c15c4bf0$a401280a@.phx.gbl>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
Thread-Index: AcN6EcFc9/ArRhOoTrW8Ejd9PK+XmQ==Newsgroups: microsoft.public.sqlserver.server
Path: cpmsftngxa06.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:306274
NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
X-Tomcat-NG: microsoft.public.sqlserver.server
Hi,
I read that Unicode data types (like nchar or nvarchar)
support any characters of any language in the world. As we
need a database that supports many different languages, I
have tried to paste chinese characters into one table
field - although the field's data type is nvarchar, it
pastes only squares (instead of the chinese characters)
Can someone please help?
Thanks in advance,
Bodo

checksums and data types

I'm creating a checksum column in a table that is to be calculated over several columns within the table.

One of the columns to be included in the checksum formula has a data type ntext.

But on trying to complete this new table design (or similarly using alter table in QA) - both return an error stating that the data type is invalid for the checksum function.

This happens for both ntext and text data types.

Can anyone tell me if there is a way round this without having to change the data type - or the valid data types that can be used for the checksum funciton?

Also reasons why would be helpful!

Thanksplease ignore - http://www.dbforums.com/t989557.html shows this not to be possible...

nevermind|||you might want to try something like so:

SELECT checksum(col1,col2,CAST(CAST(col3 as varchar(1)) as int))
FROM testTable

I am not sure if this totally works. Text and Ntext are meant to hold large amounts of text data like notes field in a customer service application. There is no implicit data conversion between int and ntext\text in sql server because that is just one of the rules and it would'nt make much since do so. To tell the truth it sounds like your problem is a design issue. However you can explicitly convert data types as shown above but please keep in mind if you try to cast character data in col3 above to an int, you will recieve an error. So you might have to add an IsNumeric in there as well.|||CHECKSUM works with non-numeric data, so there is no need to recast as INT in your formula.

Though I'm still not sure that is going to give him what he needs...|||Why are you doing this? To enforce data integrity upon INSERT/UPDATE? Or to support some business rule? Either way you're already using a database, so the answer should be in design, not checksum-based tricks.|||hey trotsky!!
calm down.|||I'd love to hear the reason behind this... I can't for the life of me figure out why you might want/need to do it. I'm also with rdjabarov, and think that this smells very strongly of a high GQ (geek quotient) workaround for a case of poor relational design!

-PatP|||that's twice that you have agreed with RDjabarov.
hmmmmmmm is the feud over?

:D|||Feud? Did I miss a meeting?

-PatP|||must have been the coma. when i first go here you guys would go at it like turtles and bunnies.

checking the existance of fields and types

I'm trying to write a program in cold fusion to check the existance of
fields and data types according to requirements

I was looking at the syscolumns table for some of this information but
I've discovered that in my playing (creating and deleting tables) thre
are multiple entries for a field that is in table that is created then
deleted then created again.

Is this a problem? Is there a better way to get at the information ie
the table, field, and type exist in a database?Use the INFORMATION_SCHEMA views. See Books Online for details.

Most metadata can be retrieved more easily from the info schema than
from system tables. There are some exceptions but system tables are
best left alone unless you really have to use them. They will be
supported only for backwards compatibility and won't reflect new
features in future versions.

--
David Portas
SQL Server MVP
--|||William Kossack (kossackw@.njc.org) writes:
> I'm trying to write a program in cold fusion to check the existance of
> fields and data types according to requirements
> I was looking at the syscolumns table for some of this information but
> I've discovered that in my playing (creating and deleting tables) thre
> are multiple entries for a field that is in table that is created then
> deleted then created again.

This sounds very strange. My guess is that you are joining syscolumns
with systypes incorrectly. (Those two tables are indeed a bit tricky
to match up.) Care to post a query that gives funny result?

> Is this a problem? Is there a better way to get at the information ie
> the table, field, and type exist in a database?

Some people tout the INFORMATION_SCHEMA views, but since they only
give a subset of the metadata information, they're pretty useless in
my opinion. They are mainly interesting if you want to write portable
meta-data queries.

There are also functions like object_id, columnproperty which can be
useful at times, but they have the drawback that they are restricted
to the current database.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp