Database size

SELECT
  table_schema AS 'Db Name',
  Round( Sum( data_length + index_length ) / 1024 / 1024, 3 )
  AS 'Db Size (MB)',
  Round( Sum( data_free ) / 1024 / 1024, 3 ) AS 'Free Space (MB)'
FROM information_schema.tables
GROUP BY table_schema ;

For More Information about sql server identity please visit this post :

IDentity in Columns Sql Server

Creating a Table With an Identity Columns

Create Table Users

(userID int Identity , UserName nvarchar(255) , Password  nvarchar(255))

Inserting a row

Insert Into Users (UserName,Password) Values ('Nicolas','123654789654')

Or

Insert Into Users Values ('Nicolas','123654789654')

Note that sql server detects identity Columns automatically event if it’s not the first ordinal column

to Get the last inserted  Identity Value

Insert Into Users (UserName,Password) Values (‘Some User’,'dgvzsdx’)

Select @@Identity

@@Identity is a Global variable (on the session level) that holds the value for your last identity

of course if you haven’t executed any insert statements you will get a null .

interchangeably you could use

SELECT SCOPE_IDENTITY()

By Craig S. Mullins

The identity property is a very powerful and useful, yet under-utilized feature of Microsoft SQL Server.  It satisfies a common requirement of many applications: the need for a sequential, ascending identifier. Whenever there is a need for a database column to contain a serial number, the identity property can be used to simplify the implementation. The primary benefit of the identity property is that SQL Server does the work to ensure that the number is incremented and inserted properly. But as with all beneficial features of a DBMS, the devil is in the details.  Let’s examine some of those details.

Column Properties

Identity is best classified a column property. Column properties are used by SQL Server to answer the questions:

1. Can this column contain nulls?
2. If nulls are not allowed, should SQL Server increment and insert a value for the column?

There are three basic column properties:

¨                  null – allows nulls in a column.

¨                  not null – does not allow nulls in a column. This forces the user to assign a specific value to a column (unless a default has been specified).

¨                  identity – does not allow nulls. Automatically defaults to the next highest number in ascending sequence.

This article will discuss the identity property only.

How Identity Works

The identity property is used to automatically generate sequential numbers for a column.  A column defined with the identity property is assigned the next sequential number whenever a row is inserted into the table. When data is inserted into the table, a value should not be included for the identity column. Instead, you should use the DEFAULT VALUES option (available with the INSERT statement). This enables SQL Server to generate the next sequential value for the identity column.

Data cannot be inserted directly into an identity column. One column per table can be assigned the identity property.  Additionally, the column must be one of the following data types:

• tinyint
• smallint
• integer
• decimal
• numeric(p,0) – the precision is flexible, but the scale must be zero (0)

At the simplest level the identity property generates numbers starting with 1 and incrementing by 1 for each insert. Of course, with database administration, it is always possible to complicate things, and the SQL Server identity function can get a little more complicated.  It is possible to start with a number other than 1 and it is also possible to increment by a number other than 1.  This is done when the table is created using the optional parameters of the identity property.  The identity property accepts two parameters, the first indicating the seed number and the second indicating the increment value.  If no parameters are specified, the default (1,1) is assumed.

To clarify this concept, consider the following three column definitions, for example:

RowId           smallint        identity

OtherId         integer         identity(100)

AnotherId       integer         identity(5,10)

The RowId column will start at 1 and increment by 1; the OtherId column will start at 100 and increment by 1; and the AnotherId column will start at 5 and increment by 10.

Special Situations

Although data cannot be inserted directly into an identity column as a general rule, it is possible to by-pass this rule. It may be necessary to specify a value to the identity column if, for example, a row was accidentally deleted, and the identity value needs to be re-created. To get the last identity value, use the @@identity global variable. This variable is accurate after an insert into a table with an identity column; however, this value is reset after an insert into a table with an identity column occurs. To allow an insert with a specific identity value, use the SET statement to set the IDENTITY_INSERT option ON.

Additionally, if an identity column exists for a table that has frequent deletions, gaps can occur in the sequence because the identity property will not re-generate values that have been used (even if they have been subsequently deleted). If you wish to avoid gaps in sequence at all costs, this may be a valid reason to avoid using the identity property.

To fill a gap in the sequence, you can analyze the existing identity values before explicitly entering one with the IDENTITY_INSERT option ON. Just query the table checking for gaps in the sequence for the identity column. Be sure to take into account the original seed value and the increment value. For example, if the increment value is 2 then there may appear to be gaps in the sequence because SQL Server is counting by 2, not because there are actual gaps.

If the column is referenced and a specific value is provided, then the identity property cannot automatically generate the next sequential value as desired.

Some Identity Advice

Instead of concocting an algorithm to create an ascending key, the identity property is a better choice. Columns assigned the identity property contain system-generated values that can uniquely identify each row within a table. It is automated and requires no additional application coding. However, be aware that it is not a panacea for planning and preparation.  For example, columns assigned the identity property can have repeating values unless a unique index has been created on that column.  This could result from an erroneous insert (instead of letting SQL Server calculate the next value by default).

One additional concern is how to identify which column in the table has been assigned the identity property. It is not necessary to remember—you can simply use the IDENTITYCOL keyword. When referencing data, use the keyword IDENTITYCOL in place of the identity column name. The IDENTITYCOL keyword can be used in an SQL data manipulation statement (SELECT, INSERT, UPDATE, DELETE) to reference an identity column.

SQL Server also enables users to find additional information about the identity property column via two system functions. The IDENT_SEED function returns the seed value specified during creation of an identity column and the IDENT_INCR function returns the increment value specified during creation of the identity column. Both of these may prove useful as you manage and manipulate data in columns assigned the identity property.

In alot of situations we find that we need to display data to the user that’s not actually in the database tables
for eamples let’s think about a table containing employees data

Create Table Employees
(ID int identity primary key , Name nvarchar(255) , isMarried bit )

insert into Employees Values ('John' , 0)
insert into Employees Values ('Debra' , 1)
insert into Employees Values ('Paul' , null)

Select ID , Name , isMarried From Employees

of course we don’t display numbers that represent marital status to the user
this we CASE comes in

Select ID , Name , Case isMarried
When 0 Then 'Single'
When 1 Then 'Married'
Else 'Unknown''
End

ok  pretty good   , CASE in SQL Server is much like Select Case in Visual Basic / Visual Basic .net or even like switch

in c / c++ / C# / php … etc . which means it can test the expression (Column) against single value such as 0 , 1 , 25 , ‘Pharo’ , GetDate() , etc .

what about ranges or even logical operators(and ,or , not) , well , in other Database Management systems such as Mysql there’s often an inline if statement , fortunately sql server offers a ‘Searched Case’ Statement which is equivalent

Create Table Products
(id int identity primary key , Name  nvarchar(255), Price decimal(15,5))
insert into Products Values('Optical Mouse' , 10)
insert into Products Values('Keyboard' , 20)
insert into Products Values('Speakers' , 60)
insert into Products Values('2gb Ram' , 120)
insert into Products Values('I7 CPU ' ,1500)

Select ID , Name , Price , 'Price Category' = Case
when Price <= 0 then 'free'

when Price > 0 and Price <=25 then 'First Category'

when Price > 25 and Price <= 70 then 'Second Category'

when Price > 70 and Price <= 150 then 'Third Category'

else 'Top  Category'  End

More Information about Case Statement ::

http://msdn.microsoft.com/en-us/library/ms181765.aspx

Here is mysql query , for retrieving the previous and next column values in a sequence, given a particular column value thisvalue

The previous value is the maximum value less than thisvalue, and the next value is the minimum value greater than thisvalue:

For Ex. We will get The Next and Previous ( ID =2 ) from MyTbl.

SELECT
  IF(ID > 2,'next','prev') AS Direction,
  IF(ID > 2,MIN(ID),MAX(ID)) AS 'Prev/Next'
FROM videos
WHERE ID <> 2
GROUP BY SIGN(ID - 2);

+———–+————+
| Direction | Prev/Next |
+———–+————+
| prev      | 1     |
| next      | 3     |
+———–+————+

and now we can make our code more easy, we will make A PROCEDURE

DELIMITER |
CREATE PROCEDURE PrevNext(
  IN db CHAR(64), IN tbl CHAR(64), IN col CHAR(64), IN seq INT
)
BEGIN
  SET @sql =
          CONCAT( "SELECT ",
          "IF(", col, " > ", seq,",'next','prev') AS Direction,",
          "IF(", col, " > ", seq, ",MIN(", col, "),MAX(", col, ")) AS 'Prev/Next'",
          "FROM ", db, ".", tbl,
          "WHERE ", col, " <> ", seq,
          "GROUP BY SIGN(", col, " - ", seq, ")" );
  PREPARE stmt FROM @sql;
  EXECUTE  stmt;
  DEALLOCATE PREPARE stmt;
END;
|
DELIMITER ;

call PrevNext('db', 'tbl', 'col', 'seq');

Enjoy.

Background ::
in SQL Server 2000 and Above we can use System Catalog View to Obtain Meta Data about our database
For Example we can list all of database tables using sys.tables .

select * From sys.tables 

and also we can list all indexes in a database using sys.indexes

select * From sys.indexes

we can join those together using a common column (object_id)

Select   I.Name 'Index Name'  , T.Name   'Table Name'
From  sys.Indexes I join sys.Tables T
On I.object_id = T.object_id
Where I.Name is not null and T.Name is not null

and easily we can generate a code to rebuild all indexes on our database

Select 'Alter Index ' +  I.Name  + ' On ' + T.Name  + ' Rebuild '
From  sys.Indexes I join sys.Tables T
On I.object_id = T.object_id
Where I.Name is not null and T.Name is not null

Execute this code in SSMS and it’ll rebuild all indexes .

Note //——> the code execution time may vary depending on your tables/indexes sizes so be carefull

be sure to use the following namespace

using System.Net;

we will create a method that sends out machine name and ip address as output parameters

void getMachineInfo(out string MachineName, out string IPAddress)
{
   MachineName= Dns.GetHostName();
   IPHostEntry iphostentry = Dns.GetHostByName(MachineName);
   IPAddress = iphostentry.AddressList[0].ToString();
}

you wanna call the method like :

string myMachineName , myIpAddress;
getMachineInfo(out myMachineName , out myIpAddress);
MessageBox.Show(string.Format("My Machine name :{0},My Ip Address :{1}",
myMachineName ,myIpAddress));

Note :
the IPHostEntry Class contains an Array of Ip Addresses which means that you can loop through it and obtain all available addresses of the machine (if any)

MachineName= Dns.GetHostName();
IPHostEntry iphostentry = Dns.GetHostByName(HostName);
foreach(IPAddress  myAddress in  iphostentry.AddressList)
{
      MessageBox.Show(myAddress.ToString());
}

MySQL remove html code from data .. here ‘s My Functions:

SET GLOBAL log_bin_trust_function_creators=1;
DROP FUNCTION IF EXISTS fnStripTags;
DELIMITER |
CREATE FUNCTION fnStripTags( Dirty varchar(4000) )
RETURNS varchar(4000)
DETERMINISTIC
BEGIN
  DECLARE iStart, iEnd, iLength int;
  WHILE Locate( '<', Dirty ) > 0 And Locate( '>', Dirty, Locate( '<', Dirty )) > 0 DO
    BEGIN
      SET iStart = Locate( '<', Dirty ), iEnd = Locate( '>', Dirty, Locate('<', Dirty ));
      SET iLength = ( iEnd - iStart) + 1;
      IF iLength > 0 THEN
        BEGIN
          SET Dirty = Insert( Dirty, iStart, iLength, '');
        END;
      END IF;
    END;
  END WHILE;
  RETURN Dirty;
END;
|
DELIMITER ; 

SELECT RemoveStripTags(‘<p>This Function will remove Paragraph Tage</p>’)   Text;
+—————————————————-+
| Test                                          |
+—————————————————-+
| This Function will remove Paragraph Tage  |
+—————————————————-+

Bill Gates gave a speech at a high school about 11 things they did not and will not learn at school. He talked about how feel-good, politically correct teachings created a generation of kids with no concept of reality and how this concept set them up for failure in the real world.

Rule 1. Life is not fair – get used to it!

Rule 2. The world won’t care about your self esteem. The world will expect you to accomplish something before you feel good about yourself.

Rule 3. You will NOT make $60,000 a year right out of high school. You won’t be a vice-president with a car phone til you earn both.

Rule 4. If you think your teacher is tough – wait til you get a boss!

Rule 5. Flipping hamburgers is not beneath your dignity. Your grandparents had a different word for hamburger flipping: it was called opportunity.

Rule 6. If you mess up its not your parents fault, so don’t whine about your mistakes; learn from them.

Rule 7. Before you were born your parents weren’t as boring as they are now. They got that way from paying your bills, cleaning your clothes, and listening to you talk about how cool you thought you were. So before you save the rainforest from the parasites of your parents generation, try delousing the closet in your own room.

Rule 8. Your school may have done away with winners and losers, but LIFE HAS NOT. In some schools they have abolished failing grades and they will give you as many times as you want to get the right answer. This doesn’t bear the SLIGHTEST resemblance to ANYTHING is real life.

Rule 9. Life is not divided into semesters.. You dont get summers off and very few employers are interested in helping you find yourself. Do that on your own time.

Rule 10. Television is not real life. In real life people actually have to leave the coffee shop and go to jobs.

Rule 11. Be nice to nerds. Chances are you will end up working for one