SQL Theory and ORM Cheetsheet

 4. What is difference between nvarchar and varchar ?

nvarchar stores uncode characters and takes varchar takes ascii characters
http://sqlhints.com/2011/12/23/difference-between-varchar-and-nvarchar/


5. What are clustered and non clustered indexes ?



6. What is transaction ?

7. What is Temporary Table ?

Temporarytables

8. Linq optimization tips ?

linq optimization tips

 8. Lazy loading ?

lazy loading vs eager loading

9. Inline query vs stored procedure ?

• Reduce network traffic -- you have to send the SQL statement across the network. With sprocs, you can execute SQL in batches, which is also more efficient.
• Caching query plan -- the first time the sproc is executed, SQL Server creates an execution plan, which is cached for reuse. This is particularly performant for small queries run frequently.
• Ability to use output parameters -- if you send inline SQL that returns one row, you can only get back a recordset. With sprocs you can get them back as output parameters, which is considerably faster.
• Permissions -- when you send inline SQL, you have to grant permissions on the table(s) to the user, which is granting much more access than merely granting permission to execute a sproc
• Separation of logic -- remove the SQL-generating code and segregate it in the database.
• Ability to edit without recompiling -- this can be controversial. You can edit the SQL in a sproc without having to recompile the application.
• Find where a table is used -- with sprocs, if you want to find all SQL statements referencing a particular table, you can export the sproc code and search it. This is much easier than trying to find it in code.
• Optimization -- It's easier for a DBA to optimize the SQL and tune the database when sprocs are used. It's easier to find missing indexes and such.
• SQL injection attacks -- properly written inline SQL can defend against attacks, but sprocs are better for this protection.

Reference : 1 . stackoverflow

10.  Dapper advantage over EF ?

mediumlink