I am new to SQL Server, so I'm not quite sure how to use this feature. I need to limit a field to only accept a set of three values: H, Q, and C. I know how to do this in a native Access table. I'm not quite sure how to do this in SQL Server. I assum
e this is what a check constraint is, but I don't know how to set one up or where to implement it.
Any suggestions would be greatly appreciated,
Crystal
Yep, it's a check constraint:
create table MyTable
(
PK int primary key
, MyCol char (1) not null constraint CK_MyTable (MyCol in ('H', 'Q',
'C'))
)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
..
"Crystal" <anonymous@.discussions.microsoft.com> wrote in message
news:BC380E12-BD25-4308-8860-08617207DEE4@.microsoft.com...
I am new to SQL Server, so I'm not quite sure how to use this feature. I
need to limit a field to only accept a set of three values: H, Q, and C. I
know how to do this in a native Access table. I'm not quite sure how to do
this in SQL Server. I assume this is what a check constraint is, but I
don't know how to set one up or where to implement it.
Any suggestions would be greatly appreciated,
Crystal
|||Try this -
create table test (
iintidentity
, jchar(1)
check (j in ('H', 'Q', 'C'))
)
|||Ok. This is what is going to make me look like a dork. . .
Now that I have the syntax. . . where do I put it to create the check constraint?
Crystal
|||The syntax I showed you is to create the constraint at the time you create
the table. However, if the table already exists, you can do an ALTER TABLE:
alter table MyTable
add
constraint CK_MyTable (MyCol in ('H', 'Q', 'C'))
go
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Crystal" <anonymous@.discussions.microsoft.com> wrote in message
news:CFC4AF0F-052F-442A-8037-307EC3A3D437@.microsoft.com...
Ok. This is what is going to make me look like a dork. . .
Now that I have the syntax. . . where do I put it to create the check
constraint?
Crystal
Showing posts with label feature. Show all posts
Showing posts with label feature. Show all posts
Thursday, March 8, 2012
Check constraint
I am new to SQL Server, so I'm not quite sure how to use this feature. I need to limit a field to only accept a set of three values: H, Q, and C. I know how to do this in a native Access table. I'm not quite sure how to do this in SQL Server. I assume this is what a check constraint is, but I don't know how to set one up or where to implement it
Any suggestions would be greatly appreciated
CrystalThis is a multi-part message in MIME format.
--=_NextPart_000_012E_01C42099.51AAF520
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
Yep, it's a check constraint:
create table MyTable
(
PK int primary key
, MyCol char (1) not null constraint CK_MyTable (MyCol in ('H', 'Q',
'C'))
)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Crystal" <anonymous@.discussions.microsoft.com> wrote in message
news:BC380E12-BD25-4308-8860-08617207DEE4@.microsoft.com...
I am new to SQL Server, so I'm not quite sure how to use this feature. I
need to limit a field to only accept a set of three values: H, Q, and C. I
know how to do this in a native Access table. I'm not quite sure how to do
this in SQL Server. I assume this is what a check constraint is, but I
don't know how to set one up or where to implement it.
Any suggestions would be greatly appreciated,
Crystal
--=_NextPart_000_012E_01C42099.51AAF520
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Yep, it's a check =constraint:
create table =MyTable
(
PK int primary =key
, =MyCol char (1) not null constraint CK_MyTable (MyCol in =('H', 'Q', 'C'))
)
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Crystal" wrote in message news:BC3=80E12-BD25-4308-8860-08617207DEE4@.microsoft.com...I am new to SQL Server, so I'm not quite sure how to use this =feature. I need to limit a field to only accept a set of three values: H, Q, =and C. I know how to do this in a native Access table. I'm not =quite sure how to do this in SQL Server. I assume this is what a check constraint is, but I don't know how to set one up or where to implement it.Any suggestions would be greatly appreciated,Crystal
--=_NextPart_000_012E_01C42099.51AAF520--|||Ok. This is what is going to make me look like a dork. .
Now that I have the syntax. . . where do I put it to create the check constraint
Crystal|||This is a multi-part message in MIME format.
--=_NextPart_000_0086_01C42136.B95D8140
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
The syntax I showed you is to create the constraint at the time you create
the table. However, if the table already exists, you can do an ALTER TABLE:
alter table MyTable
add
constraint CK_MyTable (MyCol in ('H', 'Q', 'C'))
go
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Crystal" <anonymous@.discussions.microsoft.com> wrote in message
news:CFC4AF0F-052F-442A-8037-307EC3A3D437@.microsoft.com...
Ok. This is what is going to make me look like a dork. . .
Now that I have the syntax. . . where do I put it to create the check
constraint?
Crystal
--=_NextPart_000_0086_01C42136.B95D8140
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
The syntax I showed you is to create =the constraint at the time you create the table. However, if the table =already exists, you can do an ALTER TABLE:
alter table =MyTable
add
=constraint CK_MyTable (MyCol in ('H', 'Q', 'C'))
go
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Crystal" wrote in message news:CFC=4AF0F-052F-442A-8037-307EC3A3D437@.microsoft.com...Ok. This is what is going to make me look like a dork. . .Now that I =have the syntax. . . where do I put it to create the check constraint?Crystal
--=_NextPart_000_0086_01C42136.B95D8140--
Any suggestions would be greatly appreciated
CrystalThis is a multi-part message in MIME format.
--=_NextPart_000_012E_01C42099.51AAF520
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
Yep, it's a check constraint:
create table MyTable
(
PK int primary key
, MyCol char (1) not null constraint CK_MyTable (MyCol in ('H', 'Q',
'C'))
)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Crystal" <anonymous@.discussions.microsoft.com> wrote in message
news:BC380E12-BD25-4308-8860-08617207DEE4@.microsoft.com...
I am new to SQL Server, so I'm not quite sure how to use this feature. I
need to limit a field to only accept a set of three values: H, Q, and C. I
know how to do this in a native Access table. I'm not quite sure how to do
this in SQL Server. I assume this is what a check constraint is, but I
don't know how to set one up or where to implement it.
Any suggestions would be greatly appreciated,
Crystal
--=_NextPart_000_012E_01C42099.51AAF520
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Yep, it's a check =constraint:
create table =MyTable
(
PK int primary =key
, =MyCol char (1) not null constraint CK_MyTable (MyCol in =('H', 'Q', 'C'))
)
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Crystal" wrote in message news:BC3=80E12-BD25-4308-8860-08617207DEE4@.microsoft.com...I am new to SQL Server, so I'm not quite sure how to use this =feature. I need to limit a field to only accept a set of three values: H, Q, =and C. I know how to do this in a native Access table. I'm not =quite sure how to do this in SQL Server. I assume this is what a check constraint is, but I don't know how to set one up or where to implement it.Any suggestions would be greatly appreciated,Crystal
--=_NextPart_000_012E_01C42099.51AAF520--|||Ok. This is what is going to make me look like a dork. .
Now that I have the syntax. . . where do I put it to create the check constraint
Crystal|||This is a multi-part message in MIME format.
--=_NextPart_000_0086_01C42136.B95D8140
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
The syntax I showed you is to create the constraint at the time you create
the table. However, if the table already exists, you can do an ALTER TABLE:
alter table MyTable
add
constraint CK_MyTable (MyCol in ('H', 'Q', 'C'))
go
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Crystal" <anonymous@.discussions.microsoft.com> wrote in message
news:CFC4AF0F-052F-442A-8037-307EC3A3D437@.microsoft.com...
Ok. This is what is going to make me look like a dork. . .
Now that I have the syntax. . . where do I put it to create the check
constraint?
Crystal
--=_NextPart_000_0086_01C42136.B95D8140
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
The syntax I showed you is to create =the constraint at the time you create the table. However, if the table =already exists, you can do an ALTER TABLE:
alter table =MyTable
add
=constraint CK_MyTable (MyCol in ('H', 'Q', 'C'))
go
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Crystal" wrote in message news:CFC=4AF0F-052F-442A-8037-307EC3A3D437@.microsoft.com...Ok. This is what is going to make me look like a dork. . .Now that I =have the syntax. . . where do I put it to create the check constraint?Crystal
--=_NextPart_000_0086_01C42136.B95D8140--
Check constraint
I am new to SQL Server, so I'm not quite sure how to use this feature. I ne
ed to limit a field to only accept a set of three values: H, Q, and C. I k
now how to do this in a native Access table. I'm not quite sure how to do t
his in SQL Server. I assum
e this is what a check constraint is, but I don't know how to set one up or
where to implement it.
Any suggestions would be greatly appreciated,
CrystalYep, it's a check constraint:
create table MyTable
(
PK int primary key
, MyCol char (1) not null constraint CK_MyTable (MyCol in ('H', 'Q',
'C'))
)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Crystal" <anonymous@.discussions.microsoft.com> wrote in message
news:BC380E12-BD25-4308-8860-08617207DEE4@.microsoft.com...
I am new to SQL Server, so I'm not quite sure how to use this feature. I
need to limit a field to only accept a set of three values: H, Q, and C. I
know how to do this in a native Access table. I'm not quite sure how to do
this in SQL Server. I assume this is what a check constraint is, but I
don't know how to set one up or where to implement it.
Any suggestions would be greatly appreciated,
Crystal|||Try this -
create table test (
i int identity
, j char(1)
check (j in ('H', 'Q', 'C'))
)|||Ok. This is what is going to make me look like a dork. . .
Now that I have the syntax. . . where do I put it to create the check constr
aint?
Crystal|||The syntax I showed you is to create the constraint at the time you create
the table. However, if the table already exists, you can do an ALTER TABLE:
alter table MyTable
add
constraint CK_MyTable (MyCol in ('H', 'Q', 'C'))
go
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Crystal" <anonymous@.discussions.microsoft.com> wrote in message
news:CFC4AF0F-052F-442A-8037-307EC3A3D437@.microsoft.com...
Ok. This is what is going to make me look like a dork. . .
Now that I have the syntax. . . where do I put it to create the check
constraint?
Crystal
ed to limit a field to only accept a set of three values: H, Q, and C. I k
now how to do this in a native Access table. I'm not quite sure how to do t
his in SQL Server. I assum
e this is what a check constraint is, but I don't know how to set one up or
where to implement it.
Any suggestions would be greatly appreciated,
CrystalYep, it's a check constraint:
create table MyTable
(
PK int primary key
, MyCol char (1) not null constraint CK_MyTable (MyCol in ('H', 'Q',
'C'))
)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Crystal" <anonymous@.discussions.microsoft.com> wrote in message
news:BC380E12-BD25-4308-8860-08617207DEE4@.microsoft.com...
I am new to SQL Server, so I'm not quite sure how to use this feature. I
need to limit a field to only accept a set of three values: H, Q, and C. I
know how to do this in a native Access table. I'm not quite sure how to do
this in SQL Server. I assume this is what a check constraint is, but I
don't know how to set one up or where to implement it.
Any suggestions would be greatly appreciated,
Crystal|||Try this -
create table test (
i int identity
, j char(1)
check (j in ('H', 'Q', 'C'))
)|||Ok. This is what is going to make me look like a dork. . .
Now that I have the syntax. . . where do I put it to create the check constr
aint?
Crystal|||The syntax I showed you is to create the constraint at the time you create
the table. However, if the table already exists, you can do an ALTER TABLE:
alter table MyTable
add
constraint CK_MyTable (MyCol in ('H', 'Q', 'C'))
go
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Crystal" <anonymous@.discussions.microsoft.com> wrote in message
news:CFC4AF0F-052F-442A-8037-307EC3A3D437@.microsoft.com...
Ok. This is what is going to make me look like a dork. . .
Now that I have the syntax. . . where do I put it to create the check
constraint?
Crystal
Friday, February 10, 2012
changing XML values
If I use the REPLACE feature to change only a part of the XML document, does this require the whole document be reinserted and/or parsed, or just that bit of the XML that I changed? Since its shredded out from the XML, it seems to me that I should be fine and not take this performance hit. Just trying to figure out the performance impact of our application updating the XML that already exists. Thanks.The chunk of the document that you are going to insert into the existing document will need to be reparsed, but the existing document will not need to be reparsed since it is in the shredded form, it will just have the section replaced.
changing XML values
If I use the REPLACE feature to change only a part of the XML document, does this require the whole document be reinserted and/or parsed, or just that bit of the XML that I changed? Since its shredded out from the XML, it seems to me that I should be fine and not take this performance hit. Just trying to figure out the performance impact of our application updating the XML that already exists. Thanks.The chunk of the document that you are going to insert into the existing document will need to be reparsed, but the existing document will not need to be reparsed since it is in the shredded form, it will just have the section replaced.
Subscribe to:
Posts (Atom)