Re: varchar and nvarchar, which one to use???
From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 12/24/04
- Next message: David Portas: "Re: Help with Query"
- Previous message: JJ Wang: "Re: URGENT: distribution agent failure: NT AUTHORITY\ANONYMOUS LOGON"
- In reply to: === Steve L ===: "varchar and nvarchar, which one to use???"
- Next in thread: Tim S: "Re: varchar and nvarchar, which one to use???"
- Reply: Tim S: "Re: varchar and nvarchar, which one to use???"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 24 Dec 2004 17:56:44 -0500
Steve,
It all depends on what you need to store in that datatype, Unicode or
Non-unicode data. If you will only deal with the normal ascii characters
then it is unlikely you will need an NVARCHAR. If you deal with other
languages and must store a character that requires two bytes to store
properly then you need to use it. I suggest you read up on on Unicode in
BooksOnLine to get a better idea of what that is all about. But the bottom
line is it depends on your business and if you need to characters outside of
the normal ascii range of 0-255. Using them when you don't need that
capability can lead to a lot of wasted space in the tables and indexes.
-- Andrew J. Kelly SQL MVP "=== Steve L ===" <steve.lin@powells.com> wrote in message news:1103927994.413455.11480@c13g2000cwb.googlegroups.com... i'm reading the section of an article at this site <<< http://www.sommarskog.se/dyn-search.html>>> and i was confused the correct use of varchar and nvarchar. I've been using varchar in the past, but i noticed all the code samples use something like this: DECLARE @sql nvarchar(4000) SELECT @sql = 'SELECT * FROM orders' what's the best practice using these data types? for a business like amazon.com as an example, what should be used in the database and in the stored procedures? varchar or nvarchar? can someone educate me on this in a more straight foward fashion? thank you! steve === excerpt from article ==== The Northwind database consistently uses the nvarchar data type, but I've failed to handle this in my procedure. The data in Northwind appears to be restricted to the characters in Windows Latin-1, which covers languages such as English, Spanish, French and German, so if you have a system collation based on this character set, you would never get any incorrect response from search_orders_2. However, a Russian user trying: EXEC search_orders_2 @city = N'Bräcke' would not get the orders from Folk och Fä HB in Bräcke, because Bräcke would be converted to varchar as Bracke. This is how my procedure should have read: (lines 70-71) SELECT @sql2 = @sql2 + N' AND c.City = N' + quotename(@city, '''') And that N which starts an nvarchar literal, should appear with all nvarchar parameters. (The N stands for National, and is taken from the ANSI standard SQL-92.) So why did I not give you the proper code? Well, I figured that many readers would take the code as a template for their own code, and I don't want you to include that N by routine. Because, if you use a nvarchar literal together with an indexed varchar column, the varchar column will be converted to nvarchar, and that precludes use of the index. Thus, you would get the right result, but performance would be poor.
- Next message: David Portas: "Re: Help with Query"
- Previous message: JJ Wang: "Re: URGENT: distribution agent failure: NT AUTHORITY\ANONYMOUS LOGON"
- In reply to: === Steve L ===: "varchar and nvarchar, which one to use???"
- Next in thread: Tim S: "Re: varchar and nvarchar, which one to use???"
- Reply: Tim S: "Re: varchar and nvarchar, which one to use???"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
