This article explains schemas in SQL Server. A SQL schema in a database is a collection of logical structures of data. A schema in a database is a collection of logical structures of data. The schema is owned by a database user and is the same name as the database user. From SQL Server 2005, a schema is an independent entity (container of objects) different from the user who creates that object. In other words, schemas are very similar to separate namespaces or containers that are used to store database objects. Security permissions can be applied to schemas hence schemas are an important tool for separating and protecting database objects on the basis of user access rights. It improves flexibility for security-related administration of the database.

User schema separation
Before SQL Server 2005, database object owners and users were the same things and database objects (table, index, view and so on) were owned by the user. In other words database objects were directly linked to the user and the user could not delete them without removing the database object that were associated with the user. In SQL Server 2005, a schema separation is introduced, now the database object is no longer owned by a user, group or role. The schema can be owned by the user, group or role. The schema can have multiple owners. The schema ownership is transferrable. Database objects are created within the schema. Now the user can be dropped without the dropping of the database object owned by the user. But the schema cannot be deleted if it contains a database object.

The following are advantages of user schema separation:

The schema ownership is transferrable.
Database objects can be moved among the schemas.
A single schema can be shared among multiple users.
A user can be dropped without dropping the database objects associated with the user.
Provides more control of access and level of access.

Default schema
The default schema is the first schema searched when resolving object names. The user can be defined within the default schema. Using the "SCHEMA_NAME" function we can determine the default schema for the database.

The schema can be made the default for the user by defining DEFAULT_SCHEMA with CREATE USER or ALTER USER. If there is no default schema defined then SQL will assume "DBO" as the default schema. Note that there is no default schema associated with a user if the user is authenticated as a member of the group in the Windows operating system. In this case a new schema will be created and the name is the same as the user name.

Advantages of using Schema
Act as object protection tool: A schema can be a very effective object projection tool combined with the appropriate level of user permissions. A DBA can maintain control access to an object that would be very crucial.

Managing a logical group of database objects within a database: Schemas allow database objects to be organized into a logical group. This would be advantagous when multiple teams are working on the same database application and the design team wants to maintain integrity of the database tables.

Easy to maintain the database: A schema allows a logical grouping of the database objects, so the schema can help us in situations where the database object name is the same but falls in a different logical group.

Other Advantages
A single schema can be shared among multiple databases and database users.
A database user can be dropped without dropping database objects.
Manipulation of and access to the object is now very complex and more secure. The schema acts as an additional layer of security.
Database objects can be moved among schemas.
The ownership of schemas is transferable.

A schema is a very useful database concept and helps us to separate database users from the database object owners and also helps to create a logical grouping of database objects.

In this post we are going to share the interview questions or the information which you must know as a programmer or a developer, especially if you are a Dot Net developer. I hope you will like this article.

Background
I am a dot net developer. As a dot net developer, there are so many things that I must be aware of. I am sharing those in the form of articles, you can always read my other interview questions here in the following links.

So shall we now discuss about SQL interview questions.

SQL Interview Questions
Question: What are the types of Joins in SQL. Explain?

INNER JOIN: Returns all rows when there is at least one match in BOTH the tables.
LEFT JOIN: Returns all rows from the left table, and the matched rows from the right table.
RIGHT JOIN: Returns all rows from the right table, and the matched rows from the left table.
FULL JOIN: Returns all rows when there is a match in ONE of the table.

Question: What is the default join in SQL? Give an example query?
The default join is INNER JOIN.

Example

SELECT column_name(s)  
FROM table1  
INNER JOIN table2  
ON table1.column_name=table2.column_name;  

Question: Describe all the joins with examples in SQL?
SQL LEFT JOIN
The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.
    SQL LEFT JOIN Syntax  
    SELECT column_name(s)  
    FROM table1  
    LEFT JOIN table2  
    ON table1.column_name=table2.column_name;  

SQL RIGHT JOIN
The right join returns all the rows in the right table i.e. table 2 with the matching ones on the left table (table1).
    SELECT column_name(s)  
    FROM table1  
    RIGHT JOIN table2  
    ON table1.column_name=table2.column_name;  

SQL FULL OUTER
The full join returns all rows from the left table (table1) and from the right table (table2).
    SELECT column_name(s)  
    FROM table1  
    FULL OUTER JOIN table2  
    ON table1.column_name=table2.column_name;  

Question: What is Union and Union All ? Explain the differences?
SQL UNION
The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

Note: The UNION operator selects only distinct values by default.
    SELECT column_name(s) FROM table1  
    UNION  
    SELECT column_name(s) FROM table2;  
SQL UNION ALL
    SQL UNION ALL Syntax  
    SELECT column_name(s) FROM table1  
    UNION ALL  
    SELECT column_name(s) FROM table2;  

Allows duplicate values.

Question: Differentiate Clustered and Non clustered Index in SQL?
A clustered index is one in which the index’s order is arranged according to the physical order of rows in the table. Due to this reason there can only be one clustered index per table, usually this is the primary key.

A non clustered index is one in which the order of index is not in accordance with the physical order of rows in the table.

Create Index Syntax
CREATE INDEX [ CLUSTERED | NONCLUSTERED ] PIndex ON Persons (LastName,FirstName)

Question: Explain the difference between Stored Procedure and User Defined Function?

Stored Procedure
Stored procedures are reusable code in database which is compiled for first time and its execution plan saved. The compiled code is executed when every time it is called.

Function
Function is a database object in SQL Server. Basically it is a set of SQL statements that accepts only input parameters, perform actions and return the result. Function can return only a single value or a table. We can’t use functions  to Insert, Update, Delete records in the database table(s). It is compiled every time it is invoked.

Basic Difference
Function must return a value but in Stored Procedure it is optional (Procedure can return zero or n values).
Functions can have only input parameters for it whereas procedures can have input/output parameters.

Functions can be called from Procedure whereas Procedures cannot be called from Function.

Advanced Differences
Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it,  whereas Function allows only SELECT statement in it. Procedures cannot be utilized in a SELECT statement, whereas function can be embedded in a SELECT statement. Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section, whereas function can be.

The most important feature of stored procedures over function is retention and reusing the execution plan while in case of function it will be compiled every time.
Functions that return tables can be treated as another rowset. This can be used in JOINS with other tables.
Inline Function can be though of as views that take parameters and can be used in JOINS and other Rowset operations.
Exception can be handled by try-catch block in a procedure, whereas try-catch block cannot be used in a Function.
We can use transactions in stored procedure but not in functions.

Conclusion
Did I miss anything that you may think which is needed? Could you find this post as useful? I hope you liked this article. Please share me your valuable suggestions and feedback.

In this blog, we will learn how we can check which mode of SSAS is installed on our machine. SSAS is available with three modes of installation.

• Tabular
• Multidimensional
• SharePoint

To check which mode of SSAS is installed on your machine, follow the below steps.

• Open SSMS.
• Right-click on SSAS Properties.

The Mode of Server Name is available in the property window. In our case, SSAS is installed with the Multidimensional mode, so it is showing “Multidimensional”.

In this article, we will see how to call a function from a stored procedure in SQL Server 2012. Here, I have written a scalar function named MultiplyofTwoNumber that accepts two parameters and returns one parameter. Now I want to call this from a stored procedure. So let's take a look at a practical example of how to call a function from a stored procedure in SQL Server 2012. The example is developed in SQL Server 2012 using the SQL Server Management Studio.  There are some simple things to do that are described here.

There are two types of functions in SQL Server; they are:

•  System defined function
•  User defined function

User defined functions are three types in SQL Server. They are scalar, inline table-valued and multiple-statement table-valued.

Creating a User-Defined Scalar Function in SQL Server
Now create a function named MultiplyofTwoNumber with the two parameters number1 and number2 returning one parameter named result. Both parameters have the same type, int. The function looks as in the following:
    Create FUNCTION [dbo].[MultiplyofTwoNumber]  
    (  
           @Number1 int,  
           @Number2 int  
    )  
    RETURNS int  
    AS  
    BEGIN  
           -- Declare the return variable here  
           DECLARE @Result int  
           SELECT @Result = @Number1 * @Number2;  
           -- Return the result of the function  
           RETURN @Result  
    END  

Creating a Stored Procedure in SQL Server
A function can be called in a select statement as well as in a stored procedure. Since a function call would return a value we need to store the return value in a variable. Now creating a stored procedure which calls a function named MultiplyofTwoNumber; see:
    Create PROCEDURE [dbo].[callingFunction]  
    (  
    @FirstNumber int,  
    @SecondNumber int  
    )  
    AS  
    begin  
    declare @setval int  
    select dbo.[MultiplyofTwoNumber](@FirstNumber, @SecondNumber)  
    end  

Now, we can execute the procedure with duplicate values to check how to call a function from a stored procedure; see:
    USE [registration]  
    GO  
    DECLARE  @return_value int  
    EXEC  @return_value = [dbo].[callingFunction]  
        @FirstNumber = 3,  
        @SecondNumber = 4  

Now press F5 to run the stored procedure.

A function can be called using a select statement:

    Select dbo.[MultiplyofTwoNumber](3, 4) as MultiplyOfNumbers  

Now press F5 to run the stored procedure.

Output