Thursday, June 30, 2016

Introduction to SQL Server 2012

Microsoft SQL Server Management Studio is a powerful and flexible tool.
This lesson shows you how the windows work together to present a design surface that makes your work easier. At the end of this lesson, you will know how to clear unwanted windows out of your way, how to quickly bring up windows with the information you need, and how to configure the default surface to support the work you do most often.

o   Starting SQL Server Management Studio
o   Connecting with Registered Servers and Object Explorer
o   Changing the Environment Layout
o   Displaying the Document Window
o   Showing the Object Explorer Details page


Starting SQL Server Management Studio: - Click Windows button, on the Start menu, point to All Programs, point to Microsoft SQL Server 2012, and then click SQL Server Management Studio. Alternately from the Run dialog box, type SSMS.exe and then click OK


In the next step, you connect to SQL Server using the Object Explorer component of SSMS. Click Object Explorer. In the Object Explorer menu, click the Connect button, and then click Database Engine.

The Connect to Server dialog box should appear. (If you have previously installed SSMS, the user settings might be causing the Connect to Server dialog box to appear automatically.)


In the Connect to Server dialog box, complete the Server name box. You can connect to one of three types of SQL Server. Each type has a slightly different format for the Server name box. Pick one of the following formats:

--
 A default instance of SQL Server: When you install SQL Server on a computer, you can specify that the instance of SQL Server will be a default (unnamed instance) or a named instance.
If you are connecting to a default instance of SQL Server, insert the name of the computer. For example, if you are running SSMS on a computer named Accounting and you are connecting to a default instance of SQL Server installed on that computer, type Finance in the Server name box.

--
 A named instance of SQL Server: During setup of SQL Server, you can specify a name for the instance; for example on a computer named Finance, you could specify a named instance named Department. To connect to a named instance, in the Server name box, type the computer name backslash instance name; for example Finance\ Department.




Database information is shown in Object Explorer and document windows.
Object Explorer is a tree view of all the database objects in a server.
This can contain the databases of the
·         SQL Server Database Engine,
·         Analysis Services,
·         Reporting Services,
·         Integration Services.
Object Explorer includes information for all servers to which it is connected. When you open Management Studio, you are prompted to connect Object Explorer to the settings that were last used. You can double-click any server in the Registered Servers component to connect to it, but you do not have to register a server to connect.
The document window is the largest portion of Management Studio.
The document windows can contain query editors and browser windows. By default, the Summary page is displayed, connected to the instance of Database Engine on the current computer.

   
  

Wednesday, June 29, 2016

Introduction to DBMS SQL Server

A database management system (DBMS) is system software for crafting and managing databases. The DBMS offers users and programmers with an organized way to create, retrieve, update and accomplish data.
A DBMS makes it possible for end users to create, read, update and delete data in a database. The DBMS basically serves as an interface between the database and end users or application programs, confirming that data is consistently prepared and remains simply accessible.
The DBMS accomplishes three important things: the data, the database engine that allows data to be accessed, locked and modified and the database diagram, which defines the database’s logical structure. These three foundational elements help provide concurrency, security, data integrity and uniform management procedures. Typical database administration tasks supported by the DBMS include change management, performance monitoring/tuning and backup and recovery. Many database management systems are also responsible for automated rollbacks, restarts and recovery as well as the logging and auditing of activity.
The DBMS is possibly most useful for providing a unified view of data that can be accessed by numerous users, from various locations, in a controlled manner. A DBMS can limit what data the end user sees, as well as how that end user can view the data, providing many views of a single database schema. End users and software programs are free from having to understand where the data is physically located or on what type of storage media it resides because the DBMS levers all requests.
The DBMS can offer both logical and physical data independence. That means it can protect users and applications from needing to know where data is stored or having to be worried about changes to the physical structure of data (storage and hardware). As long as programs use the application programming interface (API) for the database that is provided by the DBMS, developers won't have to modify programs just because changes have been made to the database.
With relational DBMSs (RDBMSs), this API is SQL, a standard programming language for defining, protecting and accessing data in a RDBMS.
Common types of DBMS systems:-
Popular database models and their management systems include:
Relational database management system (RDMS) - flexible to most use cases, but RDBMSTier-1 products can be quite expensive.
NoSQL DBMS - compatible for loosely defined data structures that may change over time. 
In-memory database management system (IMDBMS) - provides faster response times and better performance.
Columnar database management system (CDBMS) - well-suited for data warehouses that have a large number of similar data items.
Cloud-based data management system - the cloud service provider is accountable for providing and maintaining the DBMS.
Benefits of a DBMS

