Microsoft .NET and SQL Server Developer Blog: July 2015

Friday, 31 July 2015

Create a View in MS Sql Server 2008 R2

How to Create and Displaying a View

wel-come again, i come with my Third post, in this post i explain about the how to create a view in Sql Server 2008 R2. in my previous post i explain about how to create database and table and how to open and display table.Create a Database in MS Sql Server 2008 R2 read more about this

let's start with first open Sql Server Management studio and login with your user name and password after login, left pan display to list of database expand the database display the list of folder in this list of folder select the View folder.

View >> Right click >>New view

Display the window "Add Table" in this window list of all the table are there database,you can select one or more table from here look like this...

select table >> click on add button

all the add table display in the query window

you can select the field in the table using check box display right site of every field, check the list of field you want to include in view,it generate three view first is select field you want, second the detail description of all the selected field and the last display the generate select query for selected field.

Complete the query and after save the view using CTRL+S or file >> save

Display the "Chose Name" window to enter the name of the view and the click on OK to create view successfully .

view is display in view folder to display in the left Pan ,view all the list of create view and display the column congaing in the view after complete the step to display the view result in the right pan

SELECT TOP 1000 [ID]

,[Name]

,[Address]

,[Phone]

FROM [EMPDB].[dbo].[View_Name]

it query display the result of top 1000 record on the right pan window

SELECT ID, Name, Address, Phone

FROM View_Name

it query display the result of All record on the right pan window

i complete this post from now, i hope this is my post help for everone who starter or new on the sql server , any query and any type of confusion regarding this post please comments me so i help to learn more about this topic

Thanks

krunal patel

:-) :-)

My Popular post click on Link to view

Thursday, 30 July 2015

Create a Database in MS Sql Server 2008 R2

How to Create New Database

Wel-come to everyone in my second post related to Microsoft Sql Server, in my previous post i describe about the Microsoft sql server and it's different version and list of all version lunch year wise.

let's start froward one step to in this post i describe about how to create new database in sql server 2008 R2 using Microsoft Management Studio for MSSql 2008 R2, also i explain about how to create new table and save table give a primary key for field in table.

Start Sql Server Management Studio

Goto >>start menu >> All Programs >> Sql Server 2008 R2 >> click on Sql Server Management Studio

Open a login screen for Sql server Give Server Type, Server Name, Authentication, Login and Password and then click on Connect button to Login into Sql server management studio user Account.

In left pen see the Instance name who you login with user name Expand the instance and you see the some folder in this list select first folder name "Databases"

Right click on Database Folder >> Select New Database....

open the new database creation window, in this window you can see the field "Database Name" you can give here the name of database you want to create

Example: i give "EMPDB" for my new database name

you can also write a script for query to create new database

select new query >> open query editor window to write script

USE [master]

/****** Object: Database [EMPDB] Script Date: 07/30/2015 14:24:26 ******/

CREATE DATABASE [EMPDB] ON PRIMARY

( NAME = N'EMPDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEVELOPER2\MSSQL\DATA\EMPDB.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

( NAME = N'EMPDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEVELOPER2\MSSQL\DATA\EMPDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

Your database has been created Successful and his display in list of left pan database folder with Database name like "EMPDB" look like this

Create a new Table in Database

Expand the Database and select the "Table" folder

Select Table >> Right click >> New Table....

Open a design view in the Right pan for table design

i create Table_Emp and add three field

Field DataType

Id int

Name Varchar

Address Text

Phone Varchar

also, using script to create a new table script is

USE [EMPDB]

/****** Object: Table [dbo].[Table_Emp] Script Date: 07/30/2015 14:41:41 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[Table_Emp](

[ID] [int] NOT NULL,

[Name] [varchar](50) NULL,

[Address] [text] NULL,

[Phone] [varchar](50) NULL,

CONSTRAINT [PK_Table_Emp] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

SET ANSI_PADDING OFF

Set Primary key on ID field

select ID field >> Right Click >>select Set Primary Key

now save a table with table name "Table_Emp" look on the left pan expand the table and view the columns and key folder to see all the field and primary key in folder

all the set for table and database now you can see the data in table

Right click on table name >> Select Top 1000 Rows...

Display the result of data in Right pan

Also you can display using the query from query window

Syntax

SELECT *|<field1>,<field2> FROM <Table_NAME>

Example

select * from Table_Emp

It sql statement Display all record are there in table

OR

SELECT TOP 1000 [ID]
,[Name]
,[Address]
,[Phone]
FROM [EMPDB].[dbo].[Table_Emp]

It sql statement Display top 1000 record are there in table

now copmplete the my this post i continue with the new post on my next post...i wish to help this post to understand how to develop database and create a table

my post help full for you so please give us comment and if any query regarding this post also comment us....see you again with new post

thanks

krunal patel

My Popular post click on Link to view

Introduction of MS SQL Server

What is MS SQL Server...?

MS Sql Server is a Microsoft Sql server or MSSQL ,it is a Microsoft Database Management product, SQL Server is Microsoft's relational database management system (RDBMS), data in RDBMS is stored in database as a objects called tables, tables also stored the definition and structured of database.

Microsoft Lunch several editions of SQL Server with set of different feature and pricing of user needs, following editions:

Enterprise:

This edition for large enterprises with complicated data requirements, it include all the features of SQL Server, and its license pricing is very High.

Standard:

This edition is use for medium and small organizations, and it is included future of support data warehousing and e-commerce.

Workgroup:

This edition is use For small organizations. It is used as the backend database for small network Web servers or branch offices like VPN.

Express:

This edition is use as a Free license for distribution with limited features, it is use for best replacement of MS Access database.free version Download from Microsoft...... site

Microsoft SQL Server Version and Year Wise List

MS SQL Server 1.0 (16 bit) [1989]

MS SQL Server 1.1 (16 bit) [1991]

MS SQL Server 4.21 [1993]

MS SQL Server 6.0 [1995]

MS SQL Server 6.5 [1996]

MS SQL Server 7.0 [1998]

MS SQL Server 7.0 OLAP Tools [1999]

MS SQL Server 2000 [2000]

MS SQL Server 2000 64 bit Edition [2003]

MS SQL Server 2005 [2005]

MS SQL Server 2008 [2008]

MS Azure SQL Server DB [2010] (For Cloud Database Management)

MS SQL Server 2008 R2 [2010]

MS SQL Server 2012 [2012]

MS SQL Server 2014 [2014]

MS SQL Server 2016[2015]

(Every New MS Sql Server version come with new functionality, options, module and features)

Thanks

Krunal Patel
(Software Developer)

:-) :-)

My Popular post click on Link to view