To Recompile All The Stored Procedures For Database

Recompiling an object is an advantage when the "indexes or other changes that affect statistics are made the database, compiled stored procedures and triggers may lose efficiency. Sp_recompile is a system stored procedure in SQL to rebuild an object, the next time it runs. Recompiling you can re-optimize queries.

Here is the query to recompile all the SPS in the database.

Declare @ name nvarchar (125), @ cmd nvarchar (300)

SPList declare the cursor / Cursor * It must be observed to go through all the SPS DB * /

Select the name of sys.objects

where type = 'P'

SPList open

FETCH NEXT from SPList / * get the record of the cursor * /

in the name @

While @ @ FETCH_STATUS = 0

start

Select @ cmd = 'EXEC sp_recompile [' + @ name + ']'

Exec @ cmd / * recompile the SP * /

FETCH NEXT SPList / * Get the name of SP after the cursor * /

@ In the name

Head

SPList close

Deallocate SPList

Remove The NOT NULL Column Constraint ALTER TABLE

Many times it may be necessary to remove the limitation Not Null applied to a column in a table. It is generally necessary to overcome the unexpected integrity constraints (which rarely happens, if well designed) against a table. We can remove the constaint Not Null using the ALTER TABLE .... ALTER COLUMN ... option.

A sample query is

Alter table dbo.Test

alter column test nvarchar (10) null;

You can also reuse the next.

Alter table dbo.Test

alter column test nvarchar (10) NOT NULL;

Find The Model For SQL Server Using PATINDEX () And CHARINDEX ()

Although these features are not commonly used, is very powerful in the search for a reason and return the location of the text / string / model. By understanding the functionality of the string functions, we will be able to understand the difference too.

PATINDEX ()

The search function of a pattern in a string / expression and returns the starting position of the first occurrence of the pattern. It works similarly to the operator, as in any application.

The general syntax is

PATINDEX ('% pattern%', expression)

You may have already noticed the '%' symbols used in the model. These are important, as I mentioned before, using the same syntax used for research by the LIKE operator.

Choose PATINDEX ('% System%', description) index location, description

From dbo.TestTable

CHARINDEX ()

CHARINDEX () function is similar to PATINDEX (). However, CHARINDEX search for the string expression specified in the start position (Start loction default is 1). If you have already noted above, we can begin to determine the position of PATINDEX ()

The general syntax is

CHARINDEX (expression1, expression2 [, start_location])

An example of the same data set used earlier would look like

Select CHARINDEX ("System", description) index position, description

from dbo.TestTable

We can also start position indlude. Below is a sample search function in the string from the position of three

Select charindex ("System", description, 3) indexlocation Description

from dbo.TestTable

Finally, the main difference between the PATINDEX () and CHARINDEX () is the ability to CHARINDEX To find a specific location.

Reindex The Database With A Large Gap With "DBCC DBREINDEX"

DBCC commands is one of the most commonly used are DBAs worldwide. Today I'm going to present a simple way to index the specific table or all indexes using DBCC.

The command used for this is

DBCC DBREINDEX

General Sytax

DBCC DBREINDEX

(

table_name

[, Index_name [, fillfactor]]

)

The best feature of this command is that we need not abandon such unique or primary key restrictions applied to the index to rebuild the index. This means that the index can be rebuilt without knowing the structure of a table or limitations.

It accepts three parameters. The table name, index name and the fill factor.

This is an example below

The command below will not be indexed and table "IDX_EMPL" "TBL_DIM_EMPL" with a fill factor of 80

DBCC DBREINDEX ("TBL_DIM_EMPL", "IDX_EMPL", 80)

The following command reindex all indexes on the table "TBL_DIM_EMPL"

DBCC DBREINDEX ("TBL_DIM_EMPL", "", 80)

What NULLIF () Do? How It Differs From ISNULL ()?

NULLIF () returns zero if the two specified expressions are equal in function.

Syntax

NULLIF (expression1, expression2)

If expression1 = expression2, the function returns NULL.

ISNULL () function replaces an expression with no specified value.

