Thirulingam

Wednesday, September 30, 2020

How to Compare two tables for Column Names in SQL Server

--Provide the Schema and Table Names for comparison

DECLARE @Table1Schema VARCHAR(50) = 'dbo'

DECLARE @Table2Schema VARCHAR(50) = 'dbo'

DECLARE @Table1Name VARCHAR(50) = 'Table1'

DECLARE @Table2Name VARCHAR(50) = 'table2'

;

WITH CTE1

AS (

SELECT *

FROM information_schema.columns

WHERE table_schema = @Table1Schema

AND table_name = @Table1Name

)

,CTE2

AS (

SELECT *

FROM information_schema.columns

WHERE table_schema = @Table2Schema

AND table_name = @Table2Name

)

SELECT

--cte1.Table_Schema,cte1.Table_Name,cte1.Column_Name,

--cte2.Table_Schema,cte2.Column_Name,cte2.Table_Name,

IsNull(cte1.Column_Name, cte2.Column_Name) AS ColumnName

,CASE

WHEN cte1.Column_Name = cte2.Column_Name

THEN 'Exists in Both Tables ( ' + @Table1Name + ' , ' + @Table2Name + ' )'

WHEN cte1.Column_Name IS NULL

THEN 'Does not Exists in ' + @Table1Name

WHEN cte2.Column_Name IS NULL

THEN 'Does not Exists in ' + @Table2Name

END AS IsMatched

FROM CTE1

FULL JOIN cte2 ON cte1.Column_Name = cte2.Column_Name

Monday, June 29, 2020

Changing Attribute “type” from text to password in Jquery Textbox

Password Input Focus Event :

Password Input Blur Event :

Sample Html Page :


<html>
    <head>
        <title>Changing Attribute type from text to password</title>
        <style type="text/css">
        </style>
  <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
        <script type="text/javascript">
  $(document).ready(function() {
                   $("#password_input").focus(function() {    
       $("#password_input").prop('type','text'); 
                    });
                  $("#password_input").blur(function() {        
                   $("#password_input").prop('type','password');
                });
     });
        </script>
    </head>
    <body>
        <div id="loginBox">
            <form>
             username : <input type="text" id="username_input" name="username" /><br />
             password  :<input type="text" id="password_input" name="password" /> <br />
               
            </form>
        </div>
    </body>
</html>

Copy paste function on Textbox

Split 16 digit string into 4 parts and store them in an array in C#

For example, '1111222233334444".

Method 1:

string strNumber = "1111222233334444";

string []strArr = new string[4];

for(int i=0; i < 4; i++)
{
strArr[i] = strNumber.Substring(i*4, 4);

}

Method 2:
string initial_string = TextBox1.Text; //read from textbox

string [] number = new string[4];

number[0] = initial_string.Substring(0,4);
number[1] = initial_string.Substring(4,4);
number[2] = initial_string.Substring(8,4);

number[3] = initial_string.Substring(12,4);

Monday, June 22, 2020

How to use table variable in a dynamic sql statement?

create table #t (id int)

declare @value nchar(1)
set @value = N'1'

declare @sql nvarchar(max)
set @sql = N'insert into #t (id) values (' + @value + N')'

exec (@sql)

select * from #t

drop table #t

Wednesday, June 17, 2020

Row_Number() continue in Union all query

SELECT ROW_NUMBER() OVER (ORDER BY ...) AS Sno
FROM (
SELECT
...
UNION ALL
SELECT
...
) AS T
ORDER BY Sno

Thursday, February 13, 2020

write a C# program to count number of Vowels and Consonants in a string

