Data Types

Various data types serve specific purposes within SQL Server. Each type has defined ranges and applications for optimal use.

Server Configuration

Server Properties via SERVERPROPERTY

Retrieve server information using the SERVERPROPERTY function:

SELECT CONVERT(sysname, SERVERPROPERTY('servername'));

System Views for Server Details

Query system views to examine server configurations:

USE mydb;
GO
SELECT name, server_id, provider
FROM sys.servers;
GO

Configuration Options

Check current server settings:

SELECT name, description, value, is_advanced
FROM sys.configurations;
GO

Modifying Server Settings

Update server parameters through stored procedures:

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'fill factor', 100;
GO
RECONFIGURE;
GO

Database Creation

Create a new database with specified files:

CREATE DATABASE test0606
ON PRIMARY (
    NAME = 'test0606',
    FILENAME = 'F:\SQL\SQLserver\test\test0606.mdf',
    SIZE = 10MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)
LOG ON (
    NAME = 'test0606_log',
    FILENAME = 'F:\SQL\SQLserver\test\test0606_log.ldf',
    SIZE = 10MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 10%
);

Common Issues During Creation

Access denied errors may occur due to insufficient permissions. Resolve by adjusting service account access in services.msc.

Table Operations

Creating Tables

Define tables with constraints:

CREATE TABLE dbo.Products (
    ProductID int PRIMARY KEY NOT NULL,
    ProductName varchar(25) NOT NULL,
    Price money NULL,
    ProductDescription varchar(max) NULL
);
GO

Adding Columns

Extend existing tables with new feilds:

ALTER TABLE table_0611
ADD [0612] CHAR;

Inserting Data

Add records to tables:

INSERT INTO Table_0611 VALUES (1,1,1,1,1);

Insert specific columns:

INSERT INTO table_name (column_name_1, column_name_2) VALUES (value1, value2);

Data Manipulation

Updating Records

Modify existing entries:

UPDATE Table_0611 SET [0611] = '88' WHERE [22] = '22';

Deleting Data

Remove records or entire tables:

DELETE FROM table_name;
TRUNCATE TABLE table_name;

Removing Columns

Drop unwanted columns:

ALTER TABLE table_0611 DROP COLUMN [0612];

Database Modification

Altering Database Files

Modify database file properties:

ALTER DATABASE test0612
MODIFY FILE (
    NAME = 'test0612_db',
    SIZE = 15MB
);

Changing Column Definitions

Alter data types of existing columns:

ALTER TABLE table_0611 ALTER COLUMN [44] int;

Querying Data

Basic Selection

Select data from single tables:

SELECT column_name FROM table_name;

Multi-table Queries

Join tables using various join types:

SELECT t1.column_name, t2.column_name
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.id;

Filtering and Sorting

Use WHERE clauses and ORDER BY:

SELECT * FROM table_name
WHERE column_name = 'value'
ORDER BY column_name DESC;

Pattern Matching

Apply LIKE operator with wildcards:

SELECT * FROM table_name
WHERE column_name LIKE '%pattern%';

Range and Set Conditions

Utilize BETWEEN and IN operators:

SELECT * FROM table_name
WHERE column_name BETWEEN 1 AND 10;

SELECT * FROM table_name
WHERE column_name IN ('val1', 'val2');

Combining Results

Merge query results with UNION:

SELECT column_name FROM table1
UNION
SELECT column_name FROM table2;

Constraints

Primary Keys

Ensure unique identification:

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

Unique Constraints

Enforce uniqueness:

ALTER TABLE table_name ADD UNIQUE (column_name);

Foreign Keys

Maintain referential integrity:

ALTER TABLE child_table ADD CONSTRAINT fk_constraint
FOREIGN KEY (child_column) REFERENCES parent_table(parent_column);

Check Constraints

Validate data values:

ALTER TABLE table_name ADD CONSTRAINT chk_constraint
CHECK (column_name > 0);

Default Values

Set default values for columns:

ALTER TABLE table_name ADD CONSTRAINT df_constraint
DEFAULT 'default_value' FOR column_name;

Backup and Restore

Backup Process

Database backups create .bak files that can be restored later.

Restore Considerations

Restore operations require careful handling to maintain data consistency.