Using a DBMS to store and manage data comes with advantages, but also overhead. One of the biggest advantages of using a DBMS is that it lets end users and application programmers access and use the same data while managing data integrity. Data is better protected and maintained when it can be shared using a DBMS instead of creating new iterations of the same data stored in new files for every new application. The DBMS provides a central store of data that can be accessed by numerous users in a controlled manner.

Monday, June 27, 2016

SQL Server INFORMATION_SCHEMA views

Following are the information_schema tables:-

  • An information schema view is one of several methods SQL Server provides for obtaining metadata. 
  • Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables. 
  • The information schema views included in SQL Server comply with the ISO standard definition for the INFORMATION_SCHEMA.

INFORMATION_SCHEMA.CHECK_CONSTRAINTS
 INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
 INFORMATION_SCHEMA.COLUMN_PRIVILEGES
 INFORMATION_SCHEMA.COLUMNS
 INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
 INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
 INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS
 INFORMATION_SCHEMA.DOMAINS
 INFORMATION_SCHEMA.KEY_COLUMN_USAGE
 INFORMATION_SCHEMA.PARAMETERS
 INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
 INFORMATION_SCHEMA.ROUTINE_COLUMNS
 INFORMATION_SCHEMA.ROUTINES
 INFORMATION_SCHEMA.SCHEMATA
 INFORMATION_SCHEMA.TABLE_CONSTRAINTS
 INFORMATION_SCHEMA.TABLE_PRIVILEGES
 INFORMATION_SCHEMA.TABLES
 INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
 INFORMATION_SCHEMA.VIEW_TABLE_USAGE
 INFORMATION_SCHEMA.VIEWS

Example:-select * from INFORMATION_SCHEMA.TABLES



SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT  
FROM AdventureWorks2012_Data.INFORMATION_SCHEMA.COLUMNS  
WHERE TABLE_NAME = N'Product';  
GO  



Monday, June 20, 2016

SSIS:SCD Type1 Step by Step

Scenario: - Slowly changing dimension type 1 is useful when need to insert new data or update existing data in table columns basically it overwrites the existing data if any updates present in the data

Following are the functionalities of SCD
  • ·         Matching incoming rows with rows in the lookup table to identify new and existing rows.
  • ·         Identifying incoming rows that contain changes when changes are not permitted.
  • ·         Identifying inferred member records that require updating.
  • ·         Identifying incoming rows that contain historical changes that require insertion of new records and the updating of expired records.
  • ·        Detecting incoming rows that contain changes that require the updating of existing records, including expired ones.
Implementation:-
Following table is used to implement SCD Type scenario.

Source table 

CREATE TABLE [dbo].[MyEmployees](
       [EmployeeID] [smallint] NOT NULL,
       [FirstName] [nvarchar](30) NOT NULL,
       [LastName] [nvarchar](40) NOT NULL,
       [Title] [nvarchar](50) NOT NULL,
       [DeptID] [smallint] NOT NULL,
       [ManagerID] [int] NULL,
 CONSTRAINT [PK_EmployeeID] PRIMARY KEY CLUSTERED
(
       [EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Insert sample data into table

INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (1, N'Ken', N'Sánchez', N'Chief Executive Officer', 16, NULL)
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (16, N'David', N'Bradley', N'Marketing Manager', 4, 273)
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16)
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (273, N'Brian', N'Welcker', N'Vice President of Sales', 3, 1)
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (274, N'Stephen', N'Jiang', N'North American Sales Manager', 3, 273)
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (275, N'Michael', N'Blythe', N'Sales Representative', 3, 274)
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (276, N'Linda', N'Mitchell', N'Sales Representative', 3, 274)
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (285, N'Syed', N'Abbas', N'Pacific Sales Manager', 3, 273)
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (286, N'Lynn', N'Tsoflias', N'Sales Representative', 3, 285)

Destination table 

CREATE TABLE [dbo].[MyEmployees_Destination](
       [EmployeeID] [smallint] NOT NULL,
       [FirstName] [nvarchar](30) NOT NULL,
       [LastName] [nvarchar](40) NOT NULL,
       [Title] [nvarchar](50) NOT NULL,
       [DeptID] [smallint] NOT NULL,
       [ManagerID] [int] NULL)

Step 1:-Go to control flow drag DFT
Step2:- Double click on DFT and Drag OLEDB source to dataflow flow Configure as per below shown image

Step3:- Drag slowly changing dimension to control flow and configure as per below show image
Select destination table, Key type
Business key is used to identify unique rows in table columns Ex: - we will match EmployeeID from source to destination if the incoming row employee id matched with existing ID It will check for updates in table and updates the existing data otherwise it will insert the new row
Select business key EmployeeID and click next.

Step4:- Select dimensions column’s and Change type

Step 5:- Click next, next to screen, Uncheck inferred dimension members click next and finish. SSIS will insert required tasks automatically

Testing the package: - Execute the package, if you execute for one more time It won’t insert the data


Update data in source table with below query

UPDATE [MyEmployees] set Title='Sales Cordinator' where EmployeeID='286'

Now execute the package it will overwrite the tittle of employeeid 286,


Conclusion:- Update,insert

Friday, June 17, 2016

How to create a new user in SQL Server

Use below syntax to create user login  to sql server database

-- Which database to use.
USE master
GO

-- Delete existing user.
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'sa')
DROP USER [sa]
GO

-- Which database to use.
USE [master]
GO

-- Delete existing login.
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = 'sa')
DROP LOGIN sa
GO