public class Program
{
public static void Main(string[] args)
{
char[] sentence = new char[100];

int i, vowels = 0, consonants = 0, special = 0, n;
Console.WriteLine("Enter the Length of the sentence \n");
n = int.Parse(Console.ReadLine());
for (i = 0; i < n; i++)
{
sentence[i] = Convert.ToChar(Console.Read());
}
for (i = 0; sentence[i] != '\0'; i++)
{
if ((sentence[i] == 'a' || sentence[i] == 'e' || sentence[i] ==
'i' || sentence[i] == 'o' || sentence[i] == 'u') ||
(sentence[i] == 'A' || sentence[i] == 'E' || sentence[i] ==
'I' || sentence[i] == 'O' || sentence[i] == 'U'))
{
vowels = vowels + 1;
}
else
{
consonants = consonants + 1;
}
if (sentence[i] == 't' || sentence[i] == '\0' || sentence[i] == ' ')
{
special = special + 1;
}
}

consonants = consonants - special;
Console.WriteLine("No. of vowels {0}", vowels);
Console.WriteLine("No. of consonants {0}", consonants);
Console.ReadLine();
Console.ReadLine();
}
}
}

Enter the Length of the sentence

C#Programming

No. of vowels 3

No. of consonants 10

write a C# program to find number of occurences of a character in string

Using For Loop:

public class Program
{
public static void Main(string[] args)
{
string input = "Software@Solutions@Pvt$limited";

while (input.Length > 0)
{
Console.Write(input[0] + " : ");
int count = 0;
for (int j = 0; j < input.Length; j++)
{
if (input[0] == input[j])
{
count++;
}
}
Console.WriteLine(count);
input = input.Replace(input[0].ToString(), string.Empty);
}
Console.ReadLine();
}
}

or

string strconst="Software@Solutions@Pvt$limited";

var obj = strconst.GroupBy(x => x).Select( x =>new
{
Key= x.Key.ToString(),
Value = x.Count()
}).ToDictionary(t=>t.Key,t=>t.Value);
}
foreach (var t in obj)
{
Console.WriteLine($”{t.Key} has occurred {t.Value} times “);
}

Output:
S:3
O:3
F:1
t:3
w:1
a:1
r:1
e:2
@:2
l:2
u:1
i:3
n:1
p:1
v:1
$:1
m:1
d:1

Performance tuning SQL Server queries

Avoid VIEWs. Use them only when there are benefits of doing so. Do not abuse them.
Avoid too much JOINs on your query: use only what is necessary!
Avoid cursors at all costs!
Avoid to do much operations on your WHERE clause. If you are searching for a + 2 > 7, use a > 5 instead.
Avoid TRIGGERs. Use it only as a last resource. It is better to use CONSTRAINTs and STORED PROCEDUREs to maintain the integrity of your databases!

Don’t use the * in your queries. A SELECT * does an overload on the table, I/O and network bandwidth.
All columns involved in indexes should appear on WHERE and JOIN clauses on the same sequence they appear on index.
Verify if a critical query gains performance by turning it in a stored procedure.
Always restrict the number of rows and columns of your result. That way, you save disk, memory and network of the database server. Always verify your WHERE clause and use TOP if necessary.
Verify if your server isn’t suffering from not-enough-disk-space illness. Some times you lose time searching for all kind of problems only to find out that the server’s disk are almost full a few hours later. Always reserve at least 30% of available space on your disc.
SQL Server is case insensitive: he does not care about ‘A’ ou ‘a’. Save time and don’t use functions like LOWER and UPPER when comparing VARCHARs. (As pointed out by Aravind V Shibu, this depends on the collation of your server or table. Please check it before!)
The decreasing performance order of operators is: = (faster)>, >=, <, <=, LIKE, <> (slower)
If a query is slow and your index is not being used by it (remember to check your execution plan), you can force it using WITH(INDEX=index_name), right after the table declaration on the FROM clause.
Use EXISTS or NOT EXISTS instead of IN or NOT IN. IN operators creates a overload on database.
Try to use BETWEEN instead of IN, too.
When using LIKE operator, try to leave the wildcards on the right side of the VARCHAR.
Always avoid to use functions on your queries. SUBSTRING is your enemy. Try to use LIKE instead.
Queries with all operations on the WHERE clause connected by ANDs are processed from the left to right. So, if a operation returns false, all other operations in the right side of it are ignored, because they can’t change the AND result anyway. It is better then to start your WHERE clause with the operations that returns false most of the time.
Sometimes is better to make various queries with UNION ALL than a unique query with too much OR operations on WHERE clause. Test it.
When there is a HAVING clause, it is better to filter most results on the WHERE clause and use HAVING only for what it is necessary.
If there is a need of returning some data fast, even if it is not the whole result, use the FAST option.
Use, if possible, UNION ALL instead of UNION. The second eliminates all redundant rows and requires more server’s resources.
Use less subqueries. If you must use it, try to nest all of them on a unique block.
Use more variable tables and less temporary tables.
Use functions to reuse code. But don’t exaggerate on using them!
To delete all rows on a table, use TRUNCATE TABLE statement instead of DELETE.
If you have a IDENTITY primary key and do dozens of simultaneous insertions on in, make it a non-clusterized primary key index to avoid bottlenecks.
Now, some tips for the table structure. Sometimes it is necessary to make some alterations on the table design to extract more performance!
All tables should have a primary key. Except data warehouses and the like sometimes.
All tables should have a clusterized index, normally on the primary key.
Don’t be afraid to create non-clusterized indexes on most tables. Just be sure that you aren’t overindexing your tables! Too much indexes degrades insertions.
If you are creating an index, check the queries that are made against the table. Give preference to index columns that appear on most WHERE and JOIN clauses, and their order!

