• You are here:  
  • Home
  • SQL

Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network (including the Internet). Microsoft markets at least a dozen different editions of Microsoft SQL Server, aimed at different audiences and for workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users.

QL Server services, tools and editions

Microsoft also bundles a variety of data management, business intelligence (BI) and analytics tools with SQL Server. In addition to the R Services and now Machine Learning Services technology that first appeared in SQL Server 2016, the data analysis offerings include SQL Server Analysis Services, an analytical engine that processes data for use in BI and data visualization applications, and SQL Server Reporting Services, which supports the creation and delivery of BI reports.

On the data management side, Microsoft SQL Server includes SQL Server Integration Services, SQL Server Data Quality Services and SQL Server Master Data Services. Also bundled with the DBMS are two sets of tools for DBAs and developers: SQL Server Data Tools, for use in developing databases, and SQL Server Management Studio, for use in deploying, monitoring and managing databases.

SQL Server editions:

Microsoft offers SQL Server in four primary editions that provide different levels of the bundled services. Two are available free of charge: a full-featured Developer edition for use in database development and testing, and an Express edition that can be used to run small databases with up to 10 GB of disk storage capacity. For larger applications, Microsoft sells an Enterprise edition that includes all of SQL Server's features, as well as a Standard one with a partial feature set and limits on the number of processor cores and memory sizes that users can configure in their database servers.

Other editions include a Standard version, Web version and a Business Intelligence version. The Business Intelligence version provides Self Service and Corporate Business Intelligence tools with the Standard Edition capabilities.

Versions of SQL Server

Between 1995 and 2016, Microsoft released ten versions of SQL Server. Early versions were aimed primarily at departmental and workgroup applications, but Microsoft expanded SQL Server's capabilities in subsequent ones, turning it into an enterprise-class relational DBMS that could compete with Oracle Database, DB2 and other rival platforms for high-end database uses. Over the years, Microsoft has also incorporated various data management and data analytics tools into SQL Server, as well as functionality to support new technologies that emerged, including the web, cloud computing and mobile devices.

SQL Server 2012:

Prior versions included SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2, which was considered a major release despite the follow-up sound of its name. Next to come were SQL Server 2012 and SQL Server 2014. SQL Server 2012 offered new features, such as columnstore indexes, which can be used to store data in a column-based format for data warehousing and analytics applications, and AlwaysOn Availability Groups, a high availability and disaster recovery technology. (Microsoft changed the spelling of the latter's name to Always On when it released SQL Server 2016.)

SQL Server 2014:

SQL Server 2014 added In-Memory OLTP, which lets users run online transaction processing (OLTP) applications against data stored in memory-optimized tables instead of standard disk-based ones. Another new feature in SQL Server 2014 was the buffer pool extension, which integrates SQL Server's buffer pool memory cache with a solid-state drive -- another feature designed to boost I/O throughput by offloading data from conventional hard disks.

Microsoft SQL Server 2016:

Microsoft SQL Server 2016, which became generally available in June 2016, was developed as part of a "mobile first, cloud first" technology strategy adopted by Microsoft two years earlier. Among other things, SQL Server 2016 added new features for performance tuning, real-time operational analytics, and data visualization and reporting on mobile devices, plus hybrid cloud support that lets DBAs run databases on a combination of on-premises systems and public cloud services to reduce IT costs. For example, a SQL Server Stretch Database technology moves infrequently accessed data from on-premises storage devices to the Microsoft Azure cloud, while keeping the data available for querying, if needed.

SQL Server 2016 also increased support for big data analytics and other advanced analytics applications through SQL Server R Services, which enables the DBMS to run analytics applications written in the open source R programming language, and PolyBase, a technology that lets SQL Server users access data stored in Hadoop clusters or Azure blob storage for analysis. Also, SQL Server 2016 was the first version of the DBMS to run exclusively on 64-bit servers based on x64 microprocessors. And it added the ability to run SQL Server in Docker containers, a virtualization technology that isolates applications from each other on a shared operating system.

SQL Server 2017:

Microsoft SQL Server ran exclusively on Windows for more than 20 years. But, in 2016, Microsoft said it planned to also make the DBMS available on Linux, starting with a new version released as a community technology preview that November and initially dubbed SQL Server vNext; later, the update was formally named SQL Server 2017, and it became generally available in October of that year.

The support for running SQL Server on Linux moved the database platform onto an open source operating system commonly found in enterprises, giving Microsoft potential inroads with customers that don't use Windows or have mixed server environments. SQL Server 2017 also expanded the Docker support added for Windows systems in the previous release to include Linux-based containers.

Another notable feature in SQL Server 2017 is support for the Python programming language, an open source language that is widely used in analytics applications. With its addition, SQL Server R Services was renamed Machine Learning Services (In-Database) and expanded to run both R and Python applications. Initially, the machine learning toolkit and a variety of other features are only available in the Windows version of the database software, with a more limited feature set supported on Linux.

SQL Server 2019:

SQL Server 2019 is slated to release in late 2019; however, a preview is available for download. SQL Server 2019 CTP 3.0 is currently the latest public version available for preview— only as an Evaluation Edition. SQL Server 2019 was shown in September 2018, introducing new features and tweaks focusing on performance, security and increasing work volume data.

SQL Server 2019 allows users to join SQL Server, HDFS, and Spark containers together using a new Big Data Cluster feature. SQL Server 2019 also introduces columnstore index builds, rebuilds and static data masking. Accelerated Data Recovery is also new, which performs and undoes a redo phase in the oldest page log sequence number. As an example, this is done in the case scenario where the user closes an application which was running for an extended period of time, so the user does not have to wait long for the application to close.

Always On Availability Groups, available in SQL Server 2012, has been changed to simplify administration of availability groups. This adds support to MSDB and Master system databases. Other changes to features include the expansion to operations users can perform with Always Encrypted data; additional Polybase connectors for SQL Server, Oracle, MongoDB and Teradata; additional persistent memory options for storage; and improvements on Query Processing.

Security features in SQL Server

The advanced security features supported in all editions of Microsoft SQL Server starting with SQL Server 2016 SP1 include three technologies added to the 2016 release: Always Encrypted, which lets user update encrypted data without having to decrypt it first; row-level security, which enables data access to be controlled at the row level in database tables; and dynamic data masking, which automatically hides elements of sensitive data from users without full access privileges.

Other notable SQL Server security features include transparent data encryption, which encrypts data files in databases, and fine-grained auditing, which collects detailed information on database usage for reporting on regulatory compliance. Microsoft also supports the Transport Layer Security protocol for securing communications between SQL Server clients and database servers. 

Most of those tools and the other features in Microsoft SQL Server are also supported in Azure SQL Database, a cloud database service built on the SQL Server Database Engine. Alternatively, users can run SQL Server directly on Azure, via a technology called SQL Server on Azure Virtual Machines; it configures the DBMS in Windows Server virtual machines running on Azure. The VM offering is optimized for migrating or extending on-premises SQL Server applications to the cloud, while Azure SQL Database is designed for use in new cloud-based applications.

In the cloud, Microsoft also offers Azure SQL Data Warehouse, a data warehousing service based on a massively parallel processing (MPP) implementation of SQL Server. The MPP version, originally a stand-alone product called SQL Server Parallel Data Warehouse, is also available for on-premises uses as part of the Microsoft Analytics Platform System, which combines it with PolyBase and other big data technologies.

 

 

 

Microsoft SQL Server

https://en.wikipedia.org/wiki/Microsoft_SQL_Server

SELECT table_catalog, table_schema, table_name,*
    FROM information_schema.tables
    WHERE ( table_name) NOT IN
          (SELECT   table_name
               FROM information_schema.table_constraints
               WHERE constraint_type = 'PRIMARY KEY')
      AND table_schema NOT IN ('information_schema', 'pg_catalog')
      and TABLE_TYPE <> 'VIEW'

Different types of JOINs

  • INNER JOIN: Returns all rows when there is at least one match in BOTH tables
  • LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
  • RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
  • FULL JOIN: Return all rows when there is a match in ONE of the tables
  • SELF JOIN: is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
  • CARTESIAN JOIN: returns the Cartesian product of the sets of records from the two or more joined tables .

sql joins

For more about sql join 

http://www.w3schools.com/sql/sql_join_inner.asp

http://www.sql-join.com/sql-join-types

https://www.tutorialspoint.com/sql/sql-inner-joins.htm

 

Constraints are the rules enforced on data columns on table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.

Constraints could be column level or table level. Column level constraints are applied only to one column, whereas table level constraints are applied to the whole table.

Following are commonly used constraints available in SQL. These constraints have already been discussed in SQL - RDBMS Concepts chapter but its worth to revise them at this point.

  • NOT NULL Constraint: Ensures that a column cannot have NULL value.

  • DEFAULT Constraint: Provides a default value for a column when none is specified.

  • UNIQUE Constraint: Ensures that all values in a column are different.

  • PRIMARY Key: Uniquely identified each rows/records in a database table.

  • FOREIGN Key: Uniquely identified a rows/records in any another database table.

  • CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions.

  • INDEX: Use to create and retrieve data from the database very quickly.

Constraints can be specified when a table is created with the CREATE TABLE statement or you can use ALTER TABLE statement to create constraints even after the table is created.

Dropping Constraints:

Any constraint that you have defined can be dropped using the ALTER TABLE command with the DROP CONSTRAINT option.

For example, to drop the primary key constraint in the EMPLOYEES table, you can use the following command:

ALTER TABLE EMPLOYEES DROP CONSTRAINT EMPLOYEES_PK;

Some implementations may provide shortcuts for dropping certain constraints. For example, to drop the primary key constraint for a table in Oracle, you can use the following command:

ALTER TABLE EMPLOYEES DROP PRIMARY KEY;

Some implementations allow you to disable constraints. Instead of permanently dropping a constraint from the database, you may want to temporarily disable the constraint and then enable it later.

Integrity Constraints:

Integrity constraints are used to ensure accuracy and consistency of data in a relational database. Data integrity is handled in a relational database through the concept of referential integrity.

There are many types of integrity constraints that play a role in referential integrity (RI). These constraints include Primary Key, Foreign Key, Unique Constraints and other constraints mentioned above.

 

Source https://www.tutorialspoint.com/sql/sql-constraints.htm

SQL data type is an attribute that specifies type of data of any object. Each column, variable and expression has related data type in SQL.

You would use these data types while creating your tables. You would choose a particular data type for a table column based on your requirement.

SQL Server offers six categories of data types for your use −

Exact Numeric Data Types

DATA TYPE FROM TO
bigint -9,223,372,036,854,775,808 9,223,372,036,854,775,807
int -2,147,483,648 2,147,483,647
smallint -32,768 32,767
tinyint 0 255
bit 0 1
decimal -10^38 +1 10^38 -1
numeric -10^38 +1 10^38 -1
money -922,337,203,685,477.5808 +922,337,203,685,477.5807
smallmoney -214,748.3648 +214,748.3647

Approximate Numeric Data Types

DATA TYPE FROM TO
float -1.79E + 308 1.79E + 308
real -3.40E + 38 3.40E + 38

Date and Time Data Types

DATA TYPE FROM TO
datetime Jan 1, 1753 Dec 31, 9999
smalldatetime Jan 1, 1900 Jun 6, 2079
date Stores a date like June 30, 1991
time Stores a time of day like 12:30 P.M.

Note − Here, datetime has 3.33 milliseconds accuracy where as smalldatetime has 1 minute accuracy.

Character Strings Data Types

DATA TYPE Description
char Maximum length of 8,000 characters.( Fixed length non-Unicode characters)
varchar Maximum of 8,000 characters.(Variable-length non-Unicode data).
varchar(max) Maximum length of 231characters, Variable-length non-Unicode data (SQL Server 2005 only).
text Variable-length non-Unicode data with a maximum length of 2,147,483,647 characters.

Unicode Character Strings Data Types

DATA TYPE Description
nchar Maximum length of 4,000 characters.( Fixed length Unicode)
nvarchar Maximum length of 4,000 characters.(Variable length Unicode)
nvarchar(max) Maximum length of 231characters (SQL Server 2005 only).( Variable length Unicode)
ntext Maximum length of 1,073,741,823 characters. ( Variable length Unicode )

Binary Data Types

DATA TYPE Description
binary Maximum length of 8,000 bytes(Fixed-length binary data )
varbinary Maximum length of 8,000 bytes.(Variable length binary data)
varbinary(max) Maximum length of 231 bytes (SQL Server 2005 only). ( Variable length Binary data)
image Maximum length of 2,147,483,647 bytes. ( Variable length Binary Data)

Misc Data Types

DATA TYPE Description
sql_variant Stores values of various SQL Server-supported data types, except text, ntext, and timestamp.
timestamp Stores a database-wide unique number that gets updated every time a row gets updated
uniqueidentifier Stores a globally unique identifier (GUID)
xml Stores XML data. You can store xml instances in a column or a variable (SQL Server 2005 only).
cursor Reference to a cursor object
table Stores a result set for later processing

Source  :

http://www.w3schools.com/sqL/sql_datatypes_general.asp

https://www.tutorialspoint.com/sql/sql-data-types.htm

  1. SQL DISTINCT
  2. SQL SELECT
  3. Types of SQL Statements
  4. SQL Syntax