Solved

SQL Server CAST Usage

Posted on 2010-09-24
6
617 Views
Last Modified: 2012-05-10
Hi Experts,
My sql is;
select  cast(MyTable.MyField1 as varchar(5)) from MyTable

My field is;
COLUMN_NAME      DATA_TYPE      TYPE_NAME
MyField1           5                       smallint

I wonder What is the difference between;
1) cast(MyTable.MyField1 as varchar(5))
2) cast(MyTable.MyField1 as varchar)

Thanks,
0
Comment
Question by:kosturdur
6 Comments
 
LVL 1

Expert Comment

by:boogiefromzk
ID: 33751969
varchar(5) is varchar, limited with 5 characters length
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 150 total points
ID: 33751970
http://msdn.microsoft.com/en-us/library/ms187928.aspxlength    Is an optional integer that specifies the length of the target data type. The default value is 30. so:2) cast(MyTable.MyField1 as varchar)would be the same as2) cast(MyTable.MyField1 as varchar(30) )
0
 
LVL 8

Accepted Solution

by:
Mohit Vijay earned 200 total points
ID: 33751974
varchar(5) will allow 5 character space and only varchar will decide space at run time, best way is to use varchar(5), static allocation.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 9

Assisted Solution

by:auke_t
auke_t earned 150 total points
ID: 33752114
Hi,

a smallint can be anyting between -2^15 (-32,768) to 2^15-1 (32,767). So you'd better use varchar(6)! ;-)


SELECT cast(-32768 as varchar(5))

SELECT cast(-32768 as varchar(6))

Cheers!

Auke
0
 
LVL 5

Author Comment

by:kosturdur
ID: 33752269
3 helpfull comments so 3 answers made me increase the points
0
 
LVL 5

Author Closing Comment

by:kosturdur
ID: 33752277
Thanks all
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question