Datatype for Primary key fields ...

Hi,
As far as my understanding goes, normally PK would be set on fields whose
datatype is INT. But in one of the project I saw 99% of the tables they have
used Varchar datatype for PK fields.
This internally means that it would string comparisons. I was arguing that
SQL server isn't good at 'String comparisons'. Am I right? or Am i missing
something? Any pointers on this topic would be of great help to me.
Best Regards
Vadivel
http://vadivel.blogspot.comVadivel wrote:

> Hi,
> As far as my understanding goes, normally PK would be set on fields whose
> datatype is INT. But in one of the project I saw 99% of the tables they ha
ve
> used Varchar datatype for PK fields.
> This internally means that it would string comparisons. I was arguing that
> SQL server isn't good at 'String comparisons'. Am I right? or Am i missing
> something? Any pointers on this topic would be of great help to me.
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
This is the wrong question because the datatype for keys is determined
by the data you need to model in the table. For example how are you
going to represent names using an INTEGER?
If you meant to ask "what should I use for an artiificial surrogate
key?" then you can search the archives of this group for many previous
discussions on that topic.
David Portas
SQL Server MVP
--|||"Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
news:F2B2A710-79AB-460F-8057-46B4A0166183@.microsoft.com...
> Hi,
> As far as my understanding goes, normally PK would be set on fields whose
> datatype is INT. But in one of the project I saw 99% of the tables they
> have
> used Varchar datatype for PK fields.
> This internally means that it would string comparisons. I was arguing that
> SQL server isn't good at 'String comparisons'. Am I right? or Am i missing
> something? Any pointers on this topic would be of great help to me.
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
If you don't use surrogates, I'd say that most natural primary keys are
strings.
Even telephone numbers, license plate numbers, zip codes, serial codes,
credit card numbers, bar codes, invoice numbers, bank account numbers... are
in reality, strings.|||Are you speaking of a natural or surrogate primary key?
http://www.aspfaq.com/show.asp?id=2504
Natural keys can be a combination of most any data type. However, if the key
in it's basic form is numeric (such as SSN, CustomerNo, or PhoneNumber) then
do try to define it as integer, becuase it's storage will be smaller than
character, and it will thus result in less memory usage and fewer index
pages to traverse.
If you plan to use an identity column as a surrogate primary key, then I
don't see a reason to use anything but an integer.
http://www.windowsitpro.com/Article...ArticleID=23449
Data Type Performance Tuning Tips for Microsoft SQL Server
http://www.sql-server-performance.com/datatypes.asp
In general integer based comparisons are more efficient than Char or VarChar
comparisons, but I don't know of SQL Server specifically not being not good
at character comparisons relative to any other DBMS system such as Oracle or
DB2.
"Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
news:F2B2A710-79AB-460F-8057-46B4A0166183@.microsoft.com...
> Hi,
> As far as my understanding goes, normally PK would be set on fields whose
> datatype is INT. But in one of the project I saw 99% of the tables they
> have
> used Varchar datatype for PK fields.
> This internally means that it would string comparisons. I was arguing that
> SQL server isn't good at 'String comparisons'. Am I right? or Am i missing
> something? Any pointers on this topic would be of great help to me.
> Best Regards
> Vadivel
> http://vadivel.blogspot.com|||I know that telephone numbers, Credit card nos, Zipcode all would be varchar
fields only. As we won't be doing any mathematical calculation based on that
data there isn't a need for us to go for INT datatype. Even though u would
have those fields as Varchar in ur DB won't you have a ID field in that
table? Won't that ID field be of INT datatype?
Best Regards
Vadivel
http://vadivel.blogspot.com
"Raymond D'Anjou" wrote:

> "Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
> news:F2B2A710-79AB-460F-8057-46B4A0166183@.microsoft.com...
> If you don't use surrogates, I'd say that most natural primary keys are
> strings.
> Even telephone numbers, license plate numbers, zip codes, serial codes,
> credit card numbers, bar codes, invoice numbers, bank account numbers... a
re
> in reality, strings.
>
>|||"Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
news:1CCC4883-5193-4706-9F62-CBEECE8FBF4C@.microsoft.com...
>I know that telephone numbers, Credit card nos, Zipcode all would be
>varchar
> fields only. As we won't be doing any mathematical calculation based on
> that
> data there isn't a need for us to go for INT datatype. Even though u would
> have those fields as Varchar in ur DB won't you have a ID field in that
> table? Won't that ID field be of INT datatype?
> Best Regards
> Vadivel
Read JTs response including the links.
Your ID field (column) is a surrogate.
The use of surrogates (including Identity) has been discussed in this
newsgroup "ad nauseum".
Even though you can use surrogates as keys, there should always exist a
"natural" primary key in your tables.|||"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:u8lYzCMAGHA.3936@.TK2MSFTNGP12.phx.gbl...
> "Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
> news:1CCC4883-5193-4706-9F62-CBEECE8FBF4C@.microsoft.com...
> Read JTs response including the links.
> Your ID field (column) is a surrogate.
> The use of surrogates (including Identity) has been discussed in this
> newsgroup "ad nauseum".
> Even though you can use surrogates as keys, there should always exist a
> "natural" primary key in your tables.
One other thing:
Concerning your "performance hit" using INTs versus VARCHARs.
From what I've read, especially in today's databases, if there is one it's
negligable.|||Thanks for the links JT.
Lets assume that I have a table with just two fields,
CityID Varchar(20) -- PK
CityName Varchar(50)
Now I would be using this field CityID in some other table as a FK field. Is
that ok? Or is it advisable / mandatory to have another column with INT
datatype and use it in other tbls as FK?
Best Regards
Vadivel
http://vadivel.blogspot.com
"JT" wrote:

> Are you speaking of a natural or surrogate primary key?
> http://www.aspfaq.com/show.asp?id=2504
> Natural keys can be a combination of most any data type. However, if the k
ey
> in it's basic form is numeric (such as SSN, CustomerNo, or PhoneNumber) th
en
> do try to define it as integer, becuase it's storage will be smaller than
> character, and it will thus result in less memory usage and fewer index
> pages to traverse.
> If you plan to use an identity column as a surrogate primary key, then I
> don't see a reason to use anything but an integer.
> http://www.windowsitpro.com/Article...ArticleID=23449
> Data Type Performance Tuning Tips for Microsoft SQL Server
> http://www.sql-server-performance.com/datatypes.asp
> In general integer based comparisons are more efficient than Char or VarCh
ar
> comparisons, but I don't know of SQL Server specifically not being not goo
d
> at character comparisons relative to any other DBMS system such as Oracle
or
> DB2.
>
> "Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
> news:F2B2A710-79AB-460F-8057-46B4A0166183@.microsoft.com...
>
>|||If CityID is the primary key, then that is the column you want to use as the
foreign key when joining referencing tables. However, I don't understand why
CityID would be 20 characters long. Is this something like an ISO code
assigned to every city on the planet? If CityID contains embedded attributes
like geographic coordinates or nation, state, county codes, then split those
attributes out as seperate columns.
Provide more details about what CityID means and how it's values are
assigned.
"Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
news:67DAA04A-A277-49AB-8745-521699D2F06F@.microsoft.com...
> Thanks for the links JT.
> Lets assume that I have a table with just two fields,
> CityID Varchar(20) -- PK
> CityName Varchar(50)
> Now I would be using this field CityID in some other table as a FK field.
> Is
> that ok? Or is it advisable / mandatory to have another column with INT
> datatype and use it in other tbls as FK?
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
>
> "JT" wrote:
>

DataType converting, char to float

I need to convert Data Types in a table where all fields are char, so that the ones containing numeric values is decimal or float.
I've tried this query:
SELECT Ansilumens, CAST(RTRIM(Ansilumens) AS FLOAT) FROM dbo.projector
And I get the error: [Microsoft] [ODBC SQL Server Driver] [SQL Server] Error converting data type varchar to float.

Does anyone how I can convert chars to decimals or floats? When the tabel already contains lots of data, without loosing it..

Thanks.--Find bad values
select YourCol
from YourTable
where isnumeric(YourCol)=0

/*
Then choose, what numeric type do you want.
Float is usually used in science for storing inaccurate very high range values. Stored as single/double real binary.
Int-like datatype for integers (without decimal places). Stored as sign fixed binary.
Numeric for precise calculations, large nums (10^36), fixed decimals. Stored as sign nibble (2 decimal digits in one byte)
rounded up to 1+4n bytes.
Money is fast predefined numeric with special rounding and 4 decimal places. Stored as sign fixed binary.
*/

--if your nums are really large, try FLOAT(53) or NUMERIC(38) or ballanced NUMERIC(32,16)

--Sending test values would be your benefit.|||If you plan on using numeric or decimal data types you really have to know what is the largest numeric value you will use - not just to the left of the decimal but to the right as well (your precision and scale) otherwise you will receive an arithmetic overflow error. Float has the same issues with precision.

The following post discusses this problem:

post (http://dbforums.com/showthread.php?threadid=554550)|||Well, my problem (after some more testing) seems that everything I try to insert from VB6 is impossible to get into the db unless the datatype in the db is char, varchar, timestamp or text that is.
This is the code:

Dim InsertQuery As String
Set oConn = New Connection

Set oRec = New Recordset
oRec.Open InsertQuery, oConn

is my problem related to that everything in InsertQuery, is of course, a string when the SQL query executes?

-jr|||You need to post an actual insert statement and the data types as defined by the table.|||InsertQuery = "INSERT INTO black (Part, Serial, Time, S0) VALUES ('101-0001-00', '12345678', '" & Now & "', '432.95');"

Is a test-query I use

And the table is defined as follows:
Part - char - size 11 - Do not allow nulls
Serial - char - size 8 - Do not allow nulls
[Time] - datetime - Do not allow nulls
S0 - decimal - Precision: 2 - Do not allow nulls|||Your post has 2 main problems
1. You do not specify scale for decimal. Minimum numeric(5,2) for this insert.
2. You use VB Now() function, which is setting-specific. Use getdate() on server.

create table testNum(
Part char (11) not null
,Serial char(8) not null
,[Time] datetime not null
,S0 decimal(15,2) not null
)
GO
INSERT INTO testNum (Part, Serial, Time, S0) VALUES ('101-0001-00', '12345678', getdate(), '432.95')
--faster
INSERT INTO testNum (Part, Serial, Time, S0) VALUES ('101-0001-00', '12345678', getdate(), 432.95)|||What is the most numbers to the left of the decimal and to the right for the column S0 ? Once you know this, create that field with a precision of the 2 maximums combined and the scale of the maximum of the length to the right of the decimal.|||Thanks rnealejr and ispaleny... it was clearly my SQL knowledge (or the lack of it) that was the problem. Works great now, though. Thanks again.|||Just one more thing..
When the value is (e.g) 2300.00 the .00 is not showing in the db, how to make the decimals show even though they are only zeroes (0)?|||This will happen in enterprise manager - run a query in query analyzer and you will see them.

Datatype convert char to numeric

Hi,

I read the topic from JROdden and this case is similiar but...

I got several varchar fields with
values like
1.2
1.3
... these I can covert with
select CONVERT(dec(5,2), fieldname) as fieldname

In fact I also solved undefined- and NULL-values with.
CONVERT(decimal(12, 2), CASE WHEN GESCHKOSTMAX IS NULL OR
GESCHKOSTMAX < '0' THEN '0' ELSE GESCHKOSTMAX END) as GESCHKOSTMAX,

But now there are values like
1,4 and these ones neither CONVERT nor CAST will handle.

I tried the
SELECT DISTINCT KMPAUSCHALE
FROM extr_INTFIRMA
WHERE (isnumeric(KMPAUSCHALE) = 1)

and get
0,40
0.25
0.30 and so on...

The error is:
[Microsoft][ODBC SQL Driver][SQL Server]Error converting datatype varchar to decimal. (or float or numeric (whatever I tried))

I think the easiest way would be to insist on higher data quality but
I also would like to solve this interesting challenge.

Thanks for any hints

By the way, I followed rudys link to
http://rudy.ca/afdb.html
and now I know how I could protect myself !!!!

There must be a voice in my head saying:
Try the db-forum, try it and stay happy... ;-)

best regards and have fun with new year eve.

MichaelWhy are you worried about < 0? And how do you now they will translate to 5,2?

You should be more worried about other chars that don't translate...

SELECT CONVERT(float,ISNULL(REPLACE(GESCHKOSTMAX,',',',') ,0))
FROM extr_INTFIRMA
WHERE ISNUMERIC(REPLACE(GESCHKOSTMAX,',',','))=1

My question to you is...what do you do with the data that doesn't fit this profile?

You're exclusing an entire population of potentially valid data...

Yes you should more tightly define the columns datatype...

It's probably going to require data cleaning though...|||Originally posted by Michael Kaiser
By the way, I followed rudys link to
http://rudy.ca/afdb.html
and now I know how I could protect myself !!!!

:cool:|||Originally posted by r937
:cool:

Hi Brett,

thank you very much for that SQL-command.
I tried
SELECT CONVERT(decimal(15, 4), ISNULL(REPLACE(HERUMSATZWELT, ',', '.'), 0)) AS Expr1
FROM extr_INTFIRMA
WHERE (ISNUMERIC(REPLACE(HERUMSATZWELT, ',', '.')) = 1)

(please note the point I replaced) and it worked well.

Nevertheless there are some field which do not contain NULLS.
The ASCII(fieldname) gives me NULL as result.
So I guess this field is "not defined".

This is the reason why I' m worried about < 0.
All "undefined" went to "0".
Not very elegant but it worked...

best regards

Michael|||Originally posted by Michael Kaiser
Nevertheless there are some field which do not contain NULLS.
The ASCII(fieldname) gives me NULL as result.
So I guess this field is "not defined".

This is the reason why I' m worried about < 0.
All "undefined" went to "0".
Not very elegant but it worked...

best regards

Michael

I don't understand...ISNULL() Will only give you a 0 if the value is NULL

And what is ASCII(fieldname) for?

And I don't understand "undefined"

But hey, as long as it work

Ihr Willkommen|||Hi Brett,

the data are coming from a web-frontend into an access-db.
Obviosly the user don't have to fillout all questions and therefore
some fields remain empty.
Others change to "NULL".
I don't know why.
I imported the data to SQL server and
checked them with select distinct
I get two "empty" fields in the result.
One contains "NULL" the other "nothing" ?
I tried ASCII(fieldname) to search for "nonvisible" data but
this ASCII-command results in NULL.

When trying your command it fails because of this empty fields.

I checked the SQL online help and found the
NonEmpty function but that will not work within a sql-command
(in my view)

Whatsoever...
now it is time to go to a new years eve party...

I will try it again next year!!!

By the way...

your welcome = Ihr Willkommen
is very, very strange - hihi -

your welcome best fits in german - gern geschehen -

But I get the idea.

Thanks for your help and tons of fun the next hours...

Michael|||und einen guten rutsch ins neue jahr !!

rudy|||Hey, that's what I get for a google translation...

anyway...happy new year!

And what you're describing is an empty string, which is not null

SELECT COUNT(*)
FROM extr_INTFIRMA
WHERE GESCHKOSTMAX = ''

Should show you how many

Have a liter or 2 for me!|||Rudy - I tried selling my version of that device but no luck. I have been wearing mine for several years now - and I feel so much safer today - no aliens talking in my head anymore ... :-)

Thanks for posting that - I have not seen that for a couple of years now and it always makes me laugh.

Datatime order by Problem

Hi everybody,
I have onde table called (TB) with two fields : F1(int) and F2(varchar:10)
In this table the field F2 is used to store dates in the format
dd/mm/yyyy (27/12/2002). Its is record as varchar and undesired.
I need to select all records from a single id from the field F1 and
then order by the result set by date to result set be this way:

F1 F2
------------
01 15/12/1975
01 15/12/1980
01 16/12/1998
01 27/12/2003
------------

To do this Im using the follow syntax :

SELECT * FROM TB
WHERE (F1 = '01')
ORDER BY CAST(F2 AS datetime(103))

But it gives the follow message erro trying to select data :

