Out of all the statements in SQL (Structured Query Language), the Select statement is by far the most used once.
The select statement is used to retrieve data from tables/view/combination of tables and views in a database. The result thus generated using this statement is used for display to users through GUI, for reporting purpose, for processing the data and etc.
Such a result generated using a Select Statement is called a Result – Set.
The basic Syntax for a Select Statement is (One thing to be noted over he is SQL is not case sensitive)
SELECT Column_List FROM Table_Name
The <Table_Name> gets more prefixes depending on the Environment we have to select the data from, it can be also written as a three part identifies as [Database Name].[Schema Name].[Table Name]
Here is an example making use of the database AdventureWorks which is available from the site http://www.codeplex.com for download.
Use AdventureWorks
Go
SELECT EmployeeID,NationalIDNumber,Title,ManagerID FROM HumanResources.Employee
The above sql select statement selects four columns from the table Emplyee residing in the schema HumanResources.
| EmployeeID | NationalIDNumber | Title | ManagerID |
| 1 | 14417807 | Production Technician - WC60 | 16 |
| 2 | 253022876 | Marketing Assistant | 6 |
| 3 | 509647174 | Engineering Manager | 12 |
| 4 | 112457891 | Senior Tool Designer | 3 |
| 5 | 480168528 | Tool Designer | 263 |
Now let us see one more basic example, say instead of a few columns we want to select all the columns in a table, for that we use the asterisk symbol “*”.
When we are going to write it, It’s going to take a form of the below
SELECT * FROM [Database Name].[Schema Name].[Table Name]
Here it is
Use AdventureWorks
Go
SELECT * FROM HumanResources.Employe
That’s it for not, will further write on these basics in the comin articles.
If you have like it and want to get the articles (or) new post at sql like, subscribe to articles through email by entering your email address in the top right and verifying the same.
if you have any comments, then the below form is for you, go ahead!!!
Tagged as:
Syntax
Running a DROP statement on an SQL Server is never an easy task as far as the numbers of thoughts that run in our mind at the time of doing that are considered.
So, here is writing up on the process that we need to follow while dropping a SQL Server Database.
Firstly, the decision process that you have to really go ahead and drop the database, if yes then create a checklist of your own with the below as a starting point to it.
1. Check if any application is using the database,the chance is that they need to be pointed to a new upgraded database or the application is obsolete now and simply needs to be removed.
2. Check if any other databases are using objects of this database.
3. Check if you have any Database Level Triggers on this particular database.
4. Check if you have any Maintenance plans, exclusively referring this database.
5. Check if you have any MS SQL Server Agent Jobs referring this database exclusively.
6. Check if you have any Database Snapshots, if yes, Drop the database snapshots on the database.
7. Check if you have log shipping configured, If the database is involved in log shipping, remove log shipping before dropping the database.
8. Get a thumbs up from the owner of the database
9. Check if there are any existing users. If yes, notify them, or if they are long running check what operation they are trying to do and kill them.
10. Preferably, get the database to SINGLE_USER mode.
11. Take a full backup with “verify the backup” option on, rather take multiple copies with at least one being offsite.
12. Create a script of all the Logins in that database, in case you may miss the logins at the place of Restoration.
13. Take a backup of the master database.
14. Drop the database.
15. Take a backup of the master database.
Now for some basic points
Syntax: DROP DATABASE Database Name
For deleting a database you need to at least have CONTROL permission on the database.
We can delete a database regardless of its state – Offline, Read-only or Suspect or any other.
If you have liked the article and want to get the articles (or) new posts at
sql like, subscribe to articles through email by entering your email address in the top right and verifying the same. if you have any comments, then the below form is for you, go ahead!!!
Tagged as:
Database,
Drop
Using CHARINDEX and PATINDEX
How many times while you were writing a program, you wanted to find or search for a character or a string? The number should be quite high!!!
Talking with respect to SQL Server, how many times have we faced the situation of finding a character or a string within string or a character column?
In this article, let me take you through the basics of getting these things done with the help of Char Index and Pat Index functions.
CHARINDEX
Coming to char index, it can be best explained with its syntax. Its syntax looks like below, and it searches the expression2 for expression1 and returns the start position of the expression1 in expression2
The start location is an optional argument to the function which can be specified to as a hint to the function as from where to start the search from in the expression2
CHARINDEX ( expression1 ,expression2 [ , start_location ] )
For Example,
Lets take a string “abcde”, and lets find where “c” comes, we know the answer - that it’s 3, we want to see how the CHARINDEX works(we are learning and not questioning Microsoft J )
And the sql statement for the same is
select charindex(‘c’,'abcde’)
And the result – 3
You can try it with a column in a table as well!!!
PATINDEX
Pat Index can be used as char index, and in a way it is bit more superior to char index as it would server more purposes. The PATINDEX function returns the starting position of a pattern of characters in a string or a text column of table. The main reason why it is more useful is that, PATINDEX supports wildcard characters in the search pattern string.
The syntax of this functions is
PATINDEX ( ‘%pattern%’ , expression )
Let’s take an example to see how it is used, let’s consider the below sql statement, we are searching for a star position of BC in the expression ABCDE, we need to get the answer as two, now lets see.
SELECT PATINDEX(‘%BC%’,'ABCDE’)
Result:
2
One more example, now, let’s reapeat that ABCDE twice and check,
SELECT PATINDEX(‘%BC%’,'ABCDEABCDE’)
Result:
2
The result is still two, so, PATINDEX gives the first position when we have more than one matching patterns.
And when we don’t have any it gives a result of zero, this can be tried!!!
So, in this article we have seen on the basic uses of CHARINDEX and PATINDEX, both are used for character and string searches but with second one we can use the wild card symbols which make it useful in a larger number of cases.
Tagged as:
SQL Functions