SQL Data Types | Data Types In SQL Server

SQL Data Types :

Definition of Data Type :

SQL data types defines the type of values, that can be stored in a database or table.

Or

Data types specifies the type of any object.

Or

Data type is an attribute that specifies what type of data can be stored in a column, parameter, or variable.


For example, 

If we want to store on numeric value to Customer_Id, then we can define Customer_Id data type as int.

SQL Server has following data types :

Numeric

Numeric data can be stored. It has two sub type (1) Exact and (2) Approximate.

Date/Time

In this data type we can insert date & time.

Format of date : YYYY-MM-DD
Format of time : hh:mm:ss

Binary

Binary data stores strings of bits. The data consist of hexadecimal numbers. Data must have the same fixed lenght up to 8 KB.

Example :

The hexadecimal number of 245 is F5.

String / Char:

Using with this data type we can insert text/ character in our sql database.

Varbinary

Data can vary in the number of hexadecimal digits. It contains up to 8KB.

Notes :

In SQL Server, you can create user-defined data types, that are based on the system supplied data types.

SQL SELECT Statement | DQL Command

The SQL SELECT Statement

The SELECT statement is used to select data from a database.

The data returned is stored in a result table, called the result-set.

SELECT Syntax

SELECT column1, column2, ...
FROM table_name;

Here, column1, column2, ... are the field names of the table you want to select data from. If you want to select all the fields available in the table, use the following syntax:

SELECT * FROM table_name;

SELECT Column Example

The following SQL statement selects the "CustomerName" and "City" columns from the "Customers" table:

Example

SELECT CustomerName, City FROM Customers;

SELECT * Example

The following SQL statement selects all the columns from the "Customers" table:

Example

SELECT * FROM Customers;

Credit : w3schools

SQL WHERE Clause

WHERE Clause

Where clause used to filter data or records from database.

The WHERE clause is used to extract only those data or records, which fulfill a specified condition.
Syntax :

SELECT column1, column2,column3, . . . FROM table_name WHERE condition;

Example :

SELECT C_City FROM Customer01 WHERE C_Id = 1;

In above example, we are using equal sign.

The WHERE Clause Conditional selections used in the where clause are:

=	Equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
<>	Not equal to
Note: In some versions of SQL this operator may be written as !=
BETWEEN	Between a certain range
LIKE	Search for a pattern
IN TO	Specify multiple possible values for a column

SQL Commands | DDL, DML, TCL, DCL, DQL

SQL Commands:

SQL Command

 

SQL commands are guidelines, coded into SQL proclamations, which are utilized to speak with the database to perform explicit undertakings, work, capacities and inquiries with information.
SQL commands can be utilized for looking through the database as well as to perform different capacities like, for instance, you can make tables, add information to tables, or alter information, drop the table, set consents for clients. SQL directions are assembled into four noteworthy classifications relying upon their usefulness:

Information Definition Language (DDL) -

These SQL commands are used for making, altering, and dropping the structure of database objects. The commands are CREATE, ALTER, DROP, RENAME, and TRUNCATE.

See More >>>

Information Manipulation Language (DML) -

These SQL commands are used for putting away, recovering, changing, and erasing information.
These Data Manipulation Language commands are: INSERT, UPDATE, and DELETE.

See More >>>

Exchange Control Language (TCL) -

These SQL commands are used for overseeing changes influencing the information. These commands are COMMIT, ROLLBACK, and SAVEPOINT.

See More >>>

Information Control Language (DCL) -

These SQL commands are used for giving security to database objects. These commands are GRANT and REVOKE.

See More >>>

Data Query Language (DQL) -

This SQL command is used for retrieve value from database. This command is SELECT.

See More >>>
Recommended :

Data Definition Language :
	Create
	Drop
	Alter
	Truncate
Data Manipulation Language :
	Insert
	Update
	Delete
Data Control Language :
	Grant
	Revoke
