Data Type

Question

Data Type

Viewing 15 posts - 16 through 30 (of 41 total)

You must be logged in to reply to this topic. Login to reply

Dave62 SSCertifiable Points: 7459 More actions · Answer 1

Nice question! I had to guess the correct answer. According to the script below the correct answer may not be so simple.

-- Code from the QotD

select * into #temp from

(

select NULL col1, NULL col2

union all

select null, 'Que'

) A

-- Show what's in the temp table

Select * From #temp;

-- Query the data types for the columns in the temp table

SELECT

c.name AS [Column]

, t.name AS [Data Type]

, c.collation_name

, t.max_length

, t.[precision]

, t.scale

FROM

tempdb.sys.columns AS c

INNER JOIN tempdb.sys.types AS t ON t.system_type_id = c.system_type_id

WHERE

object_id = object_id('tempdb..#temp')

ORDER BY

c.column_id;

-- Clean up

Drop Table #temp;

Results:

Column Data Type collation_name max_length precision scale

col1 int NULL 4 10 0

col2 varchar SQL_Latin1_General_CP1_CI_AS 8000 0 0

Not quite the expected length of 3?

Enjoy!

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 2

Dave62 (5/10/2013)
Not quite the expected length of 3?

Try using c.max_length instead of t.max_length.

The column t.max_length is the maximum for the type (varchar); c.max_length is the maximum for this specific column.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Dave62 SSCertifiable Points: 7459 More actions · Answer 3

Hugo Kornelis (5/10/2013)
Try using c.max_length instead of t.max_length.
The column t.max_length is the maximum for the type (varchar); c.max_length is the maximum for this specific column.

Ah, that's more like it. Thanks Hugo!

I haven't finished my first cup of coffee yet. :doze:

PChiragS SSCarpal Tunnel Points: 4965 More actions · Answer 4

PChiragS

SSCarpal Tunnel

Points: 4965

May 10, 2013 at 7:41 am

#1614193

Interesting QoTD...

Thanks..

webrunner SSC-Dedicated Points: 31731 More actions · Answer 5

Great question. I was close but got it wrong. Still, a great teaching question.

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

ssimmons 2102 SSCommitted Points: 1634 More actions · Answer 6

I was pretty sure what the types were, but ran this to make sure.

SELECT * FROM tempdb.INFORMATION_SCHEMA.COLUMNS WHERE OBJECT_ID('tempdb..' + table_name)=OBJECT_ID('tempdb..#temp')

Revenant SSC-Forever Points: 42467 More actions · Answer 7

I got it right and I would like to brag about my educated guess, but I am afraid it was just a lucky guess.

Thanks, Yogesh, and have a great weekend, everyone.

Ken SSCoach Points: 17191 More actions · Answer 8

Revenant (5/10/2013)
I got it right and I would like to brag about my educated guess, but I am afraid it was just a lucky guess.
Thanks, Yogesh, and have a great weekend, everyone.

+1. Just had a feeling. Got lucky on this one!

SQL Muscle SSC-Addicted Points: 453 More actions · Answer 9

I recently ran into this issue with a coworker of mine while working through a data-warehousing job and although I thought I had found a reference elsewhere on the internet specifically for MS SQL Server, it actually just generically says that the implementation of casting a NULL literal can be handled differently internally per DBMS.

Great question of the day!

zymos SSCommitted Points: 1960 More actions · Answer 10

zymos

SSCommitted

Points: 1960

May 10, 2013 at 1:57 pm

#1614310

Thanks for that tricky question. 🙂

zymos SSCommitted Points: 1960 More actions · Answer 11

Thanks for that tricky question on data type. 🙂

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Tried to find a document on why the default is an INT for this type of scenario and my google-fu is failing.

nice question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Anipaul SSC-Insane Points: 24681 More actions · Answer 13

Anipaul

SSC-Insane

Points: 24681

May 12, 2013 at 10:33 pm

#1614491

Nice one...

Bangla Hall of Fame Points: 3137 More actions · Answer 14

Bangla

Hall of Fame

Points: 3137

May 12, 2013 at 11:32 pm

#1614494

Very tricky for me...Great one.....

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 15

Dave62 (5/10/2013)
Nice question! I had to guess the correct answer. According to the script below the correct answer may not be so simple.
-- Code from the QotD
select * into #temp from
(
select NULL col1, NULL col2
union all
select null, 'Que'
) A
-- Show what's in the temp table
Select * From #temp;
-- Query the data types for the columns in the temp table
SELECT
c.name AS [Column]
, t.name AS [Data Type]
, c.collation_name
, t.max_length
, t.[precision]
, t.scale
FROM
tempdb.sys.columns AS c
INNER JOIN tempdb.sys.types AS t ON t.system_type_id = c.system_type_id
WHERE
object_id = object_id('tempdb..#temp')
ORDER BY
c.column_id;
-- Clean up
Drop Table #temp;
Results:
Column Data Type collation_name max_length precision scale
col1 int NULL 4 10 0
col2 varchar SQL_Latin1_General_CP1_CI_AS 8000 0 0
Not quite the expected length of 3?
Enjoy!

You can use this as well

EXEC tempdb..sp_help '#temp';

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537