SQL server 2012 – Introduction

SQL Server 2012

    Agenda:
  •  History of SQL Server
  • Editions of SQL Server
  • Server Components and Tools
  • Instance and Browser Service
  • Starting and Stopping SQL Server Service
  • Types of Authentication
  • Management Studio
  • Types of System Databases

Introduction to SQL SERVER

  • Microsoft SQL Server is an application used to create relational databases for the Microsoft Windows family of server operating systems.
  • Microsoft SQL Server provides an environment used to generate databases that can be accessed from workstations, the Internet, or other media such as a personal digital assistant (PDA).
  • Microsoft SQL Server 2012 is a full-featured relational database management system (RDBMS) that offers a variety of administrative tools to ease the burdens of database development, maintenance, and administration
  • It is a database platform for large-scale online transaction processing (OLTP), data warehousing, and e-commerce applications; it is also a business intelligence platform for data integration, analysis, and reporting solutions.

History of SQL SERVER

Prior to version 7.0 the code base for MS SQL Server was sold by Sybase SQL Server to Microsoft, and was Microsoft’s entry to the enterprise-level database market.

SQL Server Versions are as follows:

  • SQL SERVER 7.0
  • SQL SERVER 2000(8.0)
  • SQL SERVER 2005(9.0)
  • SQL SERVER 2008(10.0)
  • SQL SERVER 2008R2(10.5)
  • SQL SERVER 2012(11.0)

SQL SERVER Editions

Microsoft makes SQL Server available in multiple editions, with different feature sets and targeting different users.

Example: Enterprise, standard, workgroup, Express etc.

SQL Server Express Edition:

  • SQL Server Express Edition is a free, lightweight, version of SQL Server.
  • While there are no limitations on the number of databases or users supported it is limited to using one processor.
  • The maximum memory for the database engine is limited to 1GB and the maximum relational database size is 10 GB

Different Express editions in SQL Server2012

SQL Server Express (Database Only) Includes only the relational database engine.
SQL Server Express with Tools The SQL Server Express with Tools edition includes the basic relational database as well as the SQL Server Management Studio Express (SSMSE)
SQL Server Express with Advanced Services It includes SSMSE, SQL Server Data Tools (SSDT), full-text search, and the Reporting Services subsystem. The Reporting Services implementation in SQL Server Express with Advanced Services is limited to 4GB and can access only the local instance

Server Components and Tools

Components:

Database Engine: SQL Server Database Engine includes the Database Engine, the core service for storing, processing, and securing data, replication, full-text search, tools for managing relational and XML data, and the Data Quality Services (DQS) server.

Reporting Services: Reporting Services includes server and client components for creating, managing, and deploying tabular, matrix, graphical, and free-form reports.
Analysis Services: Analysis Services includes the tools for creating and managing online analytical processing (OLAP) and data mining applications.

Integration Services: Integration Services is a set of graphical tools and programmable objects for moving, copying, and transforming data. It also includes the Data Quality Services (DQS) component for Integration Services.

Full Text Search: Full-Text Search

SQL Server 2008 provides the capability to issue full-text queries against plain character based data in your SQL Server tables. This capability is useful for searching large text fields, such as movie reviews, book descriptions, or case notes. Full-text queries can include words and phrases, or multiple forms of a word or phrase.

Tools:

SQL Server Configuration Manager: SQL Server Configuration Manager provides basic configuration management for SQL Server services, server protocols, client protocols, and client aliases.

SQL Server Data Tools: SQL Server Data Tools (SSDT) provides an IDE for building solutions for the Business Intelligence components: Analysis Services, Reporting Services, and Integration Services.
(Formerly called Business Intelligence Development Studio)

SQL Server Agent: SQL Server Agent is a scheduling tool integrated into SSMS that allows convenient definition and execution of scheduled scripts and maintenance jobs. SQL Server Agent also handles automated alerts

SQL Server Management Studio: SQL Server Management Studio SSMS is an integrated environment to access, configure, manage, administer, and develop components of SQL Server. Management Studio lets developers and administrators of all skill levels use SQL Server.

Communication between front-end and Back-end

 
Instances and Browser Service
From SQL Server 2000 the capability is provided to use multiple instances of database engine on same server.
Default Instance:
  • One instance can be defined as default instance
  • Default instance is identified by Server Name.
  • Ex: Microsoft (Microsoft is server name)

Named Instance:

  • There can be many Named Instances.
  • Named instance is identified by ServerName \ InstanceName
  • Ex: Microsoft\SQLEXPRESS