The syntax used is

ISNULL (expression1, expression2)

If expr1 is NULL, then the function returns expression2 output.

ISNULL functionality can be written using if ... WHEN the

CASE WHEN expression1 THEN END ELSE NULL Expression2 expression1

An example of these two functions are shown below

Select ISNULL (NULL, 'hello') as IsNullOutPut1

, IsNull (1001, 1) as IsNullOutPut2

, NULLIF (100, 100) that NullIfOutPut1

, NULLIF (1, 100) that NullIfOutPut2

Changing The SQL Database In Single User Mode And Back To The Multi-User Mode

It is often necessary to modify the database in single user mode, especially if you are the DBA. A simple example would be to change the sorting options, or settings TE. A single user only allows a user (typically the DBA) to access the database. Therefore, it is easy to make changes without worrying about the blind alleys, and any claim, DB, and also without affecting users.

It is very easy to change the database in single user mode, in fact, it's just a script. Use the following script to change the mode.

ALTER DATABASE < > SET SINGLE_USER no_wait

NO_WAIT clause to set the single-user mode, when you run the query. Replacing this is to use the system stored sp_dboption

EXEC sp_dboption < > 'SINGLE USER', False

However, you must set the database in multiuser mode for other users to use. Without it, it will not be available to others.

Here is a script to set the basis for multi-mode.

ALTER DATABASE < > GAME MULTI_USER no_wait

Or alternative

EXEC sp_dboption < 'SINGLE USER'>, False

I advise you to maintain caution while changing database in single user mode, because it would link users already connected to the database.

The Benefits And The Differences Between CHAR And VARCHAR Data Types For SQL Server

All of you reading this have encountered these types of data, but have you ever thought about what the differences and advantages of the two?

The obvious difference that comes to mind in length. Yes! You are right. Varchar Char supports of various lengths and supports fixed length. This is the main advantage of the VARCHAR data type. For example, consider the varchar (10) and char (10). You can save the "GEEKEPISODE" Both types of data using the entire length of the target and save the "geek", char data type to use 10 characters in common, but only 4 VARCHAR

From the above examples, it seems better than CHAR VARCHAR all the time. But remember, CHAR was included as a separate type because it has certain advantages over VARCHAR.

VARCHAR use the extra space to store the length of the string stored in it, and mark the end of the string. Considering that the char data type does not use this extra space. This is certainly the advantage of data type CHAR.

Summary:

CHAR

It is used to store a fixed string / character data

It requires no additional space / bytes to capture the length and the end of the string.

Still, if the length of the string data / character is fixed. Example: SSN, systemId, EmpID.

It is faster than VARCHAR, when used in collections and queries.

VARCHAR

Used to store variable-length string.

Requires additional bytes / space to enter the time and the end of the chain.

use, if the length of the data string / character can not be determined at the design stage. Example: name, brief description

 Slower than the CHAR data type when used in collections and queries.

Here the above explanation also applies to data types nchar and nvarchar.

The Script In The Batch File Using Sqlcmd

I often create batch files to run SQL scripts (especially SPS), and then schedule jobs using SQL Agent / Task Scheduler. In this post, I will show you how to create a batch file with SQL commands.

First, the command used to execute SQL queries commnd SQLCMD system. The general syntax of this command is available on the link. I'm going with a single as follows

Server Name sqlcmd-Q-ES-o Query "Output Path"

here

E = secure connection (Windows Authentication)

-Server Name = S

-Q = CommandLineQuery

O = output file path

a simple command prompt can commnad

C: \ Users \ geekEpisodes> sqlcmd-IS "Local-SRV01"-Q "Select top 10 * from MyTestDatabase .. [ForecastHistory]"-o "C: \ New Folder \ output.txt"

All commands you execute the command can be run from batch control / files. to create a batch file with SQL commands to follow the steps below


1: Create a file with a bat or cmd (for example testfile.bat, test.cmd) ...

2: Add the file commands such as

