Divide by zero in ORDER BY CLAUSE I just got a PEN-Test result from a web application. The tester stated they were able to use the ORDER BY clause of a dynamic SQL statement to retrieve information.
The name of the column that should be sorted is passed into the query by an app. The tester modified the value so it contains a complex statement that looks like CASE WHEN ... THEN 1 ELSE 1/0 END.
However, I built a very simple test case. I have an Oracle database and a SQLServer. Both contain the same tables. I query
SELECT * FROM users ORDER BY 1/0

When I execute this in Oracle the query executes fine. In SQLServer I get an error 8134 Divide by zero error encountered..
As the PEN-Tester was using the app on a different Oracle server than I'm using now and they report they were abusing the fact that Oracle eventually threw errors, I wonder if there's an Oracle setting that prevents the execution of ORDER BY clauses that evaluate to a division by zero. Is there such a setting? Is it a matter of Oracle Version in use? I'm on 12.1.0.2.0 where no exception is thrown. Is there such a setting in SQLServer, too?
I will of course modify the generation of the statement, so it checks if the values that are passed in are column aliases available for sorting.

Oracle:
order by 1/0 succeeds, because that clause alone is meaningless, the optimizer automatically eliminates it from the query at parse time and it never gets executed.
SQL> select username from t1 where username like 'SYS%' order by 1/0;

USERNAME
--------------------------------------------------------------
SYS
SYSTEM
SYS$UMF
SYSBACKUP
SYSRAC
SYSKM
SYSDG

7 rows selected.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID  cnnmg28k0vspg, child number 0
-------------------------------------
select username from t1 where username like 'SYS%' order by 1/0

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     9 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("USERNAME" LIKE 'SYS%')

No sort at all was performed.
Add something else and it will fail:
SQL> select username from t1 where username like 'SYS%' order by 1/0, 1;
select username from t1 where username like 'SYS%' order by 1/0, 1
                                                             *
ERROR at line 1:
ORA-01476: divisor is equal to zero

SQL>

Or:
SQL>  select username from t1 where username like 'SYS%' order by 1/0;

USERNAME
--------------------------------------------------------------------------------
SYS
SYSTEM
SYS$UMF
SYSBACKUP
SYSRAC
SYSKM
SYSDG

7 rows selected.

SQL> select /*+ opt_param('_optimizer_order_by_elimination_enabled', 'false') */ username from t1 where username like 'SYS%' order by 1/0;
select /*+ opt_param('_optimizer_order_by_elimination_enabled', 'false') */ username from t1 where username like 'SYS%' order by 1/0
                                                                                *
ERROR at line 1:
ORA-01476: divisor is equal to zero

SQL>

Also if the value is not fixed at parse time (e.g it is a variable) :
SQL> variable B1 number
SQL> exec :B1 := 0;

PL/SQL procedure successfully completed.

SQL> select username from t1 where username like 'SYS%' order by 1/:B1;
select username from t1 where username like 'SYS%' order by 1/:B1
                                                             *
ERROR at line 1:
ORA-01476: divisor is equal to zero

SQL>


Don't show local databases in SQL Always-On AG We're installing a SQL Server Always On AG solution(SQL Server 2016) in a test-enviroment at our organisation.
When testing the features we notice that all databases from a node are listed when we connect to a listener.
Is there a way to only show the databases that are part of the AG and not the databases from other AG's or local DB's on the node that hosts the listener?

Thanks in advance!

When testing the features we notice that all databases from a node are listed when we connect to a listener.

Correct, this is the expected behavior as the listener only "points" you to the instance where the primary databases for that availability group are currently located. It does not scope permissions or any other securable.

  Is there a way to only show the databases that are part of the AG and not the databases from other AG's or local DB's on the node that hosts the listener?

No, not in the way you're thinking due to what I states above that a listener is not a securable. If you only want an application to "see" a specific set of databases then set the permissions to those databases only. If you scope the permissions in this way, they will not show up under SSMS but the login will have access to them (assuming the login is not the database owner [this is not the same as the db_owner role]).


Database design : multiple category of users login into same system In our new system we have three type of people

Business owner
Assistant of business owner
Staff (a skilled employee)

I am having following entity

[Business] - include detail about business owner
[Assistant] - detail about the assistant
[Staff] - detail about the staff

Business rules

There will be only one business owner for a given business, means there can be only one login for business owner. He is like a super user for his business.
Business owner can be staff as well.
A single business can contains more then one staff and each staff have different kind of rights/permissions can be configured by business owner.
Only one assistant allowed per business and rights/permissions can be configured by business owner.

What would be right way to go?

Any help/suggestion appreciated!!!

The standard way to model permissions for a system is through Role-Based Security.  The typical model looks something like this:

The way it works is you have three tangible things: Users, Allowable Actions (the access that is being controlled) and Roles.  Roles are groups of both users and allowable actions, therefore there are two intersection tables which record the people in each role and the actions that role permits.  Some people include additional information in the ROLE MEMBERSHIP and PERMISSION intersection tables, like when the record was created and by whom.  Some people keep that kind of audit log information in a separate table.

In this model every type of user is stored in your USER table.  The rules that you have stated for cardinality of users (one owner, one assistant) are frankly very brittle if you ask me.  I don't like to cast rules into my data model which are almost certainly going to change.  It seems unreasonable to me that a business will use your system if it doesn't allow a manager to delegate their responsibilities for when they are off duty, on vacation or away from the business.  Your cardinality rules should be enforced in application logic or with additional tables related to USER if you really feel they need to be in the data model somehow.


Merging Sql Server Rows I have a SQL Server table with Customer Names and Phone Numbers. Customers will be an owner or party member.
There are chances where owners and party member give the same mobile number. I wanted to check the table and if the mobile number is duplicated then I need to remove it from the table. How can I achieve it?
CustomerName   MobileNumber
----------------------------
AAA             +447000000000
BBB             +447000000000
CCC             +447000000000

Result I expect:
CustomerName   MobileNumber
----------------------------
AAA             +447000000000
BBB             NULL
CCC             NULL

WITH CTE AS (
    SELECT CustomerName
        ,MobileNumber
        ,ROW_NUMBER() OVER (PARTITION BY MobileNumber ORDER BY CustomerName) AS rn
    FROM yourTable
)
UPDATE CTE
SET MobileNumber = NULL
WHERE rn > 1;

This query uses common table expression to group rows by MobileNumber and assign them numbers in chosen order. I chose order by CustomerName to get the same results as in a question description. After that it updates all rows with row number greater than 1 and set MobileNumber to NULL for them.


Query performance of a NULL vs an empty ('') varchar in SQL Server 2012 I have 3 indexed fields in a query: int, int, and varchar(250).

The query performs well when all 3 conditions are specified with real values. The int columns always have values, but there are plenty of empty string varchar values. Queries with the empty string varchar parameter perform 2-3x slower than those that search a real string (e.g. 'hello'). The query against the varchar column is a straight WHERE clause (i.e. no LIKE, just =).

I've searched around a bit but really only seem to see academic type discussions around this and I, frankly, don't really care about how they can mean different things.  I only care about the performance of the queries against a NULL or empty string varchar column.

Is this empty string the cause of the slowness? Would a NULL in it's place improve things? I can easily turn existing empty strings into NULLs and put some new logic in to make sure empty strings are always put in as NULLs. I just figured I'd ask here to get the expert opinion on this.

I'll be toying around with this anyway but it'd be nice to get an outside view telling me if I'm just spinning my wheels on it, if that's the case.

Using your info about the distribution of values (and some guessing), I would say that your problem is that MSSQL is doing an table scan when receiving an WHERE clause for empty string.

Why? Suposing that your index on varchar column does not cover the query (in other words, include all the columns referenced in the query); MSSQL have to do an seek to get the PK's columns (if it is clustered) - or record-id (if not) - and using that in a bookmark lookup to get all the columns it need.

Where comes the table (or clustered index) scan? Simple: based on index statistics, if the number of pages touched to get that data is over than 25%-33% of the total pages of the table, MSSQL considers that is too much expensive to use the index and goes the table (or clustered index) scan way.

This is discussed in deeper details on this dba.SE question.

As to solve the problem, you can:

create (or modify an existing index to be) an covering index to your query
Reduce, if possible, the columns on the query - so it become covered by an existing index


Why is the auto created statistic on this column empty? Info

My question relates to a moderately big table (~40GB data space) that is a heap
(Unfortunately, I am not allowed to add a clustered index to the table by the application owners)

An auto created statistic on an Identity column (ID) was created, but is empty.

Auto create stats & auto update stats are on
Modifications have happened in the table
There are other (auto created) statistics that are getting updated
There is another statistic on the same column created by an index (duplicate)
Build: 12.0.5546

The duplicate statistic is getting updated:

The actual question

To my understanding, all stats could be used and modifications are tracked, even if there are two statistics on exactly the same columns (duplicates), so why does this statistic remain empty?

Stats Info

DB stat info

Table Size

Column Information where the statistic is created on

[ID] [int] IDENTITY(1,1) NOT NULL

Identity column

select * from sys.stats
where name like '%_WA_Sys_0000000A_6B7099F3%';

Auto created

Getting some info on another statistic

select * From sys.dm_db_stats_properties (1802541555, 3)

In comparison with my empty stat:

Stats + Histogram from "generate scripts":

/****** Object:  Statistic [_WA_Sys_0000000A_6B7099F3]    Script Date: 2/1/2019 10:18:19 AM ******/

    CREATE STATISTICS [_WA_Sys_0000000A_6B7099F3] ON [dbo].[table]([ID]) WITH STATS_STREAM = 0x01000000010000000000000000000000EC03686B0000000040000000000000000000000000000000380348063800000004000A00000000000000000000000000

When creating a copy of the stats, no data is inside

CREATE STATISTICS [_WA_Sys_0000000A_6B7099F3_TEST] ON [dbo].[table]([ID]) WITH STATS_STREAM = 0x01000000010000000000000000000000EC03686B0000000040000000000000000000000000000000380348063800000004000A00000000000000000000000000

When manually updating the stat they do get updated.

UPDATE STATISTICS [dbo].[Table]([_WA_Sys_0000000A_6B7099F3_TEST])

I was able to reproduce this, both with an empty statistic, and a populated statistic. I arranged for an automatic statistic to be created on an empty table, and the index was created later:

