Security Features – SQL Server 2016

SQL Server 2016 is the new version of SQL Server introduced by Microsoft. There are many new features that have been introduced in SQL Server 2016. Below are the new security features introduced in SQL Server 2016.

Always Encrypted
Row Level Security
Dynamic masking
Enhanced security of duties
Enhanced SQL Server editing
Transparent data encryption
Policy based management

Angular JS

AngularJS is a JavaScript framework that can be used for creating Single Page Web applications. AngularJS is a JavaScript framework that extends the html by providing directives. AngularJS simplifies our work and makes code more readable. AngularJS provides directives which can be directly added to the Html tags for performing very complex tasks.

There are two ways in which you can add AngularJS in your web application:

Method 1 : You can download the latest version on AngularJS from official website : AngularJS

Method 2 : Add the AngularJS CDN in the script tag in to your html page:

Update data from a View

View can be used for updating the data in the sql database tables if they satisfy the below conditions:

  1. Columns must reference only one base table.
  2. The columns being modified must reference directly from the table. it should not be a computed column or using any aggregate function.
  3. The columns must not be affected by the group by, having or distinct clauses.
  4. Top is not used with the WITH CHECK OPTION clause statement anywhere in the select statement in the view.

Below is a simple example of a update view syntax:

UPDATE dbo.v_Product_SalesQuantity
SET Quantity = 3
WHERE ReferenceOrderID = 14529;

Explanation : In the above example we are updating the quantity to 3 for the products which have got the reference id 14529. One of the points to note here are that the update query runs successfully because the data was from one of the tables.

How to get the list of view from the database?

Below are the two ways that can be used to get the list of view for a database:

SQL Query 1:
SELECT OBJECT_SCHEMA_NAME(v.object_id) AS SchemaName,
FROM sys.views AS v ;

SQL Query 2:
SELECT OBJECT_SCHEMA_NAME(o.object_id) AS SchemaName,
FROM sys.objects AS o
WHERE o.type = 'V' ;

How to get the list of columns of a view?

Below is the query that can be used to get the list of columns for a view.

SELECT name,
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.v_Product_TransactionHistory');

Indexed View

View allows us to create a virtual representation of the tables in the database. Regular views are not physically stored in the database i.e. they don not have any physical location on the hard drive.

Indexed view are also know as materialized view because they are stored on the hard drive. We can create clustered and non clustered indexes on the view. Indexed view can be achieved by using WITH SCHEMABINDING option for View.

Let us take an example and understand how we can create a indexed view in sql database.

CREATE VIEW dbo.v_Product_Sales_By_LineTotal
SELECT p.ProductID,
p.Name AS ProductName,
SUM(LineTotal) AS LineTotalByProduct,
COUNT_BIG(*) AS LineItems
FROM Sales.SalesOrderDetail s
INNER JOIN Production.Product p
ON s.ProductID = p.ProductID
GROUP BY p.ProductID,

Example of Clustered and Non Clustered Index

ON dbo.v_Product_Sales_By_LineTotal (ProductID);
ON dbo.v_Product_Sales_By_LineTotal (ProductName);

What is a view in Sql?

Views helps us in reducing the complexity and provides security to the underlying tables by exposing only those data that are required by the end users. Views helps us by allowing us to create a virtual representation of tables. We can use the SELECT statement for creating views.

There are many benefits of using Views:
They can simplify the work of the developers by reducing the complexity of SELECT statement.
They can be used for protecting the sensitive data. we can restrict the developers by restricting their rights on the underlying tables.

We can create four different types of views in sql server:

Regular View
Indexed View
Partitioned View
Partially Partitioned View

Below is a sample example that helps us in creating view.

CREATE VIEW dbo.v_Product_TransactionHistory
SELECT p.Name AS ProductName,
pc.Name AS ProductCategory,
ps.Name AS ProductSubCategory,
pm.Name AS ProductModel,
th.Quantity * th.ActualCost AS ExtendedPrice
FROM Production.TransactionHistory th
INNER JOIN Production.Product p
ON th.ProductID = p.ProductID
INNER JOIN Production.ProductModel pm
ON pm.ProductModelID = p.ProductModelID
INNER JOIN Production.ProductSubcategory ps
ON ps.ProductSubcategoryID = p.ProductSubcategoryID
INNER JOIN Production.ProductCategory pc
ON pc.ProductCategoryID = ps.ProductCategoryID
WHERE pc.Name = 'Car';

In the above example we are selecting data from multiple tables and selecting only Car. We can now use below query to select the data from the View.

SELECT ProductName,
FROM dbo.v_Product_TransactionHistory
ORDER BY ProductName;