Category: ms Sql Server

MS SQL Server – Reporting services ”; Previous Next Report is a displayable component. Usage Report is basically used for two purposes – Company Internal Operations and Company External Operations. Reporting Services This is a service which is used to create and publish various kinds of reports. Following are the three requirements necessary to develop any report. Business process Layout QueryProcedureView The BIDS (Business Intelligence Studio till 2008 R2) and SSDT (SQL Server Data Tools from 2012) are environment to develop reports. Following are the steps to open BIDSSSDT environment to develop reports. Step 1 − Open either BIDSSSDT based on the version from the Microsoft SQL Server programs group. The following screen will appear. In this case, SSDT has opened. Step 2 − Go to file at the top left corner in the above screenshot. Click New and select project. The following screen will open. Step 3 − In the above screen, select reporting services under business intelligence at the top left corner as shown in the following screenshot. Step 4 − In the above screen, select either report server project wizard (it will guide you step by step through wizards) or report server project (it will be used to select customized settings) based on your requirement to develop the report. Print Page Previous Next Advertisements ”;

MS SQL Server – Login Database ”; Previous Next A login is a simple credential for accessing SQL Server. For example, you provide your username and password when logging on to Windows or even your e-mail account. This username and password builds up the credentials. Therefore, credentials are simply a username and a password. SQL Server allows four types of logins − A login based on Windows credentials. A login specific to SQL Server. A login mapped to a certificate. A login mapped to asymmetric key. In this tutorial, we are interested in logins based on Windows Credentials and logins specific to SQL Server. Logins based on Windows credentials allow you to log in to SQL Server using a Windows username and password. If you need to create your own credentials (username and password,) you can create a login specific to SQL Server. To create, alter, or remove a SQL Server login, you can take one of two approaches − Using SQL Server Management Studio. Using T-SQL statements. Following methods are used to create Login − First Method – Using SQL Server Management Studio Step 1 − After connecting to SQL Server Instance, expand logins folder as shown in the following snapshot. Step 2 − Right-click on Logins, then click Newlogin and the following screen will open. Step 3 − Fill the Login name, Password and Confirm password columns as shown in the above screen and then click OK. Login will be created as shown in the following image. Second Method – Using T-SQL Script Create login yourloginname with password=”yourpassword” To create login name with TestLogin and password ‘P@ssword’ run below the following query. Create login TestLogin with password=”P@ssword” Print Page Previous Next Advertisements ”;

MS SQL Server – Useful Resources ”; Previous Next The following resources contain additional information on MS SQL Server. Please use them to get more in-depth knowledge on this. Useful Links on MS SQL Server MS SQL Server Wiki – Wikipedia Reference for MS SQL Server. Useful Books on MS SQL Server To enlist your site on this page, please drop an email to [email protected] Print Page Previous Next Advertisements ”;

MS SQL Server – Quick Guide ”; Previous Next MS SQL Server – Overview This chapter introduces SQL Server, discusses its usage, advantages, versions, and components. What is SQL Server? It is a software, developed by Microsoft, which is implemented from the specification of RDBMS. It is also an ORDBMS. It is platform dependent. It is both GUI and command based software. It supports SQL (SEQUEL) language which is an IBM product, non-procedural, common database and case insensitive language. Usage of SQL Server To create databases. To maintain databases. To analyze the data through SQL Server Analysis Services (SSAS). To generate reports through SQL Server Reporting Services (SSRS). To carry out ETL operations through SQL Server Integration Services (SSIS). Versions of SQL Server Version Year Code Name 6.0 1995 SQL95 6.5 1996 Hydra 7.0 1998 Sphinx 8.0 (2000) 2000 Shiloh 9.0 (2005) 2005 Yukon 10.0 (2008) 2008 Katmai 10.5 (2008 R2) 2010 Kilimanjaro 11.0 (2012) 2012 Denali 12 (2014) 2014 Hekaton (initially), SQL 14 (current) SQL Server Components SQL Server works in client-server architecture, hence it supports two types of components − (a) Workstation and (b) Server. Workstation components are installed in every device/SQL Server operator’s machine. These are just interfaces to interact with Server components. Example: SSMS, SSCM, Profiler, BIDS, SQLEM etc. Server components are installed in centralized server. These are services. Example: SQL Server, SQL Server Agent, SSIS, SSAS, SSRS, SQL browser, SQL Server full text search etc. Instance of SQL Server An instance is an installation of SQL Server. An instance is an exact copy of the same software. If we install ”n” times, then ”n” instances will be created. There are two types of instances in SQL Server a) Default b) Named. Only one default instance will be supported in one Server. Multiple named instances will be supported in one Server. Default instance will take the server name as Instance name. Default instance service name is MSSQLSERVER. 16 instances will be supported in 2000 version. 50 instances will supported in 2005 and later versions. Advantages of Instances To install different versions in one machine. To reduce cost. To maintain production, development, and test environments separately. To reduce temporary database problems. To separate security privileges. To maintain standby server. MS SQL Server – Editions SQL Server is available in various editions. This chapter lists the multiple editions with its features. Enterprise − This is the top-end edition with a full feature set. Standard − This has less features than Enterprise, when there is no requirement of advanced features. Workgroup − This is suitable for remote offices of a larger company. Web − This is designed for web applications. Developer − This is similar to Enterprise, but licensed to only one user for development, testing and demo. It can be easily upgraded to Enterprise without reinstallation. Express − This is free entry level database. It can utilize only 1 CPU and 1 GB memory, the maximum size of the database is 10 GB. Compact − This is free embedded database for mobile application development. The maximum size of the database is 4 GB. Datacenter − The major change in new SQL Server 2008 R2 is Datacenter Edition. The Datacenter edition has no memory limitation and offers support for more than 25 instances. Business Intelligence − Business Intelligence Edition is a new introduction in SQL Server 2012. This edition includes all the features in the Standard edition and support for advanced BI features such as Power View and PowerPivot, but it lacks support for advanced availability features like AlwaysOn Availability Groups and other online operations. Enterprise Evaluation − The SQL Server Evaluation Edition is a great way to get a fully functional and free instance of SQL Server for learning and developing solutions. This edition has a built-in expiry of 6 months from the time that you install it. 2005 2008 2008 R2 2012 2014 Enterprise Yes Yes Yes Yes Standard Yes Yes Yes Yes Developer Yes Yes Yes Yes Workgroup Yes Yes No No Win Compact Edition – Mobile Yes Yes Yes Yes Enterprise Evaluation Yes Yes Yes Yes Express Yes Yes Yes Yes Web Yes Yes Yes Datacenter No No Business Intelligence Yes MS SQL Server – Installation SQL Server supports two types of installation − Standalone Cluster based Checks Check RDP access for the server. Check OS bit, IP, domain of server. Check if your account is in admin group to run setup.exe file. Software location. Requirements Which version, edition, SP and hotfix if any. Service accounts for database engine, agent, SSAS, SSIS, SSRS, if any. Named instance name if any. Location for binaries, system, user databases. Authentication mode. Collation setting. List of features. Pre-requisites for 2005 Setup support files. .net framework 2.0. SQL Server native client. Pre-requisites for 2008&2008R2 Setup support files. .net framework 3.5 SP1. SQL Server native client. Windows installer 4.5/later version. Pre-requisites for 2012&2014 Setup support files. .net framework 4.0. SQL Server native client. Windows installer 4.5/later version. Windows PowerShell 2.0. Installation Steps Step 1 − Download the Evaluation Edition from https://www.microsoft.com/en-us/evalcenter/download-sql-server-2019 Once the software is downloaded, the following files will be available based on your download (32 or 64 bit) option. ENUx86SQLFULL_x86_ENU_Core.box ENUx86SQLFULL_x86_ENU_Install.exe ENUx86SQLFULL_x86_ENU_Lang.box OR ENUx86SQLFULL_x64_ENU_Core.box ENUx86SQLFULL_x64_ENU_Install.exe ENUx86SQLFULL_x64_ENU_Lang.box Note − X86 (32 bit) and X64 (64 bit) Step 2 − Double-click the “SQLFULL_x86_ENU_Install.exe” or “SQLFULL_x64_ENU_Install.exe”, it will extract the required files for installation in the“SQLFULL_x86_ENU” or “SQLFULL_x86_ENU” folder respectively. Step 3 − Click the “SQLFULL_x86_ENU” or “SQLFULL_x64_ENU_Install.exe” folder and double-click “SETUP” application. For understanding, here we have used SQLFULL_x64_ENU_Install.exe software. Step 4 − Once we click on ”setup” application, the following screen will open. Step 5 − Click Installation which is on the left side of the above screen. Step 6 − Click the first option of the right side seen on the above screen. The following screen will open. Step 7 − Click OK and the following screen pops up. Step 8 − Click Next to get the following screen. Step 9 − Make sure to

MS SQL Server – Select Database ”; Previous Next Select your database based on your action before going ahead with any of the following methods. Method 1 – Using SQL Server Management Studio Example To run a query to select backup history on database called ‘msdb’, select the msdb database as shown in the following snapshot. Method 2 – Using T-SQL Script Use <your database name> Example To run your query to select backup history on database called ‘msdb’, select the msdb database by executing the following query. Exec use msdb The query will open msdb database. You can execute the following query to select backup history. Select * from backupset Print Page Previous Next Advertisements ”;

MS SQL Server – Overview ”; Previous Next This chapter introduces SQL Server, discusses its usage, advantages, versions, and components. What is SQL Server? It is a software, developed by Microsoft, which is implemented from the specification of RDBMS. It is also an ORDBMS. It is platform dependent. It is both GUI and command based software. It supports SQL (SEQUEL) language which is an IBM product, non-procedural, common database and case insensitive language. Usage of SQL Server To create databases. To maintain databases. To analyze the data through SQL Server Analysis Services (SSAS). To generate reports through SQL Server Reporting Services (SSRS). To carry out ETL operations through SQL Server Integration Services (SSIS). Versions of SQL Server Version Year Code Name 6.0 1995 SQL95 6.5 1996 Hydra 7.0 1998 Sphinx 8.0 (2000) 2000 Shiloh 9.0 (2005) 2005 Yukon 10.0 (2008) 2008 Katmai 10.5 (2008 R2) 2010 Kilimanjaro 11.0 (2012) 2012 Denali 12 (2014) 2014 Hekaton (initially), SQL 14 (current) SQL Server Components SQL Server works in client-server architecture, hence it supports two types of components − (a) Workstation and (b) Server. Workstation components are installed in every device/SQL Server operator’s machine. These are just interfaces to interact with Server components. Example: SSMS, SSCM, Profiler, BIDS, SQLEM etc. Server components are installed in centralized server. These are services. Example: SQL Server, SQL Server Agent, SSIS, SSAS, SSRS, SQL browser, SQL Server full text search etc. Instance of SQL Server An instance is an installation of SQL Server. An instance is an exact copy of the same software. If we install ”n” times, then ”n” instances will be created. There are two types of instances in SQL Server a) Default b) Named. Only one default instance will be supported in one Server. Multiple named instances will be supported in one Server. Default instance will take the server name as Instance name. Default instance service name is MSSQLSERVER. 16 instances will be supported in 2000 version. 50 instances will supported in 2005 and later versions. Advantages of Instances To install different versions in one machine. To reduce cost. To maintain production, development, and test environments separately. To reduce temporary database problems. To separate security privileges. To maintain standby server. Print Page Previous Next Advertisements ”;

MS SQL Server – Editions ”; Previous Next SQL Server is available in various editions. This chapter lists the multiple editions with its features. Enterprise − This is the top-end edition with a full feature set. Standard − This has less features than Enterprise, when there is no requirement of advanced features. Workgroup − This is suitable for remote offices of a larger company. Web − This is designed for web applications. Developer − This is similar to Enterprise, but licensed to only one user for development, testing and demo. It can be easily upgraded to Enterprise without reinstallation. Express − This is free entry level database. It can utilize only 1 CPU and 1 GB memory, the maximum size of the database is 10 GB. Compact − This is free embedded database for mobile application development. The maximum size of the database is 4 GB. Datacenter − The major change in new SQL Server 2008 R2 is Datacenter Edition. The Datacenter edition has no memory limitation and offers support for more than 25 instances. Business Intelligence − Business Intelligence Edition is a new introduction in SQL Server 2012. This edition includes all the features in the Standard edition and support for advanced BI features such as Power View and PowerPivot, but it lacks support for advanced availability features like AlwaysOn Availability Groups and other online operations. Enterprise Evaluation − The SQL Server Evaluation Edition is a great way to get a fully functional and free instance of SQL Server for learning and developing solutions. This edition has a built-in expiry of 6 months from the time that you install it. 2005 2008 2008 R2 2012 2014 Enterprise Yes Yes Yes Yes Standard Yes Yes Yes Yes Developer Yes Yes Yes Yes Workgroup Yes Yes No No Win Compact Edition – Mobile Yes Yes Yes Yes Enterprise Evaluation Yes Yes Yes Yes Express Yes Yes Yes Yes Web Yes Yes Yes Datacenter No No Business Intelligence Yes Print Page Previous Next Advertisements ”;

MS SQL Server Tutorial PDF Version Quick Guide Resources Job Search Discussion MS SQL Server is a relational database management system (RDBMS) developed by Microsoft. This product is built for the basic function of storing retrieving data as required by other applications. It can be run either on the same computer or on another across a network. This tutorial explains some basic and advanced concepts of SQL Server such as how to create and restore data, create login and backup, assign permissions, etc. Each topic is explained using examples for easy understanding. Audience This tutorial is designed for all those readers who want to learn the fundamentals of SQL Server and put it into practice. Prerequisites To go ahead with this tutorial, familiarity with database concepts is preferred. It is good to have SQL Server installed on your computer, as it might assist you in executing the examples yourself and get to know how it works. Print Page Previous Next Advertisements ”;

MS SQL Server – Execution Plans ”; Previous Next Execution plan will be generated by Query optimizer with the help of statistics and Algebrizerprocessor tree. It is the result of Query optimizer and tells how to doperform your workrequirement. There are two different execution plans – Estimated and Actual. Estimated execution plan indicates optimizer view. Actual execution plan indicates what executed the query and how was it done. Execution plans are stored in memory called plan cache, hence can be reused. Each plan is stored once unless optimizer decides parallelism for the execution of the query. There are three different formats of execution plans available in SQL Server – Graphical plans, Text plans, and XML plans. SHOWPLAN is the permission which is required for the user who wants to see the execution plan. Example 1 Following is the procedure to view the estimated execution plan. Step 1 − Connect to SQL Server instance. In this case, ”TESTINSTANCE” is the instance name as shown in the following snapshot. Step 2 − Click on New Query option on the above screen and write the following query. Before writing the query, select the database name. In this case, ”TestDB” is database name. Select * from StudentTable Step 3 − Click the symbol which is highlighted in red color box on the above screen to display the estimated execution plan as shown in the following screenshot. Step 4 − Place the mouse on table scan which is the second symbol above the red color box in the above screen to display the estimated execution plan in detail. The following screenshot appears. Example 2 Following is the procedure to view the actual execution plan. Step 1 Connect to SQL Server instance. In this case, ”TESTINSTANCE” is the instance name. Step 2 − Click New Query option seen on the above screen and write the following query. Before writing the query, select the database name. In this case, ”TestDB” is database name. Select * from StudentTable Step 3 − Click the symbol which is highlighted in red color box on the above screen and then execute the query to display the actual execution plan along with the query result as shown in the following screenshot. Step 4 − Place the mouse on the table scan which is the second symbol above the red color box on the screen to display the actual execution plan in detail. The following screenshot appears. Step 5 − Click Results which is on the left top corner on the above screen to get the following screen. Print Page Previous Next Advertisements ”;

MS SQL Server – Restoring Databases ”; Previous Next Restoring is the process of copying data from a backup and applying logged transactions to the data. Restore is what you do with backups. Take the backup file and turn it back into a database. The Restore database option can be done using either of the following two methods. Method 1 – T-SQL Syntax Restore database <Your database name> from disk = ”<Backup file location &plus; file name>” Example The following command is used to restore database called ”TestDB” with backup file name ”TestDB_Full.bak” which is available in ”D:” location if you are overwriting the existed database. Restore database TestDB from disk = ” D:TestDB_Full.bak” with replace If you are creating a new database with this restore command and there is no similar path of data, log files in target server, then use move option like the following command. Make sure the D:Data path exists as used in the following command for data and log files. RESTORE DATABASE TestDB FROM DISK = ”D: TestDB_Full.bak” WITH MOVE ”TestDB” TO ”D:DataTestDB.mdf”, MOVE ”TestDB_Log” TO ”D:DataTestDB_Log.ldf” Method 2 – SSMS (SQL SERVER Management Studio) Step 1 − Connect to database instance named ”TESTINSTANCE” and right-click on databases folder. Click Restore database as shown in the following snapshot. Step 2 − Select device radio button and click on ellipse to select the backup file as shown in the following snapshot. Step 3 − Click OK and the following screen pops up. Step 4 − Select Files option which is on the top left corner as shown in the following snapshot. Step 5 − Select Options which is on the top left corner and click OK to restore ”TestDB” database as shown in the following snapshot. Print Page Previous Next Advertisements ”;