IF OBJECT_ID(N'dbo.Heap', N'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.Heap;
END;
GO
CREATE TABLE dbo.Heap
(
    id integer NOT NULL IDENTITY,
    val integer NOT NULL,
);
GO
-- Add 1000 rows
INSERT dbo.Heap
    WITH (TABLOCKX)
    (val)
SELECT
    SV.number
FROM master.dbo.spt_values AS SV
WHERE
    SV.[type] = N'P'
    AND SV.number BETWEEN 1 AND 1000;
GO
SELECT COUNT_BIG(*)
FROM dbo.Heap AS H
JOIN dbo.Heap AS H2
    ON H2.id = H.id
WHERE H.id > 0
AND H2.id > 0;
GO
-- Empty table
TRUNCATE TABLE dbo.Heap;
GO
-- Repeat exact same query (RT = 500 + 0.2 * 1000 = 700)
GO
SELECT COUNT_BIG(*)
FROM dbo.Heap AS H
JOIN dbo.Heap AS H2
    ON H2.id = H.id
WHERE H.id > 0
AND H2.id > 0;
GO
-- Add 1000 rows
INSERT dbo.Heap
    WITH (TABLOCKX)
    (val)
SELECT
    SV.number
FROM master.dbo.spt_values AS SV
WHERE
    SV.[type] = N'P'
    AND SV.number BETWEEN 1 AND 1000;
GO
-- Add index
ALTER TABLE dbo.Heap ADD
    CONSTRAINT [PK dbo.Heap id]
    PRIMARY KEY NONCLUSTERED (id);
GO
SELECT
    S.[name],
    S.auto_created,
    DDSP.stats_id,
    DDSP.last_updated,
    DDSP.[rows],
    DDSP.rows_sampled,
    DDSP.steps,
    DDSP.unfiltered_rows,
    DDSP.modification_counter
FROM sys.stats AS S
CROSS APPLY sys.dm_db_stats_properties(S.[object_id], S.stats_id) AS DDSP
WHERE
    S.[object_id] = OBJECT_ID(N'dbo.Heap', N'U');

I found that modifications continue to be tracked accurately on all non-empty duplicates, but only one statistic is updated automatically (regardless of the asynchronous setting).

Automatic statistics updates only occur when the query optimizer needs a particular statistic, and finds that it is out of date (an optimality-related recompile).

The optimizer chooses from duplicate statistics as mentioned in the Plan Caching and Recompilations in SQL Server 2012 paper:

  An issue not directly related to the topic of this document is: given multiple statistics on the same set of columns in the same order, how does the query optimizer decide which ones to load during query optimization? The answer is not simple, but the query optimizer uses such guidelines as: Give preference to recent statistics over older statistics; Give preference to statistics computed using FULLSCAN option to those computed using sampling; and so on.

The point being that the optimizer chooses one of the available duplicate statistics (the "best one), and that one is automatically updated if found to be stale.

I believe this is a change in behaviour from older releases - or at least the documentation suggests that all out-of-date statistics for an object would be updated as part of this process, but I have no idea when this changed. It was certainly after August 2013 when Matt Bowler posted Duplicate Statistics, which contains a handy AdventureWorks-based repo. That script now results in only one of the statistics objects being updated, while at the time both were.

The above explanation matches all the behaviours I observed while attempting to reproduce your scenario, but I doubt it is explicitly documented anywhere. It does seem like a sensible optimization, since there is little value in keeping duplicates fully updated.

This is probably all at a level of detail below that which Microsoft are willing to support. This also means it could change without notice.


Why is my maintenance plan save overwriting job ownership? I'm slowly untying all the system dependencies to the previous DBA's system account and moving them to a service account. The old DBA had ownership of all the jobs in our maintenance plan. In SSMS I can change the ownership directly and verify the changes have been applied with this query:

USE MSDB
GO
SELECT GETDATE() AS 'ExecutionTime'
GO
SELECT @@SERVERNAME AS 'SQLServerInstance'
GO
SELECT j.[name] AS 'JobName',
Enabled = CASE WHEN j.Enabled = 0 THEN 'No'
ELSE 'Yes'
END,
l.[name] AS 'OwnerName'
FROM MSDB.dbo.sysjobs j
INNER JOIN Master.dbo.syslogins l
ON j.owner_sid = l.sid
ORDER BY j.[name]
GO

However when I modify the maintenance plan and save the ownership reverts to the old DBA account. I've combed through the GUI for a reference to his account but I can't find one.

Why is this happening and what I do I need to change to keep the maintenance plan from overwriting Job ownership.

There is a bug report on Microsoft Connect. There are workarounds in that link.


DB2 LUW: how to influence query planner's choice of join Suppose I have two tables, A and B.  They "share" the same primary key, which is indexed in both tables.  Table B also has a timestamp column which is separately indexed.

I do a subselect on B using the timestamp column (I give it a 24-hour interval for the timestamp to belong to), and inner join the results against A using the primary keys.

A and B are huge (O(10^9) rows).  When I give the subselect a recent 24-hour period, it seems that the statistics are poor and the planner underestimates the number of rows coming out of the subselect (say, anywhere between O(1) and O(10^5) rows, while there are actually 6M such rows for the queries I run).  It picks a nested loop join, with the subselect output as the "outer" table and A as the "inner" table, accessed via its index.  While NL joins have a bad rep, it only takes a few minutes to complete.

When I tell it to look for older timestamps ("old" 24-hour periods), it does understand that there are about 6M rows coming out of the subselect.  It picks a hash join, with A as the outer/probe table, and the subselect output as the inner/build table.  It does a table scan on A as part of the plan.  It doesn't complete even after 48 hours.

Is there a way to force or hint to the planner that it should use the nested loop join?

While I'd be interested in hearing about DB tuning as a solution, this is a box I have no authorization to tune.

...more information:  the join switches from nested loop to hash when the number of rows returned by the subselect increases beyond about 1.012*10^6.

Hash joins require an "equijoin predicate".  So I rewrote the query as an explicit join (instead of IN...(subselect)), and instead of using A.key = B.key as the join condition, I used A.key > B.key - 1 AND A.key < B.key +1.


Can @parameter IS NULL slow down the execution of query? Option 1:

CREATE PROCEDURE [dbo].[GetStudents]
@MinimumAge int = NULL
AS

select * from Students s where @MinimumAge is null or s.Age >= @MinimumAge

Option 2:

CREATE PROCEDURE [dbo].[GetStudents]
@MinimumAge int = NULL
AS

IF @MinimumAge IS NULL
BEGIN
    select * from Students s
END
ELSE
BEGIN
    select * from Students s where s.Age >= @MinimumAge
END

Will option 1 be slower than option 2 because it has extra WHERE clause? Or will SQL Server take care of that ?

I think option 1 is good as I don't have to duplicate the code, unless it's slower. The original procedure has many lines of code. So I don't want to duplicate all code just for one condition, unless it's the only good option.

There is indexing on the age column and it does not allow nulls.

The problem is, Actual SP is having many lines of code - So I don't want to duplicate all code just to add one where condition, unless it's the only good option.

Community wiki answer:

You could also try:

WHERE Age >= COALESCE(@MnimumAge, 0)

Assuming 0 is not a valid Age. Otherwise, you could use -1 as a default instead of 0.

This will allow a seek on an index keyed on Age.

The other main option is to add OPTION (RECOMPILE) to the statement:

CREATE PROCEDURE [dbo].[GetStudents]
    @MinimumAge int = NULL
AS
BEGIN
    SELECT S.*
    FROM dbo.Students AS S
    WHERE @MinimumAge IS NULL
    OR S.Age >= @MinimumAge
    OPTION (RECOMPILE);
END;

This adds a small overhead on each call as the statement (not whole procedure) is recompiled, but it allows the parameter embedding optimization, so you will get an optimal execution plan for the specific value of @MinimumAge on each execution. See Parameter Sniffing, Embedding, and the RECOMPILE Options by Paul White for more information.

You might also like to read #BackToBasics : An Updated "Kitchen Sink" Example by Aaron Bertrand and review the related Q & A SQL Server--If logic in stored procedure and the plan cache


How to select subset JSON with PostgresQL? I'm trying to deal with a JSON column that does not have an explicit JSON schema or convention. There are varying keys within the JSON. Some keys do not exist, some keys do exist but empty string values.

For example:

CREATE TABLE json_table (
    id         int,
    json_data   json
);

INSERT INTO json_table (id, json_data) VALUES
  (1, '{"foo" : "bar", "baz": "biz"}');
  (2, '{"foo" : "", "baz": "biz"}');
  (3, '{"hello" : "world"}');
  (4, '{"hello" : "world2", "foo" : "bar2", "baz" : "" }');

Is it possible to query this table and select/yield a subset of json_data with only key-value pairs that are non-empty strings?
Is it possible to query this table and select records that contain JSON keys that are within a given list? (I essentially want to find records with json_data that have the key "foo")

For #2 I do have a solution that selects only records where json_data keys are within a regex pattern:

SELECT tmp.*
FROM (
    SELECT id,
           ARRAY(SELECT json_object_keys(json_data))::text AS keys
    FROM json_table
) tmp
WHERE tmp.keys LIKE ANY(ARRAY['%foo%', '%bar%']);

This yields:

"id","keys"
1,"{foo,baz}"
2,"{foo,baz}"
4,"{hello,foo,baz}"

But there's a caveat! I'm returning records that DO have the keys, but empty string values! Is there a way to incorporate a solution to #1 into this and pre-filter the JSON to only "valid" key-value pairs?

You can remove the empty values during aggregation. And use an array operator in the final select to get only rows that contain a specific key:

SELECT tmp.*
FROM (
  SELECT id,
         ARRAY(SELECT t.k
               from json_each_text(json_data) as t(k,v)
               where nullif(trim(t.v),'') is not null) AS keys
  FROM json_table
) tmp
WHERE keys && array['foo','bar']
;

If you want the complete JSON values, rather than just the keys, you can use something like this:

SELECT tmp.*
FROM (
  SELECT id,
         (SELECT jsonb_object_agg(t.k, t.v)
               from json_each_text(json_data) as t(k,v)
               where nullif(trim(t.v),'') is not null) AS keys
  FROM json_table
) tmp
WHERE keys ?| array['foo','bar']
;

(Note that I am using jsonb for the result in this case as that is much more flexible to handle. In general it's recommended to prefer jsonb over json these days)

If you need to cleanup the json frequently, you might want to consider writing a function for that:

create function non_empty_values(p_input jsonb)
  returns jsonb
as
$$
  SELECT jsonb_object_agg(t.k, t.v)
  from jsonb_each(jsonb_strip_nulls(p_input)) as t(k,v)
  where trim(t.v::text) <> '';
$$
language sql
immutable;


Multi-master and multi slave I have been having some issues creating a redundant multi master setup. Here's the proposed layout:

The problem I'm having is when something is written to master1 it replicates to slave1 and master2 but not slave2. And when something is written to master2 it replicates to slave2 and master1 but not to slave 1.

Is there any way I can get this to work where both slaves are updated no matter which master is written to?

Problem
When a Master is also a Slave, you need to have log-slave-updates.
From what you described, each Master does not have log-slave-updates configured.
Solution
STEP 01) STOP SLAVE; on Slave1
STEP 02) STOP SLAVE; on Slave2
STEP 03) STOP SLAVE; on Master1
STEP 04) STOP SLAVE; on Master2
STEP 05) On Master1, add this to /etc/my.cnf
[mysqld]
log-slave-updates

