Schema in DBMS

A schema is a collection of objects (tables, stored procedures, functions, etc) in a database, that are associated to one another. A schema helps to structure the database to group the logically associated objects. These schema are owned by an user, assigned at the time of creating the schema. Schema name and owners can be altered.

Default schema in any database is ‘dbo’ and the default owner is ‘dbo’. When you create a table (say ‘Bloggers’), the table name is dbo.Bloggers, when no shema is mentioned. Here ‘dbo’ is the schema name.

In AdventureWorks database, you find table names as below

     

The first part is the schema name and the second part is the actual table/stored procedure name. This way, the data has been more logically arranged within the database structure, as tables and stored procedures belonging to a particular department are grouped within the same schema like ‘HumanResources’, ‘Person’, ‘Production’ instead of stored just in the database.

User-defined functions must be called as schema_name.function_name(parameters if any)

 Syntax to create schema :

Create Schema schema_name

Example: create schema Bloggers

Syntax to create an object in a schema:

Create object schema_name.object_name (…..)

Example: create table Bloggers.Table1

         ( id int,

           name varchar(50)

         )

Happy SQLing!

Please post your comments (+ve or -ve, even neutral 😅) and questions and feel free share it to with your friends.

Cos sharing is caring..! 😎

Until next blog,

Vino BI Dev

Difference between Stored Procedure and Function

Stored Procedures and Functions are named objects, which are a block of SQL codes grouped together, that can be executed many times without rewriting the queries.

Their differences are discussed below

Stored Procedure	Function
SP may or may not return values	Function always returns a value
SP may return more than one value. It cannot return a table.	Function can return only one value or a table
Supports DML Commands such as insert/update/delete	Supports only SELECT statements
SP can call functions	Functions cannot call SP
Executed as EXEC SP_NAME ‘parameters if any’	Executed as schema_name.function_name(paremeters if any). More on schema here.
Cannot be called from Select statement	Can be called from Select Statements
Exceptions can be handled by try – catch block	Exceptions cannot be handled by try – catch block

Below is an example of function called from a stored procedure :

create procedure [dbo].[Test]

@suburb varchar(255), /*parameters to be passed at the time of calling SP*/

@city varchar(255)

as

SELECT g.Country,g.State,

dbo.crimes(c.year,@city) /*Function Crimes() is being called here and arguments 'year' and 'city' passed*/

as TotalCrime

from Fact_Crime c

inner join Dim_Geography g

on c.DWGeoKey=g.DWGeoKey1

WHERE

g.Suburb=@suburb and g.City=@city

GO

/**************Function dbo.crimes()***************/

create function [dbo].[crimes](@year int,@city varchar(50))

returns decimal(9,0)

as begin 

declare @TotalCrime int

select @TotalCrime = sum([No Of Offences]) from Fact_Crime C

where C.city=@city  and C.year=@year

return @TotalCrime /*value returned to the SP*/ 

end

EXEC dbo.test 'Manurewa','Manukau' --Executing Stored Procedure and passing arguments

This function is an user-defined scalar function, called from select statement in the stored procedure.

Happy SQLing!

Please post your comments  (+ve or -ve, even neutral 😅 ) and questions and feel free share it to with your friends .

Cos sharing is caring..! 😎

Until next blog,

Vino BI Dev

Create - syntax with examples

Create is a powerful DDL command, used for creating database structure.

Create database

The first step in getting started with SQL, is to create a database and then add your tables into it.

Syntax :  create database database_name

Example: create database BloggingDb

Congratulations on creating your first database! 👏🏼

You can find it under ‘Databases’ in left panel in SSMS. Ensure you are in this database, before creating your tables here.

Create tables

To your database, you may start adding your tables.

Syntax: create table table_name

(col_name1 datatype(size if reqd),

col_name2 datatype(size if reqd)

.

)

Example:

create table table1

(

Id int not null,

FirstName varchar(50) not null,

LastName varchar(50) null,

Height float,

Weight float

)

When you do not specify whether the column can  accept null or not, by default SQL Server allows null values.

Happy SQLing!

Please post your comments  (+ve or -ve, even neutral 😅 ) and questions and feel free share it to with your friends .

Cos sharing is caring..! 😎

Until next blog,

Vino BI Dev

Difference between DDL and DML

DDL: Data Definition Language as the name suggest, these are used to define the database schema. These commands do not deal with the data, rather the structure. Some DDL commands are

• CREATE
• ALTER
• DROP
• TRUNCATE
• RENAME

