Finding locks on the table and all locking queries:

        sys.dm_tran_locks Metadata table contains the sessions which are currently locking a resource or waiting for a lock.

Finding if a table is locked :

you can filter on resource_associated_entity_id  with object id. 

SELECT 

* 

from 

sys.dm_tran_locks 

where 

DB_NAME(resource_database_id) = 'dbname' 

and resource_associated_entity_id = object_id('schemaname.tablename')

Finding all locking Queries

sys.dm_exec_requests contains exec requests on the server. It contains the "sql_handle" you can pass it to "sys.dm_exec_sql_text" table values function to get the text corresponding to the handle.

SELECT 
         (select text from sys.dm_exec_sql_text(exrequests.sql_handle))
from
sys.dm_tran_locks dbl
INNER JOIN  sys.dm_exec_requests exrequests on dbl.request_session_id = exrequests.session_i
where
DB_NAME(dbl.resource_database_id) = 'dbname'

Finding the login name of the user:

sys.dm_exec_sessions contains login information of the session.

SELECT
DB_NAME(resource_database_id)
, s.original_login_name
, s.status
, s.program_name
, s.host_name
, (select text from sys.dm_exec_sql_text(exrequests.sql_handle))
,*
from
sys.dm_tran_locks dbl
     JOIN sys.dm_exec_sessions s ON dbl.request_session_id = s.session_id
INNER JOIN  sys.dm_exec_requests exrequests on dbl.request_session_id = exrequests.session_id
where
DB_NAME(dbl.resource_database_id) = 'dbname'

Thanks for reading. Please let me know if there is any more info needed.

DML Operations on Views

Simple views:

     Views with simple select statements are updatable with normal DML statements. More precisely If the database system can determine the reverse mapping from the view schema to the schema of the underlying base tables, then the view is updatable. 

Example:

You can see actual underlying table is updated.

Read Only Views:

If DBMS cannot map the changes to the underlying base tables. 

The views with following clause in the select statements are readonly

1. Group by
2. Distinct
3. aggregates 
4. pivot
5. unpivot

We can use instead of triggers to update these views.

Instead Of Trigger:

Instead Of Insert trigger can be used to perform the operations when there is an insert performaed on the view. Example, we can insert the data into the underlying table when there is an insert performed on the view. 

Example

CREATE TRIGGER [InsteadTrigger_Name] on TestView
INSTEAD OF INSERT
AS
BEGIN

  INSERT INTO [Raghu].[dbo].[Test]
       SELECT Color, Material
       FROM inserted
END;
GO

Please feel free to comment if you need any other information, Or you want to add more.

Scripting the table data

1.       Connect to the server from which you want to copy the data from.

2.       Go to the database from which you want to copy the data,

3.       Right click -> Tasks -> Generate scripts

4.       Choose Object -> Check “Select specific database objects” -> select the table you want to script the data

5.       Go to scripting options -> Advanced -> “Type of data to script” (Choose according to your need)

6.       Next -> Next -> Finish.  It’ll script everything to C:\Users\raghu\Documents\script.sql (you can even give the path at  point 5)

Thanks you for reading. please feel free to contact me if you need any more information.

Views

Types of views:

1. General Views:  These views are normal view, which are just return the results of the underlying tables from one server  without saving the data. 
2. Indexed Views(materialized views): These views store the actual results of the view query in the clustered index structure. We use these views to improve the performance if our view query contains multiple joins and aggregations.
3. Partitioned Views: These views used to get the data from joining partitioned data from the tables which are on the multiple databases and multiple servers.
1. If the all underlying tables are in one server then these views are called "Local Partitioned view".
2. If the underlying tables are in multiple servers then the view is called "Distributed Partitioned view".

Creating General View:

Syntax:

CREATE VIEW [VIEW NAME] 

WITH [OPTION]

AS 

       [SELECT STATEMENT]

GO

Example:

      Creating a view using employee table which returns the employee information along with his manager name. Usually we save only employee manager Id in the table, we need to make a join every time when we need the manager name, instead we can create a view and use it when ever we need same as table.

if not exists(select * from information_schema.tables where table_schema = 'dbo' and table_name = 'Employee')

begin

create table dbo.Employee(Id int, Name Varchar(30),ManagerId int, salary INT)

END

GO

--Creating a view to return the employee and hiss manager

CREATE view dbo.EmployeeManager
WITH SCHEMABINDING

AS

SELECT 

emp.Id,

emp.Name,

mng.Name 'Manager Name',

emp.salary 

from 

dbo.employee emp

