SQL Server 2008 Data Types And Sizes

SQL Server Compact 3.5 supports the following data types.

Data Type	Description
bigint	Integer (whole number) data from –2^63 (–9,223,372,036,854,775,808) through 2^63–1 (9,223,372,036,854,775,807). Storage size is 8 bytes.
integer	Integer (whole number) data from –2^31 (–2,147,483,648) through 2^31–1 (2,147,483,647). Storage size is 4 bytes.
smallint	Integer data from –32,768 to 32,767. Storage size is 2 bytes.
tinyint	Integer data from 0 to 255. Storage size is 1 byte.
bit	Integer data with a value of either 1 or 0. Storage size is 1 bit.
numeric (p, s) Synonyms: decimal(p,s) and dec (p,s)	Fixed-precision and scale-numeric data from –10^38+1 through 10^38–1. The p variable specifies precision and can vary between 1 and 38. The s variable specifies scale and can vary between 0 and p. Storage size is 19 bytes.
money	Monetary data values from (–2^63/10000) (–922,337,203,685,477.5808) through 2^63–1 (922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit. Storage size is 8 bytes.
float	Floating point number data from –1.79E +308 through 1.79E+308 Storage size is 8 bytes.
real	Floating precision number data from –3.40E+38 through 3.40E+38. Storage size is 4 bytes.
datetime	Date and time data from January 1, 1753, to December 31, 9999, with an accuracy of one three-hundredth second, or 3.33 milliseconds. Values are rounded to increments of .000, .003, or .007 milliseconds. Stored as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system's reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight. Seconds have a valid range of 0–59. Format Example yyyy/mm/ddhh:mm:ss1947/08/15 03:33:20 mm/dd/yyyyhh:mm:ss04/15/1947 03:33:20 dd mmm yyyy hh:mm:ss15 Jan 1947 03:33:20 dd mmmm yyyy h:mm:ss15 January 1947 03:33:20
national character(n) Synonym:nchar(n)	Fixed-length Unicode data with a maximum length of 4000 characters. Default length = 1. Storage size, in bytes, is two times the number of characters entered.
national character varying(n) Synonym:nvarchar(n)	Variable-length Unicode data with a length of 1 to 4000 characters. Default length = 1. Storage size, in bytes, is two times the number of characters entered.
ntext¹	Variable-length Unicode data with a maximum length of (2^30–2)/2 (536,870,911) characters. Storage size, in bytes, is two times the number of characters entered. Note: ntext is no longer supported in string functions.
nchar	Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. The storage size is two times n bytes.
binary(n)	Fixed-length binary data with a maximum length of 8000 bytes. Default length = 1. Storage size is fixed, which is the length in bytes declared in the type.
varbinary(n)	Variable-length binary data with a maximum length of 8000 bytes. Default length = 1. Storage size varies. It is the length of the value in bytes.
image¹	Variable-length binary data with a maximum length of 2^30–1 (1,073,741,823) bytes. Storage is the length of the value in bytes.
uniqueidentifier	A globally unique identifier (GUID). Storage size is 16 bytes.
IDENTITY [(s, i)]	This is a property of a data column, not a distinct data type. Only data columns of the integer data types can be used for identity columns. A table can have only one identity column. A seed and increment can be specified and the column cannot be updated. s (seed) = starting value i (increment) = increment value
ROWGUIDCOL	This is a property of a data column, not a distinct data type. It is a column in a table that is defined by using the uniqueidentifier data type. A table can have only one ROWGUIDCOL column.
Timestamp/rowversion	This is an automatically generated unique binary number. Storage size is 8 bytes.

¹Ntext and image data is stored in a new data page when the number of bytes exceeds 256 in SQL Server Compact 3.5. This can affect the extent of compactness in a database, because SQL Server Compact 3.5 databases are compacted page-wise and not byte-wise.
For more information about SQL Server 2008 data types that require conversion in SQL Server Compact 3.5, see Differences Between SQL Server Compact and SQL Server.

CHECK Constraints

CHECK constraints enforce domain integrity by limiting the values that are accepted by a column. They are similar to FOREIGN KEY constraints in that they control the values that are put in a column. The difference is in how they determine which values are valid: FOREIGN KEY constraints obtain the list of valid values from another table, and CHECK constraints determine the valid values from a logical expression that is not based on data in another column. For example, the range of values for a salary column can be limited by creating a CHECK constraint that allows for only data that ranges from $15,000 through $100,000. This prevents salaries from being entered beyond the regular salary range.
You can create a CHECK constraint with any logical (Boolean) expression that returns TRUE or FALSE based on the logical operators. For the previous example, the logical expression is: salary >= 15000 AND salary <= 100000.
You can apply multiple CHECK constraints to a single column. You can also apply a single CHECK constraint to multiple columns by creating it at the table level. For example, a multiple-column CHECK constraint can be used to confirm that any row with a country/region column value of USA also has a two-character value in the state column. This allows for multiple conditions to be checked in one location.

Caution
Constraints that include implicit or explicit data type conversion may cause certain operations to fail. For example, such constraints defined on tables that are sources of partition switching may cause an ALTER TABLE...SWITCH operation to fail. Avoid data type conversion in constraint definitions.

Limitations of CHECK Constraints

---

CHECK constraints reject values that evaluate to FALSE. Because null values evaluate to UNKNOWN, their presence in expressions may override a constraint. For example, suppose you place a constraint on an int column MyColumn specifying that MyColumn can contain only the value 10 (MyColumn=10). If you insert the value NULL into MyColumn, the Database Engine inserts NULL and does not return an error.
A CHECK constraint returns TRUE when the condition it is checking is not FALSE for any row in the table. If a table that has just been created does not have any rows, any CHECK constraint on this table is considered valid. This situation can produce unexpected results, as in the following example.

CREATE TABLE CheckTbl (col1 int, col2 int);
GO
CREATE FUNCTION CheckFnctn()
RETURNS int
AS 
BEGIN
   DECLARE @retval int
   SELECT @retval = COUNT(*) FROM CheckTbl
   RETURN @retval
END;
GO
ALTER TABLE CheckTbl
ADD CONSTRAINT chkRowCount CHECK (dbo.CheckFnctn() >= 1 );
GO

The CHECK constraint being added specifies that there must be at least one row in table CheckTbl. However, because there are no rows in the table against which to check the condition of this constraint, the ALTER TABLE statement succeeds.
CHECK constraints are not validated during DELETE statements. Therefore, executing DELETE statements on tables with certain types of check constraints may produce unexpected results. For example, consider the following statements executed on table CheckTbl.

INSERT INTO CheckTbl VALUES (10, 10)
GO
DELETE CheckTbl WHERE col1 = 10;

The DELETE statement succeeds, even though the CHECK constraint specifies that table CheckTbl must have at least 1 row.

Disabling Browser Caching In VB and ASP.NET

The majority of advice on how to disable browser caching seems to revolve around how to make it work in IE.  However, in Firefox the fixes don't always seem to work.  Below are some notes and sample code on how to make it work for both browsers:

The SetNoStore() seems to be a necessity for FireFox 
The HttpCacheability.NoCache has an issue in IE (not sure about FireFox) when you are on a secure (https) page and are trying to send a file by setting the headers. It causes the file download to not work properly. 
SetValidUntilExpires(), SetRevalidation() – unsure if this is a requirement at this time. Was added because it was still giving problems in FireFox before SetNoStore() was found. 
SetExpires() I tried to set this to 5 days in the past and I still got the 2 second window.

Public Shared Sub DisablePageCaching()

 'Used for disabling page caching 
 HttpContext.Current.Response.Cache.SetExpires(DateTime.UtcNow.AddDays(-1))
 HttpContext.Current.Response.Cache.SetValidUntilExpires(False)
 HttpContext.Current.Response.Cache.SetRevalidation(HttpCacheRevalidation.AllCaches)

 HttpContext.Current.Response.Cache.SetAllowResponseInBrowserHistory(False)
HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache)
 HttpContext.Current.Response.Cache.SetNoStore()

End Sub

How to manually upgrade Nexus S from Android 2.3.6 to Android 4.0 Ice Cream Sandwich

If you own T-Mobile’s variant of Samsung Nexus S (i9020t) and don’t want to wait for the official OTA Android 4.0.3 update, you can install it manually. The procedure below will upgrade stock Android 2.3.6 build GRK39F to Android 4.0.3 build IML74K.

With a rooted phone the easiest update process is to use ROM Manager (Download ROM -> Stock Images -> Nexus S 4.0.3).

If you don’t have root, follow these manual steps (if you are new to the update process, check out this slightly outdated, but detailed update guide).

• Download the update package.
• Rename downloaded file to update.zip.
• Copy the update.zip file to the phone’s internal storage.
• Turn the phone off, press and hold Volume Up and Power to boot into the bootloader.
• Using Volume Up/Down select “recovery” and press Power button.
• After the exclamation point in a triangle appears, hold the Power button and press Volume Up.
• Using Volume Up/Down select “apply update from /sdcard” and choose the update.zip.
• After the update process is finished, select “reboot system now” and press Power.

Enjoy!

Get Create Date and Modified Date of SQL Server 2008 Database Object

SQL Query:

SELECT name       ,create_date       ,modify_dateFROM   sys.objectsWHERE  type = 'U'ORDER  BY name 

Object type:

AF = Aggregate function (CLR)

C = CHECK constraint

D = DEFAULT (constraint or stand-alone)

F = FOREIGN KEY constraint

FN = SQL scalar function

FS = Assembly (CLR) scalar-function

FT = Assembly (CLR) table-valued function

IF = SQL inline table-valued function

IT = Internal table

P = SQL Stored Procedure

PC = Assembly (CLR) stored-procedure

PG = Plan guide

PK = PRIMARY KEY constraint

R = Rule (old-style, stand-alone)

RF = Replication-filter-procedure

S = System base table

SN = Synonym

SQ = Service queue

TA = Assembly (CLR) DML trigger

TF = SQL table-valued-function

TR = SQL DML trigger

TT = Table type

U = Table (user-defined)

UQ = UNIQUE constraint

V = View

X = Extended stored procedure

No template information found - Visual Studio

When I'm trying to create a new project in Visual Studio, I got the following Error Message.

"No template information found. See the application log in Event Viewer for more details. To open Event Viewer, click Start, click Control Panel, double-click Administrative Tools, and then double-click Event Viewer."

Then I checked My Event Viewer, here i found a warning message logged by Visual Studio Application with following description.

The global template information is out of date. Regenerate the templates by running 'devenv.exe /installvstemplates' or reinstalling the application. Note: corrective action requires Administrator privileges.

For more information, see Help and Support Center athttp://go.microsoft.com/fwlink/events.asp.

To Fix My Error, I run the command devenv.exe /installvstemplates thru command prompt, as suggested in the Log. It helps me to fix my problem. Now i can able to create a projects thru Visual studio.

Hope it helps you too. still if you face the same problem, then you've to re install the application

Connect to USB based System PC Internet Connect with Reverse Tether Android App

The Android phones can be best enjoyed when you are connected to web. Without web your Android phone is just a phone for calls, games and messages. Social network, various applications, widget requires access to internet via anything of 2G, 3G or 4G LTE. In United States data plans are much higher and we know we need internet on our mobile phone and the only possible sources are to get Wi Fi or get a data plan.

If you have a data plan active, you can make use of Wi Fi tethering or Bluetooth tethering to connect your laptop or computer with internet but what if you don’t have internet on your mobile phone and desire to share your laptop connection with your phone? Is it possible? I would say yes its possible, earlier the Connectify software used to do it easily but it’s not working now. Thanks to Reverse Tether Android application which I have just found to do this trick for you.

Reverse Tether an Android application allows you to access your personal computer internet connection on your smartphone using the USB. We have seen so many applications to USB tether your device with the PC but this is the only application in the Android Play Store which does such a thing.

How does Reverse Tether Works?

Reverse Tethering works by simple connecting your phone to the computer using a USB cable. The speed of the internet that you get depends on your mobile phone is almost same as that of what you used and your phone is using the broadband internet hence it will be much faster.

This application is quite useful when –