STEP 06) On Master2, add this to /etc/my.cnf
[mysqld]
log-slave-updates

STEP 07) On Master1, Run service mysql restart --skip-slave-start
STEP 08) On Master2, Run service mysql restart --skip-slave-start
STEP 09) START SLAVE; on Slave1
STEP 10) START SLAVE; on Slave2
STEP 11) START SLAVE; on Master1
STEP 12) START SLAVE; on Master2
That's it. Everything should replicate properly from here.
Give it a Try !!!
I have discussed this before

Dec 05, 2012 : MySQL Slave Relay Logging but not logging Binary Log
May 29, 2012 : mysqld-multi with first DB as Slave and second DB as Master
May 07, 2012 : Setting Circular Replication in mysql


Intermittent connection errors I am experiencing occasional connection errors from my webserver to one of my databases.

The code attempts to connect. If it fails, it sleeps for 1 second, then retries, then (if it still fails) sleeps for 2 seconds more and retries, then gives up if it didn't work

The error messages that come back are:

SQLSTATE[HY000] [2003] Can't connect to MySQL server on '10.0.0.210' (4)
SQLSTATE[HY000] [2003] Can't connect to MySQL server on '10.0.0.210' (113)
SQLSTATE[HY000] [2003] Can't connect to MySQL server on '10.0.0.210' (113)

4 = interrupted system call

113 = no route to host

I am trying to work out what sequence of events might cause this to happen so I can try and resolve the issue. My hunch is that it's a network issue, but the two machines are both VPSs in the same cloud, so I am not sure how this could be the case!

If you are using ubuntu, give a try by using the below steps:

    run the command nano /etc/mysql/my.cnf
    comment bind-address = 127.0.0.1 using the # symbol
    restart your mysql server once.


How to change database owner for login and database created inside stored procedure? I have a signed stored procedure that creates a new database, a new login and assigns the new user to be the owner of the new database. Well my stored procedure creates the new login and new database but will not assign the new user as the owner of the new db. The stored procedure is in an application database and not master.

The error I get is:

  Cannot find the principal 'BlahUser', because it does not exist or you do not have permission.

The stored procedure is executed under a different login (simpleUser) with limited rights (execute on stored procedure).

The certificate user has the following permissions:

create any database
alter any login

The code in question is :

SET @sqlStmt = 'ALTER AUTHORIZATION ON DATABASE::'+@sessionid+'Data TO ['+@orgName+'User]'

SET @metasql = '
USE [master]
EXEC (''' + REPLACE(@sqlStmt, '''', '''''') + ''')
'
EXEC sp_executesql @metasql

I tried granting impersonate from the executing user (simpleUser) to the new user inside the stored procedure like this, but it also results in an error.  I'm not sure if users who create other users can automatically impersonate them.

SET @sqlStmt = 'GRANT IMPERSONATE ON LOGIN::'+@orgName+'User TO simpleUser';

SET @metasql = '
USE [master]
EXEC (''' + REPLACE(@sqlStmt, '''', '''''') + ''')
'
EXEC sp_executesql @metasql

The way that works best for me, to write stored procedures that execute dynamic SQL, is by not using "EXEC sp_executesql" when I start writing the stored procedure, but by using "PRINT" statements.

I make sure I get to see each and every SQL that the stored procedure generates. I copy them from the Messages tab and execute them from within Management Studio. Only after all SQLs work as intended, I allow my stored procedure to "EXEC sp_executesql" (instead of simply PRINTing the SQL statements).

Debugging works very fast this way. The most problems I encountered so far, are the result of missing spaces, like 'FROMMyTableName' or 'WHEREId = 12'.

You will have to test this as the certificate user or with an account that has similar rights. And if you can avoid using dynamic SQLs, avoid them.


How to avoid a select query for holding a Sch-S lock I am looking for an option to avoid Sch-S locks when running a Select query.

I have a database which is controlled by an application written by others as well as my own application. One of the tables has millions of rows. I don't have any issue with dirty reads etc. but I don't my select query to lock the other application indexing or modification queries that have to wait due to Sch-S locks from my query.

I tried to set isolation level snapshot before calling my query but that didn't make any difference either WITH NOLOCK option that still acquires an Sch-S lock.

I am looking for an option to run a select on a table without acquiring Sch-S or any kind of lock, maybe if we can quickly have snapshot/view or temp copy (will it work as it has millions of rows of data?) of the table without acquiring any kind of lock at all.

A Sch-S lock is a Schema stability lock.  It is taken to ensure that the structure of the table doesn't change.  This includes adding/removing columns, etc.  NOLOCK hints and isolation levels affect the locking and versioning of the data in the table, not the structure of the table itself.

Even an online index operation will need to briefly take Sch-S and Sch-M locks.  You cannot avoid this for indexing or schema modification queries.


Find out when your index was last rebuilt / reorganized I've recently come across a database, which has very high fragmentation on large tables.

Page count > 1'000'000 with fragmentation 99,...