INNER JOIN dbo.Employee mng on mng.id = emp.ManagerId

GO

Some points about view:

1. The view creation always start with "CREATE VIEW" key word.
2. The body of the view is general "SELECT" statement, which is subjected to general rules of SELECT.
3. There is no restriction on the columns in the select statement, we can Add new columns(computed or any other) or Remove the columns which we are interested in
4. If the underlying table structure changes, the view will give errors, so we can use "SCHEMABINDING" option while creating a view. so the alter table is not allowed unless we drop the view.
5. Materialized views also created with SCHEMABINDING option.

Creating indexed view:

          Indexed views are SCHEMABINDED views which are indexes defined on it. We can change the above to indexed view by creating a index on it.

CREATE view dbo.EmployeeManager

WITH SCHEMABINDING

AS

SELECT 

emp.Id,

emp.Name,

mng.Name 'Manager Name',

emp.salary 

from 

dbo.employee emp

INNER JOIN dbo.Employee mng on mng.id = emp.ManagerId

GO

CREATE CLUSTERED INDEX idx_cl_id  ON  dbo.EmployeeManager(id);
GO

*) Indexed views store the view results and create the index on them.

Altering View:

We can use "ALTER VIEW" to alter the view definition.

Example: Let's alter the view to add "Manager salary"  column.

ALTER VIEW dbo.EmployeeManager

WITH SCHEMABINDING

AS

SELECT 

emp.Id,

emp.Name,

mng.Name 'Manager Name',
                mng.salary 'ManagerSalary',

emp.salary

from 

dbo.employee emp

INNER JOIN dbo.Employee mng on mng.id = emp.ManagerId

GO

Dropping View:

        1. All the views will be stored in "sys.views" system table. 

Syntax: 

DROP VIEW [VIEWNAME]

Example:

if exists (select 'x' from sys.views where object_id = OBJECT_ID(N' dbo.EmployeeManager'))
BEGIN

       DROP VIEW dbo.EmployeeManager

GO

Uses Of  View:

1. Hide the complexity of the queries: Let us say if you have a query with complex joins and calculations you can just create a view for that query and use it anywhere. It'll make the query more readable.
2. Security: If you have a table which have some security information and you want to share some of it's data to others. You can just create a view with the data you want to share and give permission on the view to user, instead of the table. In such a way you can protect illegal access to the tables
3. Support code referring to Deleted or changed tables: If you have a code which referring to a table and in the process of clean up you decided to delete the table or change the table name. In that case the code referring to old table will break, Instead you can create a view with the old table name and refer it to the new table. In such a you can make the old code work,

PS : It is best practice to check the existence of the view before creation and altering and dropping to avoid the SQL errors in the script.

Thanks for reading. Please feel free to comment if you have any suggestions.

Adding Constraints

Adding Primary Key:

1. Primary key enforces the uniqueness of the data in table
2. Data in the table is organized using the primary key as it creates clustered index
3. It also improves the performance of the WHERE clause etc

While creating Table:

    Just specify the 'primary key' attribute next to the column which you want to add as the primaryKey.

Example:

       Create Table Test(
                Id int primary Key,
                Name varchar(20)
         )

-- Compound Primary key:

       Create Table Test(
                Id int  Not NULL,
                Name varchar(20),
                LastName varchar(30)
                Primary key (Id,Name)
         )

• It'll add the primary key on both the columns (Id, Name).

After Creating Table:

1. We need to use ALTER TABLE syntax to add the primary key to table.

ALTER TABLE Test ADD constraint PrimaryKey_Id_Test primary key(Id)
GO

Adding New Primary Key Column:

     We can just specify the 'primary key' attribute at the end of the add column statement

ALTER TABLE [TABLE NAME] 

ADD [COLUMNNAME] DATATYPE primary key

GO

Foreign Key Constraint:

1. It'll enforce the rightness in the data, when data in one table is dependent on the other
2. It'll allow us to avoid accidentally delete the data in one table while it has dependents in other tables

While creating Table:

    Just specify the 'foreign key' attribute next to the column which you want to add as the foreign key.

Example:

       Create Table Test(

                Id int foreign key references ForeignTable(Id) On Delete [OPTION],

                Name varchar(20)

         )

After Creating Table:

ALTER TABLE Test 

ADD CONSTRAINT ForeignKey_Id_Test foreign key(id) 

references ForeignTable(Id) ON DELETE [OPTION]

GO

And you can specify 4 different key words for [OPTION], Which will let you set what should happen if you try to delete the row in ForeignTable, if there a row referencing the Id of the row which we are trying to delete in ForeignTable.

• ON DELETE RESTRICT (default ): It will gives the error message saying we should delete child rows referencing the Id should be deleted in Test table.
• ON DELETE NO ACTION: same as ON DELETE RESTRICT 
• ON DELETE CASCADE:If we try to delete row in ForeignTable It'll delete all the rows which are referencing the row. It will do automatic clean up. but we might end up deleting important information.
• ON DELETE SET NULL : If we try to delete row in ForeignTable It'll set the NULL for the foreign key column of all the rows which are referencing the row.

Default Constraint:

    Default constraint ensures to add the data when there is no data is specified to  the column while inserting data.

While Creating Table:

Example:

       Create Table Test(

                Id int primary Key,

                Name varchar(20) default 'NONAME'

         )

After Creating Table:

ALTER TABLE Test

add constraint default_Id_Test default 'NONAME' for Name

GO

Dropping constraints:

ALTER TABLE TEST DROP [Constraint Name]

GO

Example:

ALTER TABLE TEST 

DROP DF__Test__LastName__108B795B

GO

1. We can't delete the rows unless we delete the constraints on the tables. 
2. we can find all the constraints in "information_schema.table_constraints" table

Please free to comment if you need any other information.

Creating, Altering and Inserting data into tables

Creating Table:

1. You can find all the table information in "information_schema.tables" system table.
2. It is best practice to find the existence of the table before creating so that even if we run the create script 2nd time, it won't give errors.

Syntax: 

CREATE TABLE [TABLENAME]([columnname] dataType, [columnName] dataType,...)
GO

    

Example:

 CREATE TABLE Test (Id int, Name Varchar(50));

 GO

Example:

Alter Table:

      Alter statement is used to Add or drop the columns and change the column datatypes and also adding the constraints.

Adding column:

Syntax:

ALTER TABLE [TABLENAME] ADD [COLUMNNAME] DATATYPE

 GO

      

Example:

ALTER TABLE Test ADD LastName Varchar(20)

 GO

      

- It will add LastName column to Test table.

Renaming the column:

Syntax:

SP_Rename 'TABLENAME.ColumnName','NewCOLUMNNAME', 'COLUMN'

 GO

      

Example:

SP_Rename 'Test .Name','FirstName', 'COLUMN'

 GO

      

- It will rename Name column FirstName.

Dropping column:

Syntax:

ALTER TABLE [TABLENAME] DROP COLUMN  [COLUMNNAME] 

 GO              

  

Example:

 ALTER TABLE Test DROP COLUMN LastName

 GO              

        

- It'll Drop LastName column form Test table.

Changing Column Datatype:

Syntax:

 ALTER TABLE [TABLENAME] ALTER COLUMN [COLUMNNAME] DATATYPE

 GO              

        

Example: 

 ALTER TABLE Test ALTER COLUMN LastName Varchar(100)

 GO              

   

- It'll change the datatype from varchar(50) to varchar(100)

Inserting Data:

Syntax: 

 INSERT INTO TABLE [TABLENAME] VALUES(1ColumnValue,2ColumnValue,,...)

 GO               

Example:

    INSERT INTO Test Values(1,'Rajesh')

    GO

    INSERT INTO Test Values(2,'Raju')

    GO        

If you want to know adding constraints to table read Adding Constraints.

Please feel free to comment if you need any other help.

Install , Update Nuget Package from Package Manager Console

Open Package Manager Console:

1. Goto Tools -> Library Package Manager >Package Manager Console

2. It'll open command line. 

Installing Package (Install-Package):

1. Use Install-Package command to install the package.(Installs on all projects of your solution)

     Install-Package Newtonsoft.Json

2. Install package to specific project

     Install-Package -ProjectName SampleWebAppAPI  Newtonsoft.Json

3. Installing specific Version of the package

     Install-Package  Newtonsoft.Json -Version 8.0.2

You can use the combination of the options

Update Package (Update-Package):

1. Update all Packages of all the Projects

    

     Update-Package 

2. Update specific package on all the projects 

     Update-Package Newtonsoft.Json

3. Update  package on all the projects to a specific version

     Update-Package Newtonsoft.Json -Version 8.0.2

4. Update Package of a specific project

     Update-Package -ProjectName SampleWebAppAPI Newtonsoft.Json

5. Reinstall the package and all its dependencies

     Update-Package  Newtonsoft.Json -reinstall

You can also use combination of options

Uninstall Package(Uninstall-Package)

1. Uninstall Package 

     Uninstall-Package  Microsoft.ASPNet.WebApi 

Please feel free to comment or contact me. 

Thank you.