Category: ms Sql Server

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 – Assign Permissions ”; Previous Next Permissions refer to the rules governing the levels of access that principals have to securables. You can grant, revoke and deny permissions in MS SQL Server. To assign permissions either of the following two methods can be used. Method 1 – Using T-SQL Syntax Use <database name> Grant <permission name> on <object name> to <usernameprinciple> Example To assign select permission to a user called ”TestUser” on object called ”TestTable” in ”TestDB” database, run the following query. USE TestDB GO Grant select on TestTable to TestUser Method 2 – Using SSMS (SQL Server Management Studio) Step 1 − Connect to instance and expand folders as shown in the following snapshot. Step 2 − Right-click on TestUser and click Properties. The following screen appears. Step 3 Click Search and select specific options. Click Object types, select tables and click browse. Select ”TestTable” and click OK. The following screen appears. Step 4 Select checkbox for Grant column under Select permission and click OK as shown in the above snapshot. Step 5 Select permission on ”TestTable” of TestDB database granted to ”TestUser”. Click OK. Print Page Previous Next Advertisements ”;

MS SQL Server – Integration Services ”; Previous Next This service is used to carry out ETL (Extraction, Transform and Load data) and admin operations. The BIDS (Business Intelligence Studio till 2008 R2) and SSDT (SQL Server Data Tools from 2012) are the environments to develop packages. SSIS Basic Architecture Solution (Collection of projects) —> Project (Collection of packages) —> Package (Collection of tasks for ETL and admin operations) Under Package, the following components are available − Control Flow (Containers and Tasks) Data Flow (Source, Transformations, Destinations) Event Handler (Sending of messages, Emails) Package Explorer (A single view for all in package) Parameters (User interaction) Following are the steps to open BIDSSSDT. Step 1 − Open either BIDSSSDT based on the version from the Microsoft SQL Server programs group. The following screen appears. Step 2 − The above screen shows SSDT has opened. Go to file at the top left corner in the above image and click New. Select project and the following screen opens. Step 3 − Select Integration Services under Business Intelligence on the top left corner in the above screen to get the following screen. Step 4 − In the above screen, select either Integration Services Project or Integration Services Import Project Wizard based on your requirement to developcreate the package. Print Page Previous Next Advertisements ”;

MS SQL Server – Create Database ”; Previous Next Database is a collection of objects such as table, view, stored procedure, function, trigger, etc. In MS SQL Server, two types of databases are available. System databases User Databases System Databases System databases are created automatically when we install MS SQL Server. Following is a list of system databases − Master Model MSDB Tempdb Resource (Introduced in 2005 version) Distribution (It’s for Replication feature only) User Databases User databases are created by users (Administrators, developers, and testers who have access to create databases). Following methods are used to create user database. Method 1 – Using T-SQL Script or Restore Database Following is the basic syntax for creating database in MS SQL Server. Create database <yourdatabasename> OR Restore Database <Your database name> from disk = ”<Backup file location &plus; file name> Example To create database called ‘Testdb’, run the following query. Create database Testdb OR Restore database Testdb from disk = ”D:BackupTestdb_full_backup.bak” Note − D:backup is location of backup file and Testdb_full_backup.bak is the backup file name Method 2 – Using SQL Server Management Studio Connect to SQL Server instance and right-click on the databases folder. Click on new database and the following screen will appear. Enter the database name field with your database name (example: to create database with the name ‘Testdb’) and click OK. Testdb database will be created as shown in the following snapshot. Print Page Previous Next Advertisements ”;

MS SQL Server – Monitor Database ”; Previous Next Monitoring refers to checking database status, settings which can be the owner’s name, file names, file sizes, backup schedules, etc. SQL Server databases can be monitored mainly through SQL Server Management Studio or T-SQL, and also can be monitored through various methods like creating agent jobs and configuring database mail, third party tools, etc. Database status can be checked whether it is online or in any other state as shown in the following snapshot. As per the above screen, all databases are in ”Online” status. If any database is in any other state, then that state will be shown as shown in the following snapshot. Print Page Previous Next Advertisements ”;

MS SQL Server – Management Studio ”; Previous Next SQL Server Management Studio is a workstation componentclient tool that will be installed if we select workstation component in installation steps. This allows you to connect to and manage your SQL Server from a graphical interface instead of having to use the command line. In order to connect to a remote instance of an SQL Server, you will need this or similar software. It is used by Administrators, Developers, Testers, etc. The following methods are used to open SQL Server Management Studio. First Method Start → All Programs → MS SQL Server 2012 → SQL Server Management Studio Second Method Go to Run and type SQLWB (For 2005 Version) SSMS (For 2008 and Later Versions). Then click Enter. SQL Server Management Studio will be open up as shown in the following snapshot in either of the above method. Print Page Previous Next Advertisements ”;

MS SQL Server – Creating Backups ”; Previous Next Backup is a copy of data/database, etc. Backing up MS SQL Server database is essential for protecting data. MS SQL Server backups are mainly three types − Full or Database, Differential or Incremental, and Transactional Log or Log. Backup database can be done using either of the following two methods. Method 1 – Using T-SQL Full Type Backup database <Your database name> to disk = ”<Backup file location + file name>” Differential Type Backup database <Your database name> to disk = ”<Backup file location + file name>” with differential Log Type Backup log <Your database name> to disk = ”<Backup file location + file name>” Example The following command is used for full backup database called ”TestDB” to the location ”D:” with backup file name ”TestDB_Full.bak” Backup database TestDB to disk = ”D:TestDB_Full.bak” The following command is used for differential backup database called ”TestDB” to the location ”D:” with backup file name ”TestDB_diff.bak” Backup database TestDB to disk = ”D:TestDB_diff.bak” with differential The following command is used for Log backup database called ”TestDB” to the location ”D:” with backup file name ”TestDB_log.trn” Backup log TestDB to disk = ”D:TestDB_log.trn” Method 2 – Using SSMS (SQL SERVER Management Studio) Step 1 − Connect to database instance named ”TESTINSTANCE” and expand databases folder as shown in the following snapshot. Step 2 − Right-click on ”TestDB” database and select tasks. Click Backup and the following screen will appear. Step 3 − Select backup type (Fulldifflog) and make sure to check destination path which is where the backup file will be created. Select options at the top left corner to see the following screen. Step 4 − Click OK to create ”TestDB” database full backup as shown in the following snapshot. Print Page Previous Next Advertisements ”;

MS SQL Server – Installation ”; Previous Next 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 check the product key selection and click Next. Step 10 − Select the checkbox to accept the license option and click Next. Step 11 − Select SQL Server feature installation option and click Next. Step 12 − Select Database engine services checkbox and click Next. Step 13 − Enter the named instance (here I used TestInstance) and click Next. Step 14 − Click Next on the above screen and the following screen appears. Step 15 − Select service account names and start-up types for the above listed services and click Collation. Step 16 − Make sure the correct collation selection is checked and click Next. Step 17 − Make sure authentication mode selection and administrators are checked and click Data Directories. Step 18 − Make sure to select the above directory locations and click Next. The following screen appears. Step 19 − Click Next on the above screen. Step 20 − Click Next on the above screen to the get the following screen. Step 21 − Make sure to check the above selection correctly and click Install. Installation is successful as shown in the above screen. Click Close to finish. Print Page Previous Next Advertisements ”;