I would like to know how long this table has gone without a reorganize / rebuild.
That way I can review changes made on that date, to try and find a cause. (Also it'd look nice on my report "this database has not been properly maintained since...)

As I'm in the process of setting up a job to quickly reorganize the affected tables (this night probably), any solution that could offer me a history of index operations would be much appreciated. If not, simply knowing that I can check this data in the future would be very helpful.

SQL Server does not maintain when an Index was last rebuild, instead it keeps information when stats were last updated.

That can be found using the STATS_DATE function.

You can use Ola's Index maintenance solution or Michelle Ufford's - Index Defrag Script. These scripts are widely tested in the community and are much flexible so that you can adapt as per your needs in your environment.

SQL Server  SP2 for 2008R2 and up has a new DMF sys.dm_db_stats_properties which tells you when your stats were last updated with other info like

  modification_counter: number of modifications for the column which leads the statistic, since the last update


Better database design approach to store thousands of users and their validations (composed by many attributes) about thousands of elements I’m looking for the best database design approach of a future web application that will store thousands of films and where (maybe) thousands of users will save their own films validations with up to thirty attributes each validation (attributes are likely to increase as the app grows). Validations are private; they’re not shared with other users.

With this scenario, when I designed the database months ago I ended up that the best approach would be the following:

One table for “users” containing UserID, nickname, password...
As many tables as necessary like “users_xxxx” to store related data of all users together.
One table to store “films” (FilmID, Name...)
UserID-numbered-tables like “film_validations_[user-ID]” with the following columns: FilmID and the thirty attributes (at least). So it could store the validation of all films of a unique user.

Sadly, the PROS I think I am getting with this approach are merely based on suppositions, not on performance tests (I am too novice to know how to do this kind of task). And the CONS about maintenance and performance are an imminent incoming headache for sure, like:

Thousands of tables to store all film validations.
Almost impossible to offer statistics based on global users film validations. Because it will be really hard to join too many tables, also having in mind the total will grow as new users sign up into the application.
Any day I want to offer more film validation attributes I will have to massively execute ALTER TABLE.
The DBMS has to operate with thousands of tables and open them before it can execute the queries.

I am still aware about these problems, but I really can’t figure out how to design it better.

Designing a single table with the UserID column, plus the thousands FilmID as columns (these would contain a Boolean value), plus the tens of film attributes looks really weird.

If we accept this approach, here is another problem: a heavy amount of columns, which in addition, will grow in the future.

Although I have read that a database has no problem managing millions of records in a single table (it would contain the thousands of film validations of [I hope] thousands of users), and the MySQL columns limit is much higher than my requirements (4.096 columns as described here), I don’t know if the performance would be poor.

Other design errors I consider about the single table approach are:

Each record would contain as many empty columns as films minus one, which is awful. I mean, each film validation would carry the rest of FilmID columns, and that has no sense because it wouldn’t be neither necessary nor optimised.
Querying for films with a validation would be a waste of memory. I guess I would have to code a query within a for loop to check which of the thousands FilmID columns contains a “true” value. In my current design, film validations are inserted into the table “film_validations_[user-ID]” as the user fill them. So with just a “SELECT * FROM film_validations_[user-ID]” is enough because I am sure it only contains rated films.

I am a recent graduate student as a web developer. Although I think I have read and learned enough about database normalization (Wikipedia, many StackOverflow Q&A before asking you, and other sources) to consider the single table approach as a bad option in this scenario, maybe in this case is the opposite having in mind all its future maintenance benefits.

Well, after some months, I am still wondering what would be the best approach designing this kind of database. Right now I’m working on moving the unfinished web application fully coded as procedural to a MVC pattern. So I think this is the right moment to correct my database design if necessary.

That’s why am asking for your suggestions based on professional expertise.

Thank you very much in advance.

Just looking at two aspects of the design:

First, it looks like you need a user table (to store information for each user of the site), and a film table (to store information about each film to be "validated", whatever that means).

From that point, you have a many-to-many relationship between users and films: each user can validate many films, and each film can be validated by many users.

This is normally handled by an intermediate table (a "mapping" or "bridge" table). This might be named something like user_film, and would have at least two or three columns:

user_film_id : integer, auto-incremented; primary key for this table
user_id : same type (and ideally, same name) as the primary key for the user table
film_id : same type (and ideally, same name) as the primary key for the film table

You can choose to simply use the user_id and film_id together as the primary key for user_film. Primary keys are generally used as a "clustered index", and the data is stored in the table based on that. Using a combined key means that each new entry could wind up anywhere in the table, making page splits and fragmentation much more likely. Using a "surrogate" key like an auto-incremented integer value means that each new record will go to the "end" of the table, saving time on inserts.

If you do use a surrogate key (as suggested), I'd put a unique index on user_id and film_id on the user_film table, to make sure that each user only has one entry for any given film.

Second, as far as the validations go, it sounds like a tagging system: you would have a number values that might help categorize the film (say, "color", "black&white", "silent", "sci-fi", "horror", "romantic-comedy", "oscar", etc.)

If that's the case, then there are a couple of options, depending on your database.

One is a simplified version of what's called an "entity-attribute-value" model.

In your case, you'd have a validation table, with an ID value (again, I recommend an auto-incremented integer value) and validation name, plus any other columns (perhaps a description of what the validation is to be used for).

Then, you'd have a user_film_validation table, with the primary key from user_film and the ID value from validation, and an auto-incrementing integer (possibly a bigint or the equivalent) to be the primary key of user_film_validation (again, you could just use the first two values as the primary key here).

This makes it easy to modify the validation names: you just have to change one record to change "black&white" to "B&W", for example, instead of updating thousands of records. It also lets you add new validations without having to alter existing tables: simply adding one record to validation makes the new entry available to every user, on every movie.


SQL Server + Microsoft Business Intelligence -- free version or online learning environment? I have some experience writing queries and stored procedures in SQL server. I would like to learn how to use the Microsoft Business Intelligence tools (SSIS/SSAS/SSRS). There are loads of books, videos, and online courses out there to learn these tools (including free courses on the Microsoft website). My challenge is that I do not have access to a SQL server database with these tools. I see that one can download a free 'Express' version of SQL server from the Microsoft website, but this version does not have SSIS/SSRS/SSAS. Is there a free version of SQL server that has these tools--either a version one would download and install oneself, or an online environment with a full version of SQL server+BI tools that one could access for learning/practice purposes?

Evaluation Edition is Enterprise Edition with a timebomb (180 days). It includes SSIS, SSAS, SSRS, etc. So you can evaluate that for 180 days to learn the ins and outs of BI.

Developer Edition, also, provides these tools for ~$50-60 (Microsoft sells it direct for $59.95, though you may find it cheaper from other retailers). The difference is no timebomb but a vastly different EULA. (And if you have an MSDN subscription, you can get this edition - well, any edition - for free as part of your subscription. But there are still licensing restrictions on use, so please pay attention to the EULAs.)

There may also be ways to evaluate these features in the cloud, using Azure Virtual Machine images. I haven't done this, so I don't know if there's any way to take full advantage of this without incurring some level of cost, but I'm pretty sure you can sign up for a trial (I'm also pretty sure it will be significantly less than 180 days).

Also SQL Server Data Tools (SSDT) includes some additional features around building and maintaining solutions that might be useful.


Multi server administration: Job that runs both on master and targets servers? I'm trying to set up multiserver administration on SQL Server 2016. I have two servers, one master and one target. I created a test job that does a simple select statement and then sends an email. I want that job to run on both master and target servers but it seems that I can't.

I can't believe that's not possible (tried to google for that but didn't find anything).

You cannot do this unless you create another copy of the job under Local in the agent.

Details about doing this is given in this post.


Distinguish between unknown and non-present foreign keys I'm in the situation of importing data from multiple other applications.
The data is stored in multiple tables that contain foreign keys.
Some of these applications don't provide all the needed data - the application doesn't give me an ID I could put in the foreign key column (either because that application doesn't manage that data or the API doesn't provide access to that field).
I've used NULL if one value is not known. In cases where the value is nothing, I've used 0 or an empty string, for example an empty string for a title if something is untitled.
However, this doesn't work with foreign keys. When using NULL I don't know if the relation is currently unknown or if there is no relation. Using a special value (e.g. 0) also doesn't work because that would violate the foreign key constraint.
I want to find a method to distinguish between the data that (a) applies for a given foreign key column but is currently unknown, and (b) the data that simply does not apply for such column and therefore will never be known.
I can distinguish the two cases (foreign key exists or not) when I get the data from the applications, but I don't know how to write that information to the database without losing that information.
I'm currently using MySQL, but I maybe switch to PostgreSQL (for other reasons).
Example
Let's say I have among others the tables music_album and file:
music_album(album_id, title, cover_id, ...)
file(file_id, ...)

music_album.cover_id is a foreign key to file.file_id.
Now the problem is as following:

application A gives me the title of the album, etc. including the cover. If the album doesn't have a cover the application will also give me that information
application B gives me the title, etc. but not the cover

If I simply stored a NULL for an album without cover from application A and also store all albums from application B with cover_id=NULL I later don't know if the album doesn't have a cover or that the application didn't give me the information about the cover.

There is a mismatch between you and your DBMS regarding special values.  Special values are special in your mind, but the DBMS can't tell that a special value is different from an ordinary value.  So when you put a special value in, it expects a valid reference to a row in the lookup table.  If the reference is invalid, it flags a constraint error.

There is a way you can get around this, but it will create further problems down the road.  You can put the special value in as a key in the lookup table, with some sort of descriptive field set to "[Unknown Value]".  Then, when you do the join, this text will pop up in place of the known value.

I hesitate to recommend this solution.  In every case where I had to work with this, dummy values in lookup tables ended up causing more problems than they solved:

Wherever the foreign key and the primary key are joined, you have to include a condition to weed out the special values, if appropriate.  If you forget, that's a bug.  It you don't forget, that's a slowdown.

It depends on what the special value means.  If the special value means "this is not a value", among other things, then there is, conceptually, nothing to join to.  If the actual join produces a result, that result is likely to be meaningless or misleading.

But, if it works for you, great.


Column 'Comments.Text' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause This question: Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause | Stack Overflow have explained the root of the problem very well and even advised to use max aggregate as a solution. But, lets take the example

group_id    item_id     comment
--------------------------------
1       abc     "blah-blah"
1       def     "tral-la-lah"
1       ghi
2       jkl
2       mno
2       pqr

I understand the solution: I must take an item to represent the group by max(item_id). But how do I take the comment of the selected item? How does it help me to take the corresponding comment?

edit

Everything is like in this question: Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause | Stack Overflow. The only complication that I address here is the extra comment column that I want to select along with the max(item_id) group by group_id.

I am using this beast, Which tools and technologies are used to build Data Explorer? | Meta Stack Exchange

Your question isn't really clear, but I think you want something like this:

select group_id,
       item_id,
       comment
from (
   select group_id,
          item_id,
          comment,
          row_number() over (partition by group_id order by item_id) as rn
   from the_unknown_table
) t
where rn = 1;

(you didn't state your DBMS, so this is ANSI SQL)

SQL Fiddle demo: http://sqlfiddle.com/#!12/a8471/1


Stop, Start, or Restart a SSAS Tabular service instance in PowerShell with Invoke-Command and $variables I'm working on some PowerShell commands / scripts to stop, or start, or restart a SSAS Tabular service instance using Invoke-Command and some $variables. The end goal is to eventually put this code into an Agent Job for easy service restarting, but that's beyond the scope of this post -- I need to get this working with the $variables first.

I'm using PowerShell 5.0 on my source (local) machine, and trying to stop / start / restart a remote SSAS 2012 Tabular server instance (11.0.6540.0).

First, I start my script with Set-ExecutionPolicy.

Set-ExecutionPolicy -Scope CurrentUser RemoteSigned -Force

Then, using the hard coded method just to get the syntax right and watching the SQL Server Configuration Manager (Config Mgr) while RDP'd into the server to verify the command is working, I use this PowerShell code:

Invoke-Command -ComputerName DevServer { stop-service 'MSOLAP$TABULAR' }

This works great. I verify in Config Mgr that the service stops.

I also verify that the 2 below PowerShell code scripts work:

Invoke-Command -ComputerName DevServer { start-service 'MSOLAP$TABULAR' }

and

Invoke-Command -ComputerName DevServer { restart-service 'MSOLAP$TABULAR' }

Excellent. Works great.

Now I start putting things into variables like so:

$tabularinstance = 'MSOLAP$TABULAR'

$server = "DevServer"
$stop = "stop-service $tabularinstance"
$start = "start-service $tabularinstance"
$restart = "restart-service $tabularinstance"

I then show the values of the variables to verify expected results:

# show values
$tabularinstance
$stop
$start
$restart
$server

And I get the following results returned in the PowerShell console:

PS H:\> # show values
$tabularinstance
$stop
$start
$restart
$server
MSOLAP$TABULAR
stop-service MSOLAP$TABULAR
start-service MSOLAP$TABULAR
restart-service MSOLAP$TABULAR
DevServer

OK, so things seem good.
I then run the below PowerShell command, expecting it to work, but nothing happens on the server; Config Mgr doesn't show the service is stopped, it's still running:

Invoke-Command -ComputerName $server { $stop }

I then realized that the $tabularinstance variable doesn't have the wrapping apostrophes, so I tried this:

$tabularinstance = '''MSOLAP$TABULAR''' # triple apostrophes needed to get the ' escape character and apply the apostrophes to variable

and then re-set the $stop, $start, and $restart variables to use the updated $tabularinstance variables:

$stop = "stop-service $tabularinstance"
$start = "start-service $tabularinstance"
$restart = "restart-service $tabularinstance"

and re-show them again to verify they include the wrapping apostrophes:

PS H:\> # show values
$tabularinstance
$stop
$start
$restart
$server
'MSOLAP$TABULAR'
stop-service 'MSOLAP$TABULAR'
start-service 'MSOLAP$TABULAR'
restart-service 'MSOLAP$TABULAR'
DevServer

Yep, they do.
I then try my InvokeCommand again:

Invoke-Command -ComputerName $server { $stop }

Negative. Still did not work. In ConfigMgr, the service is still running.

I even thought to try to assign the full command to variable to see how it all resolves:

$test = "Invoke-Command -ComputerName $server { $stop }"

And then check the results:

PS H:\> $test
Invoke-Command -ComputerName DevServer { stop-service 'MSOLAP$TABULAR' }

This looks right to me too. It looks like the exact same command I did when hard-coding everything instead of using $variables.

So why isn't my PowerShell working using the $variables method? What's missing?

The script block works a bit different when it comes to passing variables. You have to tell the command what the variable contains on the remote computer.

As well if you want to pass the full command as a variable you will need at the type of the variable as [scriptblock].

$stop = "stop-service 'MSOLAP$TABULAR'"
Invoke-Command -ComputerName $server { $stop }

This needs to be changed to something like this (changed variable names to suite my example):

$MyService = 'W32Time'
$get = [scriptblock]::create( "param(`$servicename) Stop-Service `$servicename")
Invoke-Command -ComputerName sql2014-01 -ScriptBlock $get -ArgumentList $MyService

The other way:

$MyService = 'W32Time'
Invoke-Command -ComputerName SQL2014-01 -ScriptBlock {param($servicename); Stop-Service $servicename }  -ArgumentList $MyService


Is there an ideal fstab rule for XFS volumes on EC2 for running MongoDB? I've just been following Amazon's instructions for adding a new volume to create an XFS disk for MongoDB. As per these, my fstab line looks like:

UUID=....  /data    xfs     defaults,nofail     0       2

Are the defaults OK, or should I be settings something to optimize performance? MongoDB's production notes don't mention anything.

No, as far as I know. These "default" settings are just enough. However, I would not add nofail (do not report errors for this device if it does not exist), I want to see an error if MongoDB data directory don't mount.

..In the scenario where the node is part of replica set and it is restarted and the mongod process is starting automatically. Mongod cannot find any data files, so it goes to RECOVERY -state and starts initial sync, what will fill nodes root partition at a fast pace.

But, of course in the situation, where you don't have access to virtual machines console, you must put that nofail, so you don't end up in a situation where you cannot access your machine. You go around the problem by creating a subdirectory (f.ex. mongo) under that /data (/data/mongo) and pointing dbPath to that directory. No mount, no directory --> mongod don't start.


Postgresql 9.6, create a user with only select permissions for a given database/schema I want to create a user that has:

has its default privileges revoked when I create it
can only perform selects against a given database/schema

Is this possible?

Please read below mentioned official documentation from Postgres
Official Documentation of REVOKE:
https://www.postgresql.org/docs/9.6/sql-revoke.html
Official Documentation of GRANT:
https://www.postgresql.org/docs/9.6/sql-grant.html
To grant select privilege to schema you need to run following query
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO user_name;

For more details read following Post
https://dba.stackexchange.com/questions/91953/grant-access-to-all-tables-of-a-database


is there a rule for the size of the page file on dedicated sql server servers? Hi All,

  We've been investigating further into the C: disk on my_server in
  regards to the low disk space. We can see that there aren't many files
  that can be removed in order to free up storage. However, we have
  noticed the page file on this disk is using 32 GB of storage.

  The settings are currently set to allow Windows to automatically size
  the page file, however, we would recommend turning this setting off
  and manually setting the size. By reducing the page file size manually
  to 8 GB we would free 24 GB of space on the C: disk, which would be
  the best long term solution with the issue of disk space.

should I really disable the page file?
and reduce it to 8 GB?

there is a similar question here:
Recommended page file size for SQL 2008R2 on Windows 2008R2

and this article is also very good:
Understanding the Windows Pagefile and Why You Shouldn't Disable It

should I really disable the page file? and reduce it to 8 GB?

It isn't talking about disabling the page file - it is referring to disabling the auto-size settings.

  is there a rule for the size of the page file on dedicated sql server servers?

There are a great many, often contradictory, rules-of-thumb for the size and location of page files under various scenarios so the question will probably get closed as "opinion based" soon.

A few of my thoughts:

You certainly shouldn't turn of the page file completely.
If you have disk space issues on C: and can't grow C: then saving 24G this way would be useful to you. As an alternative to dropping the swap space completely you could move it to another drive?
Of course you probably don't ever want the machine using 8Gb of swap space, let alone 32. By that point the IO from swapping pages is going to be significantly slowing down the server, it'll have probably have ground to an effective halt before you even get close to 32Gb (at which point you have as much swapped out as in physical RAM).
If the machine is doing nothing except running SQL Server, then you should have the memory settings for the SQL instance(s) set such that it won't exhaust RAM ever. There will still be a little swapping out to the page file as the OS will prefer to page out bits of itself & other services that aren't touched, to favour file cache & buffers for things that are occasionally accessed.


Execute SSIS procedure from stored procedure using non-privileged SQL login I have a web app running on a different server that uses a SQL based login (not a windows based login) to connect to the SQL server. That app is calling a stored procedure that is located in a database other than SSISDB and the stored procedure is attempting to call the following SSIS procedures:

[SSISDB].[catalog].[set_environment_variable_value]
[SSISDB].[catalog].[create_execution]
[SSISDB].[catalog].[set_execution_parameter_value]
[SSISDB].[catalog].[start_execution] @execution_id

When the attempt is made we get the following error:

  The operation cannot be started by an account that uses SQL Server
  Authentication. Start the operation with an account that uses
  Integrated Authentication.

My challenge is how to work around this problem within the confines I have:

I cannot change the code on the web app or the authentication method.
Only minimal, non-impactful changes can be made to the stored procedures such as using "Execute As"
I cannot make changes that require additional code changes - such as creating a wrapper procedure or job.

I came across this thread which describes the problem perfectly but ends in "I'll try something else." The thread also leads to this thread which has great information but does not address starting the procedure as a non-windows user.

I've been working with impersonation attempts, and various combinations of execute as and have been unsuccessful. All of the information I find gives me clues to what I need to do, but none of them directly address this exact scenario and I am hitting a wall.

Can someone please describe how to work around this specific scenario? I don't need exact step by step details (although that would be great), but I need information on specifically how to get from point A (The web app calling a stored procedure with an SQL account) to point B (A SSIS package being executed).

I feel the solution is to make the stored procedure impersonate a windows login that can run the SSIS procedures. But how to do that properly?

Other suggestions that can be used in the interim are also appreciated, but they have to fit in with my confines listed above.

Other resources I have used are here:
https://blogs.msdn.microsoft.com/sqlserverfaq/2009/07/24/how-to-impersonate-the-privileges-to-create-a-login-using-the-stored-procedures-using-execute-as-clause/

https://www.sqlservercentral.com/articles/using-certificates-to-sign-stored-procedures

http://rusanu.com/2006/03/07/call-a-procedure-in-another-database-from-an-activated-procedure/

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/79391dd7-9afb-4bfb-aa90-1fc1cc82b305/impersonation-on-server-level-doesnt-work?forum=sqlsecurity

http://blog.sqlxdetails.com/procedure-with-execute-as-login/

As far as I know session started with SQL Server authentication will not let you create security context outside of SQL Server (because the only external context available is SQL Server service account and EXECUTE AS will not let you impersonate SQL Server service account for external access).
SSISDB procedures has to have context outside of SQL Server, so session has to be started using Windows Auth.
You have few options, but all needs more changes that you mentioned in the email:
1. I think easiest: create SQL Agent job wrapper.
2. Create SQLCLR stored procedure creating new callback session (it will use service account = sysadmin, I would say too privileged).
3. Enable Service broker service and use activation to start SSIS.
4. Publish SSIS package as a view and let your app consume the view.
5. I didn't try this, but worth trying to use a loopback linked server.


SQL Server cluster questions please We have SQL Server 2008 R2 cluster active/active/active (a, b, c) and three instances run on this environment.

We did a test on one of the instances, and failover test.

Manual failover from node c to b.  After 3 minutes, the application cannot connect to SQL Server.
Back to node c we are OK and application is happy
Manual failover from node c to a.  After 3 minutes, the application cannot connect to SQL Server.
Back to node c we are OK and application is happy

Please give me some helps.

There are a few things that I can think of off the top of my head. You're running a multi-instance failover cluster so in theory I'd expect to see each node to be sized such that at any given point in time it can handle the load of all three instances. Chances are that this is not the case, but maybe it is. Ideally, you'd also have a spare node that can handle failures but that doesn't sound like it's the case here.

There are some configurations that you can check to ensure that you've not set yourself up for failure and the first one I'd check would be to run

sp_configure 'max server memory (MB)'

If your run value is 2147483647 then you've got it set to allow SQL Server to take as much memory as it thinks it needs. This is set per instance so when you have multiple instances trying to consume all available RAM you will get memory pressure.

Having said that (read: actually, start here), you've not given us any other information about what you've done to discover why the application stops responding. Is it just the application that connects to the C node that chokes, or does the original application also not work? This could end up being something as simple as the application connection string is connecting to the IP/DNS name of the C node and not the VIP. If this is the case, then when C is no longer serving SQL Server then you're not going to be able to connect.

Step 1: Ensure the connection strings are actually connecting to the instance/VIP name and not the nodes.

Step 1.5: (Thanks to Thomas Stringer), make sure that you're giving the new instance enough time to actually recover the database. Connect to the instance via SSMS and see if your databases are in recovery.

Step 2: If Step 1 is correct, then get on the node that is running multiple instances and see what's going on. I'd recommend using PerfMon because "Task Manager is a dirty, filthy liar" and looking at metrics for the various subsystems starting with Memory, Network, CPU, and Disk IO. This answer contains much of what you'd need in order to check for resource pressure assuming you have connectivity to the instance and the databases are all fully recovered.


Is it possible to attach a description field to the records in INFORMATION_SCHEMA.TABLES? In SQL Server, [database name].INFORMATION_SCHEMA.TABLES has the following columns:

TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
TABLE_TYPE

This table has no additional fields, such as "DESCRIPTION"

This is confirmed by MSDN.

My question is this: other than making a manual database or a Word document of my own, is there a system or other column or table that I can use to store a "description" or "memo" value per record in the INFORMATION_SCHEMA.TABLES table?

To deal with this as much as possible I use database, table, and field naming conventions, but that can go only so far. Some of the names have a "back story" that can be explained to future team members in a "description" field.

I have tried so far:

Google searches for related questions.
Looking for a "description" field of the table properties in SSMS (see screenshot below), but it wasn't what I had in mind.

Is there any preexisting field in a system or other table that I am missing and matches my need?

Use extended properties.  It's not in the INFORMATION_SCHEMA views but it will stay attached to that object.

I wrote about it in detail here: https://sqlstudies.com/2015/05/18/extending-your-metadata-with-extended-properties/

But here is what it would look like attached to a User:

Here is how you would query all of the extended properties for users in a given database:

-- View the extended properties
SELECT ep.*, dp.name FROM sys.extended_properties ep
JOIN sys.database_principals dp
    ON ep.major_id = dp.principal_id
WHERE class_desc = 'DATABASE_PRINCIPAL'
GO

The same thing will work for pretty much any object in SQL Server.  Obviously you'd want to modify the query somewhat but that should be pretty easy.


Upgrade postgres without messing with ports? The standard procedure for upgrading Postgres appears to require that I temporarily run both the old and the new version. This means that I have to change the port of the old version.

This seems completely unnecessary, given how I’m telling pg_upgrade exactly where to find the data directories and it could just go and read all the data directly.

Is this really the only way to upgrade (apart from dump/restore), or am I misunderstanding something? Can I not just ask the upgrader to convert all the binary data files directly, without messing with users, passwords and ports?

Given step 9 in the link you provided:

  9.Run pg_upgrade ... Obviously, no one should be accessing the clusters during the upgrade. pg_upgrade  defaults to running servers
  on port 50432 (sic) to avoid unintended client connections.  You can
  use the same port numbers for both clusters because the old and new
  clusters will not be running at the same time. ...

It looks like it is just an available option.


Get MS SQL Server to release disk space A poorly managed database table has grown to be enormous. 48+gigs of orphan records. I am trying to clean it up and put my dangerously full hard drive back to a normal state. I will be deleting approx 400 million records from this table. This is running as I type. I notice that I am not seeing any drop in my hard drive space but I am seeing the memory drop on system table queries, I am running to get table size. The database is using "Simple Recovery Model".

There are many questions similar to this with responses saying you need to shrink the database. But they go on to explain how bad/scary this is to do because of fragmented data etc.

Since the database should not be this size. Is it still bad for me to shrink it?
This is a production database. If I shrink it will it cause downtime or lock the database?
In SQL Server Management Studio you have two options for shrink, database or files. Given my situation what would be the best option?
is there a rule on the percentage of free space a DB should have?

Even reading the tag description of shrink makes me not want to do it. Is there another way?

I will be deleting approx 400 million records from this table.

Hopefully, you are doing it in chunks - to avoid bloating transaction log.

  notice that i am not see any drop in my hard drive space

You wont, as you have to explicitly shrink the database file to release space. Just deleting the records, SQL server wont release the space back to the OS.

  Since the database should not be this size. Is it still bad for me to shrink it?

It is generally a bad practice as databases should be presized properly. If you are 100% sure that your database is NOT going to be again this size, its OK for you to shrink the database (in your scenario).

  This is a production Database. If I shrink it will it cause downtime or lock the database?

Shrinking a database is a very IO intensive. Its advisable to shrink using DBCC SHRINKFILE during maintenance window or when there is minimal activity.

  In management studio you have two options, Database or Files. Given my situation what would be the best option?

You should use TSQL to do the shrink. (since you asked, shrinking FILE is the way to go instead of database). You can use shrink database in chunks - tsql script to do the shrink in chunks.

Remember that when you shrink, you are fragmenting your indexes.

  is there a rule on the percentage of free space a DB should have?

You can refer to Estimating Disk Space Requirements for Databases article. There is no fix rule, you have to take into account of how your database is expected to grow. You also have to take into account, autogrowth of your databases.

References : Why Does the Transaction Log Keep Growing or Run Out of Space?


How is the "Adjust memory quotas for a process" user right assignment used in SQL Server 2019? I am looking at the Configure Windows Service Accounts and Permissions doc (https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15) for SQL Server 2019 and it states that the SQL Service Account needs to have the Adjust memory quotas for a process permission.
Can anyone confirm why this is required and what it's function is within the SQL Engine. In my Dev environment, I do not have it enabled and all seems to be functioning correctly.

Can anyone confirm why this is required and what it's function is within the SQL Engine

This privilege when granted to user account will give ability to the account to adjust maximum memory that other process can use. See the official document

This privilege determines who can change the maximum memory that can be consumed by a process. This privilege is useful for system tuning on a group or user basis.
This user right is defined in the Default Domain Controller Group Policy Object (GPO) and in the local security policy of workstations and servers.

The SQL Server, SQL Server agent. SSAS service account should have this privilege to adjust the quota if DB engine deems necessary. But also note that if this is given to user account which is used to run certain queries it can also lead to Denial of Service attack( DoS) by changing the quota.


Complex query performance in MySQL InnoDB versus MyISAM I am running on MariaDB 10.0.15 on a 4GB RAM cloud server with Local SSD disk for database.

My model:

users <n---n> right_holders <n---n> tracks ---n> raw_detections

I have this table raw_detections with more than 100 million records. I want to sum all raw_detections for a specific user in a specific period. This is the query that I am having problem:

SELECT
   DATE(raw_detection.created_at),
   SUM(1)
FROM `raw_detection`
INNER JOIN `audio_sources` ON `audio_sources`.`id` = `raw_detection`.`audio_source_id`
INNER JOIN `cities` ON `cities`.`id` = `audio_sources`.`city_id`
INNER JOIN `tracks` ON `raw_detection`.`track_id` = `tracks`.`id`
INNER JOIN `track_right_holders` ON `tracks`.`id` = `track_right_holders`.`track_id`
INNER JOIN `right_holders` ON `track_right_holders`.`right_holder_id` = `right_holders`.`id`
INNER JOIN `user_right_holders` ON `right_holders`.`id` = `user_right_holders`.`right_holder_id`
WHERE `raw_detection`.`duplicated` = 0
AND `user_right_holders`.`user_id` = 1
AND (raw_detection.created_at >= '2015-01-18 00:00:00')
AND (raw_detection.created_at <= '2015-02-19 23:59:59')
AND (audio_sources.source_type = 'Radio')
AND (track_right_holders.role = 'Interpreter')
GROUP BY DATE(raw_detection.created_at);

There are some users that have a lot of detections (tens of thousands) per month.

When raw_detection table is MyISAM, this query takes 2-3s to run. When the table is InnoDB, it takes 10-20s to run. My questions are:

1) Why this is happening?
2) How can I improve this performance for both MyISAM and InnoDB

raw_detection schema:

CREATE TABLE `raw_detection` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `score` int(11) NOT NULL,
  `track_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `audio_source_id` int(11) NOT NULL,
  `duplicated` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `index_raw_detection_on_duplicated_and_created_at` (`duplicated`,`created_at`),
  KEY `index_raw_detection_on_created_at` (`created_at`),
  KEY `index_raw_detections_audio_source` (`audio_source_id`,`duplicated`,`created_at`),
  KEY `index_raw_detection_on_track_id_and_duplicated_and_created_at` (`track_id`,`duplicated`,`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=126224926 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

In your case, your problem is RAM. Why?
In my old post What are the main differences between InnoDB and MyISAM?, I discuss how each Storage Engine does caching.

InnoDB has a Buffer Pool that cache data and index pages
MyISAM does no dedicated caching of data, only indexes. Data get cached in OS RAM.

Here is a picture from Vadim Tkechenko that shows what InnoDB looks like:

InnoDB has more housecleaning to do against the buffer pool as new data/index pages come in and old data/index pages get purged. MyISAM does not have any housecleaning of data to do.
You will need a Cloud Server with more RAM and a larger InnoDB Buffer Pool.
UPDATE 2015-03-20 18:00 EDT
If you can give more memory to the Buffer Pool without getting a bigger Cloud Server, that's fine.
If your Cloud Server is a dedicated DB Server, you should do 75% of RAM. So, that's 3G for innodb_buffer_pool_size. If your Cloud Server is a LAMP stack, perhaps 60% of RAM, which would be 2457M.
If you have lots of data, then I would suggest a Cloud Server with more RAM.
UPDATE 2015-03-20 18:06 EDT
I also noted that you said

There are some users that have a lot of detections (tens of thousands) per month.

If this means the table has lots on INSERTS, you should run this once a week
ANALYZE TABLE raw_detection;

Then, try the query again. The index statistics might be out of sync with the amount of data currently in the table.


Table orders regarding Nested loop join in DB2 First I would like to cite the existing post Table order in Join Query

so the author is saying that the table order really doesn't make things(result, access plan)different. I have doubt on this regarding the access plan.

so the documentation of DB2 (TABLE JOIN) tells in the NLJoin, outer table is fixed and scanned only once, while the inner tables might be scanned multiple times depend on different scenarios, under this circumstance, I think the order of tables in the join really matters, we need to put big table as outer table, as we only want to scan this once.

is this the case, is this only for DB2? did I miss something?

+is there general guidance when writing SQL, how should we deal with the table join orders ?

You are right that for Nested Loop Join, the choice of which table is the inner and which the outer table matters for perforamnce.

However, there is nothing in the documentation, in the link you provided, that implies that for a query that has a INNER JOIN b, the table a will be used as inner and b as outer table when the Nested Loop Join algorithm is selected.

Any decent optimizer evaluates many different combinations of algorithms, placing of tables and order of execution, so I don't think there is any difference if you write a INNER JOIN b or b INNER JOIN a, the chosen execution plans should be the same in both cases. If there are exceptions to this, I would expect them to be for very complex queries with tens of joined tables and/or multiple groupings.

Testing and checking the actual execution plans is one way to confirm this. Another would be to analyze the source code of the query optimizer.

The general guidance when writing SQL (in whatever DBMS), is not to care at all about the table join orders. SQL code describes what you want as result, it doesn't tell the DBMS how to get it. And many optimizers now are really smarter and faster than most of us in choosing the best execution plan most of the time.

Unless documentation shows that the optimizer is naive or in a very early version and the way the queries are written, really affects the chosen execution plan.

Or testing/running of a specific query shows that it's slow and some obviously good plan was not chosen. Then you can experiment with hints (if the DBMS has such feature), try rewriting the query in different ways, check if statistics are updated, etc.


Huge database... queries too slow. How to improve performance? I'm currently managing a MySQL database with 30 million rows(many more to come). The entire database is 280GB. The server has 4GB of RAM and 3.2Ghz 4 core processor. The largest column stores an array of integers as text(varchar couldn't become large enough).

A simple

  SELECT row FROM tablename LIMIT 200,000;

takes 30 seconds to complete... I need to select all 30 million rows and go through some algorithms in at most 10 seconds to be ideal.

I'm not used to managing such a large database. Is it normal for a queries to take this long? I've heard about indexing, but can't index the text data type column.

What are some ways you suggest I can improve performance? I can't find too many ways to improve performance other than:

Indexes
More RAM
SSD
Partitioning tables(sound complicated?)

Is there anything I'm not thinking of?

I appreciate any help! :)

