CREATE NEW USER IN MS SQL SERVER

Step 1- go security section

Step 2 select login and right click and select new login user
Step 3 showing this interface
Step 5 Select SQL server Authentication create User name and password and check first 3
square box

Step 6 GO user Mapping And provide data and their role permission
Part 1 (Questions 1 to 5)

1. What is CHECK constraint in SQL Server?

A CHECK constraint can be applied to a column in a table to limit the values that can be
placed in a column. Check constraint is to enforce integrity.

https://docs.microsoft.com/en-us/sql/relational-databases/tables/create-check-
constraints?view=sql-server-2017#TsqlProcedure

2. What is COALESCE constraint in SQL Server?

COALESCE is used to return first non-null expression within the arguments. This function
is used to return a non-null from more than one column in the arguments.

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql?
view=sql-server-2017

3. What is a view in SQL Server?

A View is a select query with a name given to it or we can simply say a view is a named
Query. Views are very powerful they have lots of advantages:

 A view can combine data from multiple tables using joins and while bringing it
may require complex filters and calculated data to form the required result set.

 Sometimes for security purposes, access to the table, table structures and table
relationships are not given to the database user. All they have is access to a view
not knowing what tables actually exist in the database.

 Using the view, you can restrict the user update only portions of the records.

https://docs.microsoft.com/en-us/sql/relational-databases/views/views?view=sql-
server-2017
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?
view=sql-server-2017

4. What is the difference between UNION and UNION ALL?

UNION: To select related information from two tables UNION command is used. It is
similar to JOIN command.

UNION All: The UNION ALL command is equal to the UNION command, except that
UNION ALL selects all values. It will not remove duplicate rows, instead it will retrieve all
rows from all tables.

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-union-
transact-sql?view=sql-server-2017

https://social.msdn.microsoft.com/Forums/en-US/55b6647d-7637-4ab2-99ec-
d97420b14c88/union-vs-union-all?forum=transactsql

5. What is identity in SQL Server?

An identity column in the SQL automatically generates numeric values. We can be

defined as a start and increment value of the identity column.

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-
identity-property?view=sql-server-2017

6. What are query types in a SQL Server database?

Types of commands or queries in SQL Server are categorized into:

1. DDL (Data Definition Language)

2. DML (Data Manipulation Language)

3. DQL (Data Query Language)

4. DCL (Data Control Language)

Data Definition Language

Data Definition Language (DDL) is the part of SQL that allows a database user to create
and restructure database objects, such as the creation or the deletion of a table.

Some of the most fundamental DDL commands discussed during following hours
include the following:

 CREATE TABLE

 ALTER TABLE

 DROP TABLE

 CREATE INDEX

 ALTER INDEX

 DROP INDEX

 CREATE VIEW

 DROP VIEW

Data Manipulation Language

Data Manipulation Language (DML) is the part of SQL used to manipulate data within
objects of a relational database.

There are three basic DML commands:

 INSERT

 UPDATE

 DELETE

Data Query Language

Though comprised of only one command, Data Query Language (DQL) is the most
concentrated focus of SQL for modern relational database users. The base command is
as follows:
SELECT

This command, accompanied by many options and clauses, is used to compose queries
against a relational database. Queries, from simple to complex, from vague to specific,
can be easily created. The SELECT command is discussed in exhilarating detail during
Hours 7 through 16.

A query is an inquiry to the database for information. A query is usually issued to the
database through an application interface or via a command line prompt.
Data Control Language

Data control commands in SQL allow you to control access to data within the database.
These DCL commands are normally used to create objects related to user access and
also control the distribution of privileges among users. Some data control commands
are as follows:

 ALTER PASSWORD

 GRANT

 REVOKE

 CREATE SYNONYM

7. What is sub query in SQL Server?

A sub-query is a query which can be nested inside a main query like Select, Update,
Insert or Delete statements. This can be used when expression is allowed. Properties of
sub query can be defined as

 A sub query should not have order by clause

 A sub query should be placed in the right hand side of the comparison operator
of the main query

 A sub query should be enclosed in parenthesis because it needs to be executed

first before the main query

 More than one sub query can be included

https://docs.microsoft.com/en-us/sql/relational-databases/performance/subqueries?
view=sql-server-2017

8. Where are SQL Server user names and passwords stored in SQL Server?

User Names and Passwords are stored in sys.server_principals and sys.sql_logins. But
passwords are not stored in normal text.

https://docs.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/
sys-syslogins-transact-sql?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-
server-principals-transact-sql?view=sql-server-2017

9. What is the Stored Procedure?

A stored procedure is a set of SQL queries which can take input and send back output.
And when the procedure is modified, all clients automatically get the new version.
Stored procedures reduce network traffic and improve performance. Stored procedures
can be used to help ensure the integrity of the database.

