Database

Richard MacCutchan1-Feb-13 23:07

Richard MacCutchan

1-Feb-13 23:07

See http://msdn.microsoft.com/en-us/library/ms176089.aspx[^].

devvvy2-Feb-13 15:39

devvvy

2-Feb-13 15:39

I did but not sure if I interpreted MSDN correctly.

<br />
<br />
char [ ( n ) ]<br />
<br />
    Fixed-length, non-Unicode string data. n defines the string length and must be a value from 1 through 8,000. The storage size is n bytes. The ISO synonym for char is character.<br />
varchar [ ( n | max ) ]<br />
<br />
    Variable-length, non-Unicode string data. n defines the string length and can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size is the actual length of the data entered + 2 bytes. The ISO synonyms for varchar are char varying or character varying.

varchar(255) --- this means SQL server will always allocate 255bytes? Or depending on actual values being inserted for a particular row?

If say row 1, "Name" = 25 bytes and row 2, "Name" = 225 bytes (say no other row longer than 225), then...
POSSIBILITY 1: SQL server always allocate 225 bytes (actual length of data, taken from row 2)
POSSIBILITY 2: SQL server always allocate 255 bytes for all rows as defined in column definition varchar(255)
POSSIBILITY 3: SQL Server allocate 25 bytes for row 1, and 225 bytes for row 2 (actual length of data vary from one row to the next)

My guess is - POSSIBILITY 3 is how SQL server actually behalves. This said, then why we bother specify varchar(n)? We should always specify varchar(MAX). Two reasons I can think of after a bit of digging...[^]

a. You cannot index anything varchar longer than 900 bytes
b. by MAX, you remove column max length validation provided for you at database level.

Am I missing something?

dev

modified 2-Feb-13 21:55pm.

PIEBALDconsult2-Feb-13 5:22

PIEBALDconsult

2-Feb-13 5:22

Allocation of storage is the primary difference, but it won't affect how you work with the data.
The main difference you'll have to deal with is in trailing SPACEs -- CHAR stores them, VARCHAR doesn't. Which can occasionally cause confusion, in that with VARCHAR 'BOB' and 'BOB ' will test equal and LEN report the same length; you'll want to use DATALENGTH for VARCHAR -- this sort of thing bit me again this week. Sigh | :sigh:

devvvy2-Feb-13 23:30

devvvy

2-Feb-13 23:30

I think it's the opposite. CHAR don't store trailing termination char (2 bytes for SQL), varchar does.

I think I found the answer. With VARCHAR(MAX), you can't index the column and also you don't have length checks provided for you by default by database, that's why you should always use VARCHAR(n) as supposed to VARCHAR(MAX)

CHAR(n) when lenght same for all/most rows.

dev

Encrypt a field in the database.

jschell3-Feb-13 6:36

jschell

3-Feb-13 6:36

devvvy wrote:
I think it's the opposite. CHAR don't store trailing termination char (2 bytes for SQL), varchar does

Not sure what that statement means.

The 'char' data type is fixed length. That means that there will ALWAYS be 255 characters. And the way normal APIs to databases handle that is that they pad shorter lengths with spaces. Pretty sure that is an ANSI requirement as well.

devvvy wrote:
that's why you should always use VARCHAR(n) as supposed to VARCHAR(MAX)

Unless of course you know that you will be storing data larger than the largest integer value allowed for varchar(n).

devvvy wrote:
CHAR(n) when lenght same for all/most rows.

In normal database design most columns will be varchar because
1. User data is almost always variable
2. Application data that is text almost always has a variable length.

SP Unexpected Behavior

eddieangel1-Feb-13 7:19

eddieangel

1-Feb-13 7:19

I have a procedure that checks to see if a user added address exists in a remote database. If the address exists, the procedure retrieves the id and updates the local DB with that id. If it does not exist, the procedure adds the address to the remote database, retrieves the new id, and updates the local database with the new id. Unfortunately, without any apparent pattern some times several addresses are lumped together into one single new address id. Can someone give me a second pair of eyes on this procedure to see where it might be doing this? I am also open to improvements.

SQL

USE [RemoteCapture]
GO
/****** Object:  StoredProcedure [dbo].[usp_resolveAddressProblems]    Script Date: 02/01/2013 09:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON
/****** Object:  StoredProcedure [dbo].[usp_resolveAddressProblems]    Script Date: 10/06/2009 12:41:21 ******/

ALTER proc [dbo].[usp_resolveAddressProblems] (@servername varchar(100))

as

declare @query nvarchar(650)
declare @myAddId int
declare @myAddress varchar(100)
declare @newId int
declare @cursorString varchar(2000)
declare @intCaseId int
declare @remoteId int = 0