An index provides a path to rapidly identify the rows that match a given predicate. They help most when you need to reference a small fraction of a table at a time. Since you need all rows this is unlikely to be helpful. They also enforce a sequence. This may be helpful if your algorithm would benefit from ordering, though you do not mention it.

The DBMS copies data from disk into memory before processing the rows. So more memory helps because more data can be held in memory, with corresponding fewer IO waits. Since you need to process 280GB the last 4GB will be in memory at the end. During the next cycle that data will be evicted and fresh data read. Unless you are able to install over 280GB more memory is unlikely to help.

Partitioning is more of an archiving thing. Sharding may help, though. That would mean spreading the data across several servers. Four servers would, approximately, quarter the time to process. You'll need a custom process to stitch the answers together, though, which may not be simple.

The simple fact is that if you need to process 300M rows you need to read 300M rows. This is likely limited by storage bandwidth. An SSD will obviously help here.

However, is there a way that you wouldn't have to process all rows each time? Could you pre-calculate these values, cache intermediate results or only process deltas between queries?

Edit Are you reading all rows only to parse some values out of the text column, but you actually do not use the majority of rows in any particular query? LIKE is not going to help eliminate IO since the majority of values will be somewhere in the middle of the text. An index will not help here. The best you can achieve is to push filtering onto the DB server eliminating network time.

