A Day in the Life of a Programmer

The reality of being a programmer is somehow portrayed on this GIF. This situation merely happens when a newbie programmer seeks help from a fellow programmer.

Sometimes, it is also a challenge for an expert programmer to extend help to our fellow programmer; but yes, reaching out a hand should never be a worry.

I hope you can relate on this GIF, and gracefully extend your expertise to help or teach those who are in need of you.

Though programming is hard to understand and to solve, it is always fun; and can even be more enjoyable with hands helping each other.

Primary key in SQL Server

Primary key is a single field or combination of fields that uniquely defines a record.
None of the fields that are part of the primary key can contain a null value.
A primary key column cannot contain NULL values.
Each table can have only ONE primary key.

We can create a primary key in table with the CREATE TABLE statement.
Syntax

The syntax to create a primary key using the CREATE TABLE statement:

CREATE TABLE table_name
(
  column1 datatype { NULL | NOT NULL } PRIMARY KEY,
  column2 datatype { NULL | NOT NULL },
  ...
 
);

Example :

CREATE TABLE Persons
(
Employee_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Department varchar(255),
Salary money
)


DROP a PRIMARY KEY Constraint

We can also drop the PRIMARY KEY constraint, using the below example:

ALTER TABLE Employee
DROP CONSTRAINT pk_EmployeeID

#SQL What is the difference between a Clustered and Non Clustered Index?

What is the difference between a Clustered and Non Clustered Index?

1.       One table can only have only one clustered index.
2.       One table can only have many non-clustered indexes.
3.       A clustered index requires no separate storage than the table storage. It forces the rows to be stored sorted on the index key.
4.      A non-clustered index requires separate storage than the table storage to store the index information.
5.      A table with a clustered index is called clustered table. Its rows are stored in a B-Tree structure sorted.
6.      A table without any clustered indexes is called non-clustered table. Its rows are stored in heap structure unsorted.
7.     The default index created as part of the primary key column is a clustered index
8.     The default index created as part of the unique key column is a non-clustered index.
9.       Prior to SQL Server 2008 only 249 Non-clustered Indexes can be created. With SQL Server 2008 and above 999 Non-clustered Indexes can be created

A table can have multiple non-clustered indexes

A table can have multiple non-clustered indexes because they don’t affect the order in which the rows are stored on disk like clustered indexes.

Why can a table have only one clustered index?

Because a clustered index determines the order in which the rows will be stored on disk, having more than one clustered index on one table is impossible. Imagine if we have two clustered indexes on a single table – which index would determine the order in which the rows will be stored? Since the rows of a table can only be sorted to follow just one index, having more than one clustered index is not allowed.

Create table in SQL Server - Syntax

Create table in SQL Server - Syntax

It is very simple to create table in sql server. Create table syntax is used to create table in sql server. Tables in sql server contains columns an rows which contains records. 

Syntax - 

Create Table TableName
(
ColumnName1 data_type(size) Primary Key,
ColumnName2 data_type(size) NOT NULL,
ColumnName3 data_type(size) NOT NULL,
ColumnName4 data_type(size) NOT NULL,
......
......
);

Here, the ColumnName1, ColumnName2 , ColumnName3 and  ColumnName4 specifies the name of column in table, data_type specifies the type of  the record that column holds, size specifies the maximum length of the column in table and NOT NULL means column does not hold null value.

For example -

CREATE TABLE Employees
(
  Emp_Id  INT PRIMARY KEY,
  First_Name VARCHAR(50),
  Last_Lame VARCHAR(50) NOT NULL,
  Salary MONEY
);

SQL Server Paging of Memory Identification - PowerShell

This blog and powershell script was a fall out of that engagement.


param (
    [string]$SqlServerName = "localhost"
)

Add-Type -Path "C:\Program Files\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)

foreach ($LogArchiveNo in ($SqlServer.EnumErrorLogs() | Select-Object -ExpandProperty ArchiveNo)) {
    $SqlServer.ReadErrorLog($LogArchiveNo) |
        Where-Object {$_.Text -like "*process memory has been paged out*"}
}

The output of this script would look like below:

Why is this important?

If there is excessive memory pressure on SQL Server’s memory allocations causing memory to get paged out to disk, that could be a potentially large performance impact as it invites I/O latency to memory access. It is best practice to ensure that there is enough physical memory on the machine, as well as a well-designed memory infrastructure from SQL Server so that there isn’t overcommitting of memory in order to ensure that paging is not excessive. It is recommended that reevaluation of memory allocations and/or available physical memory is taken into account in order to relieve memory pressure for the current SQL Server instance.

SQL SERVER – Improve performance by turning off AUTO_CLOSE Database Option

Set AUTO_CLOSE Database Option to OFF


You can go to Your Database and Right Click on it. Click on Properties on the right, click menu and it will bring up the following screen. Over here you should change the Auto Close value to FALSE.