# European Windows 2012 Hosting BLOG

BLOG about Windows 2012 Hosting and SQL 2012 Hosting - Dedicated to European Windows Hosting Customer

# SQL Server 2016 Hosting - HostForLIFE.eu :: Maximum Limit Value For Integer Data Type in SQL Server 2012

November 27, 2018 10:12 by Peter

In this article, I described how to calculate the maximum range of various integer data types in SQL Server. TINYINT, SMALLINT, INT and BIGINT are all number data types. The difference between these data types are in the minimum and maximum values. So let's have a look at a practical example of how to calculate the maximum range of the integer data type in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

Calculating the maximum range of various integer data types.

Bigint Data Type
The Bigint data type represents an integer value. It can be stored in 8 bytes.

Formula   2^(n-1) is the formula of the maximum value of a Bigint data type.
In the preceding formula N is the size of the data type. The ^ operator calculates the power of the value.
Now determine the value of N in Bit:
Select (max_length * 8) as 'Bit(s)' from sys.types Where name = 'BIGInt'

Determine the maximum range of Bigint
The formula is:
2^(n-1) here N=64

Select Power(cast(2 as varchar),(64) -1) as 'Bigint max range'  from sys.types Where name = 'BIGInt'

The range of a Bigint data type is -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

INT Data Type
Int represents an integer value that can be stored in 4 bytes. INT is the short form of integer.

Formula
2^(n-1) is the formula to find the maximum of an INT data type.
In the preceding formula N is the size of data type. The ^ operator calculates the power of the value.

Now determine the value of N in Bit:
Select (max_length * 8) as 'Bit(s)' from sys.types Where name = 'Int'

Determine the maximum range of int
The formula is:
2^(n-1) here N=32
Select Power(cast(2 as varchar),(32) -1) as 'int max range'  from sys.types Where name = 'Int'

The range of an int data type is -2,147,483,648 to 2,147,483,647.

Smallint Data Type
Smallint represents an integer value that can be stored in 2 bytes.

Formula
2^(n-1) is the formula to find the maximum of a Smallint data type.
In the preceding formula N is the size of the data type. The ^ operator calculates the power of the value.

Now determine the value of N in Bit:Select (max_length * 8) as 'Bit(s)' from sys.types Where name = 'Smallint'

Determine the maximum range of Smallint
The formula is:
2^(n-1) here N=64
Select Power(cast(2 as varchar),(16) -1) as 'Smallint max range'  from sys.types Where name = 'SMALLInt'

The range of a Smallint data type is -32768 to 32767.
Tinyint Data Type
Tinyint represents an integer value that can be stored in 1 byte.
The range of a Tinyint data type is 0 to 255.

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

# How To Split/Separate Numbers And Alphabets From Alpha Numeric String In SQL Server?

November 13, 2018 10:11 by Peter

Today, I am going to explain how you can split/separate numbers and alphabets from an alphanumeric string in SQL server. When you work with any database-related application, either in Web or Windows applications, sometimes based on your requirement you have an alphanumeric string and you only want numbers from that string and want to use those numbers in your entire application as per your need, possibly as a variable, parameter, or a string concatenation.

Implementation
In my case I want to generate auto-increment token number and that token number will generate with a combination of My Invoice Number and Heder Name of Store, and in my Invoice Table Invoice Number like "HSP14569" where "HSP" is Header Name of Store. That can change based on Store selection and "14569" is my Invoice Number.

Actually, what I need is to split my invoice number from "HSP14569" To "14569" and increment with "1," so that will be "14570". Now, I will contact this new number with my header of the store.

So, yesterday I wrote one user-defined function in SQL server, which will return only numeric values from my string.

SQL Server User Defined Function
CREATE FUNCTION dbo.GetNumericValue
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END
GO

Note
You can modify this user defined function based on your need.

Let's see how you can use this user-defined function. Below, I have included some of the ways to use this function.

Sql Server Select Statment
SELECT dbo.GetNumericValue('') AS 'Empty';
SELECT dbo.GetNumericValue('HSP14569AS79RR5') AS 'Alpha Numeric';
SELECT dbo.GetNumericValue('14569') AS 'Numeric';
SELECT dbo.GetNumericValue('HSP') AS 'String';
SELECT dbo.GetNumericValue(NULL) AS 'NULL';

Output

Summary
You can see the result was generated as above. If you have some alternate way to achieve this kind of requirement then please let me know, or if you have some query then please leave your comments.

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

# SQL Server 2016 Hosting - HostForLIFE.eu :: All About Primary Key And Its Basics

November 7, 2018 08:44 by Peter

In this series of articles, we will go deep into SQL Server from scratch and will gain knowledge of queries, optimization, and database administration. This is the first article of the series where we will learn about general SQL queries and their functioning. Images have been used wherever necessary so as to make you understand every command properly.

All Queries which I am posting today you can use  directly on your query plan like copy, paste and execute this query.
Each query has a valid column name and similarly I have shown in the form of image for proper understanding and proper usage

Find all Primary key in Give Database in following format,

SELECT i.name AS IndexName,
OBJECT_NAME(ic.OBJECT_ID) AS TableName,
COL_NAME(ic.OBJECT_ID, ic.column_id) AS ColumnName
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1

Finding Constrains and Type of Constrain i.e. Primary and foreign key relation in the given database

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc IN('FOREIGN_KEY_CONSTRAINT', 'PRIMARY_KEY_CONSTRAINT')

Detailed level relationship and description of primary key and foreign key

SELECT f.name AS ForeignKey,
SCHEMA_NAME(f.SCHEMA_ID) SchemaName,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,
OBJECT_NAME(f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id

Use the above snippets as per your requirement.

In most of the cases it's is going to be used in the Database Analysis where Database size and table are large and high in number.

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

# SQL Server 2016 Hosting - HostForLIFE.eu :: How To Check If A String Contains A Substring In SQL Server?

November 2, 2018 12:14 by Peter

In this blog, I wil explain how to check a specific word or character in a given statement in SQL Server, using CHARINDEX function or SQL Server and check if the string contains a specific substring with CHARINDEX function.

Alternative to CHARINDEX() is using LIKE predicate.

Method 1 - Using CHARINDEX() function

CHARINDEX()
This function is used to search for a specific word or a substring in an overall string and returns its starting position of match. In case no word is found, then it will return 0 (zero).

Let us understand this with examples.

Syntax
CHARINDEX ( SearchString,WholeString[ , startlocation ] )

Example
Declare @mainString nvarchar(100)='Kenneth James    '
---Check here @mainString contains Kenneth or not, if it contains then retrun greater than 0 then print Find otherwise Not Find
if CHARINDEX('Kenneth',@mainString) > 0
begin
select 'Find' As Result
end
else
select 'Not Find' As Result

Output

CHARINDEX

Method 2 - Using LIKE Predicate
DECLARE @WholeString VARCHAR(50)
DECLARE  @ExpressionToFind VARCHAR(50)
SET @WholeString = 'Kenneth James'
SET @ExpressionToFind = 'James'

IF @WholeString LIKE '%' + @ExpressionToFind + '%'
PRINT 'Yes it is find'
ELSE
PRINT 'It doesn''t find'

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.