A full text search may help find labels within the large text column. Perhaps redefining the column as JSON and adding a secondary index would help. But really these are kludgy workarounds for a sub-optimal design. Relational databases are built to process relations i.e. tables. Structure the data properly as a table and you will have the best chance of getting good performance. In this case that means separating the key-value pairs embeded in the text column onto separate rows, one per value. An index on the "key" column (and whatever other columns are in the WHERE clause) will be a great start. Yes, the DB may become a little larger, but the software's designed for this. There are many, many systems around with terabytes of data in billions of rows. It will be OK.


RDS Replication Error (Apply Error 1406 / Truncation) I have a MySQL RDS instance as a master, created a Read Replica from it, and ran some schema change operations on it.  To be specific, I changed the charset and collation of all the tables and columns from utf8 to utf8mb4.  Things were replicating fine, but an error just occurred.

Apply Error 1406: Error; Data too long for column... etc

This is due to lowering the varchar length on some columns from 255 to 191.

I read that you can run some commands to skip replication errors, as described here:  http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql_rds_skip_repl_error.html

However, would this "skip" the insert, or, just truncate the data and proceed with the insert?

I'd like the data to be truncated and still added to the table rather than aborting the entire operation, but I'm not sure if that is going to happen or not.  Any suggestions would be welcome!

