Posts

Showing posts with the label T-SQL

Hey, I rewrote UNION by way of UNPIVOT

Yesterday a team mate of mine asked me to review a piece of SQL script that looked like a good candidate for UNPIVOT. Below is the amended version of that code (I obfuscated the data and added a simple data initialization using the WITH clause): ;with u as ( -- data setup select * from ( values(1, 1, 1, 1), (2, 1, 1, 0), (3, 1, 0, 0), (4, 0, 0, 0) ) t(UserID, HasDbSkills, HasBackendSkills, HasFrontEndSkills) ) select * from ( select UserID, 0 as SkillType, 1 as DevelopmentSkill from u where HasDbSkills = 1 union select UserID, 1 as SkillType, 1 as DevelopmentSkill from u where HasBackendSkills = 1 union select UserID, 2 as SkillType, 1 as DevelopmentSkill from u where HasFrontEndSkills = 1 ) t(UserID, SkillType, DevelopmentSkill) order by UserID, SkillType Indeed, after a quick analysis, this script turned out to be ripe for UNPIVOT:...
Post a Comment
Read more

Hey, I rewrote UNION by way of UNPIVOT

Yesterday a team mate of mine asked me to review a piece of SQL script that looked like a good candidate for UNPIVOT. Below is the amended version of that code (I obfuscated the data and added a simple data initialization using the WITH clause): ;with u as ( -- data setup select * from ( values(1, 1, 1, 1), (2, 1, 1, 0), (3, 1, 0, 0), (4, 0, 0, 0) ) t(UserID, HasDbSkills, HasBackendSkills, HasFrontEndSkills) ) select * from ( select UserID, 0 as SkillType, 1 as DevelopmentSkill from u where HasDbSkills = 1 union select UserID, 1 as SkillType, 1 as DevelopmentSkill from u where HasBackendSkills = 1 union select UserID, 2 as SkillType, 1 as DevelopmentSkill from u where HasFrontEndSkills = 1 ) t(UserID, SkillType, DevelopmentSkill) order by UserID, SkillType Indeed, after a quick analysis, this script turned out to be ripe for UNPIVOT:...
Post a Comment
Read more

T-SQL: COALESCE returns int for integer operations

DECLARE @can_import BIT PRINT CAST (SQL_VARIANT_PROPERTY( COALESCE (@can_import, 0), 'BaseType') AS VARCHAR(20)) Result: int
Post a Comment
Read more

CREATE DATABASE (T-SQL)

Create a SQL Server Database using T-SQL Your need to create a database by using T-SQL. The simplest command you can issue to achieve this is: CREATE DATABASE <database_name> If you open the database, you will notice that its metadata has been set to some values and perhaps, the database already contains tables, stored procedures, UDFs and other objects. How does this magic work? Actually, the SQL Server derives a new database from the model database. Below is how  MSDN explains this process: All user-defined objects in the model database are copied to all newly created databases. You can add any objects, such as tables, views, stored procedures, data types, and so on, to the model database to be included in all newly created databases. When a CREATE DATABASE database_name statement is specified without additional size parameters, the primary data file is made the same size as the primary file in the model database. Unless FOR ATTACH is specified, each new database in...
Post a Comment
Read more