SQL Server Tips and Tricks

  1. 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.
  2. What are Sparse Columns


    Please 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.



 


Write an Article
If you log in, you will be notified when someone leaves a comment.

Other users would like to know if this solution helped you.


© 2022 - ErnesTech - Privacy