sys.dm_os_sys_memory is a dynamic management view (DMV) in SQL Server that provides information about all the data pages that are currently loaded into the SQL Server buffer pool. The buffer pool is the memory area SQL Server uses to store pages from databases so that it can reduce disk I/O by accessing data directly from memory.

Key Columns:

• database_id: The ID of the database to which the cached page belongs.
• page_id: The unique ID of the page in the buffer pool.
• page_type: The type of page (e.g., data page, index page).
• row_count: Number of rows on the page.
• free_space_in_bytes: Free space on the page, in bytes.

Use Case:

You can use this view to analyze how SQL Server is using its buffer pool, such as identifying which databases or objects are consuming the most memory, and understanding how data is cached.

Example Query:

SELECT database_id, COUNT(*) AS cached_pages_count
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id;

sys.dm_db_index_usage_stats is a DMV in SQL Server that returns information about how indexes are being used. It provides statistics about index usage, such as how often they are scanned, seeked, or updated, which can help in determining whether indexes are beneficial or if they need to be optimized.

Key Columns:

• database_id: ID of the database where the index is located.
• object_id: ID of the table or view that contains the index.
• index_id: ID of the index being referenced.
• user_seeks: Number of times the index was used in an index seek operation (indicating efficient use).
• user_scans: Number of times the index was used in an index scan operation.
• user_lookups: Number of times the index was used in a lookup operation (for non-clustered indexes).
• user_updates: Number of times the index was updated (indicating write operations that affect the index).
• last_user_seek: The last time the index was used in an index seek.
• last_user_scan: The last time the index was used in a scan.
• last_user_update: The last time the index was updated.

Use Case:

• You can use this DMV to identify underutilized indexes that may not be used frequently or may not provide performance benefits.
• It can help in identifying heavily scanned or updated indexes that may need optimization or restructuring.

Example Query:

SELECT 
    db_name(ius.database_id) AS database_name,
    o.name AS table_name,
    i.name AS index_name,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates,
    ius.last_user_seek,
    ius.last_user_scan,
    ius.last_user_update
FROM sys.dm_db_index_usage_stats AS ius
JOIN sys.indexes AS i
    ON i.object_id = ius.object_id 
    AND i.index_id = ius.index_id
JOIN sys.objects AS o
    ON i.object_id = o.object_id
WHERE ius.database_id = DB_ID() -- Current database
ORDER BY ius.user_seeks DESC;

Explanation:

• This query retrieves the table and index names along with the counts for how often the index was used in seeks, scans, lookups, and updates.
• The ORDER BY ius.user_seeks DESC ensures that the indexes that are most frequently sought are listed first.
• The query also includes timestamps of when the index was last used in these operations.

sys.dm_exec_query_stats is a Dynamic Management View (DMV) in SQL Server that provides aggregate performance statistics for queries executed on the SQL Server instance. It contains information about the execution of queries, including how often they are executed and the resources they consume.

Key Columns:

• sql_handle: A unique identifier for the SQL text associated with the query. This handle can be used to retrieve the actual SQL text from sys.dm_exec_sql_text.
• execution_count: The number of times the query has been executed.
• total_logical_reads: Total number of logical reads (pages read from the buffer cache) performed by the query.
• total_logical_writes: Total number of logical writes (pages written to the buffer cache) performed by the query.
• total_worker_time: Total amount of CPU time (in milliseconds) used by the query.
• total_elapsed_time: Total time (in milliseconds) taken by the query to execute.
• creation_time: The time when the query plan was created.
• last_execution_time: The time when the query was last executed.

Use Case:

This DMV is useful for performance tuning and troubleshooting. It helps identify which queries consume the most resources and which might benefit from optimization. You can analyze execution patterns, resource usage, and query performance.

Example Query:

SELECT 
    qs.sql_handle, 
    qs.execution_count, 
    qs.total_logical_reads, 
    qs.total_logical_writes,
    qs.total_worker_time, 
    qs.total_elapsed_time,
    st.text AS sql_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_worker_time DESC;

Use unique constraints on one or more columns in the table. Example:

CREATE TABLE Persons
(
  P_Id int NOT NULL UNIQUE,
  LastName varchar(255) NOT NULL UNIQUE,
  FirstName varchar(255) NOT NULL UNIQUE, 
  Address varchar(255),
  City varchar(255)
)

Alter existing table as below

ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

If you are using a front-end application to populate the table in the database. Do your validation select query from the application on the database to check for duplicates before inserting into the database. Using constraints will prevent duplicates by throwing an exception.

1. SYSDATETIME() - To returns the server’s date and time.
2. SYSDATETIMEOffset() - It returns the server’s date and time, along with UTC offset.
3. GETUTCDATE() - It returns date and GMT (Greenwich Mean Time ) time.
4. GETDATE() - It returns server date and time.

SELECT employee_id, salary,
       SUM(salary) OVER (ORDER BY employee_id) AS running_total
FROM employees;

• The SUM() function with the OVER clause adds up salaries in order.
• ORDER BY defines the sequence for the running total.

Return the first non-null value in a list.

SELECT COALESCE(NULL, NULL, 10, NULL, NULL, 'sQl');

Output:
10

This query uses a Common Table Expression (CTE), which is a temporary named result set that can be referenced within a single query.