Get 5.6.3 (or later).  As I understand it, these need to be applied to the Replica:

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=ON;
ALTER TABLE tbl ROW_FORMAT=DYNAMIC; -- or COMPRESSED

That should allow you to keep 255 (instead of 191), thereby avoiding the truncation.


Does it makes sense to index a primary key if it is not used? I have a database with many tables which can be joined by indexed keys (primary and foreign keys). Some tables are transaction tables which have an autoincremental primary key just to define a ID for each transaction.

To use this database and process the data (cleansing, adding new columns) I copy the database. The processes on the new database (updates, inserts) are quite complex. I index columns where it makes sense but I don't want to index columns which  I don't need due to reason of performance.

Now, I come to the point: there are several huge transactions tables which have been defined from the original database as primary key but which are not used as key for joining. I would like to remove these primary keys (not the column but just: ALTER TABLE trans DROP PRIMARY KEY)  to avoid too many indexed columns. Is there any reason not to remove index on a primary key?

If this question is dependent on RDBMS: I use MySQL/MariaDB.

In MySQL, if you have an auto_increment column, it MUST also be a key/index (either primary or a unique or a non-unique one, so any index really) where auto increment column is the first in the index/key definition.

For any InnoDB tables that do not have a PRIMARY or UNIQUE NOT NULL key defined, MySQL will implicitly create (a 6-byte) internal key under the hood and use it as the clustered index.

This clustered key (the PRIMARY or the first UNIQUE NOT NULL or the 6-byte internal) is always included in ALL secondary indexes for InnoDB tables in MySQL.

In your situation, dropping the PRIMARY key will only result in MySQL creating an implicit one anyway (unless you define another PRIMARY or UNIQUE NOT NULL key explicitly). This also applies only to InnoDB tables.


Keep count in temporary variable for later use in query I have used WITH query for this as below

WITH my_count AS
(
    SELECT count(emp_name)
    FROM   employee
    WHERE  joined_date < '2017-11-30'::Date
    AND    region = 'UK'
)
INSERT INTO my_summary (property_key, month, property_value)
VALUES ('count des', '2016-11-30', my_count);

