| SQL Server Interview Questions and Answers |
| Print Book Available (207 Pages) | Sample Chapters |
SQL SERVER – 2008 – Interview Questions and Answers Complete List Download
1) General Questions of SQL SERVER
What is RDBMS?
Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage. (Read More Here)
What are the properties of the Relational tables?
Relational tables have six properties:
- Values are atomic.
- Column values are of the same kind.
- Each row is unique.
- The sequence of columns is insignificant.
- The sequence of rows is insignificant.
- Each column must have a unique name.
What is Normalization?
Database normalization is a data design and organization process applied to data structures based on rules that help building relational databases. In relational database design, the process of organizing data to minimize redundancy is called normalization. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.
What are different normalization forms?
1NF: Eliminate Repeating Groups
Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.
2NF: Eliminate Redundant Data
If an attribute depends on only part of a multi-valued key, remove it to a separate table.
3NF: Eliminate Columns Not Dependent On Key
If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key. (Read More Here)
BCNF: Boyce-Codd Normal Form
If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.
4NF: Isolate Independent Multiple Relationships
No table may contain two or more 1:n or n:m relationships that are not directly related.
5NF: Isolate Semantically Related Multiple Relationships
There may be practical constrains on information that justify separating logically related many-to-many relationships.
ONF: Optimal Normal Form
A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
DKNF: Domain-Key Normal Form
A model free from all modification anomalies is said to be in DKNF.
Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.
What is De-normalization?
De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.
What is Stored Procedure?
A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.
e.g. sp_helpdb, sp_renamedb, sp_depends etc.
What is Trigger?
A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.
Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger. (Read More Here)
What is View?
A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.
What is Index?
An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes; they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query. In a table scan SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance.
What is a Linked Server?
Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data. Stored Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server. (Read More Here)
© Copyright 2000-2009 Pinal Dave. All Rights Reserved. SQLAuthority.com
Reference : Pinal Dave (http://blog.SQLAuthority.com)
[...] SQL SERVER – 2008 – Interview Questions and Answers – Part 1 [...]
Hi Pinal,
I am Ravinder. I am preparing for sql server developer cource and simultaneously I am attending interviews . So please help me for this . I need interview questions and answers from every toipic .
And also i am preparing for certification exam so can u help me out in this it would be greatful to you. My
Hi Ravinder i am also searching data base job. please if u know any information about sql dba jobs please send me mail.
send me resume to me
Hi I am looking for MSBI-SSAS SSRS SSIS ,sql server related jobs can you help?
Hi,
I am looking for MSBI- SSIS ,sql server related jobs can you help? please
interview question instruction my help you.
I am planning to do a SQL Server 2008 online course as I am working and cant get a chance to attend a regular course ir training. Is there any good suggestions please?
send your cv on to me,
Dear Vijay,
This is Hemant Kapre, Currently i am working in ‘Wipro Infotech’ as a Tool Administrator also i have good knowledge of SQL Server 2008.
But i am not a DBA, I am keen to come as SQL DBA role.
Is there any Online course for SQL Server 2008.
I am waiting for your response.
Hemant Ratan Kapre
[email removed]
[phone address removed]
hi pinal,
using views cocept we can update or delete the records it will be reflected to actual tables ?in sql 2008 server .Then what is the difference between tables and views.can u please clarify this doubt.
View is a virtual table or a stored query. It can limit the number of columns from the original table. However it will affect the original table for all DMLs
Hi , i am changing the company . i need some interview Questions ,please if u know any information about sql dba jobs please send me mail.
Hi dave,
What are the prerequisites of logshipppping ,
plz tell me
Hi Pinal,
I am working as a ASP & ASP.NET Developer, I want change my profile to SQL SERVER. In some SQL interviews they ask you are a .Net developer we want some experienced candidates in fully SQL SERVER environment. What can i do? Pls advice me.
why do you want to shift to DB side? How many years of experience do you have in .NET?
@Madhivanan : I have 1.3 yrs exp in .NET and 1.5 yrs in ASP. SQL SERVER attract me during my experience thats why I want to shift to DB side.
Then you need to work on SQL Server and gain experience
Hello Pinal,
I hope you will reply my question as i am posting under a 2008 post :)
I’ve been asked in an interview that “Can we create a trigger inside a trigger?” and my answer was No, I hope my answer was correct.
Hi Pinal ,
Is there any SSRS related post on site. I am please provide me SSRS 2008 related post
Nice artical
i am very much interested to learn sql server can u send complex queries, questions
Hi Dave
This is satees kumar reddy. I am learing SQL Server 2005 from last 3 months, it is very interesting. Recently I found your website, it is very excellent to learn sql server. I am intersting to move as a DBA. Please tell me what i am going to do for that.
Hi sateesh reddy i am also searching data base job. please if u know any information about sql dba jobs please send me mail.
hi this is bhaskar reddy
i am in trils now and very confused
plz help me with clarifications
can u give more interview questions on sql dba 2005 as i am going to appear for interviews
Search in this site for Interview questions. You will find lot of posts
plz give me some tips for preparation
Hi Dave,
It’s quite a nice article!
I want to argue about the index, you said here “An index is a physical structure containing pointers to the data”, but I think it is not exactly the case, for non-clustered index it is true, what if for clustered index? The clustered index should contain all data itself but not just a pointer, right?
Hi All,
I am priyanka. I prepaired for sql server developer cource.
Now i want to attend interviews . So please help me for this . I need interview questions and answers from every toipic .
And also i am preparing for certification exam so anyone knows any links please send me.
thanks,
priyanka.
Hi Sir/Madam
I have one year of experience in SQL Server DBA.I want to give interviews.Can you send me updated interview questions on SQL Server on all version of SQL server.
Regards
Neeraj
hi can u share with me ur experience
how the job is in begining????????????
ur bhaskar
hi, im learning sql server 205 course.i want sample resume as a sql server developer.can any one please send me resume
thank
janaki
Relational tables have six properties:
Values are atomic is one of them ,
but i m not properly clear about atomic , what actually u mean by it here?
Relational tables have not six properties, it’s eight properties
Thank You
Hi dev,
” I wish you very happy married life”
site is really grate, i am feeling very lucky to have found such a nice site to learn sql server.
I appreciate you for doing such a wonderful job.
best regards,
subbareddy
Hi,
thank u so much.iam very happy to know that even such sites exists!!from here on i’ll visit ur page every day as a ritual.
may god bless you..keep up the gud work..
all the bst
I had a MS SQL Database backed up .bak file which is of 22 GB and i have split it into 22 files of 1 GB each file with a splitter program.
Is there a method to Restore each of these individually or say I only want to restore the first three files of 1 GB each into another MS sql server database.
How can i do it ?
Dear Rakesh,
You cannot restore the Splited File . you have unsplit it and then restore it……….
can u give more logical interview questions on sql dba 2005 as i am going to appear for interviews
Hi pinal,
i would like to know what is Data integrity?
What is a udf? partitoned tables?
Thanks
Shilpa
Hi Shilpa,
Data integrity simply means the quality of data in the database.It is useful to keep the data unchanged and unique.Data integrity can be described in following scenarious
•Entity integrity
•Domain integrity
•Referential integrity
•User-defined integrity
http://msdn.microsoft.com/en-us/library/aa933058(SQL.80).aspx
Partioned tables also best describable for data integrity and data management in the database.Parttioned tables help to keep certain set set of records to maintain as per our requirement.
Thanks a lot!!
Shilpa –
Data Integrity:
This is many things to many people. It simply means the data you have stored in various tables can be trusted and is of high quality. How the trust can be defined is up to you – but basically it can be quantified as:
Entity integrity means the presence of a unique row identifier, like Item_id which cannot be duplicated.
Domain integrity – a range of values are allowed. Nothing more, nothing less.
Referential integrity – establishing foreign keys will help you here.
and
User defined integrity – well, you define it.
UDF – is a user defined function, used to quickly (well depending on the quality of sql) do inline calculations/lookups inside a select statement. For example, you can write a udf that would check whether an Item number is valid, before you do any operations on it.
Partitioned tables comes in handy when dealing with high volume tables. You take Table A that has say 25 million records with 5 million each for each of last five years and split them into TableA_2008 (that contains only 2008 data), TableA_@2007, TableA_2006 etc with proper indexes (there are some more details involved here that I am going to omit) etc, then create a view that will be called Table A that would be union of those 5 partitioned tables.
When properly implemented, the performance gains are incredible, especially for reporting in Business Intelligence applications.
Hope this helps.
Hi Anil Pillai,
Can you please tell me what is the max number of rows we can transfer a a time from OLTP to OLAP system.
Hello Jay,
Are you facing some issue in transfering rows from OLTP to OLAP? In SQL Server there is no constraint on number of rows processed by a statement.
Regards,
Pinal Dave
I wonder if SQL Server at different organizations has limitations imposed by the hardware on which SQL Server is installed or the policies of an an organization. For example, I believe some SQL Server operations may fail with large data sets because temporary space is exhausted. Is this consistent with your experience and understanding?
BTW, thank you for your blog. You are a special author because of your deep understanding of SQL Server technical topics as well as the clear and concise way that you communicate about those topics.
Hi Pinal Dave,
Your contribution to this world of SQL DBA`s are really appreciated.
Thanks much for all the info.
Thank You,
Anup
Hi,
Can you tell me how to copy the table contents from one server to another server.
Eg:
Server : server 1
Database: db1
Table : tb1
Server : server2
Database: db2
Table : tb2
i want to copy the tb1 data into tb2. Please help me?
Hi
your taking about Database or in Table if you want table structure then please go through below mentioned ex.
select * into [server name].database name.dbo.table name from [server name].database name.dbo.table name
and if you want database contain then go Attached and Deattachment and log shipping.
Make sure you added other server as Linked server using sp_addLinkedServer
Hi you can export the data to another table in sql server,its in task
select * into server2.db2.tb2 from server1.db1.tb1
Provided that server2 is connected with Linked server
Provided that server2 is connected via Linked server
my name is krishna i am doing dotnet course……..and i have learnt sqlserver 2005…….and i find difficult to write storedprocedurs and triggers to improve on that what i want to do………
What kind of difficulties do you face?
Post the code
Thanks a lot ……
Sir i am beginner in sqlserver 2005 ,i ve interest to learn lot of things what are the step that i want to follow,i want small tips for that sir
Hi,
Please checkout with ms sql tips ,its better use ful for beginner.
vijay
sir, i have a doubt. that is the maximum number of coloumns in a table.
2)
if we dont specify the lenght of datatype what happens?
than it will automatically take default its threshold value like
a data type in int. it contact maximum 8060 length but if u specify nothing it grow automatically in 8000 after it need 60 for buffering for transactions.
You should always specify the length.
See what happens you don’t do so
http://beyondrelational.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx
Hey come on man!!! What a great stuff.. Really good job.
Hi pinal,
Great collection…just a small request..
can i use these questions in my site…along with your reference and site url..?that would add value to my site..
thanks in advance..
Hi Nancy,
They are protected by copyright. You can just copy maximum of 200 words and link back here.
Kind Regards,
Pinal
hello sir,
your articles are very nice and all
the students of sql server are enjoyed a lot
Thanks!!!
Hi ,
I am enteresting in reading sql
can you send complex querry for me
hi all,
i like this site very much…
nd i need sql server interview question,if any has thn pls send me
hi pinal,
your site is very good.but can u expalin the concepts with examples…
it will be very useful for understanding..
Nice article….It is good for starters..
Awesome article.
Good Work Pinal.
Keep it up.
Hello Sir,
your articles are very nice and all
me and my friend enjoing ur artical
can u give me more logical interview questions on sql 2005 as i am going to appear for tester interviews……….
Hello Sir.
I want to get 100 rows from particular record and onward. in oracle i can use rownum and in mySql i have function limit … i want to know what is the ms-sql alternate for it.?
I want to get 100 rows onward to one particular data … how can i ?
Moderator: Please ignore previous comment.
@Vipul
You need to use ROWCOUNT key word and set a value to this.
Below is a sample example.
/* This is original Table */
declare @table table (id int identity , ename varchar(10))
/* This is final table */
declare @finaltable table (id int , ename varchar(10))
declare @count int
set @count = 1
/* insert sample data of 20 records in original table */
while @count 5
/* This is your final result */
select * from @finaltable
/* This is how you limit no of rows to be effected */ — IMPORTANT
set rowcount 10
insert into @finaltable select * from @table where id > 5
/* This is your final result */
select * from @finaltable
Let us know if this does not helps.
~ IM.
I use your code:
/* This is original Table */
declare @table table (id int identity , ename varchar(10))
/* This is final table */
declare @finaltable table (id int , ename varchar(10))
declare @count int
set @count = 1
/* insert sample data of 20 records in original table */
while @count = 5
/* This is your final result */
select * from @finaltable
/* This is how you limit no of rows to be effected */ — IMPORTANT
set rowcount 10
insert into @finaltable select * from @table where id > 5
/* This is your final result */
select * from @finaltable
–but this give output as like blank table
–Can you help me how to show ?
–How I insert sample data of 20 records in original table?
–Please help me
Thanks
Vasant
Make sure the table has data
To insert sample data to the table, refer this post
http://beyondrelational.com/blogs/madhivanan/archive/2008/04/04/populating-sample-data.aspx
Regarding Vipul question,
how about using TOP ? for example:
SELECT TOP (100) FROM MY_TABLE ORDER BY COLUMN_A
May this could help.
Best regards,
Beta :)
F.Y.I
I also read recently in SQL 2008 books online that ROWCOUNT will not supported in the future versions of SQL Server. So please be careful when using it.
Best regards,
BB
Yes. Instead TOP operator should be used
@Vipul : You mean ‘get 100 rows from particular TABLE not record’
You can use the following syntax using TOP
Select TOP 100 from Table
TOP(100) might not work.
:)
Make sure you also use ORDER BY Clause when you use TOP operator
@Prash: Thanks for the correction :)
anyway, the works one for TOP syntax’s example (tested on SQL 2008) is like this:
SELECT TOP 100 * FROM TABLE
Note: ‘*’ means all rows available in that table, you can specify certain rows as required.
TOP is meaningless until ORDER BY clause is used
Thanks a Lot
U Do a Great Things.
Hi
I am very much interested in become SQL programmer. And obtain MS certification. I have knowledge of programming C# and ASP.Net but not in the deep. Please let me how long it will take and how many hours I need to put in to this subject daily basis. Where I can find good materials. What are the all good methods follow. Is there big scope in this field??
Sorry for all the questions. I thought this is the good place ask
Thanks in advance
D
Hello Sir, I want to develop a Gender database using SQL Server. And I want to know more in Normalizing. please send any Sample .
Thank U .
Hell sir,
I want to copy table with same structure with different name in same database. I tried “Select * into ‘newtblname’ from ‘totblname’ but this will not copy foreign key constraints.
Is there any other way to copy all structure.
Thanks
Generate Script of the table and rename the table name when running it
Hi Vikram,
Try this below Query,
Select * from ‘Newtblname’ from ‘totblname’
where 1=2
ITs works Fine.
Note that it wont copy the indexes
You mentioned “TRUNCATE cannot be rolled back”
But it can be rolled back ..
Can you please clear what you want to say?
Truncate call be rolledback if it is used inside a transaction
truncate is nthg but “permenent deletion”
its not rolled back again.
if u r use “delete” command then u can go to “roolback”.because delete is temporary deletion purpose .
I am having 3.5 years of experience in Sql server Development. can you tell me what kind of postition i can try to prior level?
There is no place like your blog which clearly describes the concepts with examples. I love your site so much that i no more look anywher except your blog.. i wish you put more and more questions and be helpful for all self starters and learners.
Nice work.. really nice information at one place.. Thanks
Hi Pinal Sir,
I m bit confused.
I m working as a Software Engineer. Currently working on VB and SQL Server. Basicall my work is in SQL Server, writing SP, triggers, backup restore. I m much more interested in SQL Server. I wana work Oracle or SQL Developer track.
But i m not getting what to do next??
Can u guide me little bit……..
Good Job Pinal
Practice…Practice…Practice…. This is the only way you can gain confidence on any programming language. I would recommend “Beginning SQL server 2005 programming” book. Don’t even miss single line. You should be good in 1 month. Seriously!
thank u very much , but we need more typical and technical questions
Hi Pinal Dave,
you are really great. I have got more solution related sql server. kindly provide me full information of BI. which type of job i can get after getting command on BI in sql server 2008. can i use all BI package in sql server 2008 express edition
reply asap.
thanks in advance
sakendra kumar
Hi Pinal Dave,
Thank u so much .. Its very much usual for preparing interviews. I am having 1 year Exp in SQL , can u please send me some more interview qusetions ..
Thanks a lot for ur wonderful Effort..
Thanks ,
Chitra.
Hi Pinal,
I am preparing for sql server administration cource and simultaneously i want to attending interviews . So please help me for this . I need interview questions and answers from every toipic .
can you plz send me the Q and Answers to my mail
Hi Pinal,
Thanks so much for the lovely article that you have posted. I am actually preparing for the MCTS in sql server 2005 and want your help and guidence. Please guide me through the process of preparation and also please tell me how will it benefit me in my current role in the company as a Dotnet developer .
Many Thanks,
Dinesh.
HI,
I have plan to write the MCTS 70-433.you have any material for the above exam.
please advice
Thanks
Babu.K
Hi Pinal,
In sql stored procedure i am using dynamic sql based on input parameters.
But it is not showing any database fields in crystal report [using C#].
So is there any way that i can get the output fields in stored prcedure & it can be displayed in database fields while creating crystal report in C#?
Kindly guide me. I tried with lots of solutions but it doesn’t work, so please give one example.
Thanks
Riddhi
Hi Riddhi,
It’s not a matter that you are using dynamic procedure, matter is that first of all you create stored procedure and execute it into database it self. If it gives result on the basis of your parameter, after then go to crystal report->file->new blank report->create new connection->select OLE DB (ADO)->Microsoft OLE DB Provider fro SQL Server->click on Next Button->select server name->userid->password->database in which you have created stored procedure ->Next->Finish
Now you have a tree available in left pan.
you can select database ->dbo->stored procedure->select your stored and click on (>) button. now you have a window for parameter->after putting the value into parameter you click on OK button.
after then go on View menu->Field Explorer->Click on database field->now your all field are available which your dynamic table returns.
Since i haven’t your email id, so how it possible to display Print screen. If you have any further query. Keep in touch.
Thanks
Sakendar Kumar (MCA)
You can’t desing a report based on the procedure whose result is dynamic. If, based on the input parameters the number of columns can be changed, it is not possible to design a report.
One option is to have as many procedures as possible for each combination of input values
Dear Sir,
I want to generate script of a table by command not by wizard facility.is there any command by which i can do this
Thanks
Preeti Agarwal
Hi Preeti,
You can try following command to generate script
C:\Program Files\Microsoft SQL Server\90\COM>tablediff.exe
Thanks,
Janki
Hi Preeti agarwal,
first of all you create my procedure
and execute one by one like :-
GenerateScript yourtablename
If your problem are still not solved then keep in touch
my email id:– [email removed for privacy and security purpose]
Create Procedure GenerateScript (
@tableName varchar(100))
as
If exists (Select * from Information_Schema.COLUMNS where Table_Name= @tableName)
Begin
declare @sql varchar(8000)
declare @table varchar(100)
declare @cols table (datatype varchar(50))
insert into @cols values(‘bit’)
insert into @cols values(‘binary’)
insert into @cols values(‘bigint’)
insert into @cols values(‘int’)
insert into @cols values(‘float’)
insert into @cols values(‘datetime’)
insert into @cols values(‘text’)
insert into @cols values(‘image’)
insert into @cols values(‘uniqueidentifier’)
insert into @cols values(‘smalldatetime’)
insert into @cols values(‘tinyint’)
insert into @cols values(‘smallint’)
insert into @cols values(‘sql_variant’)
set @sql=”
Select @sql=@sql+
case when charindex(‘(‘,@sql,1)<=0 then '(' else '' end +Column_Name + ' ' +Data_Type +
case when Data_Type in (Select datatype from @cols) then '' else '(' end+
case when data_type in ('real','money','decimal','numeric') then cast(isnull(numeric_precision,'') as varchar)+','+
case when data_type in ('real','money','decimal','numeric') then cast(isnull(Numeric_Scale,'') as varchar) end
when data_type in ('char','nvarchar','varchar','nchar') then cast(isnull(Character_Maximum_Length,'') as varchar) else '' end+
case when Data_Type in (Select datatype from @cols)then '' else ')' end+
case when Is_Nullable='No' then ' Not null,' else ' null,' end
from Information_Schema.COLUMNS where Table_Name=@tableName
select @table= 'Create table ' + table_Name from Information_Schema.COLUMNS where table_Name=@tableName
select @sql=@table + substring(@sql,1,len(@sql)-1) +' )'
select @sql as DDL
End
Else
Select 'The table '+@tableName + ' does not exist'
hi,
I am new in SQL, I am using SQL server 2005, I have one problem, the problem is the SA user password i have to reset after every 2-3 days. I donot want to reset after every few days. Anybody can help me regarding this.
Thanks
Sunil jangid
[Edited phone number]
Simply awesome………………No words to praise you !!
Your Parents should proud of you.God bless…..
hi,
Please let me know, how to fix this problem. I am very fresher in SQL Server. because of this problem my developer is not able to connect with database. when i reset SA password. developer can easly connect with database.but i cannot reset password everytime. anybody can help me reagarding this.
Sunil Jangid
Hello Sunil,
Is the password of “sa” user expiring repeatedly?
If that is the issue then, please check the in the Properties page of “sa” login uncheck the following two checkboxes:
Enforce Password policy
Enforce Password expiration
Regards,
Pinal Dave
[...] SQL SERVER – 2008 – Interview Questions and Answers – Part 1 [...]
Hi sir i have .db data base file with with thousnds of data how can open that database. i tried more but it ll not come . i have tried in sql lite and ms acess it cant open.Its Showing error Its Not sql lite database.and in sqldbx its showing binary format
Thank you for SQL server 2008 Interview book i really need it and may i know how to take backup of database with Stored procedure with data also?
Backup database your_db to disk='file_path'
Sir, i have completed my BCA and i have decided to join the Dot net course. How is dot net course related to Sql server 2008 or DBA. what are the chances of getting the job if i simultaneously complete both the courses. Please help me regarding this matter. I am really confused
it is very nice and use full blog…. Thanks lots..
Hi Pinal,
I wanted to weather the INDEX of a table is removed from a table when we restore the databse.
If it is removed then how do I REINDEX it again ?
Please Help.
When you restore the database entire database get changed to restored database
Pinal
You have done a great Job. Keep your good work going.
We hardly see people like you in this self centric world.
Kudos to your great work
Warm regards,
Ramesh Krishnamurthy
I want display row to column and column to row in sql Table by sql-query.
Look for PIVOT operator in SQL Server help file
I want display row to column and column to row in sql Table by sql-query.
As Exampel:
Table:
——————————-
Name Roll
AAAA 111
BBBB 222
——————————-
I want to display in Following Format by SQL_QUERY
Result Table:
Name AAAA AAAA
Roll 111 222
————————–
Hi Pinal.
Im working as junior level DBA in a production environment.Can i have a good article regarding performance tuning of SQL server.Also i want to know the importance of Dynamic management Views in performance of the sql server.I would like to appriciate the website and you as your efforts are worth than anything for us.Thanks in advance.
Hi Pinal,
Thanks so much for the lovely article that you have posted. I am actually preparing for the MCTS in sql server 2005 and want your help and guidence. Please guide me through the process of preparation and also please tell me how will it benefit me in my current role in the company as a Dotnet developer .
Respected sir,
my self zubair khan pursuing mca from ignou and working in cie Ltd company in last 3 years.as a Asstt IT executive.i also have knowledge of sql server7.0 and 2000.i want to make my speciliazation in database.i read ur interview question and answer it enough for IT company.i have T-sql programming skill. plz advice me sir
Hi,
i want to retrieve data from a table for a particular cell value, can anyone give me the answer…i dont knw column name and row number….
Can you post some sample data with expected result?
realy ur que. and ans. is very good
Hi dave,
I am having problems with learning the concepts of normalisation. Each time I find very difficult to normalise the given fields. Can you please share your experience on this. Or can you guide to some link where the normalisation is explained in detail with examples.
Thanks & Best regards,
kannan. R
I find it easier to think about normalization in the following way than to repeat mantras like “no redundant data”.
Everything I work with are objects. Objects are serialized as records in the database tables. Object has properties. Properties are columns in the database. Object has relationships to other object. Relationships are indicated (and forced) with foreign keys in the database.
Lastly, every object must be identifiable from other objects of same type.
Take for instance object: human. Humans are identified by SSN (but don’t use SSN as primary key in the database!). SSN is one of the properties also. Other properties are like gender, name and age. Now you got already one table defined:
CREATE TABLE Human(id INT PRIMARY KEY, SSN VARCHAR(20), Gender VARCHAR(6), Name VARCHAR(100), Age INT)
GO
INSERT INTO Human SELECT 1, ‘xxxxxx-xxx’, ‘Male’, ‘Marko Parkkola’, 33
GO
Humans usually live somewhere, let’s say in a house. House has properties like address. Now there’s another table:
CREATE TABLE House(id INT PRIMARY KEY, Address VARCHAR(100))
GO
INSERT INTO House SELECT 1, ‘Tampere, Finland’
GO
INSERT INTO House SELECT 1, ‘Keitele, Finland’
GO
Now we have house but who to put our human in there? Simple, we need a relationship table that tells where human lives. But human can possess many houses and live only in one at the time? Alright, let’s put a property there which tells which kind of relationship human has to the house:
CREATE TABLE Human_House(id INT PRIMARY KEY, HumanID INT NOT NULL, HouseID INT NOT NULL, RelationshipType VARCHAR(100))
GO
ALTER TABLE Human_Resource ADD CONSTRAINT FK_HH_Human FOREIGN KEY (HumanID) REFERENCES Human(id)
GO
ALTER TABLE Human_Resource ADD CONSTRAINT FK_HH_House FOREIGN KEY (HouseID) REFERENCES House(id)
GO
INSERT INTO Human_House SELECT 1, 1, 1, ‘Occupies’
INSERT INTO Human_House SELECT 1, 1, 2, ‘Rents’
Now examples are pretty lousy. Sorry about that. But I hope this helps, at least a bit.
hi a i manish
i know much better about sql
thanks!
hi ,
i like this forum.
hi
without using sql profiler how we monitor sql server.
It depends what you want to monitor. You can use Performance Monitor (Control Panel -> Administrative Tools -> Performace Monitor) to monitor at least something.
Hello Pinal sir,
great collection , i liked …
I am php developer and using mysql database(total 11 months exp . in mumbai).
but i am not satisfly with this .
so i want to change this to only database field.
and i have confusion where to start to become dba and in which database technology (oracle, sql server , ibm db2)
so kindly help me
thanks in advance
Respected Sir,
U have really contributed a lot for the sql learners. I have really liked all your question-answers.
Hi, Pinal Sir
I need to help, I was finished my Microsoft 2000, 2005, 2008 sql server dba tranning. Can you help me interview question and answer, and example of project. please
Hi,
Please suggest books which I can refer to passing 2008 dba certification.
Thanks & Regards
Hi all,
I really like read this forum, i find a lot of trick to a solve problem about rule, syntax sql etc.
Keep spirit Pinal Dave, regrad from me for you.
Anam
Can you tell me normalization with an example.i am still confused with it.
Search for the same in Google/Bing
hi,dave
i change my compony please send me some sql interview questions for my email id…..
There are lot of posts related to Interview questions. Search for the same in this site
can u please tell me how to write a User defined function for checking UK mob num(Regular expression)
Post some sample data with expected result
pinal good work kept up but plese response all time othervise student r not use internet
hi,
I am wokring in SQL server from 1.5 years..as eveloper and DBA now. If there is any job option, please let me know.
Pinal, its always gr8 to learn from your site.
Thanks
Tanu
[email address removed.]
Dear Sir,
First of all Thanks a lot for providing us such a nice information
and hope if any updates regarding to the SQL Server happened you will inform me.
Yours New Visitors and Student
Yogesh Chandra Upreti
Thanks!
Hello sir,
I am planning for a change so can you please provide me some interview questions on Sql server 2005 with some projects…and which book should i prefer?please let me know sir.
Thanks,
Mona
i want to validate Uk number before inserting the table in sql itself.So i am trying cal a sql function which will return true if the number has been valid.If i am suppling number like 02071931271(uK number)the function should return its a valid number.I am bit confused how to proceed this.Kindly help me.
thanks in advance.
i want to validate Uk number before inserting the table in sql itself.So i am trying cal a sql function which will return true if the number has been valid.If i am suppling number like 02071931271(uK number)the function should return its a valid number.I am bit confused how to proceed this.Kindly help me.
thanks in advance
What is the logic to be used to determine whether the number is valid or not?
Do you know of any good dvds that will teach someone, who is not an IT person, MS SQL Server 2008. I want to change carreer and a friend of mine talked to me about learning SQL Server 2008. I read a lot of books, did a lot of practices in my computer, but I still think that i am not ready to start applying for a job.
What is the least you to know to get a DBA Job.
I know that you can never know everything, with experience and friction you get better at what you do.
Just need some advise.
Thank you.
Tony
hi,
thanks
Hi,
I am working on sql 2008, I want to do sql developer certification.kindly suggest for the same.
It a great article.
Thanks
keep the good work going.
Hi Sir,
May i please get interview questions on CLUSTERING and SQL SERVER 2008. This is very urgent and important to me. Please help me. Please post it on my mail.
Regards,
Ankesh Kumar
Hi Pinal, your blog site is simply Out Standing!,I’m moving to DBA and your site has been very helpful, although i have a few questions that i’d like to ask. Is there a way to run 1 job for all databases? or selective in SQL Server 2008 R2?
Regards,
Bharath
This is one of the best article.
Through anyone easily understand about the SQL server basics.
Thanks……
Thanks for This /sql-server-2008-interview-questions-and-answers-part-All/
hi,..
i have just completed my B.C.A
and i have done sql server 2005 in dba from niit.
so i m looking job in data base plz suggest me few option.
thanks.
ihad completed my MCA.I learn the sqlserver and c#.net course.now i am searching job in software engenering. I want to know the data base like sql server.
I want to know the all faq’s of SQL server and queries.pls send me the list of questions to my ID.
pls send more interview questions and answers in sql
send more questions and answers in sql
hi i m working in telecom company as s.e. and i m working in sql server 2008 database ,i want know about sql developer,
please send me sql developer interview questions and answers.
Hi Pinal,
I am asked this question frequently in my interviews.
What kind of updates are found from sqlserver2005 to 2008?
I tried different sites could not find all of them
Thanks,
Sravanthi
Normal update with value assignment
update table set col=10 where col<10
Another is quirky update
http://beyondrelational.com/blogs/madhivanan/archive/2009/06/10/quirky-update-in-sql-server.aspx
Hi ..could u pls suggest me any gud website for interview questions of SQL DBA and developer
nice answers
Hi pinal
i would like to thank u for such a good effort that u done. then kindly tel me about the concept of joins
This is one of the best article.
Through anyone easily understand about the SQL server basics.
Thanks……
Sir, i have completed my BCA and i have decided to join the Dot net course. How is dot net course related to Sql server 2008 or DBA. what are the chances of getting the job if i simultaneously complete both the courses. Please help me regarding this matter. I am really confused
hi ,
this is vijay bhatt from Udaipur ,
need not to confuse it is jest a backend , u can also use
Oracle , DB2 , Infomix etc.
but now mostly compeny use Sql Server so u can learn easily
, try to concentrate .net application .. Go on project ..
Good Luck
vijay bhatt
Hi sir,
I am emmanuel.
what is the syntax for Left outer join and right outer join?
SQL Server help file has examples for the both
Hi,
Can you tell me how to copy the table contents from one server to another server.
Eg:
Server : server 1
Database: db1
Table : tb1
Server : server2
Database: db2
Table : tb2
i want to copy the tb1 data into tb2. Please help me?
take bakup of ur database table on one server .
and again restore another database .. its simple ..
try it .
Hi can any one give me an example for creating a complex views using oder by statemnt
Hi ,
I have one question that ,
why Stored Procedure execute fast ?
can any one give me answer …
Vijay bhatt
hi ,
how can i convert or atteach database
sql server 2000 to 2005 .
with contain …
is it possible…
vijay bhatt
What did you mean by “with contain”?
Sir
i Prasanna dadhich haveing 2.6 year exp as DBA
now a days i am looking another job
but some are the question when the interviewee ask me i was confused please help
1) how to reduse .ndf file size and where it is located
2) why the ldf file size bigger than .mdf file
3) what are the technical diff bet 2000 and 2005
4) if master db is deleted what will happen and to recover it
5) what are the disadvantage of sql 2005
please reply
waiting for that
[email removed.]
Hi Pinal,
I have 2 server, one is installed sql 2008 another one is sql 2000,
I have connected both with linked server, I can able to access from 2008 server to
sql 2000 table but from sql 2000 server to sql 2008 is not
working ..
Iam getting error “General network error. Check your network documentation.”.
can you help me to access from sql2000 to sql2008 db through linkedserver?
Thanks in advance
SankarA
hello sir
when i open sql then it not oprate the recruitment and pubs pis sugesed me what i do
Hi Pinal,
I am regular reader of your blog. I have a problem.
I have a table name T1 having only one Column name Col1 having rows –
Col1
a
b
c
And another table name T2 also having only one Column name Col1 having rows –
Col1
x
y
z
Now I want record in following format-
Col1 Col2
a x
b y
c z
This is my first question , I need it in very urgent basis. Please help sir…
Hello Kapil Dev Tripathi,
You can add same types of primary keys (viz. PK) in both the tables (Just auto incremental value) and run some kind of query like:
Select Table1.Col1, Table2.Col2
FROM Table1 INNER JOIN Table2 ON Table1.PK = Table2.PK
Regards.
To Pinal : Keep up your great work !
ihave problame whene i restor data base give me msg no 3205 i try serval times
Thank you sir..
Hello Sir,
i am really surprise when i got ur this site.
pls send more SQL interview questions and answers
Thanks & Regards
Shahid Ansari
hi,
Thanks for the availability of the all interviwe quetion sir
i am just biggener i want to read more and more in sql server
commands and all relation ship
thaks once agine
I am writing one stored procedure which has parameter as well. I also need to some kind of programming logic as well. Here is my copy of stored procedure.
I got an error that conversion failed while converting varchar to int datatype….I am using parameters and query in single quotation becasue of I am running some programming logic as well.
What would be the error?
Thanks.
You need to post the procedure code in order to help you
I have a procedure which is performing merge(update/insert) , i would like to know how to add Exception handling where i can insert that error message in the table
I’ve checked with Try..Catch , but am finding some problem … Can any one give an example for Exception handling in procedure
Hi, what is the best way to store ip address in SQL.. int, char? Why?
If it is fixed length, use char datatype. Otherwise you need to split and do some calculation to determine it if you use int datatype
Thanks for the very good FAQ’s on SQL Server.
Can any one tell me what is the pros and cons of using SQL Cache dependency in .net
Thanks Pinal for providing the so detailed information about the SQL.
I am feraz ahmad , I have 6 years of experience in web development but I am enjoying more in the data base front. I want to pursue my career in the SQL DBA.
Please suggest me correct path and where I can focus and achieve my goal.
Thanks in advance.
Hi,
I want database testing interview questions and answers. So please help me
What is a Word Wheel in sql??
hi iam geeta right now v r in training period on sql server2008 may i know how was the reqiuirements for this domain
Hi Geetha, Ram here, as u said that u r in training period on SQL SERVER 2008,bt best of my knowledge,no such institutes provides full in depth of training on domain,but the things they teach us will be usefull,n it will be fully depends upon u how u involve to learn the concepts, and requirements for this domain as a fresher it would be bit tougher but for 2+ experience it wrks out…..in hyderabad as well as in BANGlore n Chennai n pune..
Hi Pinal..
thanks in advance
Could u say me how Many type of Triggers in Sql 2008 Like DDL trigger, Instead of Trigger, Before and After, like Wise
regards
logu
Have a look at BOL, SQL Server help file. It has all information about triggers
hiiiiiiiiiiiiiii guyzzzzzzzzzzzzzzzzzzzzz?
i have one doubt in sql that is
in synonyms invalid cases r there.then i want to knw the wat is invalid case ?nd how many cases r there?at what situations r considered as invalid?
gyanesh sharma
Q…..how to create table in sql server without using create table table_name query? is there any alternative ??????
@Gyanesh,
You can use the designer to do this if u do not want to write the script.Just right click on the Tables in the object explorer under the required database and select New Table option and set the appropriate properties.
Else,
if u want to create table with the same schema as one of the existing tables , then u can use SELECT clause for this.
Ex : SELECT Col1,Col2 INTO New_Table_Name FROM Existing_Table_Name
wah..
plz send me details of sql interview questions n answers
if u have ,pls send the questions .thanks in advance
hi
i have a table name employee ..where the columns are- emp_name,emp_dept,emp_desig,salary..
now i want to see the department wise total salary and name of those employees whose sum of salary greater than 10000
Please Help How to open .db file database. i have tried in sql 2005 and sqqldbx and ms acess it ll not come. please provide some information about that
Hi penal,
Nice artical
Hi penal,
I liked your website.
I am working in mnc. and my current role is release engineer. i need to do the website and ssrs script deployments. Can you pls suggest me what all concepts i need to focus for upcoming interviews.
Thanks,
Regards,
Sneha
simple and nice explanation ..thanks a lot for the Knowledge sharing
Hello,
I am Mittal and I had a BS in Computer science degree and recently received certification in SQL SERVER. and because i dont have any experience it’ hard to find a job.. Is anyone know how i can start or what is the best things to do to start in SQL or is any training center that i can physically go and take training.
Please let me know.
Thanks,
Mittal P
raja
hi,
i have completed sql dba plz send sample resume on
Hi People,
I am Sagar, i am planning to change the current job. currenctly i am working as Prod MS SQL DBA, please can you send me some questions and answers realated to prod bda?
Thanks in devance.
Regards,
Sagar
drawbacks of triggers…???
Hi,
can we insert a value in a table by store procedure by declaring the value datatype in exec SP ? and does SP returns a value type or reference type ?
Nice information ……..just please advice me what is the good option for certification oracle 10g dba track or sql server 2008 which is the best………
i am also searching .net base job. please if u know any information about .net base jobs please send me mail.
Then give ur mail id