Showing posts with label interview. Show all posts
Showing posts with label interview. Show all posts

Monday, April 10, 2017

SQL Server Free eBooks, Softwares, Tutorials and Websites

When we start working as developer or learning, we often face this issue. Where is the best tutorials, books and other learning material? Sure, google can help you but it is difficult to keep track of all the information provided by the google. While working for more than 7 year, I have identified some resource which are really great and would like to share with you. Best part of this all are free and trustworthy.

First thing first, we need a computer with all necessary software because it doesn’t matter how much you study until you do hands on practice. Here is the list of software and official download links.

  1. SQL Server 2016 Developer Edition – it has all the necessary features and free from Microsoft. You can use this for learning purpose, please read the user agreement for all the details.
https://www.microsoft.com/en-us/sql-server/application-development


  1. Visual Studio Community Edition
https://www.visualstudio.com/downloads/


  1. SQL Server Data Tools in Visual Studio 2015
https://msdn.microsoft.com/en-us/mt186501.aspx?f=255&MSPPError=-2147217396


I would recommend you the following installation sequence as -
  1. SQL Server
  2. Visual Studio
  3. SQL Server Data Tool
Once installation is done, you need sample database to start with. Here is the link to download sample database from Microsoft –
AdventureWorks Databases and Scripts for SQL Server 2016

https://www.microsoft.com/en-us/download/details.aspx?id=49502

 

Wide World Importers sample database v1.0


https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0



Top websites to master SQL Server –
  1. Official Microsoft website - https://docs.microsoft.com/en-us/sql/sql-server/sql-server-technical-documentation
  2. https://www.mssqltips.com/
  3. https://www.w3schools.com/sql/default.asp
  4. https://www.simple-talk.com/sql/
  5. http://stackoverflow.com/

Free eBooks –
Defensive Database Programming
By Alex Kuznetsov
Download Link - http://www.sqlservercentral.com/articles/books/69647/


Mastering SQL Server Profiler

Download Link -  http://www.sqlservercentral.com/articles/books/65797/

SQL Server Execution Plans


Performance Tuning with SQL Server Dynamic Management Views


Inside the SQL Server Query Optimizer


SQL Server Execution Plans, Second Edition



SQL Server Concurrency: Locking, Blocking and Row Versioning


List of all the books –


Best online Tutorials -


Stairway to T-SQL: Beyond The Basics


Stairway to T-SQL DML


Stairway to SQL Server Indexes


Stairway to Data


Stairway to Advanced T-SQL


List of all Stairway tutorials


















    
Posted by at
Labels: ,

Monday, September 19, 2016

Books VS Internet


It’s is very easy to be confused between investing in books or we should use the power of internet. If you ask my recommendation, I would strongly suggest Books. 
Why, let’s take a simple example. Suppose you want to watch a great movie let’s say Avatar. Now you have two options, first go to theater buy ticket and watch full uninterrupted movie. Second option is watch on internet for free, sounds like a better option now. As we all know information is scattered in millions of WebPages, so assume that our movie is available in 200 different pieces on different websites. Let’s assume some how you watch all the clips. Now here are the important question, have you seen all the clips and in correct order? Even though you did, do you really enjoyed and how was your experience? 
That’s my point, it is nearly impossible to build a foundation with millions of WebPages, simply because of random order and overwhelming amount of information. So if you want to build a solid foundation and want to enjoy learning, invest in books.
Internet is good for studying very specific topic, like you want to know how many indexes we can create on a table? In such scenario it will save your time but if you are at very beginning stage of your career or even you are an expert, it is a great option to start with a well organized and tested study material which is a well written Book. Do you know what is the best thing about a book?  Just by going through its Index you come to know that what you know, what you want to know and how confident you are about those topics. 
Personally I know many expert programmers who are great at their work but still have a complete reference book like Sql server bible. It is good habit to go through its Content and refresh your knowledge.
You may know this already ‘Motivation Is What Gets You Started, Habit Is What Keeps You Going’. So make a habit to study and set target like I want to finish one book each month or whatever feasible for you. 
I personally know couple of people who have this habit. They both are loaded with professional and family responsibilities but still they manage to finish one technical book very month. If they can’t, they take leaves and finish it. That kind of commitment makes the difference in long term. 
Some people can argue about on job learning, but to be very frank most of us do the same monotonous work and margin for learning something new is very less. Most important question, why you want to leave your career on chance or fate. Take the charge and drive it in the direction where you like.
So many programmers ask me this, we are developers why we should know the architecture and internals of Sql Server. Usually I reply them with a simple question,
When you drive a car, do you think it’s good to know what kind of engine it has (Petrol/Diesel) or How to read and understand instrument cluster?  Same way it is very important to understand how Sql server interpret your code and process it.
It makes the difference between a programmer and an expert programmer.

