Creating a time table in SQL Server
In this video, we'll create a time table, with a row every 10 minutes or 20 seconds, and add columns for hour, minute and second.
My SQL Server Udemy courses are:
70-461, 70-761 Querying Microsoft SQL Server with T-SQL: https://rebrand.ly/querying-microsoft...
98-364: Database Fundamentals (Microsoft SQL Server): https://rebrand.ly/database-fundamentals
70-462 SQL Server Database Administration (DBA): https://rebrand.ly/sql-server-dba
Microsoft SQL Server Reporting Services (SSRS): https://rebrand.ly/sql-server-ssrs
SQL Server Integration Services (SSIS): https://rebrand.ly/sql-server-ssis
SQL Server Analysis Services (SSAS): https://rebrand.ly/sql-server-ssas-mdx
Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): https://rebrand.ly/microsoft-powerpiv...
----
Time tables allow to you pre-calculate times and their properties, such as hour, minute and second. This way, you don't have to work them out multiple times, and you can index them to speed up your queries.
The code in this video is as follows:
DROP TABLE IF EXISTS tblTime;
CREATE TABLE tblTime
(myTime time,
myHour tinyint,
myMinute tinyint,
mySecond tinyint)
INSERT INTO tblTime(myTime)
SELECT DISTINCT CONVERT(TIME,
DATEADD(SECOND, 20 * ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), '2029-12-31 00:00:00'))
FROM sys.objects as A
CROSS JOIN sys.objects AS B
CROSS JOIN sys.objects AS C
UPDATE tblTime
SET myHour = DATEPART(HOUR, myTime),
myMinute = DATEPART(MINUTE, myTime),
mySecond = DATEPART(SECOND, myTime)
SELECT * FROM tblTime
ORDER BY myTime
----
Links to my website are:
70-461, 70-761 Querying Microsoft SQL Server with T-SQL: http://idodata.com/querying-microsoft...
98-364: Database Fundamentals (Microsoft SQL Server): http://idodata.com/database-fundament...
SQL Server Essential in an Hour: http://idodata.com/sql-server-essenti...
70-462 SQL Server Database Administration (DBA): http://idodata.com/sql-server-databas...
DP-300: Administering Relational Databases: http://idodata.com/dp-300-administeri...
Microsoft SQL Server Reporting Services (SSRS): http://idodata.com/microsoft-sql-serv...
SQL Server Integration Services (SSIS): http://idodata.com/sql-server-integra...
SQL Server Analysis Services (SSAS): http://idodata.com/sql-server-ssas-mu...
Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): https://rebrand.ly/microsoft-powerpiv...
1Z0-071 Oracle SQL Developer – certified associate: http://idodata.com/iz0-071-oracle-sql...
SQL for Microsoft Access: http://idodata.com/sql-for-microsoft-...
DP-900: Microsoft Azure Data Fundamentals: http://idodata.com/dp-900-microsoft-a...
Watch video Creating a time table in SQL Server online, duration hours minute second in high quality that is uploaded to the channel SQL Server 101 14 September 2023. Share the link to the video on social media so that your subscribers and friends will also watch this video. This video clip has been viewed 1,097 times and liked it 34 visitors.
