|
Hi,
I was advised to stay away from using identity seeds where the number increments inteself by everytime but to use a varchar(10) for example. And I was also told that having a varchar ID slows down insert/update/ But inserting/updating 1 record shouldn't be that bad? But I'm more worried about sting searches on the ID. Will it slow down searches??
Can I please hear your thoughts on this? I am looking for best practices please. I am using SQL Server 2005.
Regards,
Brendan
|
|
|
|
|
.NET Enthusiast wrote: I was advised to stay away from using identity seeds where the number increments inteself by everytime but to use a varchar(10) for example. And I was also told that having a varchar ID slows down insert/update/ But inserting/updating 1 record shouldn't be that bad? But I'm more worried about sting searches on the ID. Will it slow down searches??
If you must use string based IDs then you should go for fixed length columns, e.g. CHAR(10). However, it is better to use an INT with an identity on it. It will be much more efficient.
|
|
|
|
|
Thanks for both replies Colin.
|
|
|
|
|
Hi,
I have a customer ID of type varchar(10) and I want to use it like a identity seed. I want the first customer to start with 1000 and then incremented each time a new customer is added. I could probably do this with C#, but I think a safer option will maybe be to do it with SQL Server 2005??
Please can someone advise me on this? I need to check first if a record exists, if not then the ID is 1000, other get the number and increment it with 1.
I hope to hear from someone soon.
Regards
Brendan
|
|
|
|
|
.NET Enthusiast wrote: I have a customer ID of type varchar(10) and I want to use it like a identity seed. I want the first customer to start with 1000 and then incremented each time a new customer is added. I could probably do this with C#, but I think a safer option will maybe be to do it with SQL Server 2005??
Please can someone advise me on this? I need to check first if a record exists, if not then the ID is 1000, other get the number and increment it with 1.
Why use a varchar? Why not declare the column as INT IDENTITY(1000,1)? That would do everything you described that you want and be a lot safer than writing your own mechanism.
|
|
|
|
|
If I have an int identity starting at 1000 then the ids will obviously be higher. It won't affect performance when searches are being done?
|
|
|
|
|
.NET Enthusiast wrote: If I have an int identity starting at 1000 then the ids will obviously be higher. It won't affect performance when searches are being done?
They are all stored as 32bits whether is it 1, 1000, or 2147483647. The value of the number is not going to affect performance.
|
|
|
|
|
Hi,
I am developing a web page for searching user records from DB. In advance search option there are 5 fields like firstname, lastname, years of experience,experience type, location.
I want to write a storedprocedure which take all these 5 parameter as input and return me record from a table.All these fields are in single table.
User can search a record by providing all the 5 fields are by providing any of these.
Plz help me in writing this query.
I am very week in T-Sql.
Thanks in Advance
Alok....
Alok...
|
|
|
|
|
Write a stored procedure that takes the values you want. Pass the value the user is searching for, or null if no value on that column
Then your query can look like:
select * from sometable
where
(ColumnA = @SearchFieldA or @SearchFieldA is null)
and (ColumnB = @SearchFieldB or @SearchFieldB is null)
etc
|
|
|
|
|
|
Good afternoon All
I have an Access Table(Property_Mass_3) and a SQL table(Property_Mass), these two table have the same structure,and they have a Primary_key named "Property_ID". So i want to Export Data from an access table to a SQl table, so am using the Export and import data wizard. so in the SQL table, there are records, these records have the Field "Property_ID", and i dont want to import records from "Property_Mass_3" that have the same Property_id of the record that exists in Property_Mass, Basically i want to import records from Accees, and if the process of importing find that the Property_ID that it tries to import exists, then it should not import that record.
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
You don't get this level of control with the Import/Export wizard.
If you are using SQL Server 2005, you could write a package using SQL Server Integration Services (SSIS) to perform the import. SSIS gives you fine control over the import logic.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
I've a varchar variable in my stored procedure returning either a date or a piece of text depending on the condtional code.
Both return fine but the date comes back as '6 DEC 2007 08:04am' because of the varchar.
How do I get it to return in the format 'dd/mm/yyy hh:mm:ss' ?
I've tried cast as, convert and the date formatting, it always returns as described.
Should I format it in my c# code behind, it that eaiser?
Thanks in advance.
Harvey
|
|
|
|
|
When you convert date to varchar, you can specify styles which will tell how to format the output of the convert function.
imnotso# wrote: How do I get it to return in the format 'dd/mm/yyy hh:mm:ss' ?
You can try
CONVERT(varchar, Getdate(), 131)
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Thanks for the quick response, I have tried that but it converts it to a strange date. not the date entered. I have done it in the code behind in c#.
Thanks though.
Harvey
|
|
|
|
|
i wanna know how can i use SQL connection to Access MS Exchange server ,
and what is the benefits?
|
|
|
|
|
hi all
i am a beginner, i have written an insert stored procedure.
i need my SP to check for the ID field whether the data is already present.
pls help ,me.
ALTER PROCEDURE [dbo].[SP_SaveGroupClassification]
@SubGroupName nvarchar(50),
--@GroupId int,
@ParentId int,
@ClassType nchar(2),
@RootId int
as
begin
INSERT INTO GroupClassification
(GroupName, ParentId, ClassType, RootId)
VALUES (@SubGroupName,@ParentId,@ClassType,@RootId)
end
return
Here i need to check the value in (@SubGroupName not to be repeated.it is my key field.
thank u
senthil
|
|
|
|
|
Hi,
what you could do is try to get the ID for the specified @SubGroupName. If the ID does not exisit, you can insert the new row.
DECLARE @ID int<br />
SET @ID = (SELECT ID FROM GroupClassification WHERE GroupName = @SubGroupName ORDER BY ID DESC)<br />
IF(@ID IS NULL)<br />
BEGIN<br />
INSERT INTO GroupClassification(GroupName, ParentId, ClassType, RootId)<br />
VALUES (@SubGroupName,@ParentId,@ClassType,@RootId) <br />
END
|
|
|
|
|
hi,
it is working
Thank u Tobias Schoenig.
senthil
|
|
|
|
|
Use IF NOT EXISTS
IF NOT EXISTS(Select a from table where x=??)
INSERT INTO...
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
kssknov wrote: Here i need to check the value in (@SubGroupName not to be repeated.it is my key field.
You should declare it as your PRIMARY KEY. As soon as you do that the database will do the work for you by ensuring that it remains unique and is not repeated.
|
|
|
|
|
The field should either be a primary key or, or should be associated with a unique index. If a unique index exists for a field (or if it's a primary key, which is effectively a special type of unique index) then any attempt to add a record that already exists is guaranteed to fail. If two attempts are made simultaneously to add the same record, only one will succeed. In that regard, this approach is much better than the approach others here suggested of querying first to see if the record exists (two clients could both check for a record and discover it doesn't yet exist, and then both add it).
There are few stylistic issues to consider:
-1- If the record already exists, checking before doing the insert will avoid having a thrown exception except in the case where two clients attempt to add the same value simultaneously. Code must be prepared to handle the exception even if it checks before the insert, but avoiding the insert will probably be faster than attempting it and having it fail
-2- If the record does not exist, checking the database for it will take a little time; adding the record without pre-checking would probably be faster in that case.
-3- Some databases allow nulls in a unique index; some do not. Microsoft allows exactly one null to appear in (adding a second item with a null value would violate the unique index constraint). This scenario is applicable to cases where, e.g., any number of workers might have no Social Security Number, but no SSN may be shared among workers. If the database does not allow nulls in the field, one may have to store an arbitrary value instead. If there's a unique positive integer record number available, one could set the SSN field to be negative that (e.g. for record 1,234 store -1234 in the SSN). One would have to recognize and handle those numbers when viewing the data, but there would be no problem handling multiple SSN-less employees.
|
|
|
|
|
hi
thank u for ur reply.
As u said i had other ways of doing the same.but the field i need to check is not a primary key field,also i cant set it as PK since other id field is already set to PK.
thank u very much
kssk
senthil
|
|
|
|
|
Hi all,
I want to port the .dbf files into Sqlserver.
The requirement are as such that
The user should be able to specify the name of Database and login Credential prior to porting the files into the Database.
we are using Visual Studio 2005 as developoemnt platform.
Please suggest us the possible way for that i can achieve my target.
Thanks and Regards
Avesh Agarwal
|
|
|
|
|
create a function that check what was the last value
last month for the employee in the field "location" AND "date_in"
and copy all the employee to a new TABLE
generate new location value and new date
(my location value is (1,2,3,4,5) only from 1 to 5)
the employee change location evry month (in the first day of the month)
from 1 to 2 from 2 to 3 from 3 to 4 from 4 to 5
and after to 1
i have a table of employees
like this
----------------------------------------
ID | location | date_in
1234 | 1 01/01/2007
3456 2 03/01/2007
5675 3 04/01/2007
-----------------------------------------
TNX
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
