다음을 통해 공유

SQL Server Tutorial - Create Tables and Views

  • 아티클

If you need to store data, perform analytics, make your data highly available then SQL Server is the thing for you! SQL Server has many components. These components are categorized on the basis of the functional ability they give to the users. Some of the components are : 

1. Database Engine : this component gives the basic abilities like storing data and running queries etc..

2. Analysis Services : helps in data analysis and mining.

3. Reporting Services : helps in creating, managing and deploying different kind of reports.

4. Integration Services : contains easy-to-use tools for transforming data

5. Master Data Service : helps in managing data.

To know more about these components go to this link : https://msdn.microsoft.com/en-us/library/ms144275.aspx

Now we know what SQL Server is made up of, lets see how it is distributed in the market. SQL Server comes in 6 different editions. These editions differ from each other from features available to them standpoint.  The 6 editions available in market today are :

1. Enterprise Edition

2. Business Intelligence Edition

3. Standard Edition  

4. Express Edition

5. Web Edition

6. Developer Edition

Check out what features are available in each of them and what your application requires. This link will help you make an appropriate decision about which SQL Server to buy/download based on what is your application requirement. Link : https://msdn.microsoft.com/en-us/library/cc645993.aspx

Lets begin with our hands-on tutorial. We will cover the basics - creation of table, creations of view.

Create a Table

STEP 1 : Download SQL Server. I am working on Enterprise edition. We will be working with Database Engine component and management studio. These two components are a part of download. Once you have downloaded SQL Server, open Management Studio. you will see some thing like below. This small pop-up "Connect to Server" allows you to declare the authentication type and server name you want to connect to. For me, the Server name is DXDEMO (which is my machine name) and the instance which I am connecting to is SQL2012. Every installation creates an instance. Instances are created to differentiate between different version/editions of SQL server on the same machine. I have chosen SQL Server authentication for this tutorial. So I put my username and PW and click "connect".

STEP 2 : Once you are connected, you will see the dashboard like this (picture below). On the "Object Explorer" panel in left, you can see all the components of SQL Server available for this edition. To create a table, we will first create a Database. Right click on "Database", click on the first option "New Database". There in the pop up write the name of the database. I have picked "TestDB" as the name of my database for this tutorial.

STEP 3 : To create tables under this database, expand your database and right click on table, click on "New Table". Define the columns and it's data types. I am going with testId and testName as my columns. Once you have defined the columns, click on save button (top menu bar). After saving, right click on "Tables" and there you can see your table listed.  

STEP 4 : Now we will insert some data into our table. For this, right click on the table name and then hover your cursor over "Script Table as", then hover over "Insert to", finally click on "New Query Editor Window". Here insert query is already written for you, just place the variables and hit "! Execute" which is in the top menu bar next to (left) Debug. This executes the Query and one row is added to the table. To see the added row, again right click on the table name, and click on "Select Top 1000 Rows". Now keep on adding new rows in the table for all your records.

Create a view

Views are virtual tables that we create from our actual master table.  For example, if the actual table had  "a" rows and "b" columns you can create many views, each with a-1, a-2.......a-n rows and b-1, b-2...b-n columns. Basic thing that comes to our mind when we think about views is, why do we create views? are views important? the answer is yes, views are important because imagine this, you have one huge master data table with rows as order id, order type, order name, quantity, quantity sold in 1990, quantity sold in 2000...preorder, order discount offered, order quantity available, etc.. this table has millions of rows. What if you want to find out about a particular order type and it's sales for a particular year? You can easily do so by creating views of this master table. Each view containing certain rows and columns -  a subset of rows and columns in the master table. If you change any value in the master table the same changes are reflected in the view also. Follow the below tutorial to learn how to create views in SQL Server.  

STEP 1 : Open the SQL Server. Expand "Database" where your table exist. Right click on "view". Click on "New View". Now you can see the list of tables available. Select and say "Add" for the table for which you are creating the view.

STEP 2 :  Once you do that you can see the columns for that table. Select the columns which you want to include in the view and click "save" from top app bar. When you click on save a "Choose Name" prompt will appear, choose a name for your view and click ok. "Refresh" the "view" in the "Object Explorer" to see your newly created view.

That’s all few minutes and you have tables and views in places. Comment and let me know your experience with working on SQL Server.

Comments