SQL Server: Date and Time Data Types (Size and Ranges)

hnrsj

Thank you for this reference, whomever made this chart!

Tip: DateTime2(3) has the same number of digits as DateTime but uses 7 bytes of storage instead of 8 bytes (SQLHINTS- DateTime Vs DateTime2).

Advertisements

SQL Server Table Smells – Simple-Talk

Here is the corrected version of Phil’s table smell:
Note: This needs to be run per database.

create proc dbo.sp_CodeSmells
as
;WITH TableSmells (TableName, Problem, Object_ID )AS
(
SELECT object_schema_name(Object_ID)+'.'+object_name(Object_ID), Problem,Object_ID FROM
(
SELECT object_id, 'wide (more than 15 columns)'
FROM sys.tables /* see whether the table has more than 15 columns */
WHERE max_column_id_used>15
UNION ALL
SELECT DISTINCT sys.tables.object_id, 'heap'
FROM sys.indexes/* see whether the table is a heap */
INNER JOIN sys.tables ON sys.tables.object_id=sys.indexes.object_id
WHERE sys.indexes.type=0
UNION ALL
SELECT sys.tables.object_id, 'No primary key'
FROM sys.tables/* see whether the table has a primary key */
WHERE objectproperty(object_id,'TableHasPrimaryKey') = 0
UNION ALL
SELECT sys.tables.object_id, 'No index at all'
FROM sys.tables /* see whether the table has any index */
WHERE objectproperty(object_id,'TableHasIndex') = 0
UNION ALL
SELECT sys.tables.object_id, 'No candidate key'
FROM sys.tables/* if no unique constraint then it isn't relational */
WHERE objectproperty(object_id,'
TableHasUniqueCnst') = 0
AND objectproperty(object_id,'
TableHasPrimaryKey') = 0
UNION ALL
SELECT DISTINCT object_id, '
disabled Index(es)'
FROM sys.indexes /* don'
t leave these lying around */
WHERE is_disabled=1
UNION ALL
SELECT DISTINCT parent_object_id, 'disabled constraint(s)'
FROM sys.check_constraints /* hmm. i wonder why */
WHERE is_disabled=1
UNION ALL
SELECT DISTINCT parent_object_id, 'untrusted constraint(s)'
FROM sys.check_constraints /* ETL gone bad? */
WHERE is_not_trusted=1
UNION ALL
SELECT DISTINCT parent_object_id, 'disabled FK'
FROM sys.foreign_keys /* build script gone bad? */
WHERE is_disabled=1
UNION ALL
SELECT DISTINCT parent_object_id, 'untrusted FK'
FROM sys.foreign_keys /* Why do you have untrusted FKs?
Constraint was enabled without checking existing rows;
therefore, the constraint may not hold for all rows. */
WHERE is_not_trusted=1
UNION ALL
/*
SELECT sys.tables.object_id, 'unrelated to any other table'
FROM sys.tables
LEFT OUTER join
(SELECT referenced_object_id AS table_ID
FROM sys.foreign_keys
UNION ALL
SELECT parent_object_id
FROM sys.foreign_keys
)referenced(table_ID)
ON referenced.table_ID=sys.Tables.object_ID
WHERE referenced.table_id IS null*/
SELECT sys.tables.object_id, 'unrelated to any other table'
FROM sys.tables /* found a simpler way! */
WHERE objectpropertyex(object_id,'TableHasForeignKey')=0
AND objectpropertyex(object_id,'TableHasForeignRef')=0
UNION ALL
SELECT DISTINCT object_id, 'unintelligible column names'
FROM sys.columns /* column names with no letters in them */
WHERE name COLLATE Latin1_general_CI_AI
NOT LIKE '%[A-Z]%' COLLATE Latin1_general_CI_AI
UNION ALL
SELECT DISTINCT object_id, 'non-compliant column names'
FROM sys.columns /* column names that need delimiters*/
WHERE name COLLATE Latin1_general_CI_AI
LIKE '%[^_@$#A-Z0-9]%' COLLATE Latin1_general_CI_AI
UNION ALL
SELECT DISTINCT parent_id, 'has a disabled trigger'
FROM sys.triggers
WHERE is_disabled=1 AND parent_id>0
UNION ALL
SELECT sys.tables.object_id, 'can''t be indexed'
FROM sys.tables/* see whether the table has a primary key */
WHERE objectproperty(object_id,'IsIndexable') = 0
)f(Object_ID,Problem)
)
SELECT TableName,
CASE WHEN count(*)>1 THEN /*only do correlated subquery when necessary*/
stuff(( SELECT ', '+Problem
FROM TableSmells t2
WHERE t1.TableName = t2.TableName
ORDER BY Problem
FOR XML PATH(''), TYPE).value(N'(./text())[1]',N'varchar(8000)'),1,2,'')
ELSE max(Problem) END
FROM TableSmells t1 WHERE OBJECTPROPERTYEX(t1.Object_ID, 'IsTable')=1
GROUP BY TableName;

Frustration with Bad Design

“This is not how you handle things. When you find security issues, and they don’t get fixed, it’s one thing to attempt to prove a point with a PEN test. It’s quite another to publicly expose information. You might find yourself in trouble, and you should be in trouble. This is a violation of the professional responsibility you undertake when working for someone.

This is the type of frustration that occurs in many IT workers. I’ve seen more than a few people working in technology that are sure they know how to properly configure and manage an application. They know how to set up security, and they become upset with a company that doesn’t do a good job of running internal systems. They know that the architecture chosen for their application will fail when a load is applied.

There are some smart people in IT, but sometimes they think they’re smarter than they are. Bad design, bad decisions, mistakes, even poor security practices will occur. However it’s usually not your company, and it’s not your place to prove that there is a flaw in a system. It’s especially true that it’s not your place to prove things without having been given permission to do so. Proving a point on your own is something children do, not professionals.

When you find problems in your organization, it is your responsibility to report them. I hope you think it’s your job as a professional to do the best job you can, following the best practices as we know them. It’s also your decision to choose to leave a job if you can’t go along with, or abide by, the decisions made by your management.

If your company has chosen poorly in their technology decisions, I understand your frustration. I’ve often shared it, but I’d advise you to do what I’ve done. State your objections and either support the chosen path or find another job.”

-written by Steve Jones from SQLServerCentral.com