Fast query, but slow from Stored Procedure / Function- How query optimizer can screw your day
How parameter sniffing can destroy your query performance
sql
Setting Firing Order Of SQL Triggers
At times there is a need to control order of execution of triggers. This could be due to business requirement but also as a way to control history of a record I usually don’ t see people put any way to prevent trigger nesting, example update trigger updating same records would fire the trigger again.
SQL Rounding Error when calculating percentages
For cross compatability between sql and mysql servers when we do a division on a aggregated variable like count we need to promote the INT to a DOUBLE type by multiplying it by 1.00 otherwise our results will loose precision Examples 1/2=0 Incorrect Results 1.00/2=.5 or 1.00/2.00=.5 Good For sake of consistency both variables have …
Show last executed query SQL Server – Disected
I found this online, without explanation so I will dissect the query and explain what is happening. SELECT deqs.last_execution_time AS [TIME], dest.TEXT AS [Query] FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest ORDER BY deqs.last_execution_time DESCSELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query] FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest ORDER BY deqs.last_execution_time …
Why do I get a java.sql.SQLException: “Unable to get information from SQL Server” when trying to connect to an SQL Server instance?
Possible solution 1 I am using jtds driver so I would suggest checkingout their proposed solution first here http://jtds.sourceforge.net/faq.html#instanceGetInfo Possible solution 2 Their solution did not for me so here is what I did : From cmd prompt run sqlcmd -L and make sure that the server you are connecting is listed in the returned …
Remove/Change default constraints sql 2000/2005
Problem When we add default constraint on a field it is automatically assigned a name in following format Format: DF__tablename__PARTOFFIELDNAME__HASHCODE Example: DF__scheduled__CREAT__00DF2177 DF__scheduled__MODIF__01D345B0Format: DF__tablename__PARTOFFIELDNAME__HASHCODE Example: DF__scheduled__CREAT__00DF2177 DF__scheduled__MODIF__01D345B0 HashCode part of the format is different on each database so we can’t just find the name of constraint and use that in our alter script. That’s when …
Alter column size mysql – ms sql
Changing column size in both MySQL and MS SQL Server. When changing from wider to narrower size data might be truncated so be aware of that. MySQL ALTER TABLE workbook MODIFY NAME VARCHAR(255) ALTER TABLE workbook modify NAME varchar(255) MS SQL ALTER TABLE workbook ALTER COLUMN [NAME] VARCHAR(255) NULL GOALTER TABLE workbook ALTER COLUMN [NAME] …
Detect SQL server version
Sometime we need to provide one script that works on multiple server, here is how to do in for SQL Servers 2000 and 2005. We will use simple technique to detect the server type using the serverproperty(‘ProductVersion’) for a list of options visit SERVERPROPERTY (Transact-SQL). So lets see what are the pros and cons of …