WITH product_customers AS (
  SELECT name, age
  FROM customer_data
  WHERE product = 'widget'
)
SELECT AVG(age) AS avg_age
FROM product_customers;

The CONCAT() function adds two or more strings together. Syntax is CONCAT(string1, string2, …., string_n)

SELECT CONCAT('SQL', ' ', 'vs', ' ', 'Python');

Output:
SQL vs Python

The CONCAT_WS() function adds two or more strings together with a separator. Syntax is CONCAT_WS(separator, string1, string2, …., string_n)

SELECT CONCAT_WS('-', 'SQL',  'vs', 'Python');

Output:
SQL-vs-Python

SELECT
   Id,
   FileInBase64, --the base64 value we want converted to varbinary
   CAST(FileInBase64 AS xml).value('xs:base64Binary(.)', 'varbinary(max)') AS FileInBinary
FROM Files

SELECT CONVERT (data_type(length)),Date, DateFormatCode)

• Data_Type - We need to define data type along with length. In the date function, we use Varchar(length) data types.
• Date - We need to specify the date that we want to convert.
• DateFormatCode - We need to specify DateFormatCode to convert a date in an appropriate form. We will explore more on this in the upcoming section.

Date format option & SQL convert date output

1. 0 - [MMM DD YYYY hh:mm(AM/PM)]
2. 1 - [MM/DD/YY] (Standard: U.S.A.)
3. 2 - [YY.MM.DD] (Standard: ANSI)
4. 3 - [DD/MM/YY] (Standard: British/French)
5. 4 - [DD.MM.YY] (Standard: German)
6. 5 - [DD-MM-YY] (Standard: Italian)
7. 6 - [DD MMM YY] (Standard: Shortened month name)
8. 7 - [MMM DD, YY] (Standard: Shortened month name)
9. 8 - [HH:MM: SS] (Standard: 24 hour time)
10. 9 - [MMM DD YYYY hh:mm:ss:mmm(AM/PM)] (Standard: Default + milliseconds)
11. 10 - [MM-DD-YY] (Standard: USA)
12. 11 - [YY/MM/DD] (Standard: JAPAN)
13. 12 - [YYMMDD] (Standard: ISO)
14. 13 - [DD MMM YYYY HH:MM:SS:MMM] (Standard: Europe default + milliseconds)
15. 14 - [HH:MM:SS:MMM] (Standard: 24 hour time with milliseconds)
16. 20 - [YYYY-MM-DD HH:MM:SS] (Standard: ODBC canonical)
17. 21 - [YYYY-MM-DD HH:MM:SS.mmm] (Standard: ODBC canonical with milliseconds)
18. 22 - [mm/dd/yy hh:mm:ss (AM/PM)] (Standard: USA with Time AM/PM)
19. 23 - [yyyy-mm-dd]
20. 24 - [hh:mm:ss]
21. 25 - [mm:ss.m]
22. 26 - [yyyy-dd-mm hh:mm:ss.mmm]
23. 27 - [mm-dd-yyyy hh:mm:ss.mmm]
24. 28 - [mm-yyyy-dd hh:mm:ss.mmm]
25. 29 - [mm:ss.m]
26. 30 - [dd-yyyy-mm hh:mm:ss.mmm]
27. 31 - [yyyy-dd-mm]
28. 32 - [mm-dd-yyyy]
29. 33 - [mm-yyyy-dd]
30. 34 - [dd/mm/yyyy]
31. 35 - [dd-yyyy-mm]
32. 100 - [MMM DD YYYY HH: SS (AM/PM)] (Standard: Default)
33. 101 - [MM/DD/YYYY] (Standard: USA)
34. 102 - [YYYY.MM.DD] (Standard: ANSI)
35. 103 - [DD/MM/YYYY] (Standard: British / French)
36. 104 - [DD.MM.YY] (Standard: German)
37. 105 - [DD-MM-YY] (Standard: Italian)
38. 106 - [DD MMM YYYY] (Standard: Shortened month name)
39. 107 - [MMM DD,YYYY] (Standard: Shortened month name)
40. 108 - [HH:MM: SS] (Standard: 24 hour time)
41. 109 - [MMM DD YYYY hh:mm:ss:mmm(AM/PM)] (Standard: Default + milliseconds)
42. 110 - [MM- DD-YY] (Standard: USA)
43. 111 - [YYYY/MM/DD] (Standard: JAPAN)
44. 112 - [YYYYMMDD] (Standard: ISO)
45. 113 - [DD MMM YYYY HH:MM:SS: MMM] (Standard: Europe default + milliseconds)
46. 114 - [HH:MM:SS: MMM] (Standard: 24 hour time with milliseconds)
47. 120 - [YYYY-MM-DD HH:MM: SS] (Standard: ODBC canonical)
48. 121 - [YYYY-MM-DD HH:MM: SS.mmm] (Standard: ODBC canonical with milliseconds)
49. 126 - [YYYY-MM-DDTHH:MM: SS.mmm] (Standard: ISO8601)
50. 127 - [YYYY-MM-DDTHH:MM: SS.mmm]
51. 130 - [DD MMM YYYY hh:mi:ss:mmm(AM/PM)] (Standard: Islamic/Hijri date)
52. 131 - [DD MMM YYYY hh:mm:ss:mmm(AM/PM)]