SQL Server Tips and Tricks

1. Error Debugging: Did you know you could double click on the SQL Error and it would take you straight to the line of code with an error?
2. SP_Who2: Did you know SQL Server comes with really helpful inbuilt Stored Procedures to help you diagnose the problem
   - For example, use #sp_who2 to see what queries are running on the server and if there are any runnaway queries blocking others.
   - You could then kill the Process Id by typing #kill ProcessIDNumber
   
   
3. SQL Server Templates: Did you know SQL Server Management comes with alot of templates you could use?
   - Click on the database, and open a Query Window.
   - Then press: (Ctrl + Alt + T)
        This will open up a Template Window where you could see alot of scripted SQL template you could use.
   
   
4. Date Type of Column: Check the Data Type of the Column in SQL Server
   

   SELECT COLUMN_NAME, DATA_TYPE
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_NAME = 'myTableName'?

5. Check the SQL Server Logs right in SQL Server Management Studio
    - Login to SQL Server
   - Find the "Management Folder"
   - Find SQL Server Logs
   - Right-Click and "View-Logs"
   https://www.learn.microsoft.com/en-us/sql/ssms/tutorials/ssms-tricks?view=sql-server-ver16
   
   
6. Create Short Cut SQL Queries in the SQL Server Management Studio
      - Tools => Option => Keyboard : Query Shortcuts
          - After Query is defined, open a new Query Window and press the short cut key combination and you should see the results.
   
   
7. sp_helpText [NameOfStroredProc] --Will display the help text for that strored Proc
   https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-helptext-transact-sql?view=sql-server-ver16
   
   
8. What does COALESCENCE mean in TSQL?
   - COALESCENCE means if the value is NULL return an empty string
   Read More Here.
   
   
9. Have you ever wondered if the results coming from the Stored Procedure are accurate? well, Stored Procedure Caches the Results, to make sure that the Stored Proc result is fresh, invalidate the Stored Procedure Cached Results by running the query below:
   DBCC FREEPROCCACHE -- This command will clear the cache for all Stored Procs, keep in mind that all Stored Proc will be recompiled which might be expensive when the Database is very busy.
10. What are Sparse Columns
    
    
    Login to See the Rest of the Answer
    
    Answer: Sparse Column is a column that is most likely to be NULL, when you mark a Column as Sparse #SQL Server does some #optimization behind the scene to make sure that Data stored in a #TPH (a Wide Table with some roles of #NULL values ) does not consume a lot of space on disk.
    
    
    2. What is TPH in Entity Framework
       - Read this documentation to understand Model Inheritance and #TPC, TPH, and #TPT
    https://docs.microsoft.com/en-us/ef/core/modeling/inheritance
    
    3. In EF Core, you could use HiLow in the DbContext Model Configuration section to let the EF Core know that on a Certain table you would want the ID to be generated before the insert. This is important when you are relying on one insert of record then retrieve the ID of that record to insert other records into another table and reference the ID as a foreign key. 
    
    Inserting One record and then waiting until the record is inserted to only get the ID creates a round trip to the database which could slow down the application. However, using a HiLow in the OnModelCreate cuts this round trip in one as you would then bundle all your inserts in _dbContext.AddRang() and add several different types of Entity referencing to one Parent ID as a foreign key.

Here are some pro tips for working with T-SQL:

1. Use SET NOCOUNT ON: This statement stops the message indicating the number of rows affected by a query, which can improve performance.

2. Avoid using SELECT *: Instead of selecting all columns, specify only the columns you need. This reduces the amount of data returned and can improve performance.

3. Use stored procedures: Stored procedures can be pre-compiled and cached, which can improve performance. They can also simplify complex queries and make them easier to maintain.

4. Use indexing: Indexes can improve query performance by allowing the database engine to quickly find the data it needs.

5. Use parameterized queries: Parameterized queries can improve performance by reusing execution plans and reducing the risk of SQL injection attacks.

6. Use temporary tables wisely: Temporary tables can be useful, but they can also slow down performance if they are not used properly. Avoid creating temporary tables in loops or for small amounts of data.

7. Use transactions: Transactions ensure that a group of SQL statements are treated as a single unit of work, which can help maintain the consistency of data.

8. Use common table expressions (CTEs): CTEs can simplify complex queries and make them easier to read and maintain.

9. Use window functions: Window functions allow you to perform calculations across multiple rows, which can be useful for tasks like ranking or running totals.

10. Monitor performance: Keep an eye on query performance and use tools like SQL Profiler to identify bottlenecks and optimize your queries.