Labels

admin (1) aix (1) alert (1) always-on (2) Architecture (1) aws (3) Azure (1) backup (3) BI-DWH (10) Binary (3) Boolean (1) C# (1) cache (1) casting (3) cdc (1) certificate (1) checks (1) cloud (3) cluster (1) cmd (7) collation (1) columns (1) compilation (1) configurations (7) Connection-String (2) connections (6) constraint (6) copypaste (2) cpu (2) csv (3) CTE (1) data-types (1) datetime (23) db (547) DB2 (1) deadlock (2) Denali (7) device (6) dotNet (5) dynamicSQL (11) email (5) encoding (1) encryption (4) errors (124) excel (1) ExecutionPlan (10) extended events (1) files (7) FIPS (1) foreign key (1) fragmentation (1) functions (1) GCP (2) gMSA (2) google (2) HADR (1) hashing (3) in-memory (1) index (3) indexedViews (2) insert (3) install (10) IO (1) isql (6) javascript (1) jobs (11) join (2) LDAP (2) LinkedServers (8) Linux (15) log (6) login (1) maintenance (3) mariadb (1) memory (4) merge (3) monitoring (4) MSA (2) mssql (444) mssql2005 (5) mssql2008R2 (20) mssql2012 (2) mysql (36) MySQL Shell (5) network (1) NoSQL (1) null (2) numeric (9) object-oriented (1) offline (1) openssl (1) Operating System (4) oracle (7) ORDBMS (1) ordering (2) Outer Apply (1) Outlook (1) page (1) parameters (2) partition (1) password (1) Performance (103) permissions (10) pivot (3) PLE (1) port (4) PostgreSQL (14) profiler (1) RDS (3) read (1) Replication (12) restore (4) root (1) RPO (1) RTO (1) SAP ASE (48) SAP RS (20) SCC (4) scema (1) script (8) security (10) segment (1) server (1) service broker (2) services (4) settings (75) SQL (74) SSAS (1) SSIS (19) SSL (8) SSMS (4) SSRS (6) storage (1) String (35) sybase (57) telnet (2) tempdb (1) Theory (2) tips (120) tools (3) training (1) transaction (6) trigger (2) Tuple (2) TVP (1) unix (8) users (3) vb.net (4) versioning (1) windows (14) xml (10) XSD (1) zip (1)

SQL Server: Run a query without execute it

In order to run a query without execute it – only compile and/or check and/or validate it:
SET NOEXEC ON



How to enable a database user?

Problem:
Giving permissions to a database user in not take effect.

Posible couse:
The user is "disabled". the user is showing up as disabled  - with a down reddish arrow.









Solution:
USE [DataBaseName]
GO
GRANT CONNECT TO [Username]
GO

SQL table "Inside Order" - Identity VS CLUSTERED INDEX

Q: What will be the result order of a select (without ORDER BY) from a table - with/without Identity and CLUSTERED INDEX?
And when we will add Identity and/or CLUSTERED INDEX - how will this affect?

A: Follow the next selects:

create table dbo.NoID
(      someSTR nvarchar(50),
       someDate datetime
) ON [PRIMARY];

insert into NoID (someSTR, someDate) values ('aaa', GETDATE())
insert into NoID (someSTR, someDate) values ('bbb', GETDATE())
insert into NoID (someSTR, someDate) values ('ccc', GETDATE())
insert into NoID (someSTR, someDate) values ('zzz', GETDATE())
insert into NoID (someSTR, someDate) values ('ddd', GETDATE())
insert into NoID (someSTR, someDate) values ('eee', '2017-01-01 00:00:00')

select * from NoID

alter table dbo.NoID add ID int identity(1,1) not null

select * from NoID



create table dbo.NoID_PKClusteredNotOrderedByTime
(
       someSTR nvarchar(50),
       someDate datetime,
       CONSTRAINT [PK_NoID_PKClusteredNotOrderedByTime] PRIMARY KEY CLUSTERED
       (
              [someSTR] ASC
       ) ON [PRIMARY]
) ON [PRIMARY];

insert into NoID_PKClusteredNotOrderedByTime (someSTR, someDate) values ('aaa', GETDATE())
insert into NoID_PKClusteredNotOrderedByTime (someSTR, someDate) values ('bbb', GETDATE())
insert into NoID_PKClusteredNotOrderedByTime (someSTR, someDate) values ('ccc', GETDATE())
insert into NoID_PKClusteredNotOrderedByTime (someSTR, someDate) values ('zzz', GETDATE())
insert into NoID_PKClusteredNotOrderedByTime (someSTR, someDate) values ('ddd', GETDATE())
insert into NoID_PKClusteredNotOrderedByTime (someSTR, someDate) values ('eee', '2017-01-01 00:00:00')

select * from NoID_PKClusteredNotOrderedByTime

alter table dbo.NoID_PKClusteredNotOrderedByTime add ID int identity(1,1) not null

select * from NoID_PKClusteredNotOrderedByTime




create table dbo.NoID_ClusteredNotOrderedByTime
(
       someSTR nvarchar(50),
       someDate datetime
) ON [PRIMARY];