[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

How can I convert the format used in the select to order by date type in a SQL Server range datetype to get a result set as above?

Thansk for attention.

Leonardo AlmeidaUse convert(datetime, f2, 103) instead of the cast.sql

DataTable and autoincremental Database Fields

Hi,

I got a problem regarding autoincremental Database Fields and DataTables. Using the technique to map the DataBase to an *.xsd file and trying to insert a Row into the table always results in this kind of error:

Cannot insert explicit value for identity column in table 'Cars' when IDENTITY_INSERT is set to OFF.

This only happens when I set CarIDs properties AutoIncrement = true, AutoIncrementSeed = -1, AutoIncrementStep = -1. Having AutoIncrement = false, results in this error:

Column 'CarID' does not allow nulls.

Do I missunderstand something, or do I need to change some parts? I would be glad if someone could help me and figure out what I am doing wrong.

Thanks a lot have a nice weekend.

Regards Johannes

Hi,

i figured out something new. Regarding the line in which the error occurs it happens when I am trying to add a new row into the Datatable.

That's my code:

1[System.ComponentModel.DataObjectMethodAttribute2 (System.ComponentModel.DataObjectMethodType.Insert,true)]3public bool InsertCar(int CarType,string CarNumberPlate,string CarMileage,4string CarServiceRange,bool CarSummerTires,bool CarWinterTires,bool CarHitch,5int CarCarageID, Guid CarUserID, DateTime CarPurchased,bool CarSold)6 {7 Fleet.CarsDataTable cars =new Fleet.CarsDataTable();8 Fleet.CarsRow car = cars.NewCarsRow();91011 car.CarType = CarType;12 car.CarNumberPlate = CarNumberPlate;13 car.CarMileage = CarMileage;14 car.CarServiceRange = CarServiceRange;15 car.CarSummerTires = CarSummerTires;16 car.CarWinterTires = CarWinterTires;17 car.CarHitch = CarHitch;18 car.CarGarageID = CarCarageID;19 car.CarUserID = CarUserID;20 car.CarPurchased = CarPurchased;21 car.CarSold = CarSold;2223 cars.AddCarsRow(car);24int rowsAffected = Adapter.Update(cars);2526return rowsAffected == 1;27 }

The error occurs in line 23. Depeding on the settings in my Fleed.xsd file for CarID, as mentioned above the error changes.

Okay tried something else and fixed it. I reconfigured my TableAdapter and activatedRefresh the data table.No it works perfectly. Ig anyone could explain that to me I would be happy.

Thanks a lot.

Regards Johannes

Dataset Window

Could someone please tell me how to bring up the dataset window in
Reporting Services so I can drag and drop the fields from the dataset?
I've searched high and low in the report designer and cannot find that
option anywhere. I am working with Reporting Services 2000.
Thank you.In Visual Studio, on the menu bar, select the View menu. When you have
a report RDL open, you should be able to see the "Datasets" option
under the View menu, usually towards the bottom of the menu.
Otherwise, try the "Other Windows >" option for more submenu options.
Regards,
Thiago Silva
Nergock@.gmail.com wrote:
> Could someone please tell me how to bring up the dataset window in
> Reporting Services so I can drag and drop the fields from the dataset?
> I've searched high and low in the report designer and cannot find that
> option anywhere. I am working with Reporting Services 2000.
> Thank you.

DataSet has no fields

Please Help. I am calling a stored procedure which creates a SQL statement and
then runs
exec (@.SQL + @.Where + @.Order)
Is there anyway to manual create fields. The Stored procedures returns the
same column names and number of columns?
--
Thanks,
Jon AWhy don't you set the proc to just do a select of the fields you want until
the report is built, then change the proc back to the exec(string) command.
That way you'll have access to the fields in the builder.
"Jon A" wrote:
> Please Help. I am calling a stored procedure which creates a SQL statement and
> then runs
> exec (@.SQL + @.Where + @.Order)
> Is there anyway to manual create fields. The Stored procedures returns the
> same column names and number of columns?
> --
> Thanks,
> Jon A|||Alternatively, you can go into the properties of the dataset that you want
fields for (by clicking the elipsis next to the name of the dataset on the
Data tab in report design) and go to the "Fields" tab. Here you can define
your fields manually and this will enable you to use them in your report
design. Be careful to name your fields exactly as they are returned from the
database otherwise RS will run into problems.
Also, another thing to try is to click on the "Refresh Fields" button on the
Data tab in report design. This executes the query and "figures out" what
fields are returned where it may not be immediately obvious from your query
design (especially where using Dynamic SQL is concerned).
Cheers
--
Tim McOwan
"Mary Bray [SQL Server MVP]" wrote:
> Why don't you set the proc to just do a select of the fields you want until
> the report is built, then change the proc back to the exec(string) command.
> That way you'll have access to the fields in the builder.
> "Jon A" wrote:
> > Please Help. I am calling a stored procedure which creates a SQL statement and
> > then runs
> > exec (@.SQL + @.Where + @.Order)
> > Is there anyway to manual create fields. The Stored procedures returns the
> > same column names and number of columns?
> >
> > --
> > Thanks,
> > Jon Asql

DataReader returns different results when there are null fields

If I query sql server I get 10 results. But when I use if (myReader.Read()) I get only 7 results. I found that there was a Null field in the DB. I changed it and it worked.
The problem is I don't want to touch the database and set all null fields. There must be a way to get all results including the Null using sqlDataReader so that if (myReader.Read()) is used it does the right comparison.

// This code is called 10 times with a select * from where item="xxx"
P21Conn.Open();

SqlDataReader myReader = cmd.ExecuteReader();

if (myReader.Read()) {

thanks
Rodin your select or sproc, use ISNULL or COALESCE around the columns that have null values.

cs|||Thank you

I did that and now it works perferct

Rod

Datafiles

I am having trouble moving BLOBs (text fields).
I have a 200Gb database in a single data file and want to split it into 4
even sized files (I have my reasons). To achieve this I plan on adding 3 data
files to the Primary filegroup. From there I will issue a DBCC dbreindex on
all my tables, which effectively recreates the index (or table if its a
clustered index) across all datafiles and I get my even distribution.
My plan comes unstuck when I encounter text fields and other BLOBs (as the
clustered index just contains pointers doesnt it'). How can I move them? Do
I have to do a select into or bcp? Can I just issue a shrink file on the
oringal large file or does that just push pages from the end of that data
file to the beginning and not to other datafiles'
Any suggestions would be great, I don't really want to create another
filegroup just yet.
Thanks :)"Malarb" <Malarb@.discussions.microsoft.com> wrote in message
news:BA0A6D10-4377-4247-A2A3-195FB60B308B@.microsoft.com...
>I am having trouble moving BLOBs (text fields).
> I have a 200Gb database in a single data file and want to split it into 4
> even sized files (I have my reasons). To achieve this I plan on adding 3
> data
> files to the Primary filegroup. From there I will issue a DBCC dbreindex
> on
> all my tables, which effectively recreates the index (or table if its a
> clustered index) across all datafiles and I get my even distribution.
> My plan comes unstuck when I encounter text fields and other BLOBs (as the
> clustered index just contains pointers doesnt it'). How can I move them?
> Do
> I have to do a select into or bcp? Can I just issue a shrink file on the
> oringal large file or does that just push pages from the end of that data
> file to the beginning and not to other datafiles'
> Any suggestions would be great, I don't really want to create another
> filegroup just yet.
I would create a new filegroup with the desired number and layout of files,
mark it as primary, and move all your objects there. Then drop the old
filegroup.
David|||You will need to create a new filegroup so you can create a new table on
this filegroup. Copy the old data to the new table. Drop the old table.
Rename the new tb to the old name. And then shrink the db to reclaim the
space.
--
-oj
"Malarb" <Malarb@.discussions.microsoft.com> wrote in message
news:BA0A6D10-4377-4247-A2A3-195FB60B308B@.microsoft.com...
>I am having trouble moving BLOBs (text fields).
> I have a 200Gb database in a single data file and want to split it into 4
> even sized files (I have my reasons). To achieve this I plan on adding 3
> data
> files to the Primary filegroup. From there I will issue a DBCC dbreindex
> on
> all my tables, which effectively recreates the index (or table if its a
> clustered index) across all datafiles and I get my even distribution.
> My plan comes unstuck when I encounter text fields and other BLOBs (as the
> clustered index just contains pointers doesnt it'). How can I move them?
> Do
> I have to do a select into or bcp? Can I just issue a shrink file on the
> oringal large file or does that just push pages from the end of that data
> file to the beginning and not to other datafiles'
> Any suggestions would be great, I don't really want to create another
> filegroup just yet.
> Thanks :)|||Hi,
I have the same problem, but i´m not a dba expert. How is sintaxe to create
a new table and accurate the it´s will in the new files group.
Could use the following command to creat a copy table'
select * into COPY_TABLE
from SOURCE_TABLE
WHERE 1=0
Thanks
"oj" wrote:
> You will need to create a new filegroup so you can create a new table on
> this filegroup. Copy the old data to the new table. Drop the old table.
> Rename the new tb to the old name. And then shrink the db to reclaim the
> space.
> --
> -oj
>
> "Malarb" <Malarb@.discussions.microsoft.com> wrote in message
> news:BA0A6D10-4377-4247-A2A3-195FB60B308B@.microsoft.com...
> >I am having trouble moving BLOBs (text fields).
> >
> > I have a 200Gb database in a single data file and want to split it into 4
> > even sized files (I have my reasons). To achieve this I plan on adding 3
> > data
> > files to the Primary filegroup. From there I will issue a DBCC dbreindex
> > on
> > all my tables, which effectively recreates the index (or table if its a
> > clustered index) across all datafiles and I get my even distribution.
> >
> > My plan comes unstuck when I encounter text fields and other BLOBs (as the
> > clustered index just contains pointers doesnt it'). How can I move them?
> > Do
> > I have to do a select into or bcp? Can I just issue a shrink file on the
> > oringal large file or does that just push pages from the end of that data
> > file to the beginning and not to other datafiles'
> >
> > Any suggestions would be great, I don't really want to create another
> > filegroup just yet.
> >
> > Thanks :)
>
>|||select/into does not allow you to specify the filegroup. You will need to
create a new table then do update.
e.g.
create table newtab(...) on filegroup
update newtab
set ...= old...
from newtab,old
where newtab.pk=old.pk
-oj
"Fabio Rebelo" <FabioRebelo@.discussions.microsoft.com> wrote in message
news:07896DF0-B18B-4ACB-A854-EFBEE387E32D@.microsoft.com...
> Hi,
> I have the same problem, but i´m not a dba expert. How is sintaxe to
> create
> a new table and accurate the it´s will in the new files group.
> Could use the following command to creat a copy table'
> select * into COPY_TABLE
> from SOURCE_TABLE
> WHERE 1=0
> Thanks
>
> "oj" wrote:
>> You will need to create a new filegroup so you can create a new table on
>> this filegroup. Copy the old data to the new table. Drop the old table.
>> Rename the new tb to the old name. And then shrink the db to reclaim the
>> space.
>> --
>> -oj
>>
>> "Malarb" <Malarb@.discussions.microsoft.com> wrote in message
>> news:BA0A6D10-4377-4247-A2A3-195FB60B308B@.microsoft.com...
>> >I am having trouble moving BLOBs (text fields).
>> >
>> > I have a 200Gb database in a single data file and want to split it into
>> > 4
>> > even sized files (I have my reasons). To achieve this I plan on adding
>> > 3
>> > data
>> > files to the Primary filegroup. From there I will issue a DBCC
>> > dbreindex
>> > on
>> > all my tables, which effectively recreates the index (or table if its a
>> > clustered index) across all datafiles and I get my even distribution.
>> >
>> > My plan comes unstuck when I encounter text fields and other BLOBs (as
>> > the
>> > clustered index just contains pointers doesnt it'). How can I move
>> > them?
>> > Do
>> > I have to do a select into or bcp? Can I just issue a shrink file on
>> > the
>> > oringal large file or does that just push pages from the end of that
>> > data
>> > file to the beginning and not to other datafiles'
>> >
>> > Any suggestions would be great, I don't really want to create another
>> > filegroup just yet.
>> >
>> > Thanks :)
>>

Datafiles

I am having trouble moving BLOBs (text fields).
I have a 200Gb database in a single data file and want to split it into 4
even sized files (I have my reasons). To achieve this I plan on adding 3 dat
a
files to the Primary filegroup. From there I will issue a DBCC dbreindex on
all my tables, which effectively recreates the index (or table if its a
clustered index) across all datafiles and I get my even distribution.
My plan comes unstuck when I encounter text fields and other BLOBs (as the
clustered index just contains pointers doesnt it'). How can I move them? Do
I have to do a select into or bcp? Can I just issue a shrink file on the
oringal large file or does that just push pages from the end of that data
file to the beginning and not to other datafiles'
Any suggestions would be great, I don't really want to create another
filegroup just yet.
Thanks "Malarb" <Malarb@.discussions.microsoft.com> wrote in message
news:BA0A6D10-4377-4247-A2A3-195FB60B308B@.microsoft.com...
>I am having trouble moving BLOBs (text fields).
> I have a 200Gb database in a single data file and want to split it into 4
> even sized files (I have my reasons). To achieve this I plan on adding 3
> data
> files to the Primary filegroup. From there I will issue a DBCC dbreindex
> on
> all my tables, which effectively recreates the index (or table if its a
> clustered index) across all datafiles and I get my even distribution.
> My plan comes unstuck when I encounter text fields and other BLOBs (as the
> clustered index just contains pointers doesnt it'). How can I move them?
> Do
> I have to do a select into or bcp? Can I just issue a shrink file on the
> oringal large file or does that just push pages from the end of that data
> file to the beginning and not to other datafiles'
> Any suggestions would be great, I don't really want to create another
> filegroup just yet.
I would create a new filegroup with the desired number and layout of files,
mark it as primary, and move all your objects there. Then drop the old
filegroup.
David|||You will need to create a new filegroup so you can create a new table on
this filegroup. Copy the old data to the new table. Drop the old table.
Rename the new tb to the old name. And then shrink the db to reclaim the
space.
-oj
"Malarb" <Malarb@.discussions.microsoft.com> wrote in message
news:BA0A6D10-4377-4247-A2A3-195FB60B308B@.microsoft.com...
>I am having trouble moving BLOBs (text fields).
> I have a 200Gb database in a single data file and want to split it into 4
> even sized files (I have my reasons). To achieve this I plan on adding 3
> data
> files to the Primary filegroup. From there I will issue a DBCC dbreindex
> on
> all my tables, which effectively recreates the index (or table if its a
> clustered index) across all datafiles and I get my even distribution.
> My plan comes unstuck when I encounter text fields and other BLOBs (as the
> clustered index just contains pointers doesnt it'). How can I move them?
> Do
> I have to do a select into or bcp? Can I just issue a shrink file on the
> oringal large file or does that just push pages from the end of that data
> file to the beginning and not to other datafiles'
> Any suggestions would be great, I don't really want to create another
> filegroup just yet.
> Thanks |||Hi,
I have the same problem, but i′m not a dba expert. How is sintaxe to create
a new table and accurate the it′s will in the new files group.
Could use the following command to creat a copy table'
select * into COPY_TABLE
from SOURCE_TABLE
WHERE 1=0
Thanks
"oj" wrote:

> You will need to create a new filegroup so you can create a new table on
> this filegroup. Copy the old data to the new table. Drop the old table.
> Rename the new tb to the old name. And then shrink the db to reclaim the
> space.
> --
> -oj
>
> "Malarb" <Malarb@.discussions.microsoft.com> wrote in message
> news:BA0A6D10-4377-4247-A2A3-195FB60B308B@.microsoft.com...
>
>|||select/into does not allow you to specify the filegroup. You will need to
create a new table then do update.
e.g.
create table newtab(...) on filegroup
update newtab
set ...= old...
from newtab,old
where newtab.pk=old.pk
-oj
"Fabio Rebelo" <FabioRebelo@.discussions.microsoft.com> wrote in message
news:07896DF0-B18B-4ACB-A854-EFBEE387E32D@.microsoft.com...[vbcol=seagreen]
> Hi,
> I have the same problem, but im not a dba expert. How is sintaxe to
> create
> a new table and accurate the its will in the new files group.
> Could use the following command to creat a copy table'
> select * into COPY_TABLE
> from SOURCE_TABLE
> WHERE 1=0
> Thanks
>
> "oj" wrote:
>

DatabaseName is blank in Trace

I am using SQL 2000.
I am taking trace and execute an sp_trace_setevent for all
the events I want to record. One of the fields I want is
the DatabaseName, so I execute:
exec sp_trace_setevent @.TraceID, 10, 35,1
When I view the trace, I get all the column names (server,
DBID, DatabaseName, etc.). There is data in all the
columns except the DatabaseName field is blank.
I know I can work around this by doing a join with the
DBID and Sysdatabases, but I find it strange that the
DatabaseName field is blank.
Has anyone else experienced this? Anyone have any ideas?
Thanks.It's not unusual - the names for DatabaseName (as well as
ObjectName) can't always be obtained as they aren't
available for every event. Use DatabaseID instead.
-Sue
On Wed, 1 Oct 2003 08:17:47 -0700, "dschrier"
<deena_schrier@.viarail.ca> wrote:
>I am using SQL 2000.
>I am taking trace and execute an sp_trace_setevent for all
>the events I want to record. One of the fields I want is
>the DatabaseName, so I execute:
>exec sp_trace_setevent @.TraceID, 10, 35,1
>When I view the trace, I get all the column names (server,
>DBID, DatabaseName, etc.). There is data in all the
>columns except the DatabaseName field is blank.
>I know I can work around this by doing a join with the
>DBID and Sysdatabases, but I find it strange that the
>DatabaseName field is blank.
>Has anyone else experienced this? Anyone have any ideas?
>Thanks.
>
>
>

Database triggers - SQL Server - Fields only allowed if listed in another field in another

I would like to ensure data integrity in a column (actually multiple columns will need a trigger) in my table(s) by setting up a trigger which allows an update of my database field only if the value which is being written to the field in the database exists in another column (in another "check" table).

eg. I only want values "Yes", "No" or "" in many of my fields, which I store in a column named "YesNoBlank" in another table.

Does anyone know the easy way to do this? / Syntax for the trigger?

Why not use PK/FK instead of trigger? I mean you can define the YesNoBlank column as Primary Key in some table, and other columns whose values must exist in the YesNoBlank column as Foreign Key referenceing the YesNoBlank column. For more information about PK/FK, you can take a look at:

Creating and Modifying FOREIGN KEY Constraints

|||

I think Check constraints are my preferance, as I have all of my fields which would potentially be Primary keys in your example in a single reference table (ie multiple columns)

My statement in('Yes','No','') does not work for my check constraint.

Does anyone know what my syntax would be?

|||A quick sample:

CREATE TABLE testConstraint (ID INT, NAME sysname, YESNO VARCHAR(3))

ALTER TABLE testConstraint
ADD CONSTRAINT yesno_check CHECK (UPPER(YESNO) in ('YES','NO'))

--This will succeed
INSERT INTO testConstraint VALUES(1,'Iori','Yes')
--This will fail
INSERT INTO testConstraint VALUES(2,'Kyo','noo')

For mor information, you can refer to:?ALTER?TABLE?