set @cursorString = 'declare myCursor cursor for select intAddressId, txtAddress, intCaseId from tbl_cdf_address where bitUserAdded = 1'
exec(@cursorString)
open myCursor
fetch next from myCursor into @myAddId, @myAddress, @intCaseId
while @@FETCH_STATUS = 0
begin
	get_address:
	set @remoteId = 0
	set @query = N'select @remoteId = intAddressId from "' + @servername + '".depo.dbo.tbl_cdf_address where txtAddress = ''' + @myAddress + ''' and intCaseId = ' + CAST(@intCaseId as varchar(10))
	
	exec sp_executesql @query, N'@remoteId int output', @remoteId output
	if @remoteId != 0 
	begin
		set @query = N'update tbl_cdf_observation set intAddressId = ' + cast(@remoteId as varchar(10)) + ' where intAddressId = ' + CAST(@myAddId as varchar(10)) + ' and intCaseId = ' + CAST(@intCaseId as varchar(10))
		exec(@query)
		set @query = N'update tbl_cdf_inspectionNotes set intAddressId = ' + cast(@remoteId as varchar(10)) + ' where intAddressId = ' + CAST(@myAddId as varchar(10)) + ' and intCaseId = ' + CAST(@intCaseId as varchar(10))
		exec(@query)
		print 'Updated ' + @myAddress
	end
	else
	begin
		set @query = N'insert "' + @servername + '".depo.dbo.tbl_cdf_address (intCaseId, txtAddress, bitActive) values (' + CAST(@intCaseId as varchar(50)) + ',''' + @myAddress + ''',1)' 
		exec(@query)
		print 'Added ' + @myAddress + ' to database.'
		goto get_address
	end
fetch next from myCursor into @myAddId, @myAddress, @intCaseId
end
close myCursor
deallocate myCursor

set @query = N'delete tbl_cdf_address'
exec(@query)
return

Cheers, --EA

vanikanc1-Feb-13 5:31

Re: Encrypt a field in the database.

1-Feb-13 5:31

Hi,

I have a field in the database that has credit card number in it. Bottom line, how can we encrypt this information using SQL SERVER? I need to encrypt it for a brief period of time, like 15 minutes, then the table is truncated.

I am using sql server 2005 within C# program/

Eddy Vluggen1-Feb-13 7:02

Eddy Vluggen

1-Feb-13 7:02

This[^] topic describes how to encrypt a column of data by using symmetric encryption in SQL Server 2012 using Transact-SQL.

--edit
There's a dropdown at the top of the page that brings you to the same topic for Sql Server 2005[^].

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
They hate us for our freedom![^]

check before insert

vanikanc31-Jan-13 4:55

31-Jan-13 4:55

Hi,

This is more for error proofing the flow of insert. Is there a way to check, before an insert if that particular order already exists in the table?

we have a job that runs by the hour, and on each hour starts with an empty table.

if the process fails in the middle, and i want to start it again, if there are already some rows in the table, not to insert those.

Eddy Vluggen31-Jan-13 5:00

Eddy Vluggen

31-Jan-13 5:00

vanikanc wrote:
Is there a way to check, before an insert if that particular order already exists in the table?

Yes; the primary key. That's the one that uniquely identifies a tupel/record. Hence, that's what you'd need to check. Most databases will do this automatic and throw an error if the record already exists.

vanikanc wrote:
if the process fails in the middle, and i want to start it again, if there are already some rows in the table, not to insert those.

Select a list of all primary key-values in the table, and skip those inserts.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
They hate us for our freedom![^]

Eddy Vluggen31-Jan-13 10:35

Eddy Vluggen

31-Jan-13 10:35

Dude, if he is inserting from a file, then there is no primary key until it is inserted Hmmm | :|

--

Aah, good point. We don't show the Autoincrement-value to the user, so the user is using a combination of fields to uniquely identify a record. That used to be the primary-key, until we switched to artificial autoincrement-keys.

You're reading the file on a line to line basis? Don't want it in memory completely, because it'd have to be restarted completely if the process dies half way. It'd be an option to write the "current amount of processed records" to another file. If it crashes, read that file and see how many lines you can safely skip.

A transaction (as said below) is indeed the best idea Smile | :)

Also, it'd be wise to load the file in a separate table first, and move it from there to the required structure.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
They hate us for our freedom![^]

R. Giskard Reventlov31-Jan-13 8:10

R. Giskard Reventlov

31-Jan-13 8:10

Wrap the process in a transaction. If it fails, it will get rolled back and the table will remain empty. At that point you can figure out what went wrong, take any remedial action and run the process again.

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum

me, me, me

vanikanc31-Jan-13 8:21

31-Jan-13 8:21

I was planning to put BEGIN TRANSACTION and COMMIT TRANSACTION, but what I am doing is reading from a file, inserting into the database, reading the next line and inserting into database. I have the insert statement in the C# page.

R. Giskard Reventlov31-Jan-13 10:09

R. Giskard Reventlov

31-Jan-13 10:09

Thsi post might be helpful: transactions with C# and class library[^] or this one: Transactions in .net[^].

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum

me, me, me

Chris Meech31-Jan-13 10:24

Chris Meech

31-Jan-13 10:24

Really good answer mark. This will stop the problem from ocurring in the first place. Smile | :)

Chris Meech
I am Canadian. [heard in a local bar]

In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

R. Giskard Reventlov31-Jan-13 10:25

R. Giskard Reventlov

31-Jan-13 10:25

Thanks.

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum

me, me, me

Corporal Agarn1-Feb-13 7:50

Corporal Agarn

1-Feb-13 7:50

If you are using SQL server 2008 look at the MERGE command. It will Update or Insert.

As to the failure, as stated look at transactions or possibly Try Catch.

vanikanc1-Feb-13 7:55

1-Feb-13 7:55

Thank you.

I am using the begin transaction/commit or rollback in catch block.

Only thing, you have to specify the transaction as part of the sqlcommand object.