Note: If there is one instance of SQL server it can listen to default port no 1433.
SQL Server Browser Service:
  • SQL Server Browser, was introduced with SQL Server 2005, runs as a Windows service
  • Upon startup, SQL Server instances are assigned ports. The Browser Service reads the registry, finds the ports and instances and stores them
  • It listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer.
  • The basic purpose of the SQL Server Browser service is to provide instance and port information to incoming connection requests.
  • It contributes to following actions:
Browsing a list of available servers
Connecting to the correct server instance

Starting and Stopping SQL Server Service:

Method1:
Control Panel > Adminitrative Tools > Services > Sql Server (SQLEXPRESS) > Right Click Start if not already started or stop
Method 2:
StartAll Programs > SQL Server 2012 > Configuration Tools  > SQL Server Configuration Manager > Click SQL Server Services > Select particular SQL Server Instance > Right Click > Start or Stop

Types of Authentication in SQL Server

Windows Authentication:
  • The identity of the client on the Domain of the OS / Network is used by Sql Server to allow or deny access to the resources in the database
 SQL Server Authentication:
  • The permissions to the client are granted based on the identity which was created and stored in SQL Server database.
Steps for Configuring Sql Server to support both the types of Authentication:
 
1. Start > Programs  > Microsoft Sql Server 2012 >  SQL Server Management Studio > Connect… > Right Click on Root of the Tree >  Properties  >Select Security >  Check SQL Server and Windows Authentication Mode.
2. Expand Security > Logins > Select User “sa” > Right Click – Properties > Set Password
3. Also Select Status (on left side)  Check Login Enabled.
4. Disconnect and Connect again with SQL Server Authentication so that we are sure the above steps are correct performed.
Introduction to SQL Server Management Studio
 
  • SQL Server Management Studio is an integrated environment or tool for accessing, configuring, managing, administering, and developing all components of SQL Server.
  • It has a rich graphical interface with script editors.
  • It works with all components of SQL Server such as Reporting Services and Integration Services, Analysis Service.
Types of System Databases
 
Master:
 
  • The master database contains all information about running servers configuration .It includes all of the logins, linked servers, endpoints, and other system-wide configuration settings.
  • The master database is also where SQL Server stores information about the other databases on this instance and the location of their files and records initialization information for the instance.
  • Master database is the logical repository for the system objects residing in the sys schema.
  • It holds information for all databases located on the SQL Server instance and is the glue that holds the engine together.
  • SQL Server cannot start without a functioning master database, you must administer this database with care and it is vital to make regular backups of this database.
Resource:
The resource database is hidden, read-only system database.
System objects are no longer stored in master but the Resource database from SQL Server 2005.
System objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database.
The Resource database does not contain user data or user metadata.
The resource database is designed to make it easy for quick database upgrades. If new system objects are being put in place, it is only necessary to swap out the resource database MDF file
Model:
 
  • Model is essentially a template database used in the creation of any new user database created in the instance.
  • You can place any stored procedures, views, users, etc. in the model database so that when a new database is created, the database will contain the objects you have placed in the model database.
 Tempdb:
  • As its name implies, Tempdb holds temporary objects such as global and local temporary tables and stored procedures.
  • This database is recreated every time SQL Server starts, and the objects contained in it will be based upon the objects defined in the model database.
  • Tempdb also houses other objects such as table variables, results sets from table-valued functions, and temporary table indexes.
  • Because tempdb will hold these types of objects for all of the databases on the SQL Server instance, it is important that the database is configured for optimal performance.
 Msdb:
  • The Msdb database is used by SQL Server Agent for scheduling alerts and jobs
  • The msdb database stores information regarding database backups (backup history), SQL Agent information, SQL Server jobs, Database mail, and some replication information such as for log shipping
 Note:
Database Mail is an enterprise solution for sending e-mail messages from the SQL Server Database Engine.Using Database Mail, your database applications can send e-mail messages to users. The messages can contain query results, and can also include files from any resource on your network.

Introduction to Sequences in SQL 2012

SQLServer 2012 introduces Sequence as new schema object which has been requested by the SQL community  and due for release since many years.

  • It is similar to sequence object of  oracle database
  • Unlike identity column sequence is not attached with table. It is  separately created
  • A sequence once created can be used with any table.
  • Unlike identity columns values that are generated when rows are inserted, an application can obtain the next sequence number without inserting the row by calling the NEXT VALUE FOR function.
  • Sequences, unlike identity columns can generate numeric values in an ascending or descending order at a defined interval and can be configured to restart when ever needed.
  • Sequence can also have a Data which is mostly any integer type but by default it is bigint.

How to create sequence?

CREATE SEQUENCE Test.CountBy1 START WITH n INCREMENT BY m;

GO

