Breaking News

Editors Picks

Showing posts with label SQL SERVER. Show all posts
Showing posts with label SQL SERVER. Show all posts

Tuesday, June 19, 2012

distance between two latitude longitude points in mysql


SELECT (1000 * 6371 * ACOS(COS(RADIANS(23.2323)) * COS( RADIANS(LATITUDE))* COS(RADIANS(LONGITUDE) - RADIANS(77.7777))+ SIN(RADIANS(23.2323))* SIN(RADIANS(LATITUDE)))) AS distance FROM TableName HAVING distance < maxdis ORDER BY distance
Read more ...

Friday, March 16, 2012

Database Connection String


MySQL Connection String

MySQL ConnectionString using MySQL ODBC Driver

<add name="entaccess" connectionString="Driver={MySQL ODBC 3.51 Driver};SERVER=server_name; DATABASE=DataBase_Name;Port=3306;USER=uid;PASSWORD=pass;Connect Timeout=0; OPTION=3;"/>

MySQL ConnectionString using MySQL.Data.dll

<add name="MySQLConnectionString" connectionString="server=server_name; user id=root; password=pwd; database=databasename; pooling=false;default command timeout=3600;" providerName="MySql.Data.MySqlClient"/>


SQLServer Connection String

SQLServer ConnectionString using sqlserver authentication mode

<add name="SQLConnectionString" connectionString="server= Data Source= server_name;Initial Catalog= DataBase_Name;User Id=myUsername;Password=myPassword; " providerName="System.Data.SqlClient"/>

<add name="SQLConnectionString" connectionString="server= Data Source= server_name; Database = DataBase_Name;User Id=myUsername;Password=myPassword; " providerName="System.Data.SqlClient"/>


SQLServer ConnectionString using Windows authentication mode

<add name="SQLConnectionString" connectionString="server= Data Source= server_name;Initial Catalog= DataBase_Name; Integrated Security=True;" providerName="System.Data.SqlClient"/>

<add name="SQLConnectionString" connectionString="server= Data Source= server_name; Database = DataBase_Name; Integrated Security=SSPI;" providerName="System.Data.SqlClient"/>
Attach a database file, located in the data directory
<add name="SQLConnectionString" connectionString=" Server=.\SQLExpress;AttachDbFilename=|DataDirectory|dbfile.mdf; Database=dbname;Trusted_Connection=Yes providerName="System.Data.SqlClient"/>

Oracle Connection String

<add name="ConnectionString" connectionString="Data Source=servername;Persist Security Info=True;User ID= uid;Password= passowrd;Unicode=True;"/>

    <add name="ConnectionString" connectionString="Data Source=servername;Persist Security Info=True;User ID= id;Password= pwd;"/>


Read more ...

Sunday, July 18, 2010

count columns table in sql server

1) you can get the number of
columns in a table using SQL?


SELECT count(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Your Table Name'


2) Count Duplicate Records – Rows

SELECT Your_Column_Name, COUNT(*) Total_Count FROM Your_Table_name
GROUP BY Your_Column_Name
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

3) The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:

SELECT COUNT(column_name) FROM table_name

4)The COUNT(*) function returns the number of records in a table:

SELECT COUNT(*) FROM table_name


Read more ...

Monday, June 28, 2010

Maximum Capacity Specifications for SQL Server












Maximum Sizes/Numbers
Object
SQL Server 7.0
SQL Server 2000
SQL Server 2005 (32-bit)
Batch size
65,536 * Network Packet Size1
65,536 * Network Packet Size1
65,536 * Network Packet Size1
Bytes per short string column
8,000
8,000
8,000
Bytes per text, ntext, or image column
2 GB-2
2 GB-2
2 GB-2
Bytes per GROUP BY, ORDER BY
8,060
8,060
8,060
Bytes per index
900
9002
9002
Bytes per foreign key
900
900
900
Bytes per primary key
900
900
900
Bytes per row
8,060
8,060
8,0608
Bytes in source text of a stored procedure
Lesser of batch size or 250 MB
Lesser of batch size or 250 MB
Lesser of batch size or 250 MB
Bytes per VARCHAR(MAX), VARBINARY(MAX),XML, TEXT, or IMAGEcolumn
N/A
N/A
2^31-1
Characters per NTEXT or NVARCHAR(MAX)column.
N/A
N/A
2^30-1
Clustered indexes per table
1
1
1
Columns in GROUP BY, ORDER BY
Limited only by number of bytes per GROUP BY, ORDER BY
Limited only by number of bytes per GROUP BY, ORDER BY
Limited only by number of bytes per GROUP BY, ORDER BY
Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement
10
10
10
Columns per index
16
16
167
Columns per foreign key
16
16
16
Columns per primary key
16
16
16
Columns per base table
1,024
1,024
1,024
Columns per SELECT statement
4,096
4,096
4,096
Columns per INSERT statement
1,024
1,024
1,024
Connections per client
Maximum value of configured connections
Maximum value of configured connections
Maximum value of configured connections
Database size
1,048,516 TB
1,048,516 TB
1,048,516 TB
Databases per instance of SQL Server
32,767
32,767
32,767
Filegroups per database
256
256
32,767
Files per database
32,767
32,767
32,767
File size (data)
32 TB
32 TB
16 TB
File size (log)
4 TB
32 TB
2 terabytes
Foreign key table references per table4
253
253
253
Identifier length (in characters)
128
128
128
Instances per computer
N/A
16
16
Length of a string containing SQL Statements (batch size)
65,536 * Network packet size 1
65,536 * Network packet size 1
65,536 * Network packet size 1
Locks per connection
Maximum locks per server
Maximum locks per server
Maximum locks per server
Locks per instance of SQL Server
2,147,483,647 (static) 40% of SQL Server memory (dynamic)
2,147,483,647 (static) 40% of SQL Server memory (dynamic)
Up to 2,147,483,6475
Nested stored procedure levels6
32
32
32
Nested subqueries
32
32
32
Nested trigger levels
32
32
32
Nonclustered indexes per table
249
249
249
Objects concurrently open in an instance of SQL Server3
2,147,483,647 (or available memory)
2,147,483,647 (or available memory)
Objects in a database
2,147,483,6473
2,147,483,6473
2,147,483,6473
Parameters per stored procedure
1,024
1,024
2,100
Parameters per user-defined function
1,024
1,024
2,100
Partitions per partitioned table or index
N/A
N/A
1,000
REFERENCES per table
253
253
253
Rows per table
Limited by available storage
Limited by available storage
Limited by available storage
Statistics on non-indexed columns
2,000
Tables per database
Limited by number of objects in a database3
Limited by number of objects in a database3
Limited by number of objects in a database3
Tables per SELECT statement
256
256
256
Triggers per table
Limited by number of objects in a database3
Limited by number of objects in a database3
Limited by number of objects in a database3
UNIQUE indexes or constraints per table
249 nonclustered and 1 clustered
249 nonclustered and 1 clustered
249 nonclustered and 1 clustered
XML indexes
N/A
N/A
249


Read more ...

Contact Us

Name

Email *

Message *