What Is the Effect of Omitting Size in Nvarchar Declaration

What is the effect of omitting size in nvarchar declaration

If you omit the size, it defaults to 30. See this:

http://msdn.microsoft.com/en-us/library/ms186939.aspx

To see this in action, try executing the following statements:

--outputs: 12345678901234567890.098765432 
select cast (12345678901234567890.098765432 as nvarchar)

--throws "Arithmetic overflow error converting expression to data type nvarchar."
select cast (12345678901234567890.0987654321 as nvarchar) 

--outputs: 12345678901234567890.0987654321 
select cast (12345678901234567890.0987654321 as nvarchar(31))

Per @krul's comment; 30 is the default length for CAST; however the default length for a data definition or variable declaration is 1.

NB: There's also an STR function which converts numeric fields to strings, for which the default length is 10.

--outputs: 1234567890
select str(1234567890)

--outputs: **********
select str(12345678901)

--outputs: 12345678901
select str(12345678901,11)

Characters omitted while doing PRINT

From the docs:

Remarks

A message string can be up to 8,000 characters long if it is a non-Unicode string, and 4,000 characters long if it is a Unicode string. Longer strings are truncated. The varchar(max) and nvarchar(max) data types are truncated to data types that are no larger than varchar(8000) and nvarchar(4000).

If you've got Unicode NVARCHAR the maximum size is 4000

What is the difference between varchar and nvarchar?

An nvarchar column can store any Unicode data. A varchar column is restricted to an 8-bit codepage. Some people think that varchar should be used because it takes up less space. I believe this is not the correct answer. Codepage incompatabilities are a pain, and Unicode is the cure for codepage problems. With cheap disk and memory nowadays, there is really no reason to waste time mucking around with code pages anymore.

All modern operating systems and development platforms use Unicode internally. By using nvarchar rather than varchar, you can avoid doing encoding conversions every time you read from or write to the database. Conversions take time, and are prone to errors. And recovery from conversion errors is a non-trivial problem.

If you are interfacing with an application that uses only ASCII, I would still recommend using Unicode in the database. The OS and database collation algorithms will work better with Unicode. Unicode avoids conversion problems when interfacing with other systems. And you will be preparing for the future. And you can always validate that your data is restricted to 7-bit ASCII for whatever legacy system you're having to maintain, even while enjoying some of the benefits of full Unicode storage.

Can I use output variables to get a value?

You had it close -- After replacing your column names to match with what is in my user table, this worked for me. (I think I replaced everything back).

On your Else, you're basically doing a second lookup by username - which is unnecessary in this case.

ALTER Procedure Check_User_Name
  (
@username varchar(25),
@password varchar(100),
 @role_id integer,
 @idn nvarchar(20) output
 )
 As
Begin
        IF NOT EXISTS(SELECT idn
         FROM [user] WHERE username = @username)
            BEGIN 
                INSERT INTO [user] (username,[password], role_id) VALUES 
                 (@username, @password) 
                select @idn=idn from [user] WHERE username = @username
                Print 'UserName inserted successfully'
            End
            Else
               Begin
                    Print 'UserName already exists'
                    SELECT @idn = idn FROM [user] 
                    WHERE username = @username
               End 

 END
 GO

Execute with:

 DECLARE @idnOut nVarChar(20)
 exec Check_User_Name @username = 'user2127184', @password = 'asdf', @role_id = 0,  @idn = @idnOut OUTPUT
 SELECT @idnOut

Are there any disadvantages to always using nvarchar(MAX)?

Same question was asked on MSDN Forums:

• Varchar(max) vs Varchar(255)

From the original post (much more information there):

When you store data to a VARCHAR(N) column, the values are physically stored in the same way. But when you store it to a VARCHAR(MAX) column, behind the screen the data is handled as a TEXT value. So there is some additional processing needed when dealing with a VARCHAR(MAX) value. (only if the size exceeds 8000)

VARCHAR(MAX) or NVARCHAR(MAX) is considered as a 'large value type'. Large value types are usually stored 'out of row'. It means that the data row will have a pointer to another location where the 'large value' is stored...

Select does not work with variable in SQL Server 2017

You need to specify a length parameter for the varchar type: What is the effect of omitting size in nvarchar declaration

When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.

This means your query is actually doing SELECT * FROM UserList WHERE UserName = 'a'.

So you want:

DECLARE @userName AS nvarchar(8)
SET @userName = 'aliserin'
SELECT * FROM dbo.UserList WHERE UserName = @userName 

You can also simplify this by using the DECLARE = syntax and omitting the AS keyword (I avoid using AS for types as it's usually used to alias column names)

DECLARE @userName nvarchar(8) = 'aliserin'
SELECT * FROM dbo.UserList WHERE UserName = @userName 

Ungrouping effect?

Using Sql Server 2005, UNPIVOT, and CTE you can try something like

DECLARE @Table TABLE(
        id VARCHAR(20),
        allocated INT,
        unallocated INT
)

INSERT INTO @Table SELECT 'foo', 2, 0
INSERT INTO @Table SELECT 'bar', 1, 2

;WITH vals AS (
        SELECT  *
        FROM    
        (
            SELECT  id,
                    allocated,
                    unallocated
            FROM    @Table
        ) p
        UNPIVOT (Cnt FOR Action IN (allocated, unallocated)) unpvt
        WHERE   Cnt > 0
)
, Recurs AS (
        SELECT  id,
                Action,
                Cnt - 1 Cnt
        FROM    vals
        UNION ALL
        SELECT  id,
                Action,
                Cnt - 1 Cnt
        FROM    Recurs
        WHERE   Cnt > 0

)
SELECT  id,
        Action
FROM    Recurs
ORDER BY id, action

---

Related Topics