Data Tips & Tricks

On production environment if we want to run a script to add a column in a table it is better to add it safely, but in MySql there is no direct way to do this. Here safely means, if this column already exists in table ignore it otherwise add this new column. Check the below query to safely add column safely in mysql: Let's say we've a table "TEST_TO_DELETE" and we want to safely add new column "MY_NEW_COLUMN" -- Drop table if exists DROP TABLE IF EXISTS TEST_TO_DELETE; -- Create table CREATE TABLE TEST_TO_DELETE  (    Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,    FirstName VARCHAR(50) ); -- Step1. check if column exists or not SELECT COUNT(*) INTO @countOfColumn FROM `INFORMATION_SCHEMA`.COLUMNS  WHERE TABLE_SCHEMA = 'TEST'          AND TABLE_NAME = 'TEST_TO_DELETE'          AND COLUMN_NAME = 'MY_NEW_COLUMN'; -- Step2. if column exists then @countOfColumn > 0 and execute ...

MySql : Int(11), Int(11 +number) If we're using Int(11) as a data-type in MySql then by default it is signed integer range between (-2147483648) and (2147483647) It's same as any object oriented programming language's "Integer" datatype, means it'll take "4 byte" storage If we make this "Int(11) as unsigned number" then it ranges between (0 and 4294967295) Example:   1. First let's create a table   CREATE TABLE TableToDelete (   FullName VARCHAR(256) DEFAULT NULL,   SignedId INT(11) DEFAULT NULL,   UnsignedId INT(11) UNSIGNED DEFAULT NULL,   IdIntMoreThan11 INT(20) DEFAULT NULL,   IdZerofill INT(20) UNSIGNED ZEROFILL DEFAULT NULL )    2. Insert some records with value, let's say "1234567891234567" INSERT INTO TableToDelete (SignedId, UnsignedId, IdIntMorethan11, IdZerofill) VALUES (1234567891234567, 1234567891234567, 1234567891234567, 1234567891234567) because number : "1234567891234567" ex...

T-SQL is the term coined by Microsoft & Sybase collaboration. It's the proprietary extension of SQL of Microsoft & Sybase. T-SQL is the main RDBMS to manage & manipulate the data in sql server. Like SQL, you don't need to write strongly type T-SQL. SQL server will parse the T-SQL into sql then execute the parsed query on top of the relational database model. Let's try to understand the T-SQL 1. SQL is a standard of ISO & ANSI. T-SQL is a dialect of SQL. 2. Every database vendors (oracle, sybase..) implement the dialects of SQL as the main language to handle data in their database. Therefore core language element is same across all the database vendors. 3. SQL is strongly typed checked. Every SQL statement should end with semicolon (" ; ")                 T-SQL: it's not mandatory to end the every statement with " ; " semicolon.                 ...

The tempdb database is one of the system databases of SQL server, it is available for all users connected to that sql instance. " tempdb " is re-created every time the sql server instance is getting started. You don't have to physically move the data and log files. Sometimes we're getting the error that drive is full. Msg 9002, Level 17, State 6, Line 1 The log fie for database 'tempdb' is full. Backup the transaction log for the database to free up some log space. Let's see how to change the tempdb file path. Step1. First check the tempdb file path and name using below query USE tempdb; GO EXEC sp_helpfile; -- -- -- -- We'll get output like this name fileid filename filegroup size maxsize growth usage tempdev 1 \tempdb.mdf PRIMARY 8192...

We can create a table with the select command as well. General syntax to create a table with the select command is : SELECT * INTO <YourNewTable> FROM <YourTable> While using "SELECT * INTO " command we should keep following concepts in mind. Identity Columns if using simple select operation, new table will inherit the Identity property New table will not inherit the Identity property for following cases Identity columns are listed more than one time in select list Using multiple select operations with UNION or UNION ALL clause Identity column is a part of an expression If identity column is required in destination table but not available in source result-set you can define your own identity function e.g. -- -- Step 1. Create a table "Table_1" with identity column (ID) -- CREATE TABLE TABLE_1 (ID INT IDENTITY(1,1)...

Like operator is used to search a specific pattern in a column value. Syntax for like operator: match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ] match_expression: is a character expression such as a column field pattern: is a specific string of characters to search for match_expression It can be maximum of 8000 bytes Pattern can have these wild card Wildcard character Description % (percentage) 0 or more characters _ (underscore) 1 character [] (square bracket) any single character withing given range [defgh] or [d-h] [^] (square bracket with negate) any single character not within the specific range [^d-h] or [^defgh] ...

During .net development using Visual studio sometime we're getting "Can't load symbol" with red-mark, although project/solution is getting compiled fine & we're not getting any build or run-time error. Like this:  There could be multiple reasons & different solutions. Most common solution which generally works for me Go to Tool => Options Find for "Resharper ..." Click on "Suspend Now" & then "Ok" Your Option window will get closed Again Go to Tool => Optins Find the "Resharper..." Click on "Resume Now" & then "Ok" & we're done. Red mark of "Can't load symbol" is gone :) Here are the screen shots of above mentioned points step-by-step. Go to Tool => Option Find for "Resharper ..." Click on "Suspend Now" & then "Ok" Your Option window will get closed Again Go to Tool => Options Fi...