Select SQLWorkorderType,sum(sqlworkordertypeid)
from(
select distinct SQLType as [SQLWorkorderType],0 as sqlworkordertypeid
from SQLWorkOrderType WOT
Union
select distinct SqlType as [SqlWorkorderType],sqlworkordertypeid from SQLWorkOrderType WOT
) as B GROUP by SqlWorkorderType
This document will explain the types of Date and Time Data Type in Sql Server and related funtions.
In SQL Server Their are six Date and Time Data Types.
1- Time
2- Date
3-SmallDateTime
4-DateTime
5-DateTime2
6-DateTimeOffSet
Format- hh:mm:ss[.nnnnnnn]
Range- nano seconds upto 100
Query- To create a table with Time Column
CREATE TABLE dizsweb(
dizswebid int Name varchar(50)
start_at TIME(0),
);
The following INSERT statement adds a row to the table
INSERT INTO dizsweb (
dizswebid ,
Name ,
start_at
)
VALUES
( '1'
'John Doe',
'09:30:00'
);
DATE data typeFormat- YYYY-MM-DD
Range- 1 Day
Query to Create a Table with Date Data Type
Create Table Dizsweb (DizswebID int, Name varchar(50), DOB Date)
Insert the data into table
Insert into Dizsweb values( 1,'SQL Azure','1994-07-28')
CREATE TABLE dizsweb (
product_id INT NOT NULL,
valid_from DATE NOT NULL,
valid_to DATE NOT NULL,
amount DEC (10, 2) NOT NULL,
PRIMARY KEY (
product_id,
valid_from,
valid_to
),
FOREIGN KEY (product_id)
REFERENCES production.products (product_id)
);
INSERT INTO dizsweb (
product_id,
valid_from,
valid_to,
amount
)
VALUES
(
1,
'2019-01-01',
'2019-12-31',
400
);
Format- YYYY-MM-DD hh:mm:ss
Range- 1 Minute
Format- YYYY-MM-DD hh:mm:ss[.nnn]
Range- 0.33 Seconds
CREATE TABLE Customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date datetime);
CREATE TABLE Customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25) default 'United States',
Birth_Date datetime);
Format- YYYY-MM-DD hh:mm:ss[.nnnnnnn]
Range- 100 nano Seconds
DECLARE @nowDateTime
DATETIME = GETDATE(),
DateTime2 DATETIME2(3)= SYSDATETIME()
SELECT DATALENGTH(DateTime) 'DateTime Storage Size',
DATALENGTH(@nowDateTime2) 'DateTime2(3) Storage Size'
Format- YYYY-MM-DD hh:mm:ss[.nnnnnnn]
Range- 100 nano seconds and as par the time Zone
CREATE TABLE dizsweb (
dizsweb DATETIMEOFFSET(7)
); INSERT INTO dizsweb (dizsweb)
VALUES(
CAST('2019-02-28 01:45:00.0000000 -08:00' AS DATETIMEOFFSET));
When you upgrade to a new version of SQL Server, there are some critical things you should do to help avoid any surprise SQL Server performance issues. I mean new version of SQL Server is 2016,2017 or 2019, Because Microsoft closed its support up to some extent to older version
Why need the Requirement to upgrade the Version?
Basically it is because Microsoft ended Its support of some versions.
There are two type of Support
1- MainStream Support
2- Extended Support
For Upgrading any Version there are some preparations which need to be taken care.
1- Find the Upgrade Blockers.
2- Clear Understanding of Version and Editions.
3- Clear Understanding of Features introduced and obsolete
3- Make a List of objects which use the features which are obsolete from the newer version and currently in use.
4- Clear Hardware Requirement for newer Versions.
For this Activity i am upgrading the Server from 2012 to 2017.
Process to Upgrade the SQL Server Version.
There are 3 ways to upgrade
- In-Place Upgrade
- Side-by-Side Upgrade
- Rolling Upgrade
Steps for an In-Place Upgrade
In-place upgrades are the easiest to perform, but the most difficult to rollback should there be any issues. The steps involved in an in-place upgrade are as follows:
Verify that backups exist for all databases (user and system).
Review the list of requirements for SQL server 2017 and install whatever is needed.
Install SQL Server 2017
Restore the Backup
Side-by-Side Upgrade
1- Backup all the database
2-Script out any and all necessary system objects.
3-Review the list of requirements for SQL server 2017 and install whatever is needed.
4- Run the script to create the Object
5-Select database(s) to migrate and it take offline.
6-Migrate database to new instance. Repeat for each database.
Rolling Upgrade
1- Select any High-Availability
2- Install new Server instance 2017 and make it as a Secondary node
3- Fail the Primary Node now New Instance will be active and will be Primary Node
Checklist need to Perform Before Planning to Upgrade about version of Server
1-Checking of Deprecated Features in new version
SELECT * FROM sys.dm_os_performance_counters WHERE object_name = 'MSSQL$JFT:Deprecated Features
2- Get the List top indexes which are in use maximum times in the current version.
SELECT OBJECT_NAME(IX.OBJECT_ID) Table_Name
,IX.name AS Index_Name
,IX.type_desc AS Index_Type
,SUM(PS.[used_page_count]) * 8 IndexSizeKB
,IXUS.user_seeks AS NumOfSeeks
,IXUS.user_scans AS NumOfScans
,IXUS.user_lookups AS NumOfLookups
,IXUS.user_updates AS NumOfUpdates
,IXUS.last_user_seek AS LastSeek
,IXUS.last_user_scan AS LastScan
,IXUS.last_user_lookup AS LastLookup
,IXUS.last_user_update AS LastUpdate
FROM sys.indexes IX
INNER JOIN sys.dm_db_index_usage_stats IXUS
ON IXUS.index_id = IX.index_id AND IXUS.OBJECT_ID = IX.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats PS
on PS.object_id=IX.object_id
WHERE OBJECTPROPERTY(IX.OBJECT_ID,'IsUserTable') = 1
GROUP BY
OBJECT_NAME(IX.OBJECT_ID) ,
IX.name ,
IX.type_desc ,
IXUS.user_seeks ,
IXUS.user_scans ,
IXUS.user_lookups,
IXUS.user_updates ,
IXUS.last_user_seek ,
IXUS.last_user_scan ,
IXUS.last_user_lookup ,
IXUS.last_user_update
3- Current Used Space at current instance and Available Space in the new instance.
4- Current OS Hardware Configuration.
5- Scripting of all user defined Objects.
