If i am using SqlCommand Class and the CommandType of it is Text, then will it add "sp_exe

If i am using SqlCommand Class and the CommandType of it is Text, then will it add "sp_executesql N" in front of the sql command automatically in fact, just like SqlDataAdapter?If you are using a CommandType of Text, just send the SELECT, INSERT, UPDATE or DELETE command. If you are using CommandType of StoredProcedure, just send the name of the stored procedure. To see exactly what is sent to the SQL Server, use SQL Profiler, which will allow you to capture the exact code sent, which will vary based upon whether you are using parameters, etc.|||that's means i still need to add "sp_executesql N" when i use SqlCommand class and CommandType is Text if i want a better performance?|||As Douglas already suggested if you are not using a stored procedure then use command type text otherwise you should generally use command type stored procedure. You can however still use command type text to run a stored procedure whch does not return records, to do this it would be: "exec procname arg1, arg, arg..."

As for the sp_executesql this internal sql server procedure basically runs every single sql command received by the server. So yes, internally it is always added.

Identity question

I have two fields that I am concerned with be unique.

Id, and Name.

The id is set to the primary key which automatically makes it unique. How would I set the Name to be unique as well?You add a constraint to it. And making it a primary key doesn't make it an identity. That's yet another type of constraint you apply to it. :)|||Ok here is the example

create table tester(a varchar primary key, b varchar(20) unique);

insert into tester values ('1','2');
insert into tester values ('1','2');
insert into tester values ('2','2');

the first insert statement inserts perfectly, and there will be errors with the second and third statement as a is primary key and is unique and second b is unique. so only first statement gets inserted in to the table.|||Ok, so I cannot specify it at the table level. It must be set at time of insert?|||You can check the uniqueness of the field when u try to insert or update the record/data. with data i think the table is useless.

So the concept of uniqueness comes when u r trying to do some transactions with the table right.

Phani...|||You DO specify it at the table level, either when issuing a CREATE or ALTER table statement.|||Is there any way to do it via Enterprise Manager. I've already created the tables.|||Yes. Check out the"Creating a Unique Constraint" article in MSDN (also in Books Online). It provdes step-by-step instructions. You might actually need a Unique Index instead (follow the link in the article for more information).

Terri|||I actually figured it out late last night, this article is exactly what I did.

Thanks for the help guys.

Identity Column Rollover

If I have an Identity column defined as a smallint
seed=1, increment=1
Does it automatically roll back to 1 when the number of records reaches
32,767. Assuming of course, the last record assigned identity 1 has been
removed.
I assume it does, but hey, I've assumed stuff like this before and gotten
burned.
JohnNo it does not automatically roll over. Instead you will receive an
overflow error and future inserts will fail. You could then reseed the
value using the DBCC CHECKIDENT command. A much better plan however, is
to create the column large enough that it won't overflow for the
forseeable future.
David Portas
SQL Server MVP
--|||No, you assume wrong. You'll get an error when you try to insert a new row.
"Arithmetic overflow error for data type smallint, value = 32768"
Change to Int if you believe that it may be a problem.
"John Manion" <JohnManion@.discussions.microsoft.com> wrote in message
news:785FF58A-2C96-4AAF-9E2A-30C918853DDE@.microsoft.com...
> If I have an Identity column defined as a smallint
> seed=1, increment=1
> Does it automatically roll back to 1 when the number of records reaches
> 32,767. Assuming of course, the last record assigned identity 1 has been
> removed.
> I assume it does, but hey, I've assumed stuff like this before and gotten
> burned.
> John|||No. SQL Server will try to increment the identity value and also give you an
error when trying to store the new value.
Example:
use northwind
go
create table t (
colA smallint not null identity
)
go
set identity_insert t on
go
insert into t (colA) values(32767)
go
set identity_insert t off
go
select * from t
go
insert into t default values
go
select * from t
go
drop table t
go
AMB
"John Manion" wrote:

> If I have an Identity column defined as a smallint
> seed=1, increment=1
> Does it automatically roll back to 1 when the number of records reaches
> 32,767. Assuming of course, the last record assigned identity 1 has been
> removed.
> I assume it does, but hey, I've assumed stuff like this before and gotten
> burned.
> John|||Not surprising.
Thanks,
John
"David Portas" wrote:

> No it does not automatically roll over. Instead you will receive an
> overflow error and future inserts will fail. You could then reseed the
> value using the DBCC CHECKIDENT command. A much better plan however, is
> to create the column large enough that it won't overflow for the
> forseeable future.
> --
> David Portas
> SQL Server MVP
> --
>

Identity

I have a numerid field with an identity set on it so that every record get's
an incremented number in this field automatically.
If I delete a record, the numbering is no longer sequential as one record
has gone.
Is there any way of forcing a re-number of the records to make them
sequential again?
Thanks
You can use DBCC CHECKIDENT to reset. But there's no feature to compress "holes" in the sequence. The question
is why does it matter if they are sequential or not? They are only used to identify something anyhow! Also, be
aware that rollbacks and things like that can mean that you consume a value which isn't inserted to the table,
leading to a "gap".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Keith" <@..> wrote in message news:%23OAeaT%23FEHA.3908@.TK2MSFTNGP12.phx.gbl...
> I have a numerid field with an identity set on it so that every record get's
> an incremented number in this field automatically.
> If I delete a record, the numbering is no longer sequential as one record
> has gone.
> Is there any way of forcing a re-number of the records to make them
> sequential again?
> Thanks
>
|||In addition to Tibor's response, if you wrote a trigger or something which
did compress the values it would very likely be quite expensive... These
PK values should have no meaning...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Keith" <@..> wrote in message
news:%23OAeaT%23FEHA.3908@.TK2MSFTNGP12.phx.gbl...
> I have a numerid field with an identity set on it so that every record
get's
> an incremented number in this field automatically.
> If I delete a record, the numbering is no longer sequential as one record
> has gone.
> Is there any way of forcing a re-number of the records to make them
> sequential again?
> Thanks
>

Identity

I have a numerid field with an identity set on it so that every record get's
an incremented number in this field automatically.
If I delete a record, the numbering is no longer sequential as one record
has gone.
Is there any way of forcing a re-number of the records to make them
sequential again?
ThanksYou can use DBCC CHECKIDENT to reset. But there's no feature to compress "ho
les" in the sequence. The question
is why does it matter if they are sequential or not? They are only used to i
dentify something anyhow! Also, be
aware that rollbacks and things like that can mean that you consume a value
which isn't inserted to the table,
leading to a "gap".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Keith" <@..> wrote in message news:%23OAeaT%23FEHA.3908@.TK2MSFTNGP12.phx.gbl...ed">
> I have a numerid field with an identity set on it so that every record get
's
> an incremented number in this field automatically.
> If I delete a record, the numbering is no longer sequential as one record
> has gone.
> Is there any way of forcing a re-number of the records to make them
> sequential again?
> Thanks
>|||In addition to Tibor's response, if you wrote a trigger or something which
did compress the values it would very likely be quite expensive... These
PK values should have no meaning...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Keith" <@..> wrote in message
news:%23OAeaT%23FEHA.3908@.TK2MSFTNGP12.phx.gbl...
> I have a numerid field with an identity set on it so that every record
get's
> an incremented number in this field automatically.
> If I delete a record, the numbering is no longer sequential as one record
> has gone.
> Is there any way of forcing a re-number of the records to make them
> sequential again?
> Thanks
>