SQL Server Ebook - Guru99

2y ago
60 Views
9 Downloads
503.39 KB
22 Pages
Last View : 2d ago
Last Download : 2m ago
Upload by : Mika Lloyd
Transcription

Learn SQL Server in 1 DayBy Krishna RungtaCopyright 2019 - All Rights Reserved – Krishna RungtaALL RIGHTS RESERVED. No part of this publication may be reproduced ortransmitted in any form whatsoever, electronic, or mechanical, includingphotocopying, recording, or by any informational storage or retrievalsystem without express written, dated and signed permission from theauthor.

Table Of ContentChapter 1: What is SQL Server? Introduction, History, Editions, Instances1.2.3.4.5.6.7.What is SQL Server?History SQL ServerSQL Server EditionsMS SQL Server as Client-Server ArchitectureKey Components and Services of SQL ServerSQL Server InstancesImportance of SQL Server InstancesChapter 2: How to Download and Install SQL Server1. How to download SQL Server Setup2. How to Install SQL ServerChapter 3: SQL Server Architecture Explained: Named Pipes, Optimizer,Buffer Manager1. Protocol Layer - SNI2. Relational Engine3. Storage EngineChapter 4: SQL Server Management Studio (SSMS): What is, Install, Versions1. Download and Install SQL Server Management Studio

2.3.4.5.6.How to access ""Management Studio.""Access ""Management studio"" using Command line.Introduction to Data Management Studio IDESSMS Tips and IssuesSSMS Versions and UpdatesChapter 5: SQL Server Database: Create, Alter, Drop, Restore1.2.3.4.5.6.7.8.9.10.Rules to Create a DatabaseCreate Database using SQL Server Management StudioCreate Database with T-SQLHow to Alter DatabaseAlter Database with SQL Server Management StudioAlter Database with Transact-SQLDelete DatabaseDelete Database SQL Server Management StudioDelete Database using Transact-SQLRestore DatabaseChapter 6: SQL Server DataTypes: Varchar, Numeric, Date Time [T-SQLExamples]1. What is Datatype?2. Why use DataTypes?3. Data type available in MS SQLChapter 7: SQL Server Variable: Declare, Set, Select, Global,Local [TSQLExamples]1. What is Variable?

2. Types of Variable: Local, Global3. How to DECLARE a variable4. Assigning a value to a VARIABLEChapter 8: SQL Server Table: CREATE, ALTER, DROP [T- SQL Examples]1.2.3.4.What is a Table?How to Create a TableAlter TableDelete TableChapter 9: SQL Server PRIMARY KEY: T-SQL Examples1. What is a Primary Key?2. How to Create Primary KeyChapter 10: SQL Server FOREIGN KEY: T-SQL Examples1. What is a Foreign Key?2. How to Create Foreign KeyChapter 11: SQL Server IF ELSE Statement: T-SQL Example1. IF Else statement2. IF statement with No Else3. Nested IF Else StatementsChapter 12: CASE statement in SQL Server: T-SQL Example1. Overview of Case in real life!

2.3.4.5.6.7.8.9.What is CASE?Simple CASESearched CASEDifference between Simple and searched caseNested CASE: CASE in IF ELSENested CASE: CASE inside CASECASE with UPDATECASE with Order byChapter 13: SQL Server SUBSTRING() Function: T-SQL ExampleChapter 14: SQL SERVER JOINS Tutorial: INNER, LEFT, RIGHT, OUTERChapter 15: Create Login, User, assign Permission: SQL Server Tutorial1. How to Create a Login2. How to create a User3. Assigning Permission to a UserChapter 16: Oracle Vs. SQL Server: Key Differences1.2.3.4.5.6.7.What is Microsoft SQL server?What is Oracle Database?Early History of Microsoft SQL:Early History of Oracle:Features of Microsoft SQL ServerFeatures of OracleDifference between SQL Server and Oracle

Chapter 17: SSIS Tutorial for Beginners: What is, Architecture, Best Practices1.2.3.4.5.6.7.8.9.10.What Is SSIS?Why we use SSIS?History of SISSSIS Salient FeaturesSSIS ArchitectureSSIS Tasks TypesOther Important ETL toolsAdvantages and Disadvantages of using SSISDisadvantages of SSISSSIS Best Practices

Chapter 1: What is SQL Server?Introduction, History, Editions,InstancesWhat is SQL Server?SQL Server is a relational database management system (RDBMS) developedby Microsoft. It is primarily designed and developed to compete with MySQLand Oracle database.SQL Server supports ANSI SQL, which is the standard SQL (Structured QueryLanguage) language. However, SQL Server comes with its ownimplementation of the SQL language, T-SQL (Transact- SQL).T-SQL is a Microsoft propriety Language known as Transact-SQL. It providesfurther capabilities of declaring variable, exception handling, storedprocedure, etc.SQL Server Management Studio (SSMS) is the main interface tool for SQLServer, and it supports both 32-bit and 64-bit environments.History SQL ServerMicrosoft and Sybase released version 1.0 in 1989. However, the partnershipbetween these two ended in the early 1990s. Microsoft maintainedownership rights to the name SQL Server. Since the 1990s,

subsequent versions of SQL Server have been released including SQL Server2000, 2005, 2008, 2012, 2014, 2016 and 2017.SQL Server EditionsFollowing editions are availableSQL Server Enterprise: It is used in the high end, large scale and missionCritical business. It provides High-end security, Advanced Analytics, MachineLearning, etc.SQL Server Standard: It is suitable for Mid-Tier Application and Data marts. Itincludes basic reporting and analytics.SQL Server WEB: It is designed for a low total-cost-of-ownership option forWeb hosters. It provides scalability, affordability, and manageabilitycapabilities for small to large scale Web properties.SQL Server Developer: It is similar to an enterprise edition for the nonproduction environment. It is mainly used for build, test, and demo.SQL Server Express: It is for small scale applications and free to use.MS SQL Server as Client-Server ArchitectureLet's have a look at the below early morning conversation between Momand her Son, Tom.

Brainstorm:Ask your brain .!!! "Can you map, who is CLIENT and who the SERVER is?"The most certain reply would be - "I am pretty smart in that and . Son is aCLIENT as he is requesting for a cup of coffee and Mother, who is CAPABLE ofpreparing coffee, is a SERVER."Here, Tom is requesting his mother, a cup of coffee. Finally, mom does someprocessing with Milk, coffee, sugar and prepare coffee to serve it hot.Analogy: MS SQL SERVER architecture.A CLIENT is an application that sends requests to the MS SQL SERVERinstalled on a given machine. The SERVER is capable of processing inputdata as requested. Finally, respond with PROCESSED OUTPUT DATA as aresult.

Key Components and Services of SQL ServerDatabase Engine: This component handle storage, Rapid transactionProcessing, and Securing Data.SQL Server: This service starts, stops, pauses, and continues an instance ofMicrosoft SQL Server. Executable name is sqlservr.exe.SQL Server Agent: It performs the role of Task Scheduler. It can be triggeredby any event or as per demand. Executable name is sqlagent.exe.SQL Server Browser: This listens to the incoming request and connects to thedesired SQL server instance. Executable name is sqlbrowser.exe.SQL Server Full-Text Search: This lets user running full-text queriesagainst Character data in SQL Tables. Executable name isfdlauncher.exe.SQL Server VSS Writer: This allows backup and restoration of data files whenthe SQL server is not running. Executable name is sqlwriter.exe.SQL Server Analysis Services (SSAS): Provide Data analysis, Data mining andMachine Learning capabilities. SQL server is integrated with R and Pythonlanguage for advanced analytics.Executable name is msmdsrv.exe.SQL Server Reporting Services (SSRS): Provides reporting features anddecision-making capabilities. It includes integration with Hadoop. Executablename is ReportingServicesService.exe

SQL Server Integration Services (SSIS): Provided Extract- Transform and Loadcapabilities of the different type of data from one source to another. It can beview as converting raw information into useful information. Executable nameis MsDtsSrvr.exeSQL Server InstancesSQL Server allows you to run multiple services at a go, with each servicehaving separate logins, ports, databases, etc. These are divided into two:Primary instancesNamed instances.There are two ways through which we may access the primary instance.First, we can use the server name. Secondly, we can use its IP address.Named instances are accessed by appending a backslash and instance name.For example, to connect to an instance named xyx on the local server, youshould use 127.0.0.1\xyz. From SQL Server 2005 and above, you are allowedto run up to 50 instances simultaneously on a server.Note that even though you can have multiple instances on the same server,only one of them must be the default instance while the rest must benamed instances. One can run all the instances concurrently, and eachinstance runs independent of the other instances.Importance of SQL Server InstancesThe following are the advantages of SQL Server instances:

1. For installation of different versions on one machineYou can have different versions of SQL Server on a single machine. Eachinstallation works independently from the other installations.2. For cost reductionInstances can help us reduce the costs of operating SQL Server, especially inpurchasing the SQL Server license. You can get different services fromdifferent instances, hence no need for purchasing one license for all services.3. For maintenance of development, production and testenvironments separatelyThis is the main benefit of having many SQL Server instances on a singlemachine. You can use different instances for development, production andtest purposes.4. For reducing temporary database problemsWhen you have all services running on a single SQL Server instance, thereare high chances of having problems with the problems, especiallyproblems that keep on recurring. When such services are run on differentinstances, you can avoid having such problems.5. For separating security privilegesWhen different services are running on different SQL Server instances, youcan focus on securing the instance running the most sensitive service.6. For maintaining a standby server

A SQL Server instance can fail, leading to an outage of services. This explainsthe importance of having a standby server to be brought in if the currentserver fails. This can easily be achieved using SQL Server instances.Summary:SQL Server is defined as a relational database management system(RDBMS) developed by MicrosoftT-SQL means Transact-SQL, a propriety Language by Microsoft Microsoftand Sybase released version 1.0 in 1989Various Editions of SQL Server are Enterprise, Standard, Web, Developer,and ExpressCritical components of SQL Server are Database Engine, SQL Server, SQLServer Agent, SQL Server Browser, SQL Server Full- Text Search, etc.You can run multiple instances of SQL Server the same on the samemachine.

Chapter 2: How to Download andInstall SQL ServerPre-RequisitesPrincipally, MS SQL server requires:.Net Framework,1GB of recommended memory, and NTFS system.How to download SQL Server SetupStep 1) Go to URL: https://www.microsoft.com/en-in/sql-server/sql- serverdownloadsMicrosoft provides two specialized free editions to work on MS SQL server:1. Developer – It has all feature which MS SQL server offers but wecannot use it in production. From the learning perspective, is it anideal candidate to start.2. Express: This is also a free version but with the limited set offeatures with no business intelligence applications.We will select the Developer edition for installation.

Step 2) Click on "Download now"We will get set up as 'SQLServer2017-SSEI-Dev.exe'.How to Install SQL ServerStep 1) Double click on "SQLServer2017-SSEI-Dev.exe". Below screen willappear with three options: Basic, Custom and Download files.

Step 2) Choose the basic version by clicking on the 'Basic' option, as it hasall default configuration required to learn MS SQL.

Step 3) 'Microsoft Server License Terms' screen will appear. Read theLicense Terms and then click 'Accept.'

Step 4) Below 'SQL server install location' window will appear.1. The Default location is C:\Program Files\Microsoft SQL Server.2. Optionally, we can also change the installation location by clicking onBrowse.3. Once the location is selected, click the 'Install' button.

Below 'Downloading install package' progress screen will be displayed. Waituntil the download is complete.Once, the download is complete; the system will initiate installing developeredition.

Below screen show installation progress.Step 5) Once installation is completed successfully, below screen will appear.

This setup is self-sufficient for proceeding further with learning SQL server,and we can 'Close' this window.However, below is a summary of the label and button:1. Instance name: This is by default labeled as MSSQLSERVER.2. Connect now: This will open a separate command line window forconnection testing of what we have just installed.The system will run by default 'select @@Version' statement to confirmthat we can connect to new MSSQLSERVER instance successfully.

3. Customize: This will open the SQL Installation center to customizefurther and add feature other than which are there as a part of theBASIC installation.4. Install SSMS: This is IDE which will take us to Microsoft SSMSdownload link. We will cover SSMS in detail in our SSMStutorial.5. Close: This will close this window. The user is now ready to installSSMS IDE as instructed in SSMS tutorial.

SQL Server supports ANSI SQL, which is the standard SQL (Structured Query Language) language. However, SQL Server comes with its own implementation of the SQL language, T-SQL (Transact- SQL). T-SQL is a Microsoft propriety Language known as Transact-SQL. It provides further capab

Related Documents:

Server 2005 , SQL Server 2008 , SQL Server 2008 R2 , SQL Server 2012 , SQL Server 2014 , SQL Server 2005 Express Edition , SQL Server 2008 Express SQL Server 2008 R2 Express , SQL Server 2012 Express , SQL Server 2014 Express .NET Framework 4.0, .NET Framework 2.0,

MS SQL Server: MS SQL Server 2017, MS SQL Server 2016, MS SQL Server 2014, MS SQL Server 2012, MS SQL Server 2008 R2, 2008, 2008 (64 bit), 2008 Express, MS SQL Server 2005, 2005 (64 bit), 2005 Express, MS SQL Server 2000, 2000 (64 bit), 7.0 and mixed formats. To install the software, follow the steps: 1. Double-click Stellar Repair for MS SQL.exe.

SQL Server 2005 SQL Server 2008 (New for V3.01) SQL Server 2008 R2 (New for V3.60) SQL Server 2012 (New for V3.80) SQL Server 2012 R2 (New for V3.95) SQL Server 2014 (New for V3.97) SQL Server 2016 (New for V3.98) SQL Server 2017 (New for V3.99) (Recommend Latest Service Pack) Note: SQL Server Express is supported for most situations. Contact .

4395 querying data with transact -sql (m20761) microsoft sql server 6552 querying microsoft sql server 2014 (m20461) microsoft sql server 1833 sql server performance tuning and optimization (m55144) microsoft sql server 4394 updating your skills to sql server 2016 (m10986) microsoft sql server

install the SQL Server Reporting Services 2019 or 2017 installation program. For more information, see Installing SQL Server Reporting Services for SQL Server 2019 and SQL Server 2017 To use the SQL Installer to install a full version of SQL Server software (for example, SQL Server 2019 Standard editio

4. To upgrade SQL Server 2008 SP3 Express to SQL Server 2012 SP2 Express, launch th e installer for SQL Server 2012 SP2 Express, and then follow the wizard. 5. Select the Upgrade from SQL Server 2005, SQL Server 2008 or SQL Server 2008R2 option under the Installation option. The Upgrade to SQL

Bitlocker Administration and Monitoring MBAM Anforderungen: Hardware: 4 CPUs 12 GB RAM 100 GB Disk OS: Windows Server 2008 R2 SP1 Windows Server 2012/R2 Windows Server 2016 SQL Server: SQL Server 2008 R2 SQL Server 2012 SP1 SQL Server 2012 SP2 SQL Server 2014 SQL Server 2014 SP1

The success of the American Revolution inspired subsequent revolutions in both the Old and New Worlds. The French Revolution of 1789 was rooted in complex political, social, and economic causes. Politically, the king was an absolute monarch with unlimited powers to levy taxes, conduct foreign affairs, and make and enforce any law he deemed necessary. Socially, the French people were divided .