Monday, January 27, 2020

TFS - Moving to a new server. How to update solutions with new server info

To solve an issue.

Steps

    Edit EP2.sln in notepad. Change from SccTeamFoundationServer = http://150.150.145.123:8080/ to SccTeamFoundationServer = http://150.150.145.124:8080/ Save. Note: file will be marked as read only. Just uncheck Read only in file properties.
    Open VS (do not have any solutions open). View -> Team Explorer 3. Right click on the server node (150.150.145.124) and choose 'Disconnect'
    Click the 'Add existing team project' button and then click the 'Servers' button 5. Click on 'Add' to add a new server. Enter the IP address and leave the defaults. Click OK and then click 'Close'
    From the drop down, choose the new server (will be labeled as the IP you entered). Click the '(Select All)' check box and then click OK. The server information will start to refresh, wait until it's done.
    At this point, close Team Explorer window and open the solution. There will be a dialog "There appears to be a discrepancy between the solution's source control information about some project(s) and the information in the project file(s)". Just click OK and wait for the solution to load.

Thursday, October 24, 2019

Change IDENTITY_INSERT to ON in SQL server

Explicit identity insert require IDENTITY_INSERT property set to ON.

SET IDENTITY_INSERT MyTable ON -- Statement Allows explicit values to be inserted into
-- the identity column of a table.
GO

INSERT INTO MyTable(ID, Name, Description)
VALUES (0, 'Special title', 'special item');
GO

SET IDENTITY_INSERT MyTable OFF -- Statement revert granted permission
GO

Wednesday, October 23, 2019

Disable All the Foreign Key Constraint in Database – Enable All the Foreign Key Constraint in Database

-- Disable all the constraint in database

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- Enable all the constraint in database

EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

How to drop all tables from a database with one SQL query?

Use the INFORMATION_SCHEMA.TABLES view to get the list of tables. Generate Drop scripts in the select statement and drop it using Dynamic SQL:

DECLARE @sql NVARCHAR(max)=''

SELECT @sql += ' Drop table ' + QUOTENAME(TABLE_SCHEMA) + '.'+ QUOTENAME(TABLE_NAME) + '; '
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

Exec Sp_executesql @sql

Sys.Tables Version

DECLARE @sql NVARCHAR(max)=''

SELECT @sql += ' Drop table ' + QUOTENAME(s.NAME) + '.' + QUOTENAME(t.NAME) + '; '
FROM sys.tables t
JOIN sys.schemas s
ON t.[schema_id] = s.[schema_id]
WHERE t.type = 'U'

Exec sp_executesql @sql

Note: If you have any foreign Keys defined between tables then first run the below query to
disable all foreign keys present in your database.

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"