But this gives me an error as below:

  ERROR:  column "my_count" does not exist
  LINE 4: ...rty_value) VALUES ('count des', '2016-11-30', my_co...
                                                                  ^
  SQL state: 42703
  Character: 244

So How do we keep count in a temporary variable for later use? If this is not possible, is there any other way to achieve this?

You need to base your insert statement on a select:

WITH my_count (cnt) AS (
  SELECT count(emp_name)
  FROM employee
  WHERE joined_date < '2017-11-30'::Date AND region = 'UK'
)
INSERT INTO my_summary (property_key, month, property_value)
select 'count des', '2016-11-30', cnt
from my_count;

Or use a scalar select:

WITH my_count (cnt) AS (
  SELECT count(emp_name)
  FROM employee
  WHERE joined_date < '2017-11-30'::Date AND region = 'UK'
)
INSERT INTO my_summary (property_key, month, property_value)
values
('count des', '2016-11-30', (select cnt from my_count));

Also, both methods could be implemented without a CTE. Taking the first query as an example, the variation without a CTE would look like this:

INSERT INTO my_summary (property_key, month, property_value)
SELECT 'count des', '2016-11-30', count(emp_name)
FROM employee
WHERE joined_date < '2017-11-30'::Date AND region = 'UK';


Compare the contents of a single column and sync rows in that colum This is SQL Server - I have 3 rows of data in a single table based on location I want the data for a single field to match in all three locations e.g I have parts in three locations, location 1, 2, and 3 but the price for the part is different in each of the three locations, I would like to sync the price for all three locations

This is table 1 - fields include item number, location, price. e.g

item    loc.    price
123     1       0.1
123     2       0.2
123     3       0.3

My desired outcome is that location 2 and 3 will have the same price as location 1.

Is this what you're looking for?

Set up demo table with data

declare @T table (ItemNumber int, location int, price decimal(11,2))

insert into @T (ItemNumber,location, price) values
(1,1,10.00),(1,2,20.00),(1,3,30.00)

select * from @T

| ItemNumber | location | price |
|------------|----------|-------|
| 1          | 1        | 10.00 |
| 1          | 2        | 20.00 |
| 1          | 3        | 30.00 |

The update statement

UPDATE @T
SET price = (
        SELECT price
        FROM @T
        WHERE location = 1
        )
WHERE location <> 1

select * from @T

| ItemNumber | location | price |
|------------|----------|-------|
| 1          | 1        | 10.00 |
| 1          | 2        | 10.00 |
| 1          | 3        | 10.00 |


Group by unique pairs with no multiplicity I have a table AreaLocation with AreaID as the PKey and Location whereby there are many locations for each AreaID. Correspondingly there is another table Books with:
BookingID | DriverID | BLocation | Miles | Fare

I'm trying to get the average efficiency of each driver by area, i.e. I'd looking for a view with
| DriverID | AreaID | AVERAGE(Fare/Miles) |

where I'd like to group by distinct DriverID/AreaID however I'm not sure whether the following will work:
CREATE VIEW EFFICIENCY AS
 SELECT AreaID, Bookings.DriverID, AVERAGE(Bookings.Fare/Bookings.Miles)
 FROM AreaLocations
 RIGHT JOIN Bookings ON
 ( AreaLocations.Location = Bookings.Location )
 GROUP BY DISTINCT (AreaID, Bookings.DriverID)

as I don't think distinct works on pairs.

With full_Group_on you can use for example this, if a "_" is not present  your columns
CREATE VIEW EFFICIENCY AS
SELECT SUBSTRING_INDEX(area_driver, '_', 1) as AreaID,SUBSTRING_INDEX(area_driver, '_', -1) AS DriverID, AvgPrice
 FROM (SELECT CONCAT(AreaID,"_",Bookings.DriverID) area_driver, AVERAGE(Bookings.Fare/Bookings.Miles) AvgPrice
 FROM AreaLocations
 RIGHT JOIN Bookings ON
 ( AreaLocations.Location = Bookings.Location )
 GROUP BY  CONCAT(AreaID,"_",Bookings.DriverID)) f1


How to store the result of multiple select queries into one csv file? How to store the result of all of the queries below into one CSV file in PostgreSQL by executing one COPY (......) to.... statement.

select id,name from first;
select id,salary,father_name from second;
select id,score,bonus,extra from third;

far as i know not possible if you want append one table after another to same file. But you can get them to same file using union

SQL:

select f.id as fid , null sid   from first f union select null fid , s.id sid from second s

that would create file where all data from first table are
1;null
2;null
....
and second table
null;1
null;2

or you can use psql command line tool in linux and call :

psql mydb -c "select id,name from first; " >> myOutFile.txt
psql mydb -c "select id,salary,father_name from second; " >> myOutFile.txt
psql mydb -c "select id,score,bonus,extra from third; " >> myOutFile.txt

In *nix, the > (greater than) character operator redirects output to a file, overwriting the file.

When doubled, the >> operator redirects output to a file appending the redirected output at the end.


One filegroup, multiple datafiles, how to get list of tables in each file In one of my production databases, I have a filegroup with 3 data files sitting on 3 different disks. There are over 50 tables on this filegroup.

How do I identify the location of each of the 50 tables between the 3 data files? I need to know on which data file does the clustered index for each table reside.

Example :

Table A : Datafile 1 : FG1
Table B : Datafile 2 : FG1
Table C : Datafile 1 : FG1
Table D : Datafile 3 : FG1

An object in a filegroup will use all datafiles in the filegroup. Any table in FG1 resides equally on Datafile1, Datafile2 and Datafile3. If you need to control placement you need to create distinct filegroups. To remove a file from a file group use DBCC SHRINKFILE EMPTYFILE.


How to get around not being able to use Temp Tables in Functions? So, to get straight to the point. A Client wants to be able to view all of their staff of a certain position are going to be available to perform a set of shifts between 2 dates.

Seems simple, have a table-valued function to get all staff if that position then use a single-valued function that runs through that staff members leave or current shifts to see if any will stop them from working this shift.

ALTER FUNCTION [Human_Resources].[fIsEmployeeOffshoreBetweenDates] (@EmpID INT, @StartDate DATETIME, @EndDate DATETIME)
RETURNS INT
BEGIN
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
    /*Then it exists*/
    DROP TABLE #Temp

    SELECT *
    INTO #Temp
    FROM Human_Resources.v_GetEmployeeManning(@EmpID)

    DECLARE @RequestID INT

    WHILE EXISTS(SELECT * FROM #Temp)
    BEGIN

        SELECT TOP 1 @RequestID = ID FROM #Temp

        IF (General.IsRangeBetweenDateRange((SELECT TOP 1 StartDate FROM #Temp WHERE ID = @RequestID), (SELECT TOP 1 EndDate FROM #Temp WHERE ID = @RequestID), @StartDate, @EndDate) = 1)
        BEGIN
            PRINT N'IS WITHIN RANGE'
            RETURN 0;
        END

        DELETE #Temp WHERE ID = @RequestID

    END

    RETURN 1;
END

But then I found out that Functions can't actually contain temporary tables. Does anyone know how I could get around this?

Your best bet is to rewrite the function to remove the need for the temp table at all.  This will benefit you in other ways as your current row by row approach will not scale well.

The example below shows one way of getting the same results without using a temp table.  I had to make assumptions about the data types since you didn't include the function header.

DECLARE @EmpID INT
DECLARE @StartDate DATE
DECLARE @EndDate DATE

;WITH CTE_HR
    (
    SELECT HRM.*
        , IsRangeBetween = General.IsRangeBetweenDateRange(HRM.StartDate, HRM.EndDate, @StartDate, @EndDate)
    FROM Human_Resources.v_GetemployeeManning(@EmpID) AS HRM
    )
SELECT * FROM CTE_HR
WHERE IsRangeBetween = 1


Nonclustered index storage on clustered columnstore In SQL Server, a non-unique nonclustered index on a rowstore table incorporates the base object's bookmark (RID or clustering key) at all levels of the nonclustered index structure. The bookmark is stored as part of the nonclustered index key at all index levels.

On the other hand, if the nonclustered index is unique, the bookmark is present only at the leaf level of the index - not as part of the key (the bookmark is present as one or more included columns, in effect).

In SQL Server 2016, it is possible to build a nonclustered b-tree index on a column-oriented table (one that has a clustered columnstore index).

What is the 'bookmark' used for a nonclustered b-tree index on a clustered columnstore table?
Do the differences between unique and non-unique nonclustered indexes described above still apply?

The "bookmark" is the columnstore index original locator (per "Pro SQL Server Internals" by Dmitri Korotkevitch). This is an 8-byte value, with the columnstore index's row_group_id in the first 4-bytes and an offset in the second 4-bytes.
If you use DBCC PAGE to look at the non-clustered index, the 8-byte columnstore index original locator appears in the "uniquifier" column of the DBCC PAGE output. This shows that a unique non-clustered index does not need to include the columnstore row locator, whereas a non-unique non-clustered index does.

The following code creates a columnstore-organized table with a unique and non-unique b-tree nonclustered index on the same column:

CREATE TABLE dbo.Heapish
(
    c1 bigint NOT NULL,
    c2 bigint NOT NULL,
    INDEX CCI_dbo_Heapish CLUSTERED COLUMNSTORE
);
GO
INSERT dbo.Heapish WITH (TABLOCKX)
    (c1, c2)
SELECT TOP (1024 * 1024 * 8)
    c1 = ROW_NUMBER() OVER
        (ORDER BY C1.[object_id], C1.column_id),
    c2 = ROW_NUMBER() OVER
        (ORDER BY C1.[object_id], C1.column_id)
FROM master.sys.columns AS C1
CROSS JOIN master.sys.columns AS C2
ORDER BY
    c1
OPTION (MAXDOP 1);
GO
CREATE UNIQUE NONCLUSTERED INDEX UNIQUE_c2 ON dbo.Heapish (c2) WITH (MAXDOP = 1);
CREATE NONCLUSTERED INDEX NONUNIQUE_c2 ON dbo.Heapish (c2) WITH (MAXDOP = 1);

We can see the size of the index row at different levels of the b-tree using sys.dm_db_index_physical_stats:

SELECT
    DDIPS.index_level,
    DDIPS.page_count,
    DDIPS.record_count,
    DDIPS.min_record_size_in_bytes,
    DDIPS.max_record_size_in_bytes
FROM sys.dm_db_index_physical_stats
(
    DB_ID(),
    OBJECT_ID(N'dbo.Heapish', N'U'),
    INDEXPROPERTY(OBJECT_ID(N'dbo.Heapish', N'U'), N'UNIQUE_c2', 'IndexID'),
    NULL, 'DETAILED'
) AS DDIPS;

SELECT
    DDIPS.index_level,
    DDIPS.page_count,
    DDIPS.record_count,
    DDIPS.min_record_size_in_bytes,
    DDIPS.max_record_size_in_bytes
FROM sys.dm_db_index_physical_stats
(
    DB_ID(),
    OBJECT_ID(N'dbo.Heapish', N'U'),
    INDEXPROPERTY(OBJECT_ID(N'dbo.Heapish', N'U'), N'NONUNIQUE_c2', 'IndexID'),
    NULL, 'DETAILED'
) AS DDIPS;

The output is:

Both structures have the same row size at the leaf level, but the nonunique nonclustered index is 12 bytes larger than the unique nonclustered index at the non-leaf levels due to the 8-byte columnstore locator, plus 4 bytes of overhead for the first variable-length column in a row (uniquifier is variable length).


SQL Server Analysis Services - where do I store user logins? If I would like to create a Business Intelligence (BI) system using SSAS, with SSRS getting data from the SSAS cube.

Which would be the best possible option to store users' credentials used with SSAS?

Active directory seems to be the only option, but unfortunately it creates the need for additional Active Directory licenses and therefore higher costs.

Is there any other solution?

SSAS works with windows authentication only. Being in a domain is the normal way to do this, hence Active Directory is usually the way to go. You have not provided much details in terms of suggesting something else.


MySQL Read lock and query performance analyses I've a table with the following structure.

CREATE TABLE `wallet_details` (
  `id` varchar(32) NOT NULL,
  `status` varchar(16) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`(3))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

It contains around 30 Million rows.

I need to run the following query on it:

select * from my_table where `status` = "UNASSIGNED" limit 1 for update

Following is the explain output of it:

mysql> explain select * from my_table where `status` = "UNASSIGNED" limit 1 for update;
+----+-------------+----------------+------------+------+---------------+------------+---------+-------+----------+----------+-------------+
| id | select_type | table          | partitions | type | possible_keys | key        | key_len | ref   | rows     | filtered | Extra       |
+----+-------------+----------------+------------+------+---------------+------------+---------+-------+----------+----------+-------------+
|  1 | SIMPLE      | my_table       | NULL       | ref  | idx_status    | idx_status | 11      | const | 12898668 |   100.00 | Using where |
+----+-------------+----------------+------------+------+---------------+------------+---------+-------+----------+----------+-------------+
1 row in set, 1 warning (0.09 sec)

The above query is not scaling very much and I wanted to understand how the above works, at which stage is the query behaving undesirably/heavily, and how can I improve its performance.

Few of my questions are:

Would the query run through all of the 12 million rows before fetching the final row (see query has limit 1 condition)?
On how many rows would the read lock be on?

Don't use "prefix indexes" (status(3)); they usually hurt more than they help.
Shrink the table size...
What kind of values are in id VARCHAR(32).  (It is unusually to have such a bulky PRIMARY KEY.)
Change status from a multi-byte varchar to a 1-byte ENUM.
If there are other columns or indexes in the table, we need to see them in order to understand whether there are side issues.
What is the value of innodb_buffer_pool_size?  How much RAM do you have?
The query will (based on the EXPLAIN) scan through the table until it finds the first entry with "UNASSIGNED".  Without knowing the distribution of the data, I can't say when that would occur.
Eliminating the prefixing may speed things up.
Saying SELECT id instead of SELECT * may speed things up.
(I don't know how whether the rows before the first "UNASSIGNED" will be locked.)


union only dispalying the first query First time posting in Stack Exchange so excuse any faux pas (well let me know them of course).

I'm using union for the first time on an already Frankenstein'ed query I've pull together so I'm looking to get insight into what might be preventing the 2nd part of the union. The query's business problem is sales for a customer have a particular point multiplier for their type of product, and are elliglbe for a redemption once they reach a threshold. So the query is adding this multiplier in, filtering based on the HAVING threshold query, and then dividing by the redemptions.

SELECT
    SUM(points/1000) AS QUARTER_1
FROM (
    SELECT ID,
        SUM(CASE WHEN multiplier = 'TWO' THEN (sales * 2)
            WHEN multiplier = 'ONE' THEN (sales * 1)
            ELSE 0 END ) as points
    FROM transactions
    where start_trx_date between '2017-07-01' AND '2017-10-01'
    group by ID
    HAVING
        SUM(CASE WHEN multiplier = 'TWO' THEN (sales * 2)
            WHEN multiplier = 'ONE' THEN (sales * 1)
            ELSE 0 END ) > 1000
    ) t
UNION
 SELECT
    SUM(points/1000) AS QUARTER_2
FROM (
    SELECT ID,
        SUM(CASE WHEN multiplier = 'TWO' THEN (sales * 2)
            WHEN multiplier = 'ONE' THEN (sales * 1)
            ELSE 0 END ) as points
    FROM transactions
    where start_trx_date between '2017-07-01' AND '2017-10-01'
    group by ID
    HAVING
        SUM(CASE WHEN multiplier = 'TWO' THEN (sales * 2)
            WHEN multiplier = 'ONE' THEN (sales * 1)
            ELSE 0 END ) > 1000
    ) t

The query is working in the sense that no errors are coming up, the problem is my result is one cell of Quarter_1 - any clues on what I need to do?

By default UNION - merge distinct values (https://www.postgresql.org/docs/10/static/queries-union.html) so if your quarter values same(and looking for code - it is exactly) - one of the results would be omitted.

Use UNION ALL if need keep all results


msdb.dbo.sysjobhistory data gets deleted whenever SQL Server services are restarted I am facing an issue where my msdb.dbo.sysjobhistory data is getting deleted whenever my SQL Server services are restarted or the instance fails over.

I am using SQL Server 2014 SP2 in a cluster environment, with below SQL Agent History setting:

Can anyone please suggest what the issue is?

My response is based on possible reasons of problem.
Make sure you have configured History setting properly and the job to purge history scheduled properly. To validate these settings:

Right click on SQL Server Agent and got to Properties.

Go to History tab.

Make sure setting is configured properly.

The job named syspolicy_purge_history used to execute above settings.

So right click on the job in Jobs folder in SQL Server Agent and check Schedule of the Job.

It should be scheduled according to your requirement.

Thanks!