DML: Data Manipulation Language are used to manipulate data. These are used for populating data into and retrieve from database. They use condition clauses like where, if  along with operators ( like arithmetic, comparison, logical etc) and  joins to specify conditions to fetch the required data from the appropriate table, column and row, from the database structure. These commands deal with data directly from insertion, updating and deletion. Some DDL commands are

• SELECT
• INSERT
• UPDATE
• DELETE

Happy SQLing!

Please post your comments  (+ve or -ve, even neutral 😅 )and questions and feel free share it to with your friends . 

Cos sharing is caring..! 😎

Until next blog,

Vino BI Dev

Instance in SQL Server

An instance is a SQL Server service. Each instance has definite set of services with unique settings like Registry Structure, Directory Structure and service names which resonates with the instance name and ID created at the time of Set Up.  Each instance has several system databases and one or more user created databases and runs independently. SQL Server database engine can have multiple instances on the same machine. For instance (no pun intended!) your need is to have only one instance, then its better to have the default. You can configure instance through SQL Server Installation file, where you would have encountered 2 types of instances

·         Default -as the name suggests, this is the default instance which takes only the server name. There can be only one default instance in a server. MSSQLServer  is the default name.

·         Named – You may name your instance as you’d like, pertaining to the below limitation

Ø  Less than 16 char and not case sensitive

Ø  No special characters (underscore '_' is accepted in between but not at the start or end of the name) or keywords or the term ‘Default’

Ø  Server_Name\Instance_Name (Eg: .\TEST ) is used in SSMS to connect to this instance of the database engine.

Few reasons for creating multiple instances for database engine are

•          If your databases have more than one language, you may create instances for each language
•          If the compatibility of your databases demands specific version, you may create instances             accordingly
•          Create instances for different users based on their roles and requirement to view databases 
•      To  configure a different server mode for Analysis Services

You can find the instances in your local machine, by typing SQLServerManager13.msc for [SQL Server 2016] in Search and hit enter. Below image shows the default instance configured in my laptop.

Hope we have better understanding of instance now!🙋

Happy SQLing!

Please post your comments (+ve or -ve, even neutral 😅 ) and questions and feel free share it to with your friends .

Cos sharing is caring..! 😎

Until next blog,

Vino BI Dev

Installing SQL Server and SSMS in your laptop

Well, turn on the music♮♮♮, maybe grab your cuppa ⛾and lets get started!

Step 1: Start with downloading free SQL Server from internet.

Go to  https://visualstudio.microsoft.com/dev-essentials/

 

Step 2: Join or Sign in using your Microsoft Account ( If you don't have one, you may sign up for free!) 

Step 3: Click 'Downloads'

 

Step 4: Type 'SQL Server 2016 Developer'.

 

Step 5: Once downloaded, open the Setup file, and the below window pops up.

Click ‘Installation ’.

 

Step 6: Click 'New SQL Server stand-alone installation or add features to an existing installation'. It takes through set up wizards to aid with the installation, and is quiet helpful while setting up for the very first time.

Step 7: Click Free edition and choose 'Developer'

 

Step 8: Accept Terms and Conditions

 

Step 9: Wait for 'Global Rules check in progress' to be completed, following the below dialog box.

 

Step 10:

Step 11:

 

Step 12: Choose the features. I have selected some commonly used features for my learning- Database Engine Services, Analysis Services, Reporting Services, Client Tools Connectivity and Master Data Services. 

                                                                                                                                                                      

                                                                                                                       

 

Step 13: Choose 'Default Instance' if configuring for the first time. Otherwise select 'Named instance', and give an instance name. Click Next. Find more on instance here.

 

Step 14: Choose appropriate Server Mode for your Analysis Services, depending upon the feature requirements. I have chosen Multidimensional and Data Mining,compatible with my projects. Add current user. Click Next. 

 

Step 15: Click Install

This would have set up SQL Server 2016, successfully. Next is to set up Sql Server Management Services, which is the UI to access SQL Server.

Setting up SSMS

Step 1: Open SQL Server Installation Center and now click ‘Install SQL Server Management Tools’

Step 2: This would take you to download SSMS - https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?redirectedfrom=MSDN&view=sql-server-ver15

 

Step 3: Run the SSMS-ENU Setup file and now SQL Server 2016 can be access using SSMS in your local computer

Congratulations and cheers to your patience 🙌! You're all set now!!

You may now access SQL Server through SSMS, by start -> SSMS and log in with your credentials. Be aware of the instance name, if you have given one in Step 13.

Happy SQLing!

Please post your comments (+ve or -ve, even neutral 😅 ) and questions and feel free share it to with your friends .

Cos sharing is caring..! 😎

Until next blog,

Vino BI Dev