1 / 21

SQL SERVER TUTORIALS

Learn about SQL Server Interview Questions. Kaashiv Infotech Offers SQL Server Interview Questions for our futher carrer. Kaashiv Infotech offers Inplant training, Internship and Course for Students in SQL Server.

kaashiv1
Download Presentation

SQL SERVER TUTORIALS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. KAASHIV INFOTECH The Asia, India, Tamil Nadu Book Of Record Holders SQL SERVER –Booklet 6 - Gives you the interview tips in SQL Server SQL SERVER Interview Questions-2014

  2. KAASHIV INFOTECH Welcomes you to the Expert Voice Corner Mr.J.VenkatesanPrabu VenkatesanPrabuJayakantham (venkat) has more than 8 years experience in the Microsoft Technologies such as VB.Net, ASP.Net, C#.net, SSIS, SSAS, ADO.Net, etc., He is the Managing Director of KAASHIVINFOTECH (http://www.kaashivinfotech.com/),a software company in Chennai. Before that, he worked in HCL Technologies (India and Australia) for six years as Project Lead. As a service motive, Venkat contributed more than 700 articles which is read by the developers in 170 countries (400 developers per day) (http://venkattechnicalblog.blogspot.com/). Aligned with KaaShiv InfoTech’s mission,he met more than 20,000 young minds and spreaded Microsoft Technologies / Career guidance programs. Venkat won many awards in his career, which includes Prestigious Microsoft MVP (Most Valuable Professional) award for the years 2008,2009,2010,2011,2012,2013 and won many awards. List of other awards in his career,

  3. Microsoft certified Smart .Net Candidate in 2004 • Most valuable member for dotnetspider site in 2007 • HCL SQL Subject Matter expert (SME - SQL Server) for the year (2008,2009) • HCL Special contribution award winner on Dotnet skills for year(2008) HCL SQL Knowledge Champion for the year 2009 • Mind Cracker MVP on SQLServer –2010 for the year 2010,2011 • INETA champion - Gold Member – 2010 for the year 2010 HCL Service Contribution Award for the year 2010 • Leading Lights "Rising Star" award from Common Wealth Bank, Australia for the year 2010 • TECHNICAL CERTIFICATION • Cisco certified Network Associate (CCNA) – 2004 • Microsoft Certified Application Developer (MCAD) – 2005

  4. ACKNOWLEDGEMENT I would like to thank my family members for their support and encouragement. Without their support it would be impossible for me to publish this e-book. I would also like to thank my KaaShiv InfoTech team for their support to publish this e-book. DISCLAIMER All rights reserved. No part of this book may be copied, adapted, abridged or stored in any retrieval system, computer system, photographic or other system or transmitted in any form or by any means without the prior written permission of the copyright holders. Any breach will entail legal action and permission without further notice.

  5. 1.What is the difference between a “Local Temporary Table” and • “Global Temporary Table”? • A Local Temporary Table is created by giving it a prefix of # whereas a Global Temporary Table is created by giving it a prefix of ##. • A Local Temporary Table cannot be shared among multiple users whereas a Global Temporary Table can be shared among multiple users. • A Local Temporary Table is only available to the current DB connection for the current user and are cleared when the connection is closed whereas a Global Temporary Table is available to any connection once created. They are cleared when the last connection is closed. • 2.What are DMVs? • Dynamic Management Views (DMVs), are functions that give you information on the state of the server. DMVs, for the most part, are used to monitor the health of a server. They really just give you a snapshot of what’s going on inside the server. They let you monitor the health of a server instance, troubleshoot major problems and tune the server to increase performance.

  6. 3.What are DBCC commands? • In very basic terms the Database Consistency Checker (DBCC) is used to aid in server maintenance. DBCC commands, many of which are completely undocumented, provide a set of commands that let you perform routing maintenance, status and validation checks. The most common DBCC commands are: DBCC CHECKALLOC (Lets you check disk allocation); DBCC OPENTRAN (Lets you check any open transactions); and DBCC HELP (shows a list of available DBCC commands to aid your server maintenance processes). • 4. What are the basic functions for master, msdb, model, tempdb and resource databases? • 1. The master database holds information for all databases located on the SQL Server instance and is theglue that holds the engine together. Because SQL Server cannot start without a functioning masterdatabase, you must administer this database with care. • 2. The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping. • 3. The tempdb holds temporary objects such as global and local temporary tables and stored procedures.

  7. 4. The model is essentially a template database used in the creation of any new user database created in the instance. • 5. The resoure Database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata. • 5. What is Service Broker? • Service Broker is a message-queuing technology in SQL Server that allows developers to integrate SQL Server fully into distributed applications. Service Broker is feature which provides facility to SQL Server to send an asynchronous, transactional message. it allows a database to send a message to another database without waiting for the response, so the application will continue to function if the remote database is temporarily unavailable. • 6. Where SQL server user names and passwords are stored in SQL server? • They get stored in System Catalog Views sys.server_principals and sys.sql_logins.

  8. 7. What is Policy Management? Policy Management in SQL SERVER 2008 allows you to define and enforce policies for configuring and managing SQL Server across the enterprise. Policy-Based Management is configured in SQL Server Management Studio (SSMS). Navigate to the Object Explorer and expand the Management node and the Policy Management node; you will see the Policies, Conditions, and Facets nodes. 8. What is Replication and Database Mirroring? Database mirroring can be used with replication to provide availability for the publication database. Database mirroring involves two copies of a single database that typically reside on different computers.

  9. 9. What are Sparse Columns? • INTERNSHIP IN KAASHIV INFOTECH-Best internship provider in Chennai • Web Application DesigningProject DocumentationLive In house Application DevelopmentWindows ADO.NET ApplicationTemplate Designing-Live Template Designing, CSSA sparse column is another tool used to reduce the amount of physical storage used in a database. They are the ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values.

  10. 10. What does TOP Operator Do? The TOP operator is used to specify the number of rows to be returned by a query. The TOP operator has new addition in SQL SERVER 2008 that it accepts variables as well as literal values and can be used with INSERT, UPDATE, and DELETES statements. 11. What is CTE? CTE is an abbreviation Common Table Expression. A Common Table Expression (CTE) is an expression that can be thought of as a temporary result set which is defined within the execution of a single SQL statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. 12. What is MERGE Statement? MERGE is a new feature that provides an efficient way to perform multiple DML operations. In previous versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions, but now, using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it. One of the most important advantages of MERGE statement is all the data is read and processed only once.

  11. INTERNSHIP IN KAASHIV INFOTECH • -Best internship provider in Chennai • Web Application Designing • Project Documentation • Live Inhouse • Application Development • Windows ADO.NET Application • Template Designing-Live Template Designing, CSS 13. What is Filtered Index? Filtered Index is used to index a portion of rows in a table that means it applies filter on INDEX which improves query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes. When we see an Index created with some where clause then that is actually a FILTERED INDEX.

  12. 14. Which are new data types introduced in SQL SERVER 2008? • The GEOMETRY Type: The GEOMETRY data type is a system .NET common language runtime (CLR) data type in SQL Server. This type represents data in a two-dimensional Euclidean coordinate system. • The GEOGRAPHY Type: The GEOGRAPHY datatype’s functions are the same as with GEOMETRY. The difference between the two is that when you specify GEOGRAPHY, you are usually specifying points in terms of latitude and longitude. • New Date and Time Datatypes: SQL Server 2008 introduces four new datatypes related to date and time: DATE, TIME, DATETIMEOFFSET, and DATETIME2. • DATE: The new DATE type just stores the date itself. It is based on the Gregorian calendar and handles years from 1 to 9999. • TIME: The new TIME (n) type stores time with a range of 00:00:00.0000000 through 23:59:59.9999999. The precision is allowed with this type. TIME supports seconds down to 100 nanoseconds. The n in TIME (n) defines this level of fractional second precision, from 0 to 7 digits of precision. • The DATETIMEOFFSET Type: DATETIMEOFFSET (n) is the time-zone-aware version of a datetimedatatype. The name will appear less odd when you consider what it really is: a date + a time + a time-zone offset. The offset is based on how far behind or ahead you are from Coordinated Universal Time (UTC) time.

  13. The DATETIME2 Type: It is an extension of the datetime type in earlier versions of SQL Server. This new datatype has a date range covering dates from January 1 of year 1 through December 31 of year 9999. This is a definite improvement over the 1753 lower boundary of the datetimedatatype. DATETIME2 not only includes the larger date range, but also has a timestamp and the same fractional precision that TIME type provides • 15. What are the Advantages of using CTE? • Using CTE improves the readability and makes maintenance of complex queries easy. • The query can be divided into separate, simple, logical building blocks which can be then used to build more complex CTEs until final result set is generated. • CTE can be defined in functions, stored procedures, triggers or even views. • After a CTE is defined, it can be used as a Table or a View and can SELECT, INSERT, UPDATE or DELETE Data.

  14. KaaShiv InfoTech Offers Best Inpant Training in Chennai. The training at KAASHIV INFOTECH focus on developing the technical oriented concepts that turn graduates into employable assets. Handled only by professionals from MNC companies, we know how to equip you with strong technologies fundamentals. InPlant Training Schedule for CSE/IT/MCA Students

  15. InPlant Training Schedule for Electronic/Electrical/EIE Students:

  16. Mechanical/Civil Inplant training Schedule:

  17. Tags:inplanttraining in chennai,Bestinplanttraining Program in Chennai Anna Nagar,Best and Effective inplanttraining Program in Chennai at Anna Nagar ,inplanttraining Program for Engineering Students , inplanttraining Program for Arts and Science Students , inplanttraining Program for BE Students , inplanttraining Program for Information Technology Students ,inplanttraining Program in Chennai , Best and Effective inplanttraining Program in Chennai,Best and good inplanttraining Program in Chennai,inplanttraining Program for Computer Science Students, inplanttraining Program for Electronics and Communication Students,inplanttraining Program for Electrical and Electronics Students , inplanttraining Program for Engineering Studentsinannanagar , inplanttraining Program for Arts and Science Students in annanagar,EffectiveinplanttrainingProgram,Effective and Free inplanttrainingProgram,bestinplanttraining in chennai near rountana,bestinplanttraining for engineering students in chennai,bestinplanttraining in annanagar,inplanttraining for arts and science students in tamilnadu,bestinplanttraining for arts and science students in annanagar near rountana,bestinplanttraining for uggraduates,bestinplanttraining for pg graduates,bestinplanttraining for b.e/b.techstudents,bestinplanttraining for ug graduates in chennai,bestinplanttraining for ug graduates in annanagar,bestinplanttraining for ug graduates in tamilnadu,bestinplanttraining for pg graduates in chennai,

  18. best inplanttraining for pg graduates in annanagar,bestinplanttraining for pg graduates in tamilnadu,inplanttraining for csestudents,inplanttraining for it students,inplanttraining for ecestudents,inplanttraining for eeestudents,inplanttraining on android in chennai,inplanttraining on java in chennai,inplanttraining on embedded systems in chennai,inplanttraining on matlab in chennai,inplanttraining on .net in chennai,inplanttraining on android in annanagar,inplanttraining on java in annanagar,inplanttraining on embedded systems in annanagar,inplanttraining on matlab in annanagar,inplanttraining on .net in annanagar,best summer inplanttraining for arts and science ug graduates in chennai,best summer inplanttraining for arts and science pg graduates in chennai,best summer inplanttraining for engineering ug graduates in chennai,best summer inplanttraining for engineering pg graduates in chennai,best summer inplanttraining for arts and science ug graduates in annanagar,best summer inplanttraining for arts and science pg graduates in annanagar,best summer inplanttraining for engineering ug graduates in annanagar,best summer inplanttraining for engineering pg graduates in annanagar,bestinplanttraining for mba graduates in chennai,bestinplanttrainingfomca graduates in chennai,bestinplanttraining for mba graduates in annanagar,bestinplanttraining for mca graduates in annanagar,best summer inplanttraining for mba graduates in chennai,best summer inplanttraining for mca graduates in chennai,best summer inplanttraining for mba graduates in annanagar, best summer inplanttraining for mba graduates near rountana,best summer inplanttraining for mca graduates near rountana

  19. Address: KAASHIV INFO TECHShivanantha Building,X41, 5th Floor,2nd Avenue,(Near Ayyappan Temple)Anna Nagar, Chennai = 600040. Send Us Your Request Email To arun@kaashivinfotech.com,kaashiv.info@gmail.com,venkat@kaashivinfotech.com Contact Number : 9840678906 ;; 9003718877 ;; 9962345637 ; Visit our other websites: http://inplanttrainingchennai.com/- Inplant Training Portal http://inplanttrainingchennai.com/ - Internship Portal jobsanddumps.com – Job Portal https://plus.google.com/u/0/108546120202591604585 https://plus.google.com/u/0/b/110228862465265998202/dashboard/overview https://plus.google.com/u/0/b/117408505876070870512/dashboard/overview

  20. https://plus.google.com/u/0/b/104468163439231303834 /dashboard/overview https://plus.google.com/u/0/b/117640664472494971423/dashboard/overview KaaShiv InfoTech Facebook Page https://www.facebook.com/KaaShivInfoTech Inplant Training Program in Chennai https://www.facebook.com/pages/Inplant-Training-Program-in-Chennai/1402097696706380 Internship in Chennai https://www.facebook.com/pages/Internship-in-Chennai-KaaShiv/1446147235603704

  21. Inplant Training https://www.facebook.com/pages/Inplant-Training/256116284550327

More Related