• You don’t have a mobile data plans
• When you have a limited data plans
• When you need to upgrade via OTA update
• If you are in roaming and likes to avoid roaming data charges
• If you don’t trust the security or the privacy of your Wi Fi network
• If you Wi Fi connection is weak or slow or get disconnected all the time
• If you don’t want to visit the burger or bakery just for the sake of internet access
• Prefer to get maximum download and upload speed

This application is quite easy but there are few bad thing is that this application requires rooting access on your mobile phone.

As soon as you setup and run this application on to your mobile phone, you would be taken with the set up your phone connection. Tap on Next. This will ask for the USB Connection and if you have connected your phone to the USB then well and fine.

In the next step you will see the tutorial where you see the connection, how to manage it on your computer. Tap on Next.

Now you are ready just tap on Start.

In the next screen, you need to tap on connect as seen on the top right side. It will further ask for the user permissions, give it.

Once you do that you are now connected with an active internet.

Reverse Tether Android Application Download

Reverse Tether Android application can be easily downloaded from Android Apps Labs for free. To download it directly on to your mobile phone, you need to visit the Android Apps Labs page listing of Reverse Tether application from your phone’s browser and then click on Install to automatically proceed with the installation.

Alternatively one can download this application using the QR code mentioned below. You need to open any bar code scanning application from your mobile phone and then point the camera on to the following square –

Verdict

Reverse Tether is the only application of its kind and sometime you might need this once while when your PC is not working. The application works great and easy to understand.

Android Advices Application Rating – 4 / 5

Merge in Oracle

MERGE INTO TBL_MenU b
USING (
  SELECT TRANNAME,TRANID
  FROM TBL_TRANS
   ) e
ON (b.MenuTEXT = e.TRANNAME)
WHEN MATCHED THEN
  UPDATE   SET b.MENUURL =   CONCAT( 'GuideMain.aspx?TransId=',     e.TRANID)
 

Generate create table script using a SQL Query

select  'create table [' + so.name + '] (' + o.list + ')' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE ' + so.Name + ' ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ')' END
from    sysobjects so
cross apply
    (SELECT
        '  ['+column_name+'] ' +
        data_type + case data_type
                when 'sql_variant' then ''
                when 'text' then ''
                when 'decimal' then '(' + cast(numeric_precision_radix as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
                else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end + ' ' +
        case when exists (
        select id from syscolumns
        where object_name(id)=so.name
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1
        ) then
        'IDENTITY(' +
        cast(ident_seed(so.name) as varchar) + ',' +
        cast(ident_incr(so.name) as varchar) + ')'
        else ''
        end + ' ' +
         (case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' +
          case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', '

     from information_schema.columns where table_name = so.name
     order by ordinal_position
    FOR XML PATH('')) o (list)
left join
    information_schema.table_constraints tc
on  tc.Table_name               = so.Name
AND tc.Constraint_Type  = 'PRIMARY KEY'
cross apply
    (select '[' + Column_Name + '], '
     FROM       information_schema.key_column_usage kcu
     WHERE      kcu.Constraint_Name     = tc.Constraint_Name
     ORDER BY
        ORDINAL_POSITION
     FOR XML PATH('')) j (list)
where   xtype = 'U'
AND name        NOT IN ('dtproperties')
AND so.name='MIndoExpenses'

Here MIndoExpenses is the table name for which Create Table script is generated.

SQL SERVER – Alternate Fix : ERROR 1222 : Lock request time out period exceeded

Here is the script that can kill locks

Create Table #Tmp
(
spid smallint,
ecid smallint,
status nchar(30),
loginame nchar(128),
hostname nchar(128),
blk char(5),
dbname nchar(128),
cmd nchar(16)
)

Create Table #TmpLocks
(
spid smallint,
dbid smallint,
ObjId int,
IndId smallint,
Type nchar(4),
Resource nchar(16),
Mode nvarchar(8),
Status nvarchar(28)
)

Insert Into #Tmp
Exec sp_who

Insert Into #TmpLocks
Exec sp_lock

If(Select Count(*) From #Tmp T
Join #TmpLocks TL On T.spid = TL.spid
Where /*This is for tempdb*/ dbid = 2 And objid In (1, 2, 3)) > 0

Then you can kill the concerned spid with the command :

Kill — The concerned spid

Drop Table #Tmp
Drop Table #TmpLocks