Hi everybody,
Want to share two important commands for MS SQL:
SET STATISTICS IO ON
SET STATISTICS TIME ON
After you turn it on, you may see following in the output window:
with this ouptut window you may see how long it took actually to execute some query, and if added indexes improved or didn'...
Hello everybody,
today I want to leave SQL fix for error message:
"There are problems on database server side:
READ_COMMITTED_SNAPSHOT is not set for current database.
On your Acumatica instance it may look liike this:
For fixing run this SQL:
ALTER DATABASE Saddleback_staging SET READ_COMMITTED...
Hello everybody,
today I want to leave a post about the following case: you need to index rows in your database by some unique value, which can be null. And you don't want index to include those values which are null.
How to achieve it? Following T-SQL can do this:
CREATE UNIQUE INDEX idx_s...
Hello everybody,
today I want to document one hack that preserved me plenty of space on my hard drive.
Recently I've tried to make back up of production database and was shocked to see that it had almost 200 Gb on my hard drive. So it made me wonder how can I have smaller backups.
And...
Hello everybdoy,
today I want to write some notes about hierarchy research in ms sql database.
Imagine you have following structure in your table
TableWithParentChild:
id | rootID | ...
Hello everybody,
some sql notes:
dbcc freeproccache; this one will clean plan of cached of sql.
Another note of how to find Sql code and plan by id from dm_exec_cached_plans
create function SqlAndPlan(@handle varbinary(max))
returns table
as
return select sql.text, cp.usecounts,cp.cac...
Few days ago I faced following challenge. SQL profiler gave me ugly SQL, which was unreadable for my eyes.
For example like this:
exec sp_executesql N'SELECT APAddress.AddressID, APAddress.VendorID, APAddress.VendorAddressID, APAddress.IsDefaultAddress, APAddress.RevisionID, APAddress.AddressLine...
Today I needed to get all columns of specifict table.
SQL which you can use for this purpose is the following:
SELECT COLUMN_NAME 'All_Columns' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Specific table'