MS SQL Notes

Notes about MS SQL.

Select All Product Names Beginning with A

"SELECT * FROM products WHERE prod_name LIKE 'A%'"

Use SQL Developer Edition

SQL Server Developer is available as a full-featured free edition for development and testing.

SQL Server Management Studio (SSMS)

SQL Server Management Studio (SSMS) - Use SQL Server Management Studio (SSMS) to query, design, and manage your databases and data warehouses, wherever they are - on your local computer, or in the cloud. SMS is free! Download

https://docs.microsoft.com/en-us/sql/ssms/tutorials/connect-query-sql-server?view=sql-server-2017

Open SSMS (Microsoft SQL Server Tools / Microsoft SQL Server Management Studio 17)

The first time you run SSMS, the Connect to Server window opens. If it doesn't open, you can open it manually by selecting Object Explorer > Connect > Database Engine.

 Object Explorer > Connect > Database Engine

The format of the name is HOSTNAME\INSTANCENAME. If you see only the host name, then you've installed the default instance and your instance name is MSSQLSERVER. When you connect to a default instance, the host name is all you need to enter to connect to your SQL server.

Use Windows Authentication.

How to Connect to MS SQL Using C#

https://www.guru99.com/c-sharp-access-database.html

Understanding "login failed" (Error 18456) error messages in SQL Server 2005:

https://blogs.msdn.microsoft.com/sql_protocols/2006/02/21/understanding-login-failed-error-18456-error-messages-in-sql-server-2005/

Walkthroughs for Connecting Windows Forms Applications to Data

https://docs.microsoft.com/en-us/visualstudio/data-tools/connecting-to-data-in-windows-forms-applications?view=vs-2015

In Visual Studio / View / SQL Server Object Explorer

https://stackoverflow.com/questions/12220865/connecting-to-local-sql-server-database-using-c-sharp

YouTube Video Tutorial on How to Connect to MS SQL Server Using C# in Visual Studio 2017

C# Tutorial 25 C# Databases:

https://www.youtube.com/watch?v=OdDkFPO_nto

In Visual Studio: View / Server Explorer

Connection string can be put in App.config file in the appSettings tag.

<appSettings>

<add key="provider" value="System.Data.SqlClient" />

<add key="connectionString1" value=""/>

</appSettings>

To Get Connection String Using Visual Studio

In VisualStudio 2015:

Server Explorer / Data Connections

Data Source=localhost;Integrated Security=True

Libraries That Are Important

using System.Data.Common;
using System.Configuration;

SQL Server Connection Pooling

Connection pooling is done to improve performance. See:

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling?view=netframework-4.8

C# Database Programming for Beginners Part 1

Part 2 Querying the Database with SQL

Part 3 Database Connection and Populating List Box

Add New Data Source

Visual Studio / Data Sources tab / Data Sources window / Add New Data Source

Add Reference

Project / Add Reference / to System.Configuration

Add a Database to a Project

View / Solution Explorer / Right Click on Project / Add / New Item / Service-Based Database

This will then appear as a .mdf file in the Solution Explorer and also in the Server Explorer.

In Server, right click on Tables and go to Add New Table.

Types of SQL Files

SQL Server databases use two files - an MDF file, known as the primary database file, which contains the schema and data, and a LDF file, which contains the logs. See wikipedia. A database may also use secondary database file, which normally uses a .ndf extension.

Set Field as Primary Key

Click on Field Name / Properties Window / Table Designer / Identity Specification / (Is Identity) / Set to True

This means it will increment the value by 1 each time you insert a new record.

Field Types

nvarchar(MAX) - Like a free form text field.

Create Table

In Server Explorer / Tables / Add Table / Then Add Your Fields / Hit the Update button.

Associative Table

It's function is to relate other tables together.

Foreign Key

You can right click on Foreign Key in Table Design window to add new Foreign Key. Visual Basic will show the T-SQL for this.

Schemas and Object Names

Schemas are namespaces for database objects.

Fully-qualified name:

[server_name.][database_name.][schema_name.]object_name

When referring to a table, best practice is to include schema name:

schema_name.object_name

Introduction to T-SQL

T-SQL to Create a Database

CREATE DATABASE [YourDatabaseName]

T-SQL to Create a New Table

USE [YourDatabaseName]
GO
CREATE TABLE [YourTableName]
{
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
...
}

T-SQL to Retrieve all the Data from a Table

USE [YourDatabaseName]
GO
SELECT * FROM [YourTableName]

Transact-SQL Data Types

Types of SQL Joins

SQL Queries

Ultimate List of 40 Important SQL Queries from ByteScout

Microsoft SQL Resources

SQL Server Technical Documentation from Microsoft

Azure Data Studio
A rich SQL editor offering modern editor features and cross-platform compatibility for Windows, MacOS and Linux.

Visit LowPrices.co.uk for Your UK Shopping