So start now and make the difference. What you need just habit to study and learn. If you are wondering from where you should start. I am providing a list of my favorite books here, feel free to browse and check out other users reviews. 
T SQL Programming -   

Microsoft SQL Server 2012 T-SQL Fundamentals

T-SQL Querying (Developer Reference (Paperback))

Beginning T-SQL 2012

Pro T-SQL 2012 Programmer's Guide (Professional Apress)

Sql Server internals and architecture -

Professional SQL Server 2012 Internals and Troubleshooting

Microsoft SQL Server 2012 Internals (Microsoft Press)

Pro SQL Server Internals (Professional Apress)

The Guru's Guide to SQL Server Architecture and Internals
Posted by at
Labels: ,

Tuesday, September 13, 2016

Interview Questions for Companies

In this article I will be posting interview questions by companies. Whenever I am facing any interview or someone shares the question , I will be adding here. 

Sutherland Hyderabad  Telephonic Round
Q.  What is cursor in SQL Server?
Q. Tell me the Difference between Replace and Coalesce functions with examples?
Q. What is objects in SQL server?
Q. What is stored procedures?
Q. Describe your current role and responsibilities?
Q. How to rename a database from script?
Q. Explain Triggers, their types and usage by example?
Q.  What is Identity column?
Q. Describe Relationship types and usage, one to one, not to many,  many to many?
Q. What is Foreign Key and usage?

Capgemini Telephonic 1St Round

 

Q. Explain all types of join with example?

Q. What will be the output when you have null values in joining column and what will be the output for all the joins type?

Q. Explain rank and dense_rank function and difference between them ?

Q. what is row_number , explain with example ?

Q. Explain sql server range function ?

Q. How execution plan being generated for Static and dynamic Sql Script ?


Q. Which is better Static or dynamic Sql Script ? Give example when you will use Static and when to use dynamic sql.


Capgemini Telephonic 2nd Round ( client call Morgan Stanley) 

Q. We have one container,  one 3 liter jug and 5 liter jug. How we can measure 1 and 4 liter? 
Q. There are four person,  two adults and two kids standing rever side.  There is a boat, which can hold one adult or two kids. How they can cross the river. 
Q. Suppose our application is running very slow,  how you will approach the problem.  Explain the steps how you will find the route cause. 
Q. Explain query optimization and execution plan. 
Q. Are you willing to learn new technologies? 
23rd September 2016

Deloitte Bangalore

Interview process consist of four rounds, one written test and three rounds of interviews.
Most of the technical discussion is based on written test. Here is the questions which I remember.
Q1. Write a query to calculate cumulative sum (running total) of employee salary.
EmpID                   Salary
1                                             15  
2                                             25
3                                             100
4                                             NULL
5                                             50
6                                             NULL
Expected output is –
EmpID                   RunningTotal
1                                             15  
2                                             40                  
3                                             140
4                                             140
5                                             190
6                                             190
Q2. We have Employee table and a column name gender. Here gender is represented as M and F. Write query to replace M with ‘Male’ and F with ‘Female’, with a single Update statement.
Q3. Explain slowly changing dimensions and their types.
Q4. Explain the difference between having clause and where.
Q5. If a column alias is used in where clause, does it work and why?
Select E.Name as ‘EmpName’, E.Salary as ‘EmpSalary’
From Employee E
Where  EmpSalary>5000
Q6. Design database schema for Facebook events and response.
Q7. Explain star and snowflake schema.
Q8. What are the advantages and disadvantages of snowflake schema?
Q9. Explain the database schema of your current project (high level) and design the
Data warehouse for it.





Posted by at
Labels:

Monday, September 12, 2016

SQL Server Interview Questions And Answers

This is post going to be more like a list of question for self assessment. So I will not be providing answer, instead of that I will provide the link for the best answer I can find on internet. I am pretty much sure you know most of the answers. I will be adding new questions frequently.

Q. what are the Codd's rule for RDBMS ?
https://en.wikipedia.org/wiki/Codd%27s_12_rules

Q. Describe index in Sql Server?
https://msdn.microsoft.com/en-us/library/ms190457.aspx

Q. Explain ACID properties of database transaction?
https://en.wikipedia.org/wiki/ACID

Q. What is Tempdb and how it is useful in Sql Server ?
https://msdn.microsoft.com/en-us/library/ms190768.aspx?f=255&MSPPError=-2147217396

Q. What is MSDB ?
https://msdn.microsoft.com/en-in/library/ms187112.aspx

Q. What is Master Database?
https://msdn.microsoft.com/en-us/library/ms187837.aspx

Q. What is Model Database?
https://msdn.microsoft.com/en-us/library/ms186388.aspx

Q. What is Resource Database?
https://msdn.microsoft.com/en-us/library/ms190940.aspx

Q. Explain Transaction Isolation level in Sql Server ?
https://msdn.microsoft.com/en-us/library/ms190940.aspx

Q. what is Statistics in Sql Server?
https://msdn.microsoft.com/en-us/library/ms190397.aspx?f=255&MSPPError=-2147217396

Q. What is Views in Sql Server and how many types are there?
https://msdn.microsoft.com/en-IN/library/ms187956.aspx

Q. What is execution plan in Sql Server?
https://technet.microsoft.com/en-us/library/ms178071%28v=sql.105%29.aspx?
f=255&MSPPError=-2147217396

Q. What is Subquery and describe types of it?
 https://technet.microsoft.com/en-in/library/ms189575%28v=sql.105%29.aspx?
f=255&MSPPError=-2147217396

https://technet.microsoft.com/en-in/library/ms175838(v=sql.105).aspx

Q. What is cursor in Sql Server?
https://technet.microsoft.com/en-us/library/ms191179(v=sql.105).aspx

Q. Explain transactions in Sql Server?
https://technet.microsoft.com/en-us/library/ms190612(v=sql.105).aspx

 Q. What is distributed queries?
https://technet.microsoft.com/en-us/library/ms188721(v=sql.105).aspx

Q. What do you mean by Lock and Latches in Sql Server?
https://technet.microsoft.com/en-us/library/ms189849(v=sql.105).aspx

https://msdn.microsoft.com/en-us/library/ms177421.aspx

Q. What is a Job in Sql Server and how to schedule it?
https://msdn.microsoft.com/en-IN/library/ms190268.aspx?f=255&MSPPError=-2147217396

https://msdn.microsoft.com/en-in/library/ms191439.aspx

Q. What do you know about Dynamic Management Views and Functions in Sql Server ?https://msdn.microsoft.com/en-us/library/ms188754.aspx

Q. What is Trigger in Sql Server?                                                                                  https://msdn.microsoft.com/en-IN/library/ms189799.aspx 


Recommended book for Interview 

SQL Server Interview Questions

SQL Server Interview Questions and Answers: For All Database Developers and Developers Administrators

Posted by at
Labels:

SQL Server Interview Questions And Answers

This is post going to be more like a list of question for self assessment. So I will not be providing answer, instead of that I will provide the link for the best answer I can find on internet. I am pretty much sure you know most of the answers. I will be adding new questions frequently.

Q. what are the Codd's rule for RDBMS ?
https://en.wikipedia.org/wiki/Codd%27s_12_rules

Q. Describe index in Sql Server?
https://msdn.microsoft.com/en-us/library/ms190457.aspx

Q. Explain ACID properties of database transaction?
https://en.wikipedia.org/wiki/ACID

Q. What is Tempdb and how it is useful in Sql Server ?
https://msdn.microsoft.com/en-us/library/ms190768.aspx?f=255&MSPPError=-2147217396

Q. What is MSDB ?
https://msdn.microsoft.com/en-in/library/ms187112.aspx

Q. What is Master Database?
https://msdn.microsoft.com/en-us/library/ms187837.aspx

Q. What is Model Database?
https://msdn.microsoft.com/en-us/library/ms186388.aspx

Q. What is Resource Database?
https://msdn.microsoft.com/en-us/library/ms190940.aspx

Q. Explain Transaction Isolation level in Sql Server ?
https://msdn.microsoft.com/en-us/library/ms190940.aspx

Q. what is Statistics in Sql Server?
https://msdn.microsoft.com/en-us/library/ms190397.aspx?f=255&MSPPError=-2147217396

Q. What is Views in Sql Server and how many types are there?
https://msdn.microsoft.com/en-IN/library/ms187956.aspx

Q. What is execution plan in Sql Server?
https://technet.microsoft.com/en-us/library/ms178071%28v=sql.105%29.aspx?
f=255&MSPPError=-2147217396

Q. What is Subquery and describe types of it?
 https://technet.microsoft.com/en-in/library/ms189575%28v=sql.105%29.aspx?
f=255&MSPPError=-2147217396

https://technet.microsoft.com/en-in/library/ms175838(v=sql.105).aspx

Q. What is cursor in Sql Server?
https://technet.microsoft.com/en-us/library/ms191179(v=sql.105).aspx

Q. Explain transactions in Sql Server?
https://technet.microsoft.com/en-us/library/ms190612(v=sql.105).aspx

 Q. What is distributed queries?
https://technet.microsoft.com/en-us/library/ms188721(v=sql.105).aspx

Q. What do you mean by Lock and Latches in Sql Server?
https://technet.microsoft.com/en-us/library/ms189849(v=sql.105).aspx

https://msdn.microsoft.com/en-us/library/ms177421.aspx

Q. What is a Job in Sql Server and how to schedule it?
https://msdn.microsoft.com/en-IN/library/ms190268.aspx?f=255&MSPPError=-2147217396

https://msdn.microsoft.com/en-in/library/ms191439.aspx

Q. What do you know about Dynamic Management Views and Functions in Sql Server ?https://msdn.microsoft.com/en-us/library/ms188754.aspx

Q. What is Trigger in Sql Server?                                                                                  https://msdn.microsoft.com/en-IN/library/ms189799.aspx 


Recommended book for Interview 

SQL Server Interview Questions

SQL Server Interview Questions and Answers: For All Database Developers and Developers Administrators

Posted by at
Labels:

Thursday, September 8, 2016

Sql Server Special Table Types

Special Table Types


Objective of this post is to provide a list of special tables available in Sql Server and brief description of that. If you wish to study them in details, I will be providing the links. Let's get started –
There are following special types of table are available in Sql Server.
1. Temporal table
2. Memory-Optimized table
3. Partitioned tables
4. Temporary table
5. System tables
6. Wide tables

Let's explore them one by one.

Temporal Table - Also known as system versioned tables are introduced in Sql Server
2016 and this kind of table keeps the full history of data changes and it's almost like Slowly changing dimensions in Data warehouse. Each such table has two additional column of data type datetime2 to maintain the validity period of the record. For details visit the below link.
https://msdn.microsoft.com/en-IN/library/dn935015.aspx

Memory Optimized Table -
Memory-optimized tables are completely conformed to ACID properties of database and it resides in main memory. Sql server keeps a copy of it in the disc just for durability. These kinds of tables provide better performance than a disk based tables but it depends on many factors and volume of data is the major factor. Here is the syntax to create a Memory-optimized table –

Create Table [dbo].[DemoTable]
(
ID Int PRIMARY KEY,
Email nvarchar(256),
FullName nvarchar(256)
) With (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)

https://msdn.microsoft.com/en-us/library/dn511014.aspx

Partitioned tables
In this kind of table you can partition the data on the basis of a partition function or a column. Suppose you have data of employees and you want to partition the data on the basis of office location. If you have five work locations , so you will be creating five partition. Again there are two kind of partitioning Vertical and Horizontal. For details visit below link.

http://www.sqlshack.com/database-table-partitioning-sql-server/

Temporary tables
Temporary tables provide work space to store intermediate result set. They are created on the fly and doesn’t store in disk. They are fast and provide all the features of a table. Again there are three kind of temporary tables.
A. Local Temporary Table
Syntax –
Create Table #DemoT
(
ID int,
Name varchar(256)
)

B.Global Temporary Table
Syntax -
Create Table ##DemoT
(
ID int,
Name varchar(256)
)

C.Table Variables
Declare  @DemoT
(
ID int,
Name varchar(256)
)
There are difference between these three types like you can create index on Temp tables and Sql server maintains the statistics for Temp tables but it’s not true for table variables . For details check this –
https://technet.microsoft.com/en-us/library/ms177399(v=sql.105).aspx
https://technet.microsoft.com/en-us/library/ms175010(v=sql.105).aspx

System Table –
These tables hold data about Database itself and should not be modified or delete.
Be extra careful while working with system tables, these are good source of information about and inside of database. Example are Sys.objects, Sys.tables, Sys.columns


https://msdn.microsoft.com/en-IN/library/ms179932.aspx?f=255&MSPPError=-2147217396


Wide Tables
As the name suggest wide table can have up to 30,000 columns. This kind of tables has very limited use because most of the column should be NULL to adhere to the total row size 8019 bytes.  Wide table can be created or existing table can be converted to wide table, be extra careful before using them. Wide table require performance considerations, you can find the details below –

https://technet.microsoft.com/en-us/library/cc645884%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

Posted by at
Labels: ,

Tuesday, September 6, 2016

Sql Server – Listing all tables Metadata (Table Size, row count, Index Size)

Sql Server – Listing all tables Metadata (Table Size, row count, Index Size)


Suppose you need to find out Table size, row count , index size etc. for a table. In this case the easiest way to do that is using sp_spaceused, here is the syntax –

EXEC sp_spaceused 'TableName' Like EXEC sp_spaceused 'DimCustomer'
The output will be something like that –

name
rows
reserved
data
index_size
unused
ItemFlatten
259458
251464 KB
109072 KB
141880 KB
512 KB


Looks good and easy but what if you want the list of all tables in your database and their metadata. Here is the script for that -


Begin
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
  /*Then it exists*/
  DROP TABLE #Temp

select O.name as 'TableName',O.type_desc,SUM (reserved_page_count)  as 'ReservedPages',
SUM (used_page_count) as 'Usedpages',
SUM (
                     CASE
                           WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
                           ELSE 0
                     END
                     ) as Pages,
                     SUM (
                     CASE
                           WHEN (index_id < 2) THEN row_count
                           ELSE 0
                     END
                     ) as Row_Count
                     Into #Temp
from Sys.objects O
Inner join sys.dm_db_partition_stats S on O.object_id=S.object_id and O.type in ('U')
Group By O.name,O.type_desc



 SELECT
              name = TableName,
              type_desc,
              rows = convert (char(20), Row_Count),
              reserved = LTRIM (STR (ReservedPages * 8, 15, 0) + ' KB'),
              data = LTRIM (STR (Pages * 8, 15, 0) + ' KB'),
              index_size = LTRIM (STR ((CASE WHEN Usedpages > Pages THEN (Usedpages - Pages) ELSE 0 END) * 8, 15, 0) + ' KB'),
              unused = LTRIM (STR ((CASE WHEN ReservedPages > Usedpages THEN (ReservedPages - Usedpages) ELSE 0 END) * 8, 15, 0) + ' KB')
              From #Temp
              Order By TableName

              Drop Table #Temp

End

Output will be something like this –

Please share your feedback and let me know if you feel this can be improved in anyway.


Posted by at
Labels: , , ,
Subscribe to: Posts (Atom)