top of page

PowerShell Server Baseline | SQL Table

Step 0: Creating Table for the Information

SQL Table with Database "ServerManager"

 

 

USE [ServerManager]
GO
/****** Object:  Table [dbo].[ComputerServices]    Script Date: 9/18/2021 7:14:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ComputerServices](
   [ID] [bigint] IDENTITY(1,1) NOT NULL,
   [ComputerName] [nvarchar](150) NOT NULL,
   [ServiceName] [nvarchar](150) NOT NULL,
   [DisplayName] [nvarchar](250) NOT NULL,
CONSTRAINT [PK_ComputerServices] PRIMARY KEY CLUSTERED 
(
   [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

PowerShell Export SQL Table

SQL Tables, Things 2 Know ?

A table in SQL is a collection of data organized into rows and columns. Each row represents a single record, and each column represents a data attribute or field of the record. Tables are fundamental structures in a relational database.

​

PowerShell View SQL Table

  • Columns: EmployeeID, FirstName, LastName, Department

  • Rows: Each individual record (e.g., John Doe, Jane Smith, Bob Brown)

​

Primary Key

A primary key is a field (or combination of fields) in a table that uniquely identifies each row in that table. It must contain unique values and cannot contain NULL values. A table can have only one primary key, which can consist of single or multiple columns.

​

Characteristics of a Primary Key

  1. Uniqueness: Ensures that each record can be uniquely identified.

  2. Non-nullability: Primary key columns must always have a value.

  3. Immutable: The values in the primary key should not change.

​

​

Creating a SQL Table

To create a table in SQL, you use the CREATE TABLE statement. Here’s an example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50)
);

Summary

  • Tables store data in rows and columns.

  • Primary Keys uniquely identify each row in a table.

  • Primary keys ensure data integrity by enforcing uniqueness and non-nullability.

  • SQL provides various commands (CREATE TABLE, ALTER TABLE, INSERT, SELECT, UPDATE, DELETE) to manage and manipulate data within tables.

​

By understanding and utilizing these concepts, you can effectively

design and manage relational databases.

SQL Data Types

SQL data types define the kind of data that can be stored in a column. Different SQL database systems (such as MySQL, PostgreSQL, SQL Server, and Oracle) may have slight variations in data types, but there are common categories that are broadly used. Below is a list of common SQL data types along with their descriptions:

​

Numeric Data Types

  1. INT / INTEGER

    • Description: A whole number without a fractional component.

    • Example: 1, -5, 100

  2. SMALLINT

    • Description: A smaller range of integers.

    • Example: 32767, -32768

  3. BIGINT

    • Description: A large range of integers.

    • Example: 9223372036854775807, -9223372036854775808

  4. DECIMAL / NUMERIC

    • Description: A fixed-point number with exact precision.

    • Syntax: DECIMAL(p, s) where p is precision (total number of digits) and s is scale (number of digits after the decimal point).

    • Example: DECIMAL(10, 2) can store values like 12345.67

  5. FLOAT

    • Description: A floating-point number for approximate values.

    • Example: 123.456, -123.456

  6. REAL / DOUBLE PRECISION

    • Description: A floating-point number with double precision.

    • Example: 123.456789, -123.456789

Character String Data Types

  1. CHAR / CHARACTER

    • Description: A fixed-length character string.

    • Syntax: CHAR(n) where n is the number of characters.

    • Example: CHAR(10) will always store 10 characters, padding with spaces if necessary.

  2. VARCHAR / CHARACTER VARYING

    • Description: A variable-length character string.

    • Syntax: VARCHAR(n) where n is the maximum number of characters.

    • Example: VARCHAR(50) can store up to 50 characters.

  3. TEXT

    • Description: A large variable-length character string.

    • Example: Can store long paragraphs of text.

Binary Data Types

  1. BINARY

    • Description: A fixed-length binary data.

    • Syntax: BINARY(n) where n is the number of bytes.

    • Example: BINARY(10) will store 10 bytes of binary data.

  2. VARBINARY

    • Description: A variable-length binary data.

    • Syntax: VARBINARY(n) where n is the maximum number of bytes.

    • Example: VARBINARY(50) can store up to 50 bytes of binary data.

  3. BLOB

    • Description: A large binary object, used for storing binary data such as images or files.

    • Example: Can store binary data of various sizes.

Date and Time Data Types

  1. DATE

    • Description: Stores a date value (year, month, day).

    • Example: 2024-06-16

  2. TIME

    • Description: Stores a time of day (hour, minute, second).

    • Example: 14:30:00

  3. DATETIME

    • Description: Stores both date and time.

    • Example: 2024-06-16 14:30:00

  4. TIMESTAMP

    • Description: Stores a date and time value, typically including time zone information.

    • Example: 2024-06-16 14:30:00.000

  5. YEAR

    • Description: Stores a year value.

    • Example: 2024

Boolean Data Type

  1. BOOLEAN

    • Description: Stores a value of TRUE or FALSE.

    • Example: TRUE, FALSE

Special Data Types

  1. ENUM

    • Description: A string object with a value chosen from a list of permitted values.

    • Syntax: ENUM('value1', 'value2', 'value3')

    • Example: ENUM('small', 'medium', 'large')

  2. SET

    • Description: A string object that can have zero or more values, each chosen from a list of permitted values.

    • Syntax: SET('value1', 'value2', 'value3')

    • Example: SET('reading', 'swimming', 'coding')

  3. JSON

    • Description: Stores JSON (JavaScript Object Notation) formatted data.

    • Example: {"name": "John", "age": 30}

  4. XML

    • Description: Stores XML (Extensible Markup Language) formatted data.

    • Example: <person><name>John</name><age>30</age></person>

  5. UUID

    • Description: Stores Universally Unique Identifiers.

    • Example: 550e8400-e29b-41d4-a716-446655440000

bottom of page