We can write a query like below to get the all the Tables which are modified using an ALTER statement in last 10 days. A Table is also considered as modified when an Index on the table is Created or Altered.
SELECT name 'Table',modify_date 'Last Modified Date' FROM sys.tables WHERE DATEDIFF(D,modify_date, GETDATE()) < 10
Note: If a table is never altered after it’s creation then modify_date column value in the sys.objects will be it’s creation date.
In NVARchar DataType column we can store both Single byte and Double byte data. Many a times we want to know how many records have Single byte or Double byte data. Let us understand this with an example.
CREATE DATABASE SqlHintsDemo GO USE SqlHintsDemo GO -- Create Customer Table which has NVARchar column CustomerName CREATE TABLE dbo.Customer (CustomerId int, CustomerName NVarchar(50)) GO --Populate Customer table with single byte and --double byte CustomerName records INSERT INTO dbo.Customer (CustomerId, CustomerName) VALUES (100, 'Basavaraj') INSERT INTO dbo.Customer (CustomerId, CustomerName) VALUES (200, N'尊敬卿') INSERT INTO dbo.Customer (CustomerId, CustomerName) VALUES (300, 'Biradar') GO
| Query to get all the Customers whose CustomerName column contains SINGLE Byte Data |
-- Query to get all the customers whose CustomerName -- column contains SINGLE Byte Data SELECT * FROM dbo.Customer WHERE CustomerName = CAST(CustomerName AS VARCHAR(50))
RESULT:
| Query to get all the Customers whose CustomerName column contains DOUBLE Byte Data |
--Query to get all the customers whose CustomerName --column contains DOUBLE Byte Data SELECT * FROM dbo.Customer WHERE CustomerName != CAST(CustomerName AS VARCHAR(50))
RESULT:
You may also like to read below other popular articles:
Difference Between Sql Server VARCHAR and NVARCHAR Data Type
Difference Between Sql Server VARCHAR and VARCHAR(MAX) Data Type
Difference between Sql Server Char and Varchar Data Type
Difference between DateTime and DateTime2 DataType
Please correct me, if my understanding is wrong. Comments are always welcome.
A friend of mine was needed a script where he wanted a One-to-One record mapping between two tables records which don’t have any common column between them for joining. That is he wanted to match the first record in the first table to the first record in the second table, second record in the first table to the second record in the second table and so. on. Let me represent his requirement pictorially:
[ALSO READ] Joins In Sql Server
We can achieve this by a script like below:
CREATE DATABASE SqlHintsDemo
GO
USE SqlHintsDemo
GO
--- Create Mentor Table and populate Mentor's data
CREATE TABLE Mentor (MentorId int, MentorName Varchar(40))
INSERT INTO Mentor (MentorId, MentorName) VALUES (100, 'Prabhu')
INSERT INTO Mentor (MentorId, MentorName) VALUES (200, 'Basavaraj')
INSERT INTO Mentor (MentorId, MentorName) VALUES (300, 'Sharan')
INSERT INTO Mentor (MentorId, MentorName) VALUES (400, 'Keerthi')
--- Create Mentee Table and populate Mentor's data
CREATE TABLE Mentee (MenteeId int, MenteeName Varchar(40))
INSERT INTO Mentee (MenteeId, MenteeName) VALUES (1, 'Anna')
INSERT INTO Mentee (MenteeId, MenteeName) VALUES (2, 'ShreeGanesh')
INSERT INTO Mentee (MenteeId, MenteeName) VALUES (3, 'Shashank')
--- USE CTE to get the expected results
;WITH MentorTable (CommonId, MentorId, MentorName) AS
(
SELECT ROW_NUMBER() OVER(ORDER BY MentorId) AS CommonId,MentorId, MentorName
FROM Mentor
)
,MenteeTable (CommonId, MenteeId, MenteeName) AS
(
SELECT ROW_NUMBER() OVER(ORDER BY MenteeId) AS CommonId,MenteeId, MenteeName
FROM Mentee
)
SELECT MR.MentorId,MR.MentorName,ME.MenteeId,ME.MenteeName
FROM MentorTable MR
LEFT OUTER JOIN MenteeTable ME
ON MR.CommonId = ME.CommonId
[ALSO READ] Joins In Sql Server