sqlcmd-IS "Local-SRV01"-Q "Select top 10 * from MyTestDatabase .. [ForecastHistory]"-o "C: \ New Folder \ output.txt"

sqlcmd-IS "Local-SRV01"-Q "select count (1) of MyTestDatabase .. [ForecastHistory]"-o "C: \ New Folder \ Output2.txt"

sqlcmd-ES "SRV01 local"-Q "Exec MyTestDatabase .. [spPopulateData]"

3: Now save it.

4: Double-click the file and query the file exeuted. This file can be used in the Task Scheduler and other applications.

String Soundex() And Its Purpose In The SQL

Soundex code was developed to help defeat the effects of all spelling variations, which can occur in similar-sounding name (Smith, Smithe, Smythe, etc.). This allows users to index is based on the Soundex code is based on votes and "no", as it is written.

The Soundex system is not foolproof - after Gough sounding names and Goff (Goff), but a different code is generated for these two names. Soundex can however be more widely used to obtain a desired result (closer to a desired result atleast) This is an algorithm commonly used by many researchers.

Depending on SQL string is available for this and usually come handy. SOUNDEX () function.

It accepts a string (name) as a parameter and returns a 4 character. This code is known as code name soundex / string is sent as a parameter.

For example:

Choose Soundex ('Smith') O / P: S580

Select SOUNDEX ('Smith') O / P: S580

SELECT SOUNDEX ('green') O / P: G652

Another use for this is contrary to a user enters a field where you need to search for specific words, but words can be wrong (even if it can not produce 100% conversion).

Sequence Number As Per Each Group

Use the following to display the sequence numbers as per each group


select
         myGroupID, 
         myGROUPCOUNTER = ROW_NUMBER() OVER (PARTITION BY [myGroupID] ORDER BY [myGroupID])
from
         myGroupTable

Database Files Location in SQL

Use the following to find the location of all database files (.mdf and .ldf) present in SQL server

SELECT physical_name as Path_Name FROM master.sys.master_files

Identity Column in SQL

Use following to make an identity column in a table of SQL database.

CREATE TABLE dbo.TableName(

ID INT IDENTITY(1,1) ,

LittleDescription varchar(50) ,

DetailedDescription varchar(1000)

);

SQL Server Database - Recover From Suspect mode

Sometimes you find your SQL database in suspect mode. This normally happens when your PC abnormally shutdowns or any other major hardware failure. Here i will explain how to recover your precious data from this.

In many cases there will be no change in the size of MDF and LDF files, but more likely LDF is corrupted.

So here is the solution of your problem.

• First make sure that database is in suspect mode
  • Create a new empty database at some safe location
  • Now stop the SQL Server service
  • Replace the old MDF file over newly created
  • Start SQL service and database will try to recover it. (During this database will go to suspect mode)
• Now we will put the database to emergency mode (This can be done by using ALTER DATABASE or following commands)

Sp_configure 'allow updates', 1
GO
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = ''
GO
sp_dboption '', 'single_user', 'true'
GO
Sp_configure 'allow updates', 0
GO
Reconfigure with override
GO

• Try to use DBCC CHECKDB ('', REPAIR_ALLOW_DATA_LOSS) This will rebuild log file and run full repair of the MDF file)

DBCC CHECKDB('', REPAIR_ALLOW_DATA_LOSS)
Go

(*If it gives error use next step)

• Then use DBCC REBUILD_LOG ('','new LDF path') to rebuild the LDF file

DBCC REBUILD_LOG('', 'new LDF path')
Go

• Almost done. Now we will push database to normal mode (Again use either ALTER DATABASE or following commands)
  

Sp_configure 'allow updates', 1
GO
Reconfigure with override
GO
Update sysdatabases set status = 16 where name = ''
GO
Sp_configure 'allow updates', 0
GO
Reconfigure with override
GO

• Restart the engine of Microsoft SQL Server (I have successfully checked on Microsoft SQL 2000)
• Verify your database by checking its tables and data.

* Don't forget to make a daily backup schedule next time :)

How To Get/Retrieve Size Of Tables In SQL Server Databases

