Windows Ranking Functions

Windows Ranking Functions / Ranking Functions in SQL

Ranking functions are used to assign a rank to each row within a partition of a result set. The rank is based on the values in one or more columns. The ranking functions include:

• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• NTILE(n)

ROW_NUMBER(): Assigns a unique sequential integer to rows within a partition.	RANK(): Assigns a rank to each row, with gaps for ties.
NTILE(n): Divides rows into buckets and assigns numbers.	DENSE_RANK(): Similar to RANK() but without gaps in the sequence.

The ranking functions are often used in conjunction with the OVER() clause to define the partitioning and ordering of the result set.

SELECT column1, column2, ..., 
       RANK() OVER (PARTITION BY column1 ORDER BY column2) AS rank_column
FROM table_name;
      

In this example, the RANK() function assigns a rank to each row within the partition defined by column1, ordered by column2.

SQL to import images from folder to table

To import images from a folder into a SQL Server table, you need to store the image data in a column of type VARBINARY(MAX) or IMAGE (though IMAGE is deprecated, it’s still widely used in legacy systems). You can use SQL Server's BULK INSERT command or SQL Server Integration Services (SSIS), but if you're trying to do this directly from a folder using a SQL query, you'll generally need a stored procedure or script that reads the image files from the folder, converts them to binary data, and inserts them into the database.

Here’s an example using T-SQL along with PowerShell or SQL CLR to achieve this. For simplicity, I'll focus on an example using T-SQL with OPENROWSET and the BULK option.

Steps:

1. Create a table to store the image.
2. Use OPENROWSET to import the images into the table.

Create the Table

First, create a table with a VARBINARY(MAX) column to store the image data.

CREATE TABLE ImageTable (
    ImageID INT IDENTITY(1,1) PRIMARY KEY,
    ImageData VARBINARY(MAX),
    FileName NVARCHAR(255)
);

Import Image into the Table
You can then use the OPENROWSET function to read the images from a folder and insert them into the table. OPENROWSET is useful for importing data from files.
Example SQL Query to Insert Images
Assuming all the images are in C:\Images\ directory:

DECLARE @FilePath NVARCHAR(4000)
DECLARE @FileName NVARCHAR(255)

-- Declare the directory where your images are stored
SET @FilePath = 'C:\Images\'

-- Insert a specific image
SET @FileName = 'example.jpg'

INSERT INTO ImageTable (ImageData, FileName)
SELECT * FROM OPENROWSET(BULK @FilePath + @FileName, SINGLE_BLOB) AS Image
OPENROWSET with the BULK option reads the image as a binary large object (BLOB) and returns it in a VARBINARY(MAX) format.
SINGLE_BLOB tells SQL Server to read the entire file as a single binary value.
The INSERT INTO ImageTable inserts the binary data into the table, along with the image file name.

Recursive CTE (Common Table Expression)

 In SQL Server, you can generate a sequence of numbers using various methods. One common approach is to use a CTE (Common Table Expression) with a recursive query or by leveraging a system table that contains a large number of rows. 

Here I am going to generate the sequence numbers from 1 to 50 (We can increase the number if needed).

Using Recursive CTE (Common Table Expression)

This method uses recursion to generate a sequence of numbers.

Implicit conversion vs Explicit conversion SQL server

In SQL Server, implicit conversion and explicit conversion refer to how data types are automatically or manually converted when performing operations that involve different data types.

1. Implicit Conversion

Implicit conversion happens automatically when SQL Server needs to convert one data type to another. This occurs when the data types are compatible, and SQL Server can convert the value without needing explicit instructions from the user.

Example of Implicit Conversion:

Suppose you are performing a query where you add a varchar column and an int column. SQL Server will automatically convert the int to varchar to perform the operation.

DECLARE @num INT = 100;
DECLARE @str VARCHAR(50) = 'The number is ';

SELECT @str + CAST(@num AS VARCHAR(50)) AS Result;

In this example, @num (an int) is implicitly converted to a varchar when combined with @str (a varchar).

2. Explicit Conversion

Explicit conversion occurs when you explicitly tell SQL Server how to convert a value from one data type to another using the CAST() or CONVERT() functions.

Example of Explicit Conversion:

DECLARE @num INT = 100;
DECLARE @str VARCHAR(50);

-- Explicitly converting the integer to a string (varchar)
SET @str = CONVERT(VARCHAR(50), @num);

SELECT @str AS Result;

Here, the CONVERT() function is used to explicitly convert the integer value @num into a varchar before assigning it to @str.

Key Differences:

• Implicit Conversion: Automatically handled by SQL Server when the data types are compatible.
• Explicit Conversion: Requires the user to specify the conversion using functions like CAST() or CONVERT().

Note:

Implicit conversions may lead to performance overhead or errors in cases where automatic conversion cannot be done, especially with incompatible data types. In such cases, explicit conversion is preferred.