Transaction Control Language :
	Commit
	Rollback
	Savepoint
Data Query Language:
	Select

SELECT Command In SQL| DQL Command

SELECT Command

SELECT command is one type of DQL (Data Query Language) command, which used to find information from one or more tables, and return the query as a Result Set.

Definition of Result Set :

When we are using select query its retrieve data or values in a result table, called the result-set.
Syntax :

1) SELECT column_name1, column_name2 FROM table_name ;

2) SELECT * FROM table_name ;

Select command has two types of parameters :

1) column_name - This parameter specifies the name of column, which data we want to retrieve.
2) table_name - This parameter specifies the name of table.

Example:

// Select Customer City and Address from Customer01 table

SELECT C_City, C_Address FROM Customer01;

// Select all data from table

SELECT * FROM Customer01 ;

* * * * * * * * * * * * * * * * * 

In Easy Language :

Select command used to retrieve data or values from database .

[Preview] Limitations of SQL Server 2019

Limitations of MS SQL Server 2019 :

Image Credit :- smallbizclub

• The ALL SELECTED DAX function is not yet supported.

• RLS (Row Level Security) defined on the calculation-group table does not yet supported. 

• Object Level Security defined on the calculation-group table is not yet supported.

• Details Rows expressions referring to calculation items are not yet supported.

• MDX is not yet supported.

DAX stands for Data Analysis Expressions.

DAX is a formula language and it contains functions, operators, and constants that can be used in a formula or expression to calculate and return one or more values. 

MDX stands for Multidimensional Expressions.

It is a query language for Online Analytical Processing (OLAP) using with database management system. Same like SQL, it is a query language for OLAP cubes.

MDX is also one of the calculation language, with syntax similar to spreadsheet formulas.

Major limitation:

It only supported queries that used vector-based (batch execution mode), which required the use of a column store index (which works best with analytical workloads, more than transaction processing).

[New] The Most Recent Updates for Microsoft SQL Server 2019 | SQL Server Versions

Most Recent Updates For SQL Server 2019

SQL Server 2019 brings creative security and consistence highlights, industry-driving execution, mission-basic accessibility, and progressed investigation to every one of your information outstanding tasks at hand, presently with help for enormous information worked in.

Best Feature of SQL Server 2019

Here, we are provide the most recent service pack and new updates for SQL Server 2019.

SQL Server Versions 2019

Version	New Update	Build Number
SSMS	18.0(RC1)	15.00.18098.0
SSMS	17.9.1 (GA/Stable)	14.0.17289.0
Azure Data Studio	Latest	-
2019	CTP 2.4	-

Download New Updates :

1) SSMS 18.0(RC1) Download :

Download SQL Server Management (SSMS) 18.0 (RC1)

2) SSMS 17.9.1 Download :

    SSMS 17.9.1 is the current General Availability (GA)

Download SQL Server Management Studio (SSMS) 17.9.1
Download SQL Server Management Studio 17.9.1 Upgrade Package (upgrades 17.x to 17.9.1)

3) Azure Data Studio Download :

Azure Data Studio may runs on Windows, mac OS, and Linux.

Platform	Download	Release Date	Version
Windows	User Installer (recommended) System Installer .zip	April 18, 2019	1.6.0
mac OS	.zip	April 18,2019	1.6.0
Linux	.deb .rpm .tar.gz	April 18,2019	1.6.0

4) Download CTP 2.4

SQL Server 2019 CTP 2.4


Recommended :

SQL Server 2019 New Features

[Latest] SQL Server 2019 New Features | SQL Server Developer Edition

SQL Server 2019 New Features 

Image Credit :- Blog

Microsoft's leading database is an best tool, with in-cloud and on-premises versions providing incredible storage and high analystic tools. It's additionally turned into an essential application for data scientists, giving a structure to building and testing machine learning models. There is more in SQL Server, and teh new release version can indicate where Microsoft thinks your data needs will go over the next few years.

