MSSQL Knowledge

Microsoft SQL Server

Microsoft SQL Server is a Relational Database Management System (RDBMS) designed to run on platforms ranging from laptops to large multiprocessor servers. SQL Server is commonly used as the back-end system for websites and corporate CRMs and can support thousands of concurrent users.

SQL Server comes with a number of tools to help you with your database administration and programming tasks. Although SQL Server can also be run as a desktop database system, it is most commonly used as a server database system.

 

Server Database Systems

Server based database systems are designed to run on a central server, so that multiple users can access the same data simultaneously. The users normally access the database through an application.

For example, a website could store all its content in a database. Whenever a visitor views an article, they are retrieving data from the database. As you know, websites aren't normally limited to just one user. So, at any given moment, a website could be serving up hundreds or even thousands of articles to its website visitors. At the same time, other users could be updating their personal profile in the members' area, or subscribing to a newsletter or anything else that website users do. Generally, it's the application that provides the functionality to these visitors. It is the database that stores the data and makes it available.

SQL Basic Functions Chapter2

LTRIM and RTRIM Functions

Ø  LTRIM Function

Syntax:

SELECT LTRIM (' Amit') AS Names

Result:

Names

Amit

LTRIM in sql server removes blanks from the beginning (left) of a string. For example, if three blank spaces appear to the left of a string such as ' Amit', you can remove the blank spaces with the above query.

It does not matter how many blank spaces precede the non-blank character. All leading blanks will be excised.

Ø  RTRIM Function

Syntax:

SELECT RTRIM ('Amit ') + Bhardwaj AS Names

 Result:

Names

Amit Bhardwaj

Similarly, RTRIM in sql server removes blanks from the end (right) of a string. For example, if blank spaces appear to the right of Amit in the names column, you could remove the blank spaces using the RTRIM, and then concatenate "Bhardwaj" with the + sign, as shown above.

 

Length Function

 Syntax:

Len(string)
OR SELECT LEN(column_name) FROM tbl_name

Output:

SELECT Len ("Learnsqlserver")

would return 14

 

Use of LIKE in SQL Query

 The LIKE in sql server allows you to use wildcards in the where clause of an SQL server statement. This allows you to perform pattern matching.
You can use LIKE condition with in your select, insert, update, or delete sql statements.
The patterns that you can choose from are:

% allows you to match any string of any length.
_ allows you to match on a single character

E.g

SELECT * FROM tbl_Student WHERE sname LIKE '%Amit%'

 

Date Function

Syntax

SELECT GETDATE(), -- Current Date and Time
CURRENT_TIMESTAMP, -- Current Date and Time

Output

SELECT GETDATE()

would return 2011-09-14 20:24:17.060

 

 

REPLICATE Function

The REPLICATE function repeats a given character expression a designated number of times.

Syntax:

REPLICATE ( character_expression ,integer_expression )

Result:

SELECT REPLICATE ('AZ ', 15)

This returns:
AZ AZ AZ AZ AZ AZ AZ AZ AZ AZ AZ AZ AZ AZ AZ