-- Add new login.
CREATE LOGIN [samanth] WITH PASSWORD=N'S0meComplexPassword', DEFAULT_DATABASE=master
GO

-- Which database to use.
USE [master]
GO

-- Add new user.
CREATE USER [samanth] FOR LOGIN [samanth] WITH DEFAULT_SCHEMA=[dbo]
GO

-- Add to database read / write roles
EXEC sp_addrolemember 'db_datareader', 'samanth'
EXEC sp_addrolemember 'db_datawriter', 'samanth'
GO


-- Add to database owner role? 
-- Only give out if application needs a high level of privileges.
-- EXEC sp_addrolemember 'db_owner', 'samanth'



How to disable foreign key constraint in sql server

Some times we need to delete unwanted data from the tables at that time foreign key will not allow deleting data from the table. Then Use below syntax for disabling/enabling foreign key temporarily
Method 1:-
-- Disable all table constraints

ALTER TABLE TableName NOCHECK CONSTRAINT ALL

-- Enable all table constraints

ALTER TABLE TableName WITH CHECK CHECK CONSTRAINT ALL

-- Disable single constraint

ALTER TABLE TableName NOCHECK CONSTRAINT Constraint

-- Enable single constraint

ALTER TABLE TableName WITH CHECK CHECK CONSTRAINT Constraint

Method 2:-

If you want to disable all constraints in the database just run this code:
-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- enable all constraints

exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Thursday, June 9, 2016

How to remove duplicate rows from a table in SQL Server

I have reproduced the scenario by using below query for better explanation

insert into [HumanResources].[Department1] --- identity_insert turned off

select * from [HumanResources].[Department]

Below table contain duplicate records

I used row_number function to trace out duplicate rows from the [HumanResources].[Department1]

WITH CTE AS(
   SELECT *,
     RN = ROW_NUMBER()OVER(PARTITION BY departmentid,name,groupname  ORDER BY departmentid )
   FROM [HumanResources].[Department1]
)
DELETE  FROM CTE WHERE RN > 1 



Sql server count rows in all tables

How to get number of records in each table of a database?

sp_msforeachtable 'select ''?'' ,count(*) from ?'



How to get number of rows in some of the tables of a database

sp_msforeachtable
'if ''?'' in (''[dbo].[Account]'')
select ''?'' as table_name , count(*) as number_of_rows from ? '

---how to get tables which are having 0 rows, or the emplty table

sp_msforeachtable
'select ''?'' as table_name , count(*) as number_of_rows from ? having count(*)=0'



--Alter all table and enable trigger on all of the table

sp_msforeachtable 'alter table ? enable trigger all'

Using all other parameters of sp_msforeachtable , we can use below parameter as per requirement

@command1 – first command that will execute for all tables
@command2 –this command will execute next to command1
@command3- this command will execute next to command2

@whereand – it has another parameter called @whereand, which is appended to the WHERE clause of the internal query that is being used to find the tables (and should start with an AND).
One can also use aliases like o against sysobjects, and a second alias syso against sys.all_objects.

@precommand- is the command which will be executed before all othercommands defined in the parameters of sp_msforeachtable
@postcommand - is the command which will be executed after all othercommands defined in the parameters of sp_msforeachtable


For displaying the tables which names are in the where and condition, along with tables, it will also display current database name, before the result of @command1 and after the result of @command1