insert into NoID_ClusteredNotOrderedByTime (someSTR, someDate) values ('aaa', GETDATE())
insert into NoID_ClusteredNotOrderedByTime (someSTR, someDate) values ('bbb', GETDATE())
insert into NoID_ClusteredNotOrderedByTime (someSTR, someDate) values ('ccc', GETDATE())
insert into NoID_ClusteredNotOrderedByTime (someSTR, someDate) values ('zzz', GETDATE())
insert into NoID_ClusteredNotOrderedByTime (someSTR, someDate) values ('ddd', GETDATE())
insert into NoID_ClusteredNotOrderedByTime (someSTR, someDate) values ('eee', '2017-01-01 00:00:00')

select * from NoID_ClusteredNotOrderedByTime

CREATE CLUSTERED INDEX [ix_NoID_ClusteredNotOrderedByTime] ON dbo.NoID_ClusteredNotOrderedByTime
(
       [someSTR] asc
) ON [PRIMARY]
GO

select * from NoID_ClusteredNotOrderedByTime

alter table NoID_ClusteredNotOrderedByTime add ID int identity(1,1) not null

select * from NoID_ClusteredNotOrderedByTime

drop INDEX [ix_NoID_ClusteredNotOrderedByTime]  ON dbo.NoID_ClusteredNotOrderedByTime
alter table NoID_ClusteredNotOrderedByTime drop column ID

alter table NoID_ClusteredNotOrderedByTime add ID int identity(1,1) not null
select * from NoID_ClusteredNotOrderedByTime



create table dbo.NoID_ClusteredNotOrderedByTimeIdentityBeforeCI
(
       someSTR nvarchar(50),
       someDate datetime
) ON [PRIMARY];

insert into NoID_ClusteredNotOrderedByTimeIdentityBeforeCI (someSTR, someDate) values ('aaa', GETDATE())
insert into NoID_ClusteredNotOrderedByTimeIdentityBeforeCI (someSTR, someDate) values ('bbb', GETDATE())
insert into NoID_ClusteredNotOrderedByTimeIdentityBeforeCI (someSTR, someDate) values ('ccc', GETDATE())
insert into NoID_ClusteredNotOrderedByTimeIdentityBeforeCI (someSTR, someDate) values ('zzz', GETDATE())
insert into NoID_ClusteredNotOrderedByTimeIdentityBeforeCI (someSTR, someDate) values ('ddd', GETDATE())
insert into NoID_ClusteredNotOrderedByTimeIdentityBeforeCI (someSTR, someDate) values ('eee', '2017-01-01 00:00:00')

select * from NoID_ClusteredNotOrderedByTimeIdentityBeforeCI

alter table NoID_ClusteredNotOrderedByTimeIdentityBeforeCI add ID int identity(1,1) not null

select * from NoID_ClusteredNotOrderedByTimeIdentityBeforeCI

CREATE CLUSTERED INDEX [ix_NoID_ClusteredNotOrderedByTimeIdentityBeforeCI] ON dbo.NoID_ClusteredNotOrderedByTimeIdentityBeforeCI
(
       [someSTR] asc
) ON [PRIMARY]
GO

select * from NoID_ClusteredNotOrderedByTimeIdentityBeforeCI

/*
drop table dbo.NoID
drop table dbo.NoID_ClusteredNotOrderedByTime
drop table dbo.NoID_PKClusteredNotOrderedByTime
drop table NoID_ClusteredNotOrderedByTimeIdentityBeforeCI
*/

TSQL command - Get list of files in a folder

declare @files table (ID int IDENTITY, FileName varchar(100))
insert into @files execute xp_cmdshell 'dir \\\Public\itai'
select * from @files
       where FileName like '%.DAT%' -- filters

sqlzoo - SQL training

https://sqlzoo.net/wiki/SELECT_basics

Columns tables and types

select schema_name(o.schema_id) as SchemaName, object_name(o.object_id) as TableName, c.name as ColumnName, t.name as ColumnType
from sys.columns c
join sys.types t on c.system_type_id = t.system_type_id
join sys.objects o on c.object_id = o.object_id
where c.name = 'SUG-MUTZAR' or c.name = 'STATUS'
order by c.name

Cannot perform alter on a function because it is an incompatible object type

If you have a Multi-Statement Table-Valued Function (MSTVF):

CREATE FUNCTION [DataAccess].[ufn_GetLastPoliciesByTZ]
( ... )
RETURNS @ret TABLE (ID INT)
AS
BEGIN
...
RETURN
END


If you will try to alter it to – with changing it to an Inline Table-Valued Function:

ALTER FUNCTION [DataAccess].[ufn_GetLastPoliciesByTZ]
(
       @ClientPolTable [DataAccess].[TT_ClientPoliciesByTZ] READONLY,
       @SugMimshak INT,
       @FromNechonutDate DATETIME = NULL, -- In case no date is selected
       @ToNechonutDate DATETIME = NULL
)
RETURNS TABLE AS
RETURN
(  
       SELECT .....
)


You will be got this error:
Cannot perform alter on 'DataAccess.ufn_GetLastPoliciesByTZ' because it is an incompatible object type.

Solution:
You can't alter a function from MSTVF to Inline. So, drop and create again the function.
CREATE FUNCTION [DataAccess].[ufn_GetLastPoliciesByTZ]() RETURNS TABLE AS RETURN (SELECT 1 AS ID)