Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Difference between INFORMATION_SCHEMA.COLUMNS and sys.columns Expand / Collapse
Author
Message
Posted Thursday, August 21, 2014 10:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:32 AM
Points: 2,418, Visits: 6,704
Lynn Pettis (8/21/2014)
Eirikur Eiriksson (8/21/2014)
Lynn Pettis (8/21/2014)
Eirikur Eiriksson (8/21/2014)
Quick note, INFORMATION_SCHEMA is an ANSI compliant metadata view collection added in 2000 if I remember correctly, sys.* is SQL Server specific.



It may be but it isn't necessarily implemented by all RDBS vendors. Plus, the there really isn't truly portable SQL code either.


And even if it was implemented by most, any cross platform attempt would most likely fail anyway when it comes to using the metadata.


Which is why I will stay with the system views, more information available if needed. If I switch RDBMS I'll just have to learn new things anyway.


Same here, especially when the INFORMATION_SCHEMA objects are mostly views querying the sys.* objects. The following definitions clearly show the difference

CREATE VIEW sys.tables AS
SELECT o.name, o.object_id, o.principal_id, o.schema_id, o.parent_object_id,
o.type, o.type_desc, o.create_date, o.modify_date,
o.is_ms_shipped, o.is_published, o.is_schema_published,
isnull(ds.indepid, 0) AS lob_data_space_id,
rfs.indepid AS filestream_data_space_id,
o.property AS max_column_id_used,
o.lock_on_bulk_load, o.uses_ansi_nulls, o.is_replicated, o.has_replication_filter,
o.is_merge_published, o.is_sync_tran_subscribed, o.has_unchecked_assembly_data,
lob.intprop AS text_in_row_limit,
o.large_value_types_out_of_row,
o.is_tracked_by_cdc,
o.lock_escalation_option AS lock_escalation,
ts.name AS lock_escalation_desc,
o.is_filetable,
o.is_memory_optimized,
o.durability_option as durability,
d.name as durability_desc
FROM sys.objects$ o
LEFT JOIN sys.sysidxstats lob ON lob.id = o.object_id AND lob.indid <= 1
LEFT JOIN sys.syssingleobjrefs ds ON ds.depid = o.object_id AND ds.class = 8 AND ds.depsubid <= 1 -- SRC_INDEXTOLOBDS
LEFT JOIN sys.syssingleobjrefs rfs ON rfs.depid = o.object_id AND rfs.class = 42 AND rfs.depsubid = 0 -- SRC_OBJTOFSDS
LEFT JOIN sys.syspalvalues ts ON ts.class = 'LEOP' AND ts.value = o.lock_escalation_option
LEFT JOIN sys.syspalvalues d ON d.class = 'DOPT' AND d.value = o.durability_option
WHERE o.type = 'U'


CREATE VIEW INFORMATION_SCHEMA.TABLES
AS
SELECT
DB_NAME() AS TABLE_CATALOG,
s.name AS TABLE_SCHEMA,
o.name AS TABLE_NAME,
CASE o.type
WHEN 'U' THEN 'BASE TABLE'
WHEN 'V' THEN 'VIEW'
END AS TABLE_TYPE
FROM
sys.objects o LEFT JOIN sys.schemas s
ON s.schema_id = o.schema_id
WHERE
o.type IN ('U', 'V')

Post #1605910
Posted Thursday, August 21, 2014 3:55 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 2,268, Visits: 3,426
This is an easy one:
ALWAYS use sys.; NEVER use INFORMATION_SCHEMA.

Not only might I_S be inaccurate, I've found it's also much slower and much more prone to locking/deadlocking. The I_S view definitions perhaps don't look like they should be, but they are.


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1606059
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse