SQL/SSRS Interview questions I thought of blogging some SQL/SSRS interview questions.

Below are some.

I will add more, when I complete the compilation

1. What is OLTP(Online Transaction Processing)?

OLTP stands for Online Transaction Processing.

It is mainly used, when you have frequent inserts/deletes/updates.

OLTP will have normalized tables.

OLAP stands for Online Analytical processing.

OLAP is used, when we need historical data to be preserved and do not have frequent updates/deletes/inserts.

Tables are denormalized, and are used for multi-dimensional analysis/modelling.

OLAP forms the basis for data mining and knowledge discovery

2. What is normalization and what are the different forms of normalization?

Normalization is a way to arrange data in your database.

Primary goal being to reduce redundancy of data.

There are 6 normal forms (http://en.wikipedia.org/wiki/Database_normalization)

Generally most of the databases are normalised upto 3 NF (NF – normal form)

First Normal Form (1NF): No repeating columns or groups of columns

Second Normal Form (2NF): No partial dependencies on a concatenated key.

Third Normal Form (3NF): No dependencies on non-key attributes

Fourth normal Form (4NF) : Isolate independant multiple relationships

Fifth normal Form (5NF): Isolate semantically related multiple relationships

3.What are views?

A view is a kind of virtual table or a stored query.

The result set of the SELECT statement forms the virtual table returned by the view.

Example:

CREATE VIEW view_name AS

SELECT column_name(s)

FROM table_name

WHERE condition

Example: http://www.youtube.com/watch?v=to_0_bODY2Y

4. What are indexed views?

A view with a unique clustered index is an indexed view.

Example:

CREATE TABLE sample_table(

A            INT PRIMARY KEY, 

B int

)

GO

CREATE VIEW sample_view WITH SCHEMABINDING AS

SELECT A, B

FROM dbo.sample_table

GO

CREATE UNIQUE CLUSTERED INDEX idx_MyView ON sample_view(B)

5. What are stored procedures?

Stored procedures are group of SQL statements that are created previously and stored.

Stored procedures can accept parameters, and also have output parameters.

Permisions can be modifed for stored procedures.

Example: 

CREATE PROCEDURE sp_Sample

@param varchar(10)

AS

SELECT A, B

FROM sometable

WHERE B = @param

EXECUTE sp_Sample ‘ABC’

Example: http://www.youtube.com/watch?v=jx63oKvU8Iw

6. Give an example for Stored Procedures with parameters and output parameters.

CREATE PROCEDURE GetImmediateManager

   @employeeID INT,

   @managerID INT OUTPUT

AS

BEGIN

   SELECT @managerID = ManagerID 

   FROM HumanResources.Employee 

   WHERE EmployeeID = @employeeID

END

7.What is xml data type?

The xml data type lets you store XML documents and fragments in a SQL Server database.

Example:

http://www.youtube.com/watch?v=OO-AnA_52Og

8. Explain filestream with an Example.

FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) 

data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. 

Example: http://www.youtube.com/watch?v=ONYjKN21aIw

9.Explain stored procedure, function and the difference between them?

Stored procedures are group of SQL statements that are created previously and stored.

User defined function is a database object which has one or more sql statements , which can accept zero or more parameters and return 

either a value or table.

Difference:

1. Stored Procedure can have output parameters – UDF ‘s cannot.

2. Stored Procedures return integers – UDF’s can return scalar or tables.

3. Stored procedures can have Insert/Delete/update Statement – UDF’s cannot

4. Stored Procedures are compiled at compile time – UDF’s are compiled at runtime.

5. Stored procedure cannot be used in the select/where/having clause – UDF’s can be

Example: 

Stored procedures: https://www.youtube.com/watch?v=jx63oKvU8Iw

User Defined Functions: https://www.youtube.com/watch?v=vBg216_AODA

10. When do we use a having clause and when do we use a where clause?

Having Clause applies to groups – hence used with groupby clause.

Where is applied row by row

Example:

SELECT A, AVG(B)

FROM some_table

GROUP BY A

HAVING AVG(B) >10

11.Explain user defined functions and the difference forms of UDF

https://www.youtube.com/watch?v=vBg216_AODA

12. What are recursive stored procedures?

Stored Procedures calling itself in a repetative fashion are called Recursive stored procedures.

13.What are the different error handling techniques?

https://www.youtube.com/watch?v=PUYlJHMhoAs

14. Write a query to find the second/third/nth largest Salary in a table: Emp(EmpID, EmpName, Salary)

SELECT TOP 1 salary

FROM (SELECT DISTINCT TOP 2 salary

FROM employee

ORDER BY salary DESC) a

ORDER BY salary 

15. What is a trigger? What are the different types of them?

Answer: A trigger is an event (set of DDL statements) that is automatically fired when some sort of an insert/delete/update occurs on a 

database.

Triggers can be nested.

Types of triggers:

DDL trigger 

DML trigger (instead of/after trigger)

Example:

CREATE TRIGGER T1

ON TABLE_NAME

AFTER INSERT

AS

BEGIN

ROLLBACK TRANSACTION

END

GO

16.Explain linked server with an example.

A Linked Server is a connection to an external data source. The remote data source can be DB2, Oracle, Access or any other data source that uses an OLE DB provider.

Example:

 sp_addlinkedserver [ @server = ] ‘server‘ 

    [ , [ @srvproduct = ] ‘product_name‘ ]
[ , [ @provider = ] ‘provider_name‘ ]
[ , [ @datasrc = ] ‘data_source‘ ]
[ , [ @location = ] ‘location‘ ]
[ , [ @provstr = ] ‘provider_string‘ ]
[ , [ @catalog = ] ‘catalog‘ ]

 17.Explain collation and collation sensitivity
Collation:  Collation controls the way string values are sorted. The default collation sorts alphabetically using the standard Latin character set. Other collations will sort in different orders Collation sensitivity:

Width sensitivity:When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive

Accent sensitivity:  If a and á, o and ó are treated in the same way, then it is accent-insensitive.

Case sensitivity:  If A and a, B and b, etc. are treated in the same way then it is case-insensitive. SQL distinguishes using ASCII values of A(65) and a(97)

Kana Sensitivity:  When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive

18.What is primary key/Unique Key and difference between them?

Primary key:  The key of a relational table that uniquely identifies each record in the table. Primary Key doesn’t allow NULLS

                            Clustered Indexes are created on Primary Keys.

Unique Key:   Unique Key enforces uniqueness of the column on which they are defined. Unique Key allows one NULL value

                     Non-Clustered indexes are created on Unique Keys.          

19.Explain 1:1, 1:m, m:m relationships1:1 -In a one-to-one relationship, a row in table A can have no more than one matching row in table B, and vice versa. A one-to-one relationship is created if both of the related columns are primary keys or have unique constraints.

1:many – In 1:many type of relationship, a row in table A can have many matching rows in table B, but a row in table B can have only one matching row in table A. 

many:many: In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa. 20.Explain delete and truncate statement and the differenceDelete:  The DELETE Statement is used to delete rows from a table. 

DELETE is a logged operation on a per row basis

Example:

DELETE FROM table_name [WHERE condition]; 

Truncate:  The TRUNCATE command is used to delete all the rows from the table and free the space containing the table 

21.Explain the merge statement with an example.
Merge Statements perform insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table. 
22.What is an index? What are the different types of indexes? what is the difference between them?

An index can be created in a table to find data more quickly and efficiently. it is a structure within SQL that is used to quickly locate specific rows within a table.

CLUSTERED INDEX: Clustered indexes define the logical order of the table. The leaf level of the clustered index has the actual data pages of the table. Because of this there can only be one clustered index per table. 

A table that does not have a clustered index is referred to as a heap

NON-CLUSTERED INDEX:  Here, the leaf level of a nonclustered index has a pointer as part of each index row. That pointer is either the clustered index key in the cases where the base table has a clustered index or the Row Identifier in the cases where the table is a heap 

23.Difference between char and nvarchar / char and varchar data-type?

char [ ( n ) ] : Fixed-length, non-Unicode string data. n defines the string length and must be a value from 1 through 8,000. The storage size is n bytes. 
varchar [ ( n | max ) ] : Variable-length, non-Unicode string data. n defines the string length and can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). 
nvarchar [ ( n | max ) ] : Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). 
24.What are joins? What are the different types of joins?

Joins are used to query data from more than 2 tables, based on a relationship between columns in these tables.

JOIN: Return rows when there is at least one match in both tables.

LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table

RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table

FULL JOIN: Return rows when there is a match in one of the tables

Installing Sample databases (Adventure Works)

Next, lets install some sample databases for us to work with. One of the most commonly used databases is the SQL adventureworks database. SQL server adventureworks database download is available here.

Once you download the database, lets look at how to setup sql database.

Step 1: Install your downloaded database (sql server 2008 sample databases download), by choosing the correct instance name.

Step 2: After installation, open SSMS --> your instance name, then check if the below databases are visible.

Step 3: You can also install some sample reports from here

SQL Server Reporting Services Installation

Lets move forward with getting to know how to install sql server 2008 reporting services. Please note, most of the steps are the same for installing sql server reporting services 2005.
Below are the steps to install SQL reporting services 2008 with express Database.

Step 1 :  Download the 64 bit or 32 bit version of SQL 2008 Express with advances services from here .

Step 2:  Right Click on the downloaded file and "Run as Administrator".

Step 3: The files will extract itself.

Step 4: In case you get a pop-up saying "This program has compatibility issues", click Run Program.

Step 5: You should be getting a progress screen.

Step 6: The SQL server Installation Center should fire up.

Step 7 : Select installation on right hand tab and click on "New SQL server stand -alone installation"

Step 8: SQL server will load the setup files.

Step 9 : Click OK, it should take you to next page.

Step 10 : Click Install.

Step 11: Click next, then again next.

Step 12 : Click next

Step 13 :  In feature selection, make sure you select 'Database Engine Services' and 'Reporting Services'

Step 14 :   Next, select Named Instance and give it any name (you can also choose Default instance)

Step 15 : Click next

Step 16 :  Select Mixed Mode --> enter in any password --> and specify SQL server admins.

Step 17 : Click next, and select 'Install, but do not configure under Reporting Services configuration.

Step 18 : Click Next

Step 19 : Click Next

Step 20 :  Click Install.

Step 21: After Installation is done, click next

Step 22 : Click Next.

Your installation is almost complete. In order to start using SSRS, you need to perform a few more steps.

Step 23: Click Start --> All programs --> Microsoft SQL 2008 --> Configuration Tools --> Reporting Services Configuration --> select your instance

Step 24 : Click web service URL on the left hand side and click the apply button.

Step 25 :  Click database on the left hand side, select change database --> create a new report server database and complete the steps.

Step 26 : Click report manager URL and click apply (Note down the URL displayed. It should be of the form http://machinename/reports)

Step 27: open IE and hit the noted down URL

Step 28 : You should get a screen like this.

That's all. We are all set. Let's install some sample databases and get started learning SSRS 2008

What is SSRS?

• SSRS or SQL server Reporting Services which is also called Microsoft SQL reporting, is a component of the Microsoft BI stack.

• The Microsoft Business Intelligence Stack mainly consists of SQL Database Engine, SSRS, SSAS, SSIS and other tools

• SSRS is a set of readymade tools, that helps you to create, deploy and manage reports.

• You can extend reporting functionality using C# and VB as programming languages.

• Microsoft SSRS or Business Intelligence SSRS, lets you create very rich reports (Tabular/Graphical/Interactive/free-form) from various datasources with rich data visualization (Charts, Maps, sparklines).

• All these reports can be viewed via a web browsers.

• SSRS allows are reports to be exported in various formats (Excel, PDF, word etc)

• SSRS allows reports to be delivered via emails or dropped to a share location in an automated fashion.

• SSRS provides a host of security features, in order to control, who sees which reports etc.

• You can download some samples from here.

• Next, lets look at how to install SSRS and get you started.

Table Methods

What is a static method? how they are accessed?

Static method  are  attached  to aclass but dont need that class should  be  instantiated  to exrcute  that method  they usually used to instantiate the class
Static  method  can be  accessed by doubled  colon(::)


Pubilc  static mystaticmethod()
{
}
then this method  can be  accessed by 
myclass::mystatic method();

How to deploy all AX2012 report

There're 3 different ways to deploy Dynamics AX2012 reports:
Through AOT
AOT > SSRS Reports > Reports > right click on report > Deploy Element

Through Visual Studio
Open the report project > Right click on the project or solution node > Deploy

Through PowerShell
Publish-AXReport -ReportName *

Adding FileNameOpen Extended Data Type to a Form

After add EDT FileNameOpen to form using New -> stringEdit, 

add some form methods:

str fileNameLookupFilename()
{
    return '';
}

str fileNameLookupTitle()
{
    return "@SYS53008";
}

str fileNameLookupInitialPath()
{
    return '';
}

container fileNameLookupFilter()
{
    return ['All files','*.*'];
}

After get the file path, call insert method:

public void AvInsertAttachment(filename file, int i)

{    

    DocuRef docuRef;

    DocuActionArchive archive;

    ;           

    

    if(file)

    {

        ttsbegin;

        docuRef.clear();

        docuRef.RefRecId = DirPartyEntity.RecId;

        docuRef.RefTableId = tableNum(CustTable);

        docuRef.RefCompanyId = DirPartyEntity.dataAreaId;

        docuRef.Name = strFmt("Consultant Attachment %1", i);

        docuRef.TypeId = 'File';

        docuRef.insert();

        archive = new DocuActionArchive();

        archive.add(docuRef, file);

        ttsCommit;

    }

}

Result:

Queries in AX 2012

EXPANDED QUERY IN THE AOT

Query query;

QueryRun queryRun;

QueryBuildDataSource qbds;

QueryBuildRange qbr;

SalesTable SalesTable;

;

query = new Query();

//this line attaches a table to the qbds data source object

qbds = query.addDataSource(TableNum (SalesTable));

//this line attaches a range to the 'SalesTable' 

//data source, the range is the CustAccount

qbr = qbds.addRange(FieldNum (SalesTable,CustAccount));

// The range is set to '2001'

qbr.value ('2001');

// The query will sort by sales id

qbds.addSortField (FieldNum(SalesTable,SalesId));

// The queryRun object is instantiated using the query

queryRun = new QueryRun(query);

// The queryRun object loops through all records returned

while (queryRun.next())

{

// The current record is assigned to the salesTable variable

SalesTable = queryRun.get(tableNum(SalesTable));

print SalesTable.SalesId;

}