SQL Study (edit)

SQL Interview Questions

https://www.interviewbit.com/sql-interview-questions/

What C# Datatype is mapped with which Datatype in SQL Server?

https://www.dotnettricks.com/learn/entityframework/entity-framework-interview-questions

Entity Framework Interview Questions

https://www.fullstack.cafe/blog/entity-framework-interview-questions

Example of Code First Approach

https://www.c-sharpcorner.com/UploadFile/abhikumarvatsa/code-first-approach-in-entity-framework/

What are the advantages/disadvantages of Code First Approach?

Advantages:

  • Based on business objects we can decide the database structure.
  • We can decide which classes need to be serialized and can specify the collection to eager load.
  • Good for smaller applications.

Disadvantages:

  • All database related stuffs should be included in the code.
  • For Stored Procs, we need to use the Fluent APIs to write it in code.
  • Not good for data intensive applications.

What are the advantages of Model First Approach?

Model first approach gives the flexibility to design the Entity Models independently and gives an option to improve at later stages.
  • Model classes can be created by drawing it in the edmx designer, so no much of database is required.

What are the advantages and disadvantages of Database First Approach?

Advantages:

  • Easy to create entity models if there is an existing database.
  • Preferred approach for data intensive applications.

Disadvantages:

  • Once we create a edmx file from an existing database, huge pile of code is generated.
  • If we want to add the additional functionality to the models generated, we need to extend the models.

Drop all tables in SQL Server

https://stackoverflow.com/questions/8439650/how-to-drop-all-tables-in-a-sql-server-database

Drop all tables in SQL Server

DECLARE @Sql NVARCHAR(500);
DECLARE @Cursor CURSOR;
SET @Cursor = CURSOR FAST_FORWARD
FOR SELECT DISTINCT 
 sql = 'ALTER TABLE [' + tc2.TABLE_SCHEMA + '].[' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + '];'
 FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
 LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME = rc1.CONSTRAINT_NAME;
OPEN @Cursor;
FETCH NEXT FROM @Cursor INTO @Sql;
WHILE(@@FETCH_STATUS = 0)
 BEGIN
 EXEC sp_executesql 
 @Sql;
 FETCH NEXT FROM @Cursor INTO @Sql;
 END;
CLOSE @Cursor;
DEALLOCATE @Cursor;
GO
EXEC sp_MSforeachtable 
 'DROP TABLE ?';
GO