Like, all the previous Microsoft SQL Server 2016, Microsoft SQL Server 2017, and so on versions, the Microsoft SQL Server 2019 is the Latest version also jam-packed with new features and capabilities. It will help organizations evaluate and test relational data with multiple integrations of various software. By creatinng a simplified view of data, users can have a comprehensive view of information.

SQL Server Developer Edition | SQL Server 2019 Release History

Below table lists the release history for SQL Server 2019 preview CTP releases.

Version	Release	Release Date
15.0.1400.75	CTP 2.4	2019-3-27
15.0.1300.359	CTP 2.3	2019-3-01
15.0.1200.24	CTP 2.2	2018-12-11
15.0.1100.94	CTP 2.1	2018-11-06
15.0.1000.34	CTP 2.0	2018-09-24

The new version of SQL Server 2019 is available in Linux and Windows versions and support for cotainers and Kubernetes is added. Using with Docker, including container support and the Linux Version of SQL Server is an intriguing alternative, as it will enable you to build SQL Server with huge Kubernetes based analytic engines that work across Azure facilitated data lakes using Apache Spark.

Download Latest Version : Download CTP 2.4

Top New Features of SQL Server 2019

Always On Availability Groups

Microsoft introduced Always On Availability Groups in SQL Server 2012, and has been making improvements to the feature in each release since.
Most biggest complaints most DBAs have about this feature, which allows multiple copies of a database to be replicated across multiple servers, is that there was no support for the system databases MSDB and Master, which contain log-ins and SQL Server Agent jobs, schedules and notifications.My personal favorite is DBA Tools, which runs PowerShell to keep log-ins and jobs synchronized between the availability group member nodes.

Microsoft is correcting in SQL Server 2019 this by addressing both Master and MSDB, which will simplify the administration of availability groups. Also it has the ability to have up to five synchronous replicas, up from the previous limit of three. Also you can have a total of nine replicas in your availability group, with the option to extend that further using distributed availability groups.

Accelerated Data Recovery

There are two features I'm writing about here that I thought were sheer magic the first time I saw them.

If you have ever killed a long-running transaction and waiting away for that transaction to roll back, this feature will be of interest to you. SQL Server's process was to identify the oldest page Log Sequence Number (LSN), then perform an undo and a redo phase. The undo phase involved traversing the transaction log file in reverse -- which, for large transactions, can take nearly the same amount of time as the original transaction. Another side-effect of this behavior is that the transaction log cannot be truncated while this rollback is taking place, which can result in disk space issues.

Currently , Accelerated Database Recovery (ADR) is available in public preview in Azure SQL Database, as well as Azure SQL Data Warehouse. It will be soon on by default for all existing and new databases or resultset in Azure. ADR will also be available in public preview in one of the upcoming CTP builds of SQL Server 2019, and is expected to be in the Release-To-Manufacturing (RTM) version of SQL Server 2019.

It works is by using a persisted version store in the database itself, as opposed to the TempDB version store for Read Committed Snapshot Isolation. This works in conjunction with a process called logical revert, which reads the version store and provides instantaneous rollback and undo. There are also some in-memory and clean-up components that provide background processing to support the overall process.

This feature does have some overhead, but any mission-critical system that has to be patched regularly or simply has long-running transactions that could be rolled back will benefit from the new behavior.

Scalar Function Inlining

The other feature that I refer to as simply magic was referenced in this Microsoft Research whitepaper released in the spring of 2018. The first time I tested it, I was blown away.

The Scalar functions that can be included in a query, is a concept that is very familiar to developers, who are used to writing in their object-oriented code. Sadly, this has been a problematic design pattern in SQL Server. Scalar functions are executed in a black box, giving no insight into the underlying query operations to process them and / through a combination of factors existing in the SQL Server codebase / are limited in performance. The two biggest problems are the fact that these functions are limited to single-threaded execution and, more importantly, are executed row by row.

Other New Features

