DIFFERENCES BETWEEN SQL SERVER CLUSTERED INDEX SCAN AND INDEX SEEK

Index scan means it retrieves all the rows from the table and index seek means it retrieves selective rows from the table.

INDEX SCAN:

Index Scan touches every row in the table it is qualified or not, the cost is proportional to the total number of rows in the table.

Thus, a scan is an efficient strategy if the table is small or most of the rows qualify for the predicate.

INDEX SEEK:

Index Seek only touches rows that qualify and pages that contain these qualifying rows.

The cost is proportional to the number of qualifying rows and pages rather than the total number of rows in the table.

They are two types of Indexes are there:

1. Clustered Index.

2. Non Clustered Index.

Clustered Index:

A non-clustered index can consist of one or more columns, but the data storage is not dependent on this create index statement as is the case with the clustered index.

For a table without a clustered index, which is called a heap, the non-clustered index points the row (data).

In the circumstance where the table has a clustered index, then the non-clustered index points to the clustered index for the row (data).

Although many implementations only have a single column for the clustered index, in reality a clustered index can have multiple columns.

Just be careful to select the correct columns based on how the data is used. The number of columns in the clustered (or non-clustered) index can have significant performance implications with heavy INSERT, UPDATE and DELETE activity in your database.

SQL Server – Get Size of All Databases in MB and GB

 The Following script  will retrieve the size of all your databases in MB and GB.

SELECT d.NAME
    ,ROUND(SUM(CAST(mf.size AS bigint)) * 8 / 1024, 0) Size_MBs
    ,(SUM(CAST(mf.size AS bigint)) * 8 / 1024) / 1024 AS Size_GBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 -- Skip system databases
GROUP BY d.NAME
ORDER BY d.NAME

--Exclude Offline Databases

 SELECT d.NAME
    ,ROUND(SUM(CAST(mf.size AS bigint)) * 8 / 1024, 0) Size_MBs
    ,(SUM(CAST(mf.size AS bigint)) * 8 / 1024) / 1024 AS Size_GBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 
--and d.state_desc='online' -- Skip system databases
GROUP BY d.NAME
ORDER BY d.NAME

Database level roles in Azure SQL Database

 Database security is critical for an organization to protect unauthorized access to the sensitive and critical data stored in the database objects. There are many layers of security in terms of infrastructure security, database authentication, authorization, encryption.

Once you create a new Azure SQL DB using Azure portal or Azure CLI, the provisioning process deploys a logical Azure SQL Server in the Azure region. You get a virtual master database for managing the configuration and security at the server level. It also configures a server-level principal as a database owner of the Azure database. This account has the highest permissions in Azure SQL DB(PaaS) and has sufficient rights to manage server and database-level security.

The following table summarizes the difference in database security management of Azure SQL Database and on-premises SQL Database.

Fixed Database Roles in Azure DB

Expand the Azure SQL DB and navigate to security -> Roles -> Database Roles to get a list of available fixed database roles, expand the Azure SQL DB and navigate to Security -> Roles -> Database Roles. You get the following fixed-database roles.

Additional roles in the virtual master database

If you look at the same database roles in the virtual master database, you get additional database roles, as shown below.

Azure Database contains additional security roles: loginmanager for creating logins and dbmanager for creating databases.

Note: The users in the master database can only be added to these database roles.

Loginmanager role:

Users in the loginmanager database role can create and delete the logins in the master database.

dbmanager role

The dbmanager role allows the user to create a database, delete a database as a database owner. It allows users to connect the Azure database as a DBO user that contains all DB permissions. The user in the role does not have permission to access other databases that they do not own.