https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/create-a-
stored-procedure?view=sql-server-2017

10. How can we check the SQL Server version?

Simply by running the following command:

SELECT @@VERSION AS 'SQL Server Version'; 

https://docs.microsoft.com/en-us/sql/t-sql/functions/version-transact-sql-configuration-
functions?view=sql-server-2017

Part 3 (Questions 11 to 15)

11. What is Normalization?

 Database normalization is a data design and organization process applied to data structures based
on rules that help building relational databases. In relational database design, the process of
organizing data to minimize redundancy is called normalization. Normalization usually involves
dividing a database into two or more tables and defining relationships between the tables. The
objective is to isolate data so that additions, deletions, and modifications of a field can be made
in just one table and then propagated through the rest of the database via the defined
relationships.

12. What is De-normalization?

De-normalization is the process of attempting to optimize the performance of a database by

adding redundant data. It is sometimes necessary because current DBMSs implement the
relational model poorly. A true relational DBMS would allow for a fully normalized database at
the logical level, while providing physical storage of data that is tuned for high performance. De-
normalization is a technique to move from higher to lower normal forms of database modeling in
order to speed up database access.

13. What are different normalization forms?

1. 1NF: Eliminate Repeating Groups Make a separate table for each set of related attributes, and
give each table a primary key. Each field contains at most one value from its attribute domain.
2. 2NF: Eliminate Redundant Data If an attribute depends on only part of a multi-valued key,
remove it to a separate table.
3. 3NF: Eliminate Columns Not Dependent On Key If attributes do not contribute to a
description of the key, remove them to a separate table. All attributes must be directly dependent
on the primary key.
4. BCNF: Boyce-Codd Normal Form If there are non-trivial dependencies between candidate key
attributes, separate them out into distinct tables.
5. 4NF: Isolate Independent Multiple Relationships No table may contain two or more 1:n or
n:m relationships that are not directly related.
6. 5NF: Isolate Semantically Related Multiple Relationships There may be practical constrains
on information that justify separating logically related many-to-many relationships.
7. ONF: Optimal Normal Form A model limited to only simple (elemental) facts, as expressed in
Object Role Model notation.
8. DKNF: Domain-Key Normal Form A model free from all modification anomalies is said to be
in DKNF.

14. What is the difference between sequence and identity?

An identity column in the table has auto-generate & auto increase value with each new row
insert. The user cannot insert value in the identity column.

The sequence is a new feature introduced with SQL Server 2012 similar to Oracle’s sequence
objects. A sequence object generates a sequence of unique numeric values as per the
specifications mentioned. Next VALUE for a SEQUENCE object can be generated using the
NEXT VALUE FOR clause.

IDENTITY is column level property & tied to a particular table. This cannot be shared among
multiple tables.

SEQUENCE is an object defined by the user with specific details and can be shared by multiple
tables. This is not tied to any particular table.

IDENTITY property cannot be reset to its initial value but the SEQUENCE object can be reset to
initial value any time.

Maximum value cannot be defined for IDENTITY whereas this can be done for SEQUENCE
object.

For more information check the links below

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-
server-ver15

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?
view=sql-server-ver15

15. What is Collation??

Collation refers to a set of rules that determine how data is sorted and compared. Character data
is sorted using rules that define the correct character sequence, with options for specifying case
sensitivity, accent marks, kana character types and character width.

https://docs.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-
support?view=sql-server-ver15

SQL Server 2019 Top New Features

1. Intelligent Query Processing Enhancements

2. Accelerated Database Recovery (ADR)

3. AlwaysEncrypted with Secure Enclaves

4. Memory-optimized Tempdb Metadata

5. Query Store Custom Capture Policies

6. Verbose Truncation Warnings

7. Resumable Index Build

8. Data Virtualization with Polybase

9. Last Actual Execution Plan DMF

10. Multiple Internal Performance Improvements

Resources

https://docs.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-ver15?
view=sql-server-ver15

https://www.microsoft.com/en-gb/sql-server/sql-server-downloads

SQL Server 2022 Features

Beginning with SQL Server 2012, Microsoft has launched a new release of SQL Server
every two years at most. This puts IT professionals in a situation where we must decide if
updating to the latest version is worth the time and costs. To help with this decision, this
tip provides a list of key features (by category) that are new or have been enhanced in
the latest version, SQL Server 2022.

Analytics

Azure Synapse Link for SQL Server

This feature allows you to run near real-time analytics with minimum impact on the
operational workload and reduces complexity because it doesn't require using ETL
processes to deal with data movement.

Object Storage Integration

Currently, you are not limited to using Azure Storage. SQL Server 2022 supports S3-
compatible object storage to take backups to URLs and Data Lake virtualization.

Always On and High Availability

Link to Azure SQL Managed Instance

SQL Server 2022 allows you to connect SQL Server instances to an Azure SQL Server
managed instance, allowing you to offload read-only workloads to the cloud.

Contained Availability Groups

You will be able to create an availability group with its own users, logins, permissions,
and SQL Agent jobs. To date, if you wanted to grant access for a new login to an
availability group database, you had to create the user on all servers that host the
availability group.

Distributed Availability Group

There will be added support to modifying the required synchronized secondaries to

commit and an increased number of TCP connections on links with high latencies.

There are also improvements in the thread usage by availability groups, specifically in
the parallel redo. Now each database can benefit from parallel redo. Additionally, now
redo records are batched to improve speed.

Security

SQL Server 2022 integrates with Microsoft Defender for Cloud in your VMs if you have
installed the SQL Server IaaS Agent extension (SqlIaasExtension).

Integration with Microsoft Purview

Microsoft Purview is a data governance solution that eases data administration across
on-premises and multi-cloud environments. Now you can apply access policies,
including the new "SQL Performance Monitor", and "SQL Security Auditor" roles to
instances.

Azure Active Directory Authentication

Now we can connect to SQL Server using Azure AD authentication on Linux and
Windows servers if they are Azure Arc-enabled servers. Azure Arc-enabled servers allow
you to manage Windows and Linux physical and virtual servers hosted outside of Azure.

Always Encrypted Improvements

Performance improvements due to added support for multithreading inside the enclave
and key caching.

New Server Roles

SQL Server 2022 added new server-level roles that are very handy to DBAs.

Dynamic Data Masking

Now the UNMASK permission adds more granularity allowing you to grant this
permission at the database, schema, table, and even column levels.

Improvements in Certificate and Keys Management

SQL Server 2022 added support to create certificates from PFX files. Additionally, you
can back up and restore certificates and symmetric keys from Azure BLOB storage.
Another improvement is that system-generated certificates now use RSA-3072 by
default.

Networking

SQL Server 2022 makes encryption mandatory when using MS-TDS 8 and TLS 1.3.

Ledger

This new version of SQL Server introduces an implementation of blockchain. I suppose

that the first thing that comes to mind when someone talks about blockchain is Bitcoin
and cryptocurrencies. Still, other uses for this technology make it useful to guarantee
the integrity of data.

Query Store Related

SQL Server 2022 allows us to enable Query Store on secondary replicas. This is very
useful when you have different workload types amongst the primary and the secondary
replicas. For example, if your primary role supports an OLTP solution and the
secondaries are used for reporting, now you can have two distinct query stores for the
different workload types.

Another interesting new feature of Query Store is the possibility to set up query hints for
the queries in the Query Store using
the sp_query_store_set_hints and sp_query_store_clear_hints stored procedures.

Also, with this release of SQL Server, Microsoft includes a set of enhancements relying
on Query Store that they name Intelligent Query Processing (IQP). The most important,
in my opinion, is Query Processing Feedback which provides feedback for the following
settings that affect query execution:

 Cardinality estimation
 Degree of parallelism (DOP)
 Memory grant

But, to take advantage of this feature, you will need to enable Query Store in read-write
mode.

Other items included in Intelligent Query Processing are Optimized Plan Forcing and
Parameter Sensitive Plan optimization.

Performance Related

SQL Server 2022 extends segment elimination capabilities to string, binary, guid, and
datetimeoffset for scale greater than two.

Another interesting new feature is the introduction of an XML compression option in the
CREATE TABLE and CREATE INDEX commands. This option allows us to compress off-
row XML data for both XML columns and indexes.

Speaking of indexes, another very useful feature is included in this release: Resumable
add table constraints. As its name suggests, now we can pause and resume the creation
of constraints if they were created using the RESUMABLE = ON option. This option
requires that you add the ONLINE = ON option.

Yet another index-related improvement, but this time on columnstore indexes, is the
introduction of ordered clustered columnstore indexes to improve performance for
queries based on ordered column predicates. This is done by sorting the data prior to
index compression.

There are also changes in the way SQL Server manages files. Concurrent updates to
global allocation map (GAM) pages and shared global allocation map (SGAM) pages
reduce page latch contention while allocating/deallocating data pages and extents. The
number of Virtual Log Files (VLF) created if the log file growth is less than or equal to 64
MB and more than 1/8 of the current log size is now set to 1 VLF instead of the 4 VLFs
on previous SQL Server versions.

Hardware Integration

This latest version of SQL Server uses the CPUs AVX-512 instruction set to improve
batch mode operations. This is something to consider when choosing the right CPU for
your new server.

Additionally, a new feature named Integrated Acceleration & Offloading provides a

framework for offloading specific SQL Server workload compute to hardware devices. At
this time, it works with Intel's QuickAssist Technology (QAT) and allows for offloading
backup (restore) compression (decompression) tasks.