How To Get/Retrieve Size Of Tables In SQL Server Databases

Sometime we wonder why an application database grew so heavy during the short period of development and testing process. Here most of us wanted to know what are the actual stats of used space by each table and if we need this information without the use of Management Studio's table properties dialog, then follow this...

In fact SQL Server gives us everything we need with a Stored Procedure that is called sp_spaceused. But this SP does not support iterating over the tables in some database, so we needed to leverage another Stored Procedure sp_msForEachTable, but this is undocumented.

SET NOCOUNT ON

DBCC UPDATEUSAGE(0)

-- Get Total DB size.

EXEC sp_spaceused

-- Table row counts and their sizes.

CREATE TABLE #tempTBL

(

[nameOfTable] NVARCHAR(128),

[rowsInTable] CHAR(11),

reserved_size VARCHAR(18),

data_size VARCHAR(18),

index_size VARCHAR(18),

unused_size VARCHAR(18)

)

-- Insert into temp table

INSERT #tempTBL EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

-- Show it to user

SELECT * FROM #tempTBL

-- Number of rows

SELECT SUM(CAST([rowsInTable] AS int)) AS [rowsInTable] FROM #tempTBL

-- Drop the temp table

DROP TABLE #tempTBL

.

Update Statement in SQL

This is used when you want to update records in database table.

This will update column1 and column2 in a table for all rows/records.

Update Table_Name set column1 = value1, column2 = value2


This will update column1 and column2 in a table for the records that are filtered by using the criteria in WHERE clause.


Update Table_Name set column1 = value1, column2 = value2 Where columnX = valueX

Insert Into statement in SQL

This is used when you want to insert new record into database table.

You may write this statement in two different styles.

INSERT INTO my_table VALUES (value1, value2,...)
This is used when you don't want to specify column names during insert operation. so you have to specify the values for columns in same sequence as they appear in database table.


INSERT INTO my_table(column1,column4) VALUES (value1, value4)
This is used when you exactly want to specify column names for which values will be inserted during insert operation.

Order By Keywork in SQL

The keyword "order by" is used to sort the result set.

This sorts a specified column and by default it sorts in ascending order but you may specify the word "Desc" to get result set in descending order.

For ascending sort:

Select column(s) From Table_Name order by column1

Select column(s) From Table_Name order by column1 asc

For descending sort:

Select column(s) From Table_Name order by column1 desc

AND & OR Operators in SQL

In where clause you may filter records but if you want to filter on the basis of more than one condition then you may use "AND" or "OR" operators.

The AND & OR Operators

The AND operator displays a record only when the first condition and the second condition both are true
The OR operator displays a record only if the first condition or the second condition is true

Select column(s) From Table_Name where column1 = value1 AND column2 = value2

Select column(s) From Table_Name where column1 = value1 OR column2 = value2

You may also use the combination of both operators.

Select column(s) From Table_Name where (column1 = value1) AND (column2 = value2 OR column3 = value3)

Where clause in SQL

This "where" clause is used to filter results.

When you want to select, update or delete records, you may use where clause to filter records on which operation will be performed. It is to filter result set on the basis of some criteria, or you may say that it will fetch only those records that fulfill criteria.

Select column(s) From Table_Name 

     Where column = value

"=" can be replaced with any of the following operators.

=	Equal
<> or !=	Not equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
BETWEEN	Between an inclusive range
LIKE	Search for a pattern
IN	If you know the exact value you want to return for at least one of the columns

Select statement in SQL

Select statement is used in SQL to select data from database objects. You will get a result table on the execution of select statement, which is called result-set.

Select column(s) From Table_Name

or 

Select * From Table_Name

First will return only specified columns and second will return all columns in a table. 

select and SELECT are same as SQL is not case sensitive.

Select Distinct: 

--------------

Some columns may have duplicate data, select statement returns all occurrences of data whereas select distinct is used when you want to avoid duplicate records/data in result set.

This is applied on column for which you want to filter duplicate records.

Select Distinct column(s) From Table_Name