SQL Server Tips and Tricks

1. 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.
   
   
2. 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
   
   
3. What does COALESCENCE mean in TSQL?
   - COALESCENCE means if the value is NULL return an empty string
   Read More Here.
   
   
4. 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.
5. 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.