Profile cover photo
Profile photo
Yuri Abele
47 followers
47 followers
About
Posts

Post has attachment
MSSQL - How to clear all caches?
Script to clear all caches? (For example for performance debug) SET NOCOUNT ON;
GO
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
DBCC FREESYSTEMCACHE ('ALL');
GO
DECLARE @StartedAt DATETIME2 = GETDATE();
-- Query
PRINT ' Elapsed time: ' + CAST(DATEDIFF(ms, @Sta...
Add a comment...

Post has attachment
MSSQL - T-SQL equivalent of VBA VAL() function
There the text of scalar-value function to extract numbers from string and convert to integer: IF OBJECT_ID(N'dbo.fn_extract_digits') IS NOT NULL
DROP FUNCTION dbo.fn_extract_digits;
GO

CREATE FUNCTION dbo.fn_extract_digits (@str VARCHAR(MAX))
RETURNS IN...
Add a comment...

Post has attachment
MSSQL: Function to generate empty rows
There the text of inline table-value function to generate a specified count of empty rows: IF OBJECT_ID(N'dbo.GenerateRows') IS NOT NULL
DROP FUNCTION dbo.GenerateRows;
GO
CREATE FUNCTION dbo.GenerateRows (@Count INT)
RETURNS TABLE
AS
RETURN
WITH TenRows ...
Add a comment...

Post has attachment
MSSQL: How to replicate to readonly database?
First, I have to apologize, title of article lies - it is unfortunately impossible to set up replication into the READ-ONLY database. One of the reasons why we want to have the target database is read-only, is the need to avoid direct changes of data in it....
Add a comment...

Post has attachment
MSSQL - Immediately PRINT big messages - max 8000 bytes (4000 unicode characters)
Transact-SQL function PRINT has two disadvatages: - is limited for 8000 bytes - 8000 VARCHAR characters or 4000 NVARCHAR (unicode) characters - does not send messages immediately to the client, usually user have to wait until the procedure is complete befor...
Add a comment...

Post has attachment
MSSQL - Convert speсial types to strings or HTML:<br/>MONEY to string, DATETIME to string, GUID or UNIQUEIDENTIFIER to string, VARBINARY or BINARY to string, XML to string, TIMESTAMP to string
Most of datatypes is relative simple convert to a string. For this we can use the CONVERT() or even the CAST() function: SELECT int_as_string = CONVERT(VARCHAR(20), int_field) FROM MyTable;

SELECT int_as_string = CAST(int_field AS VARCHAR(20)) FROM MyTabl...
Add a comment...

Post has attachment
MSSQL - Kill all my processes (except current)
Template to kill all my processes (connections) except current USE master
GO

DECLARE @sql VARCHAR(MAX) = '-- Kill all my processes (except current: ' + CAST(@@SPID AS VARCHAR(20)) + ')';
SELECT @sql = @sql + CHAR(13) + CHAR(10) + 'kill ' + CONVERT(varchar(...
Add a comment...

Post has attachment
MSSQL - T-SQL stored procedure to replace placeholders in texts
It is often necessary to make the query text from constant pieces and variables. For example we have a many tables with such structure: (
ID INT IDENTITY(1,1) PRIMARY KEY,
Number INT,
ChangeDate DATETIME
) and we need dynamically, depending of v...
Add a comment...

Post has attachment
**
To get all list of all script objects (VIEWs, SPs, UDFs) which are depending on some other script object we can use help of sys.sql_expression_dependencies system view: SELECT
object_name = OBJECT_NAME(D.referencing_id),
O.type,
O.type_desc
FROM
sys.sql...
Yuri's Corner
Yuri's Corner
yabele.blogspot.com
Add a comment...

Post has attachment
MSSQL - Very fast method to get statistic (rows and bytes) for all database tables
Typical way to count all rows in the table ist to send such query: SELECT COUNT(*) FROM MyTable; This method has some disadvantages: We can only get a rows count, but not a physical size of table This query works on one table, name of which we have to defin...
Add a comment...
Wait while more posts are being loaded