If you have problem with your database server performance and you do not know where to start your investigation you can use below query to have some clue. SELECT TOP 20 qs.total_elapsed_time / qs.execution_count / 1000000.0 AS AverageSecond ,qs.total_elapsed_time /… Read More ›
SQL Server
Update from in SQL Server (lookup)
USE Arm_DB Go CREATE TABLE TBL_ARM1 ( ID INT IDENTITY(1,1) ,Title Nvarchar(50) ) Go CREATE TABLE TBL_Arm2 ( ID INT Identity(1,1) ,Name Nvarchar(50) ,Arm1_ID Int ,Arm_Title Nvarchar(50) ) GO INSERT INTO TBL_ARM1 VALUES (‘Arm1_1’) INSERT INTO TBL_ARM1 VALUES (‘Arm1_2’) INSERT… Read More ›
List of all transactions that are running in SQL Server’s instance
SELECT sqltext.TEXT ,req.session_id ,req.status ,req.command ,req.cpu_time ,req.total_elapsed_time ,req.blocking_session_id ,req.percent_complete ,req.estimated_completion_time/1000/60 estimated_completion_time_Min ,start_time FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext ORDER BY start_time
How to modify and add column in SQL Server’s table with TSQL
USE Arm_DB Go IF OBJECT_ID(‘TBL_Persons’,’U’) IS NOT NULL DROP TABLE TBL_Persons CREATE TABLE TBL_Persons ( ID INT IDENTITY PRIMARY KEY ,NAME Char(50) ) GO ALTER TABLE TBL_Persons ADD Lname Nvarchar(70) Go ALTER TABLE TBL_Persons ALTER COLUMN NAME NVARCHAR(50) GO INSERT… Read More ›
How to save image or any others file type directly from file in SQL Server with binary format
USE Arm_DB GO IF OBJECT_ID(‘TBL_Image’,’U’) IS NOT NULL DROP TABLE TBL_Image CREATE TABLE TBL_Image ( [ID] INT IDENTITY(1,1) ,[Image] VARBINARY(MAX) CONSTRAINT PK_TBL_Image PRIMARY KEY CLUSTERED (ID) ) Go INSERT INTO TBL_Image(Image) SELECT * FROM OPENROWSET(BULK ‘C:\Users\Public\Pictures\Sample Pictures\Koala.JPG’,SINGLE_BLOB ) i
How to create Database in SQL Server with TSQL
USE master GO CREATE DATABASE Arm_DB ON ( NAME = Arm_DB_Dat ,FILENAME = ‘C:\Arman\Mine\MyBlog\Database\Arm_DB_Dat.MDF’ ,SIZE = 20 ,MAXSIZE = 200 ,FILEGROWTH = 8 ) LOG ON ( NAME = Arm_DB_Log ,FILENAME = ‘C:\Arman\Mine\MyBlog\Database\Arm_DB_Log.LDF’ ,SIZE = 15MB ,MAXSIZE = 125MB ,FILEGROWTH… Read More ›