It preserves the unmatched rows from the first (left) table, joining them with a NULL row in the shape of the second (right) table.
- Select * From Table1
- Left Outer Join
- on table1.ID=table2.ID
A left outer join displays all the rows from the first table and the matched rows from the second table.
Example
The following query retrieves the employee name and the corresponding department he belongs to, whereas all the departments are displayed even if the employee is not assigned to any department.
- SELECT e.EmployeeID, e.Employee_Name, d.Department_Name
- FROM employee e(nolock) LEFT JOIN department d(nolock)
- ON e.DepartmentID = d.DepartmentID
Output
33. What is a right join in SQL Server?
Answer:
Right Join: A RIGHT OUTER JOIN is one of the JOIN operations that allows you to specify a JOIN clause. It preserves the unmatched rows from the Table2 (right) table, joining them with a NULL in the shape of the Table1 (left) table. A LEFT OUTER JOIN B is equivalent to B RIGHT OUTER JOIN A, with the columns in a different order.
- Select * From Table1
- Right Outer Join
- on table1.ID = table2.ID
The right outer join displays all the rows from the second table and matching rows from the first table.
Example - SELECT e.EmployeeID, e.Employee_Name, d.Department_Name
- FROM employee e(nolock) RIGHT JOIN department d(nolock)
- ON e.DepartmentID = d.DepartmentID
Output
34. What is the database engine in SQL Server?
The SQL Server Database Engine, SQL Server Agent, and several other SQL Server components run as services. These services typically are started when the operating system starts. This depends on what is specified during setup; some services are not started by default.
A service is a type of application (executable) that runs in the system background. Services usually provide core operating system features, such as Web serving, event logging, or file serving. Services can run without showing a user interface on the computer desktop. The SQL Server Database Engine, SQL Server Agent, and several other SQL Server components run as services. These services typically are started when the operating system starts. This depends on what is specified during setup; some services are not started by default.
This article describes the management of the various SQL Server services on your machine. Before you log in to an instance of SQL Server, you need to know how to start, stop, pause, resume, and restart an instance of SQL Server. After you are logged in, you can perform tasks such as administering the server or querying a database.
Let's start now, select start/All Programs/Microsoft SQL Server2005/Configuration Tools/SQL Server Configuration Manager. It should look like Figure 1.
35. What are Analysis Services in SQL Server?
Answer: The purpose of analysis services is to turn data into information and provide quick and easy access to that information for decision-makers. SSAS provides OLAP by letting you design, create and manage multidimensional structures that contain data aggregated from other data sources, such as relational databases. It also provides many data mining algorithms for mining data from data sources. For delivering OLAP and data mining, it uses client and server technologies.
The main idea of SSAS is to provide fast results from data sources when we apply a query because in order to make a decision we need data of various dimensions.
We will look at the components of the Architecture in detail.
Server Architecture: This runs as a Windows service. The Msmdsrv.exe application is a server component. This application consists of security, XMLA listener, query processor and other components that perform the following tasks:
Client Architecture: SSAS has a thin client Component Architecture. All queries and calculations are resolved by the server only. So for each request, a server to client connection is required. There are several providers with SSAS to support various programming languages. These providers communicate using SOAP packets. You can understand this better with the following diagram:
36. What are the integration services in SQL Server?
Answer: Integration Services is a platform for building high-performance data integration and workflow solutions, including extraction, transformation, and loading (ETL) operations for data warehousing.
This includes graphical tools and wizards for building and debugging packages.
Uses of Integration Services:
One use of Integration Services is to merge data from multiple data stores and update the data to data warehouses and/or data marts. Create the Data Transformation process logic and automate the data loading process.
Architecture of Integration Services
Some important components of using Integration Services:
- SSIS Designer
- Runtime engine
- Tasks and other executables
- Data Flow engine and Data Flow components
- API or object model
- Integration Services Service
- SQL Server Import and Export Wizard
- Other tools, wizards and command prompt utilities
37. What are the data quality services in SQL Server?
Answer:
SQL Server Data Quality Services:
SQL Server 2012 Data Quality Services (DQS) is the data quality product from Microsoft SQL Server 2012. DQS enables you to perform a variety of critical data quality tasks, including correction, enrichment, standardization and de-duplication of your data.
DQS provides the following features to resolve data quality issues.
- Data Cleansing
- Matching
- Reference Data Services
- Profiling and Monitoring
- Knowledge Base
DQS enables you to perform data cleansing using cloud-based reference data services provided by reference data providers. DQS also provides profiling that is integrated into its data-quality tasks, enabling to analyze the integrity of the data.
Data Quality Server
Data Quality Server is implemented as three SQL Server catalogs: DQS_MAIN, DQS_PROJECTS, and DQS_STAGING_DATA.
- DQS_MAIN includes DQS Stored Procedures, DQS engine, and published Knowledge Bases.
- DQS_PROJECTS includes data that is required for Knowledge Base management and DQS project activities.
- DQS_STAGING_DATA provides an intermediate staging database where you can copy your source data to perform DQS operations, and then export your processed data.
Click on the following link to read further:
SQL Server 2012 Data Quality Services (DQS)
38. What are the reporting services in SQL Server?
Answer: SQL Server Reporting Services is a comprehensive reporting platform that includes processing components. Processing components are the basis for the multilayered architecture of SQL Server Reporting Services. Processing components interact with each other to retrieve data and deliver a report.
SQL Server Reporting Services has the following two basic components.
Architecture of SQL Server Reporting Services
The following diagram shows the architecture of SQL Server Reporting Services.
Tools and Components of SQL Server Reporting Services architecture
This architecture consists mainly of the following types of components and tools.
- Report Builder
- Report Designer
- Report Manager
- Report Server
- Report server database
- Data sources
Click on the following link to read further:
Architecture of SQL Server Reporting Services (SSRS)
39. What are the master data services in SQL Server?
Answer: The goal of MDS is to address the challenges of both operational and analytical master data management by providing a master data hub to centrally organize, maintain, and manage your master data. This master data hub supports these capabilities with a scalable and extensible infrastructure built on SQL Server and the Windows Communication Foundation (WCF) APIs.
Master Data Services Components
The wizard installs Master Data Services Configuration Manager, installs the files necessary to run the Master Data Services Web service, and registers assemblies. After installation, you use the Master Data Services Configuration Manager to create and configure a Master Data Services database in a SQL Server instance that you specify, create the Master Data Services Web application, and enable the Web service.
Data Stewardship
Master Data Manager is the data stewardship portal in which authorized business users can perform all activities related to master data management. At minimum, a user can use this Web application to review the data in a master data model. Users with higher permissions can make changes to the master data and its structure, define business rules, review changes to master data, and reverse changes.
Model Objects
Most activities in MDS revolve around models and the objects they contain. A model is a container for all objects that define the structure of the master data. A model contains at least one entity, which is analogous to a table in a relational database. An entity contains members, which are like the rows in a table, as shown in Figure 7-1. Members (also known as leaf members) are the master data that you are managing in MDS. Each leaf member of the entity has multiple attributes, which correspond to table columns in the analogy.
Master Data Maintenance
Master Data Manager is more than a place to define model objects. It also allows you to create, edit, and update leaf members and consolidated members. When you add a leaf member, you initially provide values for only the Name and Code attributes, as shown in Figure 7-4. You can also use a search button to locate and select the parent consolidated member in each hierarchy.
Click on the following link to read further:
Chapter 7: Master Data Services
40. What is replication in SQL Server?
Answer: Replication is a process or method to synchronize the data across multiple servers. Replication is done by a replica set. A replication maintains the same data set. Replica sets provide redundancy and high availability with multiple copies of data on different database servers.
Replication removes dependencies from a single server so replication protects a database from the loss of a single server. Replication provides a mechanism to recover from hardware failure and service interruptions.
Replication is also used to increase the read capacity.
Replication provides choices for the client to select a different server for read and write operations. Replication maintains copies in different data centers to increase the locality and availability of data for distributed applications.
Example: Snapshot Replication
Step 1
Open the replication node in your database and choose the option Local Publications.
Step 2
Right-click on Local Publications and click on New publication.
Step 3
After clicking on the new publication tab the following window will appear and click on the “Next” button.
Click on the following link to read further:
Snapshot Replication (Setting up Publisher) in SQL Server 2008: Part 2
41. How do you select data from an SQL Server table?
Answer: To select specific rows or all columns, selecting distinct rows, filtering with where clause, sorting rows using orderby and so on, we will be using the AdventureWorks2012 database.
To select all the rows and columns from a table, we use the following query:
- SELECT * FROM HumanResources.Employee
Execute the query by pressing F5 or via the execute button.
Output
There is another way to select all the columns from a table. Instead of using * we can specify the column names.
- SELECT BusinessEntityID, NationalIDNumber, LoginID, OrganizationNode, OrganizationLevel, JobTitle, BirthDate, MaritalStatus, Gender, HireDate, SalariedFlag, VacationHours, SickLeaveHours, CurrentFlag, rowguid, ModifiedDate FROM HumanResources.Employee
The output will be the same.
If you feel lazy in writing this long query given above then what you can do is go to the Object Explorer window, then expand adventureWorks2012 then select HumanResourcesEmployee table and right-click on it. After that "select script table as" then select "To", then you will see a New query editor window.
SQL Server will generate the SELECT query for us.
42. What is a Check in SQL?
Answer: A Check Constraint is a rule that identifies valid values for columns of data. A Check Constraint helps to enforce Domain Integrity. If the condition in a Check Constraint is not satisfied then it prevents the value from entering into the database.
Syntax: - Create table tableName(Column1 dataType Check(expression), Column2, columnN)
Example: - create table emp(empId int check(empId >10),empName varchar(15))
Output:
- insert into emp values(8,'d')
Output:
Dropping the Check Constraint:
First, we can determine the name of the constraint using the following command:
Output:
43. What is a default in SQL?
Answer: Constraints are rules that decide what kind of data can enter into the database tables. SQL server has six types of constraints and we will explore all these constraints here with suitable examples. The constraints that we are going to explore are listed below:
- Primary Key Constraint
- Foreign Key Constraint
- Not Null Constraint
- Unique constraint
- Default Constraint
- Check Constraint
Default Constraint
The default constraint allows you to set a default value for the column. That means when a row is created for the first time, and there is no entry specified for the column that has a default constraint on it, then the default value is stored in the column.
Note that this not a Not Null constraint and do not confuse the default value constraint with disallowing the Null entries. The default value for the column is set only when the row is created for the first time and column value is ignored on the Insert. Modification to the column with NULL value or even the Insert operation specifying the Null value for the column is alowed.
Let us set the Default value of 1 for the Class. Here are the steps:
- Bring up the table designer
- Select the Class Row as you already did.
- At the bottom of the layout, you will see a Column properties as shown in the below picture. Set the default as shown below:
column properties
For further information click on the link:
Table Constraints in SQL 2005
44. What is a constraint in SQL?
Constraints are the rules that decide what kind of data can enter into the database tables. SQL server has six types of constraints and we will explore all these constraints here with suitable examples. The constraints that we are going to explore are listed below:
- Primary Key Constraint
- Foreign Key Constraint
- Not Null Constraint
- Unique constraint
- Default Constraint
- Check Constraint
First, Create two tables
To explain these constraints we need two tables. Firstly, let us create these tables. Run the scripts shown below to create the tables. Copy and paste the code into the new Query Editor window, then execute it.
- CREATE TABLE Student(StudId smallint, StudName varchar(50), Class tinyint);
- CREATE TABLE TotalMarks(StudentId smallint, TotalMarks smallint);
- Go
Note that there are no constraints at present on these tables. We will add the constraints one-by-one.
Primary Key Constraint
A table column with this constraint is called the key column for the table. This constraint helps the table to make sure that the value is not repeated and also that there are no null entries. We will mark the StudId column of the Student table as the primary key. Follow these steps:
- Right-click the student table and click on the modify button
- From the displayed layout select the StudId row by clicking the Small Square like button on the left side of the row.
- Click on the Set Primary Key toolbar button to set the StudId column as the primary key column.
Now, this column does not allow null values and duplicate values. You can try inserting values to violate these conditions and see what happens. A table can have only one Primary key. Multiple columns can participate in the primary key column. Then the uniqueness is considered among all the participant columns by combining their values.
Not Null Constraint
This constraint is useful to stop storing the null entries in the specified columns. We will mark the student name column as the not null column. This allows us to always have some entries in the student name column of the student table without having NULL. Follow the steps below:
- As you did previously, bring up the table design view by clicking the modify context menu for the table.
- Remove the checkmark as shown in the picture below. This action will enable the Not Null constraint for the StudName column.
Default Constraint
The default constraint allows you to set a default value for the column. That means when a row is created for the first time, and there is no entry specified for the column that has a default constraint on it, then the default value is stored in the column. Note that this not a Not Null constraint and do not confuse the default value constraint with disallowing the Null entries. The default value for the column is set only when the row is created for the first time and column value is ignored on the Insert. Modification to the column with NULL value or even the Insert operation specifying the Null value for the column is allowed.
Let us set the Default value of 1 for the Class. Here are the steps:
- Bring up the table designer
- Select the Class Row as you already did.
- At the bottom of the layout, you will see a Column properties as shown in the below picture. Set the default as shown below:
For further information, click on the following link:
Table Constraints in SQL 2005
45. How do I define constraints in SQL?
Answer: Constraints
Constraints are rules and restrictions applied to a column or a table such that unwanted data can't be inserted into tables. This ensures the accuracy and reliability of the data in the database. We can create constraints on single or multiple columns of any table. Constraints maintain the data integrity and accuracy in the table.
Constraints can be classified into the following two types.
Column Types Constraints
Definitions of these types of constraints are given when the table is created.
- Create Table My_Constraint
- (
- IID int NOT NULL,
- Salary int CHECK(Salary > 5000)
- )
Table Types Constraints
Definitions of these types of constraints are given after the creation of the table using the Alter Command.
-
- Alter Table My_Cosntraint
-
- Add constraint Check_Constraint Check(Age>50)
SQL Server contains the following six types of constraints:
- Not Null Constraint
- Check Constraint
- Default Constraint
- Unique Constraint
- Primary Constraint
- Foreign Constraint
Let us understand each constraint briefly.
Not Null Constraint
A Not Null constraint restricts the insertion of null values into a column. If we are using a Not Null Constraint for a column then we cannot ignore the value of this column during insertion of data into the table.
Column Level
Syntax - CREATE TABLE Table_Name
- (
- Column_Name Datatype CONSTRAINT Constraint_Name NOT NULL,
- );
Example - Create Table My_Constraint
- (
- IID int NOT NULL,
- Name nvarchar(50) CONSTRAINT Cons_NotNull not null,
- Age int Not Null,
- )
Table Level
Syntax - ALTER TABLE Table_Name
- ALTER COLUMN Column_Name Datatype NOT NULL
Example - Alter Table My_Constraint
- Alter Column IId int Not Null
Without SQL Command
We can also create a Not Null constraint in Microsoft SQL Server without execution of a SQL query.
First right-click on the table and select and click on the design option. Now check all the columns in the “Allow Nulls” option that should have a Null Value.
Check Constraint
A Check constraint checks for a specific condition before inserting data into a table. If the data passes all the Check constraints then the data will be inserted into the table otherwise the data for insertion will be discarded. The CHECK constraint ensures that all values in a column satisfy certain conditions.
46. What is the meaning of Not Null in SQL?
Constraints are rules that decide what kind of data can enter into the database tables. SQL server has six types of constraints and we will explore all these constraints here with suitable examples. The constraints that we are going to explore are listed below:
- Primary Key Constraint
- Foreign Key Constraint
- Not Null Constraint
- Unique constraint
- Default Constraint
- Check Constraint
This constraint is useful to stop storing the null entries in the specified columns. We will mark the student name column as the not null column. This allows us to always have some entries in the student name column of the student table without having NULL. Here are the steps:
- Like you did previously, bring up the table design view by clicking the modify context menu for the table.
- Remove the checkmark as shown in the picture below. This action will enable the Not Null constraint for the StudName column.
Example:
Click on the following link to read further:
Table Constraints in SQL 2005
47. How to alter a table schema in SQL Server?
Answer:
Altering Tables: It is used to modify an existing table.
- CREATE TABLE Stock
- (
- ID SMALLINT
- );
- mysql > ALTER TABLE Stock - > ADD COLUMN Quantity SMALLINT UNSIGNED NOT NULL, - > MODIFY ID SMALLINT UNSIGNED NOT NULL, - > ADD PRIMARY KEY(ID);
- mysql > Describe Stock;
- mysql > ALTER TABLE Stock;
Example in SQL: