Sunday, May 19, 2019

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


Monday, April 29, 2019

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



Thursday, April 25, 2019

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 .

Wednesday, April 24, 2019

[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).


Tuesday, April 23, 2019

[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

VersionNew UpdateBuild Number
SSMS 18.0(RC1)15.00.18098.0
SSMS17.9.1 (GA/Stable)14.0.17289.0
Azure Data StudioLatest-
2019CTP 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.

PlatformDownloadRelease DateVersion
WindowsUser Installer (recommended)
System Installer
.zip
April 18, 20191.6.0
mac OS.zipApril 18,20191.6.0
Linux.deb
.rpm
.tar.gz
April 18,20191.6.0

4) Download CTP 2.4

SQL Server 2019 CTP 2.4


Recommended :

SQL Server 2019 New Features


Sunday, April 21, 2019

[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.


VersionReleaseRelease Date
15.0.1400.75CTP 2.42019-3-27
15.0.1300.359CTP 2.32019-3-01
15.0.1200.24CTP 2.22018-12-11
15.0.1100.94CTP 2.12018-11-06
 15.0.1000.34CTP 2.02018-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.


Tuesday, April 16, 2019

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;



Monday, April 15, 2019

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.

Saturday, April 6, 2019

REVOKE | Revoke Commands In SQL | DCL Command

REVOKE Command


Revoke command used to removes user access rights or privileges to the database objects.



Syntax :

REVOKE privilege_list ON object_name
FROM {user_name | PUBLIC | role_name};
Revoke command has four types of parameters:

1) privilege_list - This parameter specifies the types of privilege.
2) object_name - This parameter specifies the name of the database object like table, view etc.
3) user_name - This parameter specifies the name of the user.
4) role_name - This parameter specifies set of privileges grouped together.

Keyword

PUBLIC - Used to revoke rights to all user.

Example :

REVOKE SELECT ON Customer01 FROM user1;

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

In Easy Language :


Revoke command used to remove user access privileges.


GRANT | Grant Command In SQL | DCL Command

GRANT Command


Grant command used to provide access or privileges on the database objects to the users.



Syntax :

GRANT privilege_list ON object_name
TO {user_name | PUBLIC | role_name}
[with GRANT option];


Grant command has five types of parameter :

1) privilege_list - This parameter specifies the access right or privilege granted to
the user.

2) object_name - This parameter specifies the name of the database object like table, view
etc.

3) user_name - This parameter specifies the name of the user to whom an access right is
being granted.

4) with GRANT option - It allows users to grant access rights to other
users.

5) role_name - This parameter specifies set of privileges grouped together.

Keyword

PUBLIC - Used to grant rights to all the users.

Example :
 
GRANT SELECT ON Customer01 TO user1 ;

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

In Easy Language :

Grant command used to access privileges.