Create a table named 'select' with column 'table'

SET QUOTED_IDENTIFIER ON
CREATE TABLE "SELECT" ("TABLE" int)

When SET QUOTED_IDENTIFIER is ON, all strings delimited by double quotation marks are interpreted as object identifiers. Therefore, quoted identifiers do not have to follow the Transact-SQL rules for identifiers. They can be reserved keywords and can include characters not usually allowed in Transact-SQL identifiers

Reference: http://msdn.microsoft.com/en-us/library/aa259228(v=sql.80).aspx

or

CREATE TABLE [SELECT] ([TABLE] INT)

Customize sql startup query window from:

C:\Users\My-PC\AppData\Local\VirtualStore\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SsmseeTemplates\Sql\SqlFile.sql

(Chclt)

C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\SqlFile.sql

Replace multiple spaces with one/none in sql server

select replace(replace(replace(LTrim(RTrim('      6      Spaces  6      Spaces.      ')),'  ',' |'),'| ',''),
--'|','')
' |','')

select replace(
replace(
replace(
LTrim(RTrim('      6      Spaces  6      Spaces.      '))
,' ',' |')
,'| ',''),
' |',' ')

--Trim the field
--Mark double spaces
--Delete double spaces offset by 1
--Tidy up

sort array of int values without using built-in functions (using Bubble sort)

string res = "";
int[] arr = { 800, 11, 50, 771, 649, 770, 240, 9 };
for (int k = 0; k < arr.Length; k++)
{
for (int i = 0; i < arr.Length - 1; i++)
{
if (arr[i] > arr[i + 1])
{
int hold = arr[i + 1];
arr[i + 1] = arr[i];
arr[i] = hold;
}
}
res += arr[k].ToString();
arr[k] = arr[k];
}

string s = "1,2,3,,3,2";
string[] ss = (s.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries));
for (int k = 1; k < ss.Length; k++)
{
for (int i = 0; i < (ss.Length - 1); i++)
{
if (Convert.ToInt32(ss[i].Trim()) > Convert.ToInt32(ss[i + 1].Trim()))
{
string hold = ss[i];
ss[i] = ss[i + 1];
ss[i + 1] = hold;
}
}
}
s = string.Join(",", ss);

split string in c# with regular expression instead of forloop

string s = "er32(s),re4'wr(a),f2.0(t)A(y),(a)";
        s = Regex.Replace(s, @"[\w\s';.&,:]+\(([\w\d.]+)\)", "$1");
        Response.Write(s);

Convert column to row in SQL Server

select (select convert(varchar(max),id)+',' from #tbl1 for xml path('')) colTOrow

Get the html control to asp without using runat='server' property

string strValue = Page.Request.Form["name of the control"].ToString();

How to get the current row in the grid view rowcommand event

GridViewRow row = ((LinkButton)e.CommandSource).NamingContainer as GridViewRow;

In RowDataBound for datakeys

GridView1.DataKeys[e.Row.RowIndex].Value.ToString()

((DataRowView)e.Row.DataItem)["mainid"].ToString()

persistedselection property for a gridview control in asp

You can specify how row selection is persisted when the GridView control is in paging mode. By default, row selection is persisted by row index. For example, if the second row in a page is selected, the second row in all pages will be selected.

Alternatively, you can specify that row selection is persisted by data-row key. In that case, when a row is selected, rows in other pages are not selected. To enable this functionality, set the EnablePersistedSelection property to true as shown in the following example:

<asp:GridView id="GridView1" runat="server" PersistedSelection="true">
</asp:GridView>

datarow to dataset in asp

DataTable dt = (DataTable)ViewState["tabData"];
DataRow[] dr = dt.Select("test_cd='899'");

DataSet dsnew = new DataSet();
DataTable dtnew = dt.Clone();
dtnew.Constraints.Clear();
DataRow drnew = dtnew.NewRow();

drnew.ItemArray = dr[0].ItemArray;
dtnew.Rows.Add(drnew);
dsnew.Tables.Add(dtnew);

Query to attaching of database using only .mdf file without associated log file

CREATE DATABASE ... FOR ATTACH_REBUILD_LOG

with log file: CREATE DATABASE ... FOR ATTACH

Order of the sqlquery that logically processed

What is the order of execution in the following list?

• select


• from


• where


• group by


• order by


• having

A: from,where,group by,having,select,order by

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

Rename/Alter the column for a table in sqlserver

To add new column: Alter Table TableName add ColName dataType

To alter a column: Alter Table TableName Alter Column ColumnName DataType NULL/NOT NULL

To rename a column:

EXEC sp_rename
@objname = ' TableName. OldColumnName’,
@newname = 'New ColumnName',
@objtype = 'COLUMN'

note: computed columns(col3=col1+col2) doesnt hav a chance to rename, the only way is it has to be dropped and added back

Ex: Alter Table tblAddress Alter Column Address VARCHAR(512) NULL

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

To rename the table name:

sp_RENAME 'TestTable', 'NewTable'

can u set innerjoin for update command?

Update Employee set State = s.StateID  
from Employee e  
inner join State s on e.State = s.StateName

Decide whether i/p parameter contains integers or not in sqlserver

IF PATINDEX('%[0-9]%',@val) > 0  
print 'i/p value contain integer'
else
print 'Not contain integer'