There are number of other new features, including online columnstore index builds and rebuilds, static data masking (which allows for masked data to be persisted to other environments), and enhancements to Always Encrypted to allow more operations to be performed on encrypted data in order to make the feature more useful.

In SQL Server 2019, there are also many subtle enhancements to a wide array of features in SQL Server, including persistent memory options for data storage, and broader external database (Oracle, Teradata and MongoDB) support for PolyBase, it is aligns to the targets for SQL Server Big Data clusters.

SQL Server 2019 preview :

Download SQL Server 2019 preview to install on Windows.
Install on Linux for Red Hat Enterprise Server, SUSE Linux Enterprise Server, and Ubuntu.
Run on SQL Server 2019 preview on Docker.

Also See :

SQL Server 2019 Release Date - March 26, 2019
SQL Server 2018 Release Date -  Sep 24, 2018
SQL Server 2017 Release Date - Oct 2, 2017

DQL Command | Data Query Language | SQL Command

DQL Command

DQL command is used to retrieve data from database.

DQL command has one type :

• SELECT

SELECT Command

SELECT command is used to find information from one or more tables, and return the query as a result set.

Syntax :

1) SELECT * FROM table_name;
2) SELECT column_name1, column_name2 FROM table_name;

Using with * sign we can retrieve all data from database.
Also we can select particular data from database.

Example :

1) SELECT * FROM Customer01;
2) SELECT C_Id, C_FirstName FROM Cutomer01;

* * * * * * * * * * * * * * * * *

In Easy Language

Select command used to retrieve data from database.

ROLLBACK | Rollback In SQL Server

ROLLBACK Command

This command restore the database to last committed state.

It is also used with savepoint command to jump to a savepoint in a transaction.

Syntax :

1) ROLLBACK;

2) ROLLBACK savepoint_name;

Example :

ROLLBACK save1;

Note :

We can rollback the same data that was not previously committed.

* * * * * * * * * * * * * *

In Easy Lanhguage

Rollback command used to rollback(undo) all database or particular savepoint.

SAVEPOINT | Savepoint In SQL

SAVEPOINT Command

Savepoint command used to save temporarily transaction, so that you can rollback to that point whenevee necessary.
Savepoint command used to create savepoint after change the database.

Syntax :

SAVEPOINT savepoint_name;

Example :

SAVEPOINT save1;

Note :

If we have made a mistake in the database and use rollback without the savepoint , then it rollbacks the complete transaction and we lost all data.

Commit | Commit In SQL Server | TCL Command

Commit Command

Commit command used to save permanently transaction to database.

Syntax :

commit;

Example :

commit;

TCL | TCL Commands | SQL Command

Transaction Control Language

In SQL, there are following types of TCL Commands. As under,

• COMMIT
• ROLLBACK
• SAVE POINT

TCL commands are used to manage transaction in database. Like commit, rollback, save point.

These TCL commands are used to manage the changes made by DML commands.

COMMIT Command

Commit command used to save transaction into the database permanently.

Syntax :

commit ;

Example :

commit ;

ROLLBACK Command

Rollback command used to restores the database to last committed state.
It can be also used with SAVEPOINT command to jump to a save point in an ongoing database transaction.
Syntax :

ROLLBACK TO savepoint_name ;

Rollback command has one parameter

1) savepoint_name - This parameter used to provide name of  save point, which we want to rollback.

Example :

DELETE FROM Customer01 WHERE C_Id = 105;
ROLLBACK;

SAVEPOINT Command

Savepoint command used to set a point in a transaction. When you can roll the transaction back to a certain point without rolling back the entire transaction.

Syntax :

SAVEPOINT savepoint_name;

Example :

SAVEPOINT cust_1;


Recommanded :

Commit Command
Rollback Command
Savepoint Command

Note:

• If we are use DML commands (INSERT, UPDATE, DELETE), the changes made by these commands are not permanent, until the current session is closed.