Where n,m can be any positive of negative integer

OR

CREATE SEQUENCE Temp.NumDesc   AS decimal(3,0)    START WITH 125

INCREMENT BY 25   MINVALUE 100  MAXVALUE 200  CYCLE;

 

How to use sequence?

To activate the sequence or to fetch  the first value

SELECT NEXT VALUE FOR Temp.NumDesc

 

To fetch current value

SELECT current_value FROM Temp.NumDesc

To insert value in a column of a  table using sequence

INSERT  Test.TestTable(CounterColumn,Name) VALUES(NEXT VALUE FOR Test.CountBy1,’Samuel’);

           Identity Sequence
Defines as part of a table It is not tied to the table
Auto generates values on insert Value needs to be generated Explicitly
Can not restart the value Can be restarted  all over again
Value can not be  Cached Can be Cached
Same value cannot be used again Same value can be used several times without regenerating new value

All SQL date formats

Hi,

In this Post I will list all the SQL date formats which we generally use while writing the SQL queries,

The most frequently asked questions in SQL Server forums is how to format a datetime value or column into a specific date format. So here is a list of different date formats that come standard in SQL Server as part of the CONVERT function.

 

Type Code Format Code

100

Apr 16 2013 11:01AM SELECT convert(varchar,getdate(),100)

101

4/16/2013

SELECT convert(varchar,getdate(),101)

102

2013.04.16 SELECT convert(varchar,getdate(),102)

103

16/04/2013 SELECT convert(varchar,getdate(),103)

104

16.04.2013 SELECT convert(varchar,getdate(),104)

105

16-04-2013 SELECT convert(varchar,getdate(),105)

106

16-Apr-13

SELECT convert(varchar,getdate(),106)

107

16-Apr-13

SELECT convert(varchar,getdate(),107)

108

11:01:54

SELECT convert(varchar,getdate(),108)

109

Apr 16 2013 11:01:54:223AM SELECT convert(varchar,getdate(),109)

110

4/16/2013

SELECT convert(varchar,getdate(),110)

111

4/16/2013

SELECT convert(varchar,getdate(),111)

112

20130416

SELECT convert(varchar,getdate(),112)

113

16 Apr 2013 11:01:54:223 SELECT convert(varchar,getdate(),113)

114

11:01:54:223 SELECT convert(varchar,getdate(),114)

 

Please copy the below code and execute in SQL management student you will get the above information

select ‘100’ TypeCode,convert(varchar,getdate(),100) Format, ‘SELECT convert(varchar,getdate(),100)’ Code UNION

select ‘101’ TypeCode,convert(varchar,getdate(),101) Format, ‘SELECT convert(varchar,getdate(),101)’ Code UNION

select ‘102’ TypeCode,convert(varchar,getdate(),102) Format, ‘SELECT convert(varchar,getdate(),102)’ Code UNION

select ‘103’ TypeCode,convert(varchar,getdate(),103) Format, ‘SELECT convert(varchar,getdate(),103)’ Code UNION

select ‘104’ TypeCode,convert(varchar,getdate(),104) Format, ‘SELECT convert(varchar,getdate(),104)’ Code UNION

select ‘105’ TypeCode,convert(varchar,getdate(),105) Format, ‘SELECT convert(varchar,getdate(),105)’ Code UNION

select ‘106’ TypeCode,convert(varchar,getdate(),106) Format, ‘SELECT convert(varchar,getdate(),106)’ Code UNION

select ‘107’ TypeCode,convert(varchar,getdate(),107) Format, ‘SELECT convert(varchar,getdate(),107)’ Code UNION

select ‘108’ TypeCode,convert(varchar,getdate(),108) Format, ‘SELECT convert(varchar,getdate(),108)’ Code UNION

select ‘109’ TypeCode,convert(varchar,getdate(),109) Format, ‘SELECT convert(varchar,getdate(),109)’ Code UNION

select ‘110’ TypeCode,convert(varchar,getdate(),110) Format, ‘SELECT convert(varchar,getdate(),110)’ Code UNION

select ‘111’ TypeCode,convert(varchar,getdate(),111) Format, ‘SELECT convert(varchar,getdate(),111)’ Code UNION

select ‘112’ TypeCode,convert(varchar,getdate(),112) Format, ‘SELECT convert(varchar,getdate(),112)’ Code UNION

select ‘113’ TypeCode,convert(varchar,getdate(),113) Format, ‘SELECT convert(varchar,getdate(),113)’ Code UNION

select ‘114’ TypeCode,convert(varchar,getdate(),114) Format, ‘SELECT convert(varchar,getdate(),114)’ Code

Please do Comment to the posts..

Thank You….