Skip to content

Latest commit

 

History

History
88 lines (65 loc) · 3.89 KB

how-to-create-new-database-objects-using-queries.md

File metadata and controls

88 lines (65 loc) · 3.89 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic
Create New Database Objects Using Queries
Get acquainted with the Transact-SQL Editor. See how to open this editor, and view examples that show how to use it to create a new table, function, or view.
markingmyname
maghan
02/09/2017
sql
ssdt
conceptual

How to: Create New Database Objects Using Queries

If you prefer to use scripts to create or edit views, stored procedures, functions, triggers, or user-defined-types, you can use the Transact-SQL Editor. The Transact-SQL Editor provides IntelliSense and other language support. For more information, see Use Transact-SQL Editor to Edit and Execute Scripts.

The Transact-SQL Editor is invoked when you use the View Code contextual menu to open a database entity in a connected database or a project. It is also automatically opened when you use the New Query contextual menu from the SQL Server Object Explorer, or add a new script object to a database project. If you are not connected to a database but want to execute a query against it, you can also use the New Query Connection dialog box by selecting Transact-SQL Editor menu from the SQL menu to connect to a database and launch the Transact-SQL Editor.

Warning

The following procedures use entities created in previous procedures in the Connected Database Development section.

To create a new table using a Transact-SQL query

  1. Right-click the Trade database node and select New Query.

  2. In the script pane, paste in this code:

    
CREATE TABLE [dbo].[Fruits] (  
    [Id]         INT NOT NULL,  
    [Perishable] BIT DEFAULT ((1)) NULL,  
    PRIMARY KEY CLUSTERED ([Id] ASC),  
    FOREIGN KEY ([Id]) REFERENCES [dbo].[Products] ([Id])   
);

  3. Click the Execute Query button in the Transact-SQL Editor toolbar to run this query.

  4. Right-click the Trade database in SQL Server Object Explorer and select Refresh. Notice that new Fruits table has been added to the database.

To create a new function

  1. Replace the code in the current Transact-SQL Editor with the following:

    
CREATE FUNCTION [dbo].GetProductsBySupplier  
(  
@SupplierId int  
)  
RETURNS @returntable TABLE   
(  
[Id] int NOT NULL,   
[Name] NVARCHAR (128) NOT NULL,  
[Shelflife] INT NOT NULL,  
[SupplierId] INT NOT NULL,  
[CustomerId] INT NOT NULL  
)  
AS  
BEGIN  
INSERT @returntable  
SELECT *  from Products p  
where p.SupplierId = @SupplierId  
RETURN   
END

    This function will return all rows in the Products table whose SupplierId equals to the specified parameter. Click the Execute Query button in the Transact-SQL Editor toolbar to run this query.

  2. In SQL Server Object Explorer, under the Trade node, expand the Programmability and Functions nodes. You can find the new function you just created under Table-valued Functions.

To create a new view

  1. Replace the code in the current Transact-SQL Editor with the following. Then click the Execute Query button above the editor to run this query.

    CREATE VIEW [dbo].PerishableFruits   
AS SELECT p.Id, p.Name FROM dbo.Products p  
join dbo.Fruits f on f.Id = p.Id  
where f.Perishable = 1

  2. In SQL Server Object Explorer, under the Trade node, expand the View node to locate the new view you just created.

See Also

Manage Tables, Relationships, and Fix Errors
Use Transact-SQL Editor to Edit and Execute Scripts