A flag (indicator) that shows for each row which conditions was used to select it Suppose I have the table foo. I want to filter the rows of this table using different conditions (cond_1, cond_2, ..., cond_n - the order is important) combined in a disjunction, i.e.:
SELECT * FROM foo WHERE cond_1 OR cond_2 OR ... OR cond_n;

Is it possible (in PostgreSQL 13) to distinguish for each row which condition cond_i was used to select it? Since I stated that the order is important the question may be reformulated as - I want to know the first cond_i that selected the row, for each row.

Now consider that I have in foo a column for this purpose, e.g. selected_by.

Is it possible to distinguish for each row which condition cond_i was used to select it and store it in selected_by? (the same as 1st question but for an UPDATE statement)

Two things are important: the order of conditions (as it was highlighted earlier); performance (i.e. table foo may have many rows, the number of conditions is small, up to 5-10).

If your conditions are mutually exclusive (you you only care about matching the first of an order you control) then you could get away with a single column:
SELECT *,
  CASE
    WHEN name = 'meatfeast' THEN 'name'
    WHEN diet = 'vegan' THEN 'diet'
  END as reason_for_appearing
FROM
  pizza
WHERE
  name = 'meatfeast' OR diet = 'vegan'

You repeat your where conditions as individual clauses of a case when, (in the order you care about them)

If they aren't mutually exclusive and you want to know all the reasons why it might have matched you'll either need multiple columns ..
SELECT *,
  CASE WHEN name = 'john' THEN 'yes' END as because_of_name,
  CASE WHEN city = 'Chicago' THEN 'yes' END as because_of_city
FROM
  person
WHERE
  name = 'john' OR city = 'chicago'

.. unless you want to get really involved with a huge case when of all the conditions combinations (which is 2^N, as I'm sure you can imagine)
Or you might alternatively do some kind of concat you can pick apart programmatically or the user can read
SELECT *,
  CONCAT(
    CASE WHEN name = 'john' THEN 'name' END,
    CASE WHEN city = 'Chicago' THEN ' city' END
  ) as because_of
FROM
  person
WHERE
  name = 'john' OR city = 'chicago'

I suppose it rather depends on what you want to do with it after


In SQL Server, what's the impact of a checkpoint to log records in log buffer? Does checkpoint operations flush everything in the log buffer to log file? Or just the log records relating to the dirty pages that are about to be flushed? I found some inconsistent description about this by the top names in the SQL Server industry.
From Kalen Delaney's "Microsoft SQL Server 2012 Internals":

Checkpoint operations also write log records from transactions in progress to disk because the cached log records are also considered to be dirty.

"from transactions in progress", yes that's reasonable, since the log records for committed transactions were already written to disk. So it basically means all unflushed log records will be flushed.
From Itzik Ben-Gan's "Understanding log buffer flushes" at https://sqlperformance.com/2018/11/sql-performance/understanding-log-buffer-flushes:

SQL Server needs to harden dirty data pages, e.g., during a checkpoint process, and the log records representing the changes to those pages were not yet hardened (write ahead logging, or WAL in short)

Are the the log records in log buffer that corresponding to the dirty pages all unflushed log records in log buffer? I'm not sure. Is Itzik's description basically means the same thing as Kalen's description?

We could implement a DBMS such that every thread that needs to write a log record writes it straight to disk immediately and waits for acknowledgement. This would be inefficient since there would be many small IOs and many threads waiting. It is more efficient for each thread to write to an in-memory buffer and have that buffer written to disk as a single large IO. All threads that write log records add to this one buffer and when it is written to disk (i.e. "flushed" or "hardened") all log records from all threads are written.
So when must that buffer be flushed? There are three mandatory circumstances

When a transaction commits.
When a checkpoint is taken.
When the buffer is full.

The system can additionally write the buffer any time it chooses. The
Log records must be flushed before a commit acknowledgement is sent to the client to satisfy the durability guarantee of ACID.
Checkpoints flush dirty pages from the bufferpool to disk, whether the transaction which dirtied those pages has committed or not. On restart after failure the DBMS needs a way to figure out which changes to those dirty pages should be retained and which removed. SQL Server follows the ARIES recovery protocol for recovery and ARIES requires the write ahead log to be flushed before the dirty pages are flushed.
Naturally the buffer must be of finite size and so the system must be able to empty it at any point. The log contains many types of record other than user data changes. ARIES protocol takes care of the various combinations of flushed log, flushed or unflushed bufferpool pages, and committed, uncommitted and rolled back transactions.
The authors you quote are saying the same thing - that log records must be flushed before the corresponding dirty pages can be flushed. This is an echo of the documentation linked previously

SQL Server has logic that prevents a dirty page from being flushed before the associated log record is written.


The disappearing act of the "Invalid length parameter passed to the LEFT or SUBSTRING function" error I'm running into the "Invalid length parameter passed to the LEFT or SUBSTRING function" error, but it goes away and the query works when I include the column I'm passing into those functions, any clue?

Doesn't work:

SELECT
  SQT.QUOTATIONID,
  UPPER(LEFT(Email.LOCATOR, CHARINDEX('@', Email.Locator) - 1)) AS Manager
  --, Email.LOCATOR
FROM SALESQUOTATIONTABLE AS SQT
INNER JOIN HCMWORKER AS H
    ON SQT.WORKERSALESRESPONSIBLE = H.RECID
INNER JOIN DIRPARTYTABLE AS D
    ON H.PERSON = D.RECID
INNER JOIN LOGISTICSELECTRONICADDRESS AS Email
    ON D.PRIMARYCONTACTEMAIL = Email.RECID

  Invalid length parameter passed to the LEFT or SUBSTRING function

Works:

SELECT
  SQT.QUOTATIONID,
  UPPER(LEFT(Email.LOCATOR, CHARINDEX('@', Email.Locator) - 1)) AS Manager
  , Email.LOCATOR
FROM SALESQUOTATIONTABLE AS SQT
INNER JOIN HCMWORKER AS H
    ON SQT.WORKERSALESRESPONSIBLE = H.RECID
INNER JOIN DIRPARTYTABLE AS D
    ON H.PERSON = D.RECID
INNER JOIN LOGISTICSELECTRONICADDRESS AS Email
    ON D.PRIMARYCONTACTEMAIL = Email.RECID

This is continuously repeatable, and the only change I made to the query was included the "Email.LOCATOR" column.
This query was working for years and just randomly stopped working today. I'm pretty certain it's a data issue, but am still perplexed why selecting the Email.LOCATOR column fixes the issue.

I think the issue is similar to this one: Strange behaviour in TSQL function (parameter with int variable or NULL behaves differently)?

Specifically what Aaron Bertrand mentions in his answer:

  ... because you can't always rely on SQL Server filtering rows before attempting calculations

What I think happens is that Email.Locator has some values that do not contain a @. When these values are processed, the CHARINDEX() is 0 and the LEFT() is called with parameter -1, so the error is thrown.

Bu why the error is thrown in one query and not the other? It's likely because the two queries are executed with different plans. The optimizer chooses a different plan (due to the extra column or due different statistics than last month or for whatever reason) and all the values of the column are read (and the calculations are done) before the joins to the other tables.

To avoid the issue, I suggest you use CASE, replacing

LEFT(Email.Locator, CHARINDEX('@', Email.Locator) - 1)

with:

LEFT(Email.Locator, CASE WHEN CHARINDEX('@', Email.Locator) > 0
                         THEN CHARINDEX('@', Email.Locator) - 1
                         ELSE 0
                    END)


Should DBAs also be acting as Security Administrators? One standard I have been tasked to bring my databases up to asks me to separate security functionality and non-security functionality. Based on supplemental reading, this seems to be talking about hardware, software, and firmware dealing with encryption, authentication, authorizations and auditing.

Pursuant to this goal, and just as a general question to you experienced people, is it acceptable for a DBA to also be acting as a Security Administrator? Separation of duties would seem to say no, as DBA work, while administrative and highly privileged in nature, is mostly not security-related in nature. But if it would be prohibitive to hire someone as a DBA and another as a security administrator, what would be the largest areas of concern, if any, if one person performed both of these sets of duties?

ETA: I can't give many details to the best of my understanding of my restrictions, but security concerns are high, budget is tight, and the databases range from small, non-application databases with few users to users in the thousands with an expansive number of tables, each with an expansive number of rows of information. DBAs are utilizing the DBA role, so the most expansive set of privileges. And it looks like I accidentally removed the oracle tag, so I'll put that back on and mention that this is an Oracle database.

For the sake of discussion, let's focus on the largest subset of databases. These have personal and financial information in them. I think I can say that we are shooting for a standard that heavily leans on the NIST database standard, but isn't NIST for whatever reason. Not sure if that helps at all.

I have functioned as all the roles in the past. Small place, one server, 3 databases and I was the only IT. So I did it all; DBA, cabling, hardware, software, programming. It should be separated out, but if you don't have the manpower or the money, then combined roles is the only way to cover them all.


Linked Servers security context and transaction logging I have many large logging tables that are populating with data constantly. The information is rarely or never read and I want to move them out of the database. So, the idea is to:

create linked server
create new database on the linked server with mirror logging tables
each night/several days/week a job will delete the records from the first database and insert them into the second one

The general goal is to reduce the first database size and put it on SSD drives and put the historical not queried data on slow storage.

The mirror tables will have less indexes and can be additionally compressed via row,page,column store or clr compressions. It will be working under simple recovery model.

I have never used linked servers and have the following questions:

If large amount of data is deleted from the first database and inserted into the second one, only the delete operation is logged in the transaction log file of the first database, right?
If I have two linked servers with many databases like this:

Server A             Server A1

Database A           Database A1
Database B           Database B1
Database C           Database C1

How can a restrict a user who has can query database A and A1, to query database B1 and C1?

Don't use a linked server for this. It will likely be slow. Linked servers are suitable for small queries over remote databases, not for bulk loading data.

I would use SSIS or whichever ETL system you feel more comfortable with.


Show Monthly Revenue By EmployeeID I have a database with sale data for the past 5 years, 2012 - current.  I am in need of showing a breakdown by employeeID and the monthly sale amount.  I know this syntax will work, but it is going to be quite cumbersome to add a new case statement every time we roll into a new month.  Is there a quicker/more efficient way to accomplish this?

This is sample DDL (sample as it is only a very few rows)...

Declare @Monetary Table(empID varchar(10), empsaleamt float, empsaledate date)
Insert Into @Monetary (empID, empsaleamt, empsaledate) Values
('123abc', 100.00, '20160101'), ('123abc', 200.00, '20160101'), ('123abc', 300.00, '20160201')
,('456cde', 200.00, '20160101'), ('456cde', 100.00, '20160201'), ('456cde', 100.00, '20160301')

Select
empID
,Sum(case when (Year(empsaledate) = '2016' AND Month(empsaledate) = '01') Then empsaleamt Else 0 End) As Jan16Sales
,Sum(case when (Year(empsaledate) = '2016' AND Month(empsaledate) = '02') Then empsaleamt Else 0 End) As Feb16Sales
,Sum(case when (Year(empsaledate) = '2016' AND Month(empsaledate) = '03') Then empsaleamt Else 0 End) As Mar16Sales
FROM @Monetary
Group By empID
ORDER By empID ASC


Run several commands against data in text file (SQL Server 2008 R2) I'm new to this website so forgive me if I'm asking a rudimentary question and/or one that's been asked 100 times before. I wasn't able to find an answer during my searches here.

I'd like to have my script read in values from a txt file (one value per line) and have three different statements executed for each value.

Pseudo code:

$file = getfile('c:\file.txt');

foreach($value in $file)
{
    DELETE x WHERE x = '$value';
    DELETE y WHERE y = '$value';
    DELETE z WHERE z = '$value';
}

I understand that this may not be as easy as my pseudo code desires but all my searches on Google returned long articles with copious amounts of SQL that were way over my head.

I'm looking for examples that I can implement, good articles on the subject, and/or a slap in the face telling me to wake up and realize this is simply not easy in SQL.

Building on Rob's answer here's some sample code to do the work.

CREATE TABLE #Keys (YourKey int)

BULK INSERT #Keys FROM 'C:\YourFile.txt'

DELETE FROM YourTable
WHERE Id in (select YourKey from #Keys)

This will need to be cleaned up with your table and column names, but it'll do the delete as a single operation.


MySQL uses intersection instead compound index I have compound index on following columns:

(country_id, lazy, has_avatar, inactive, gender, id, age, challenge_count)

When I run following query:

SELECT COUNT(*)
FROM `user_profile`
WHERE (`user_profile`.`lazy` = 0 AND
      `user_profile`.`has_avatar` = 1 AND
      `user_profile`.`inactive` = 1 AND
       NOT (`user_profile`.`id` = 3247028 ) AND
      `user_profile`.`country_id` = 374 AND
      `user_profile`.`challenge_count` < 10 AND
      `user_profile`.`age` BETWEEN 18 and 28 AND
      `user_profile`.`gender` = 'F' )

It is used correctly. However, when I run following query it is not used.

The WHERE parameters are the same. In this case mysql uses intersection of separate indexes:

SELECT *
FROM `user_profile`
INNER JOIN `iqApp_basecountry`
ON ( `user_profile`.`country_id` = `iqApp_basecountry`.`id` )
INNER JOIN `auth_user` ON ( `user_profile`.`user_id` = `auth_user`.`id` )
LEFT OUTER JOIN `iqApp_relationshipstatus`
ON ( `user_profile`.`relationship_status_id` = `iqApp_relationshipstatus`.`id` )
LEFT OUTER JOIN `iqApp_workstatus`
ON ( `user_profile`.`work_status_id` = `iqApp_workstatus`.`id` )
LEFT OUTER JOIN `iqApp_fieldofwork`
ON ( `user_profile`.`field_of_work_id` = `iqApp_fieldofwork`.`id` )
LEFT OUTER JOIN `iqApp_fieldofstudy`
ON ( `user_profile`.`field_of_study_id` = `iqApp_fieldofstudy`.`id` )
LEFT OUTER JOIN `iqApp_educationlevel`
ON ( `user_profile`.`education_level_id` = `iqApp_educationlevel`.`id` )
LEFT OUTER JOIN `iqApp_religion` ON ( `user_profile`.`religion_id` = `iqApp_religion`.`id` )
WHERE (`user_profile`.`lazy` = 0 AND
     `user_profile`.`has_avatar` = 1 AND
     `user_profile`.`inactive` = 1 AND NOT
      (`user_profile`.`id` = 3247028 ) AND
     `user_profile`.`country_id` = 374 AND
     `user_profile`.`challenge_count` < 10 AND
     `user_profile`.`age` BETWEEN 18 and 28 AND
     `user_profile`.`gender` = 'F' )
  LIMIT 1

Here is explain:

SELECT_TYPE: SIMPLE
Table: user_profile
Type: index_merge

POSSIBLE_KEYS: PRIMARY,user_id,user_profile_d860be3c,age,gender,challenge_count,lazy,has_avatar,inactive,country_id,country_id_2

KEY
user_profile_d860be3c,lazy,has_avatar,inactive,gender

KEY_LEN
5,1,1,1,5

REF
None

ROWS
35394

EXTRA
Using intersect(user_profile_d860be3c,lazy,has_avatar,inactive,gender); Using where

The selectivity has changed with the added selections.So now the optimizer will think that an intersection of indexes is more efficient than just one index.Compound indexes have a cardinality that matches all their columns,it`s not separate for column so MySQL will try to reduce this selection with an index intersection.Find out which column is the most selective(has the least values returned) and rewrite it with this in mind.

You could turn off index merge with a SESSION option but this the bulldozer approach.


If the depth of tree is limited to 3 (case of your sample). You can do like this.

(SELECT t1.* FROM tree AS t1 LEFT JOIN tree AS t2 ON t2.parent_id = t1.uid LEFT JOIN tree AS t3 ON t3.parent_id = t2.uid  WHERE t1.parent_id = 1)
UNION
(SELECT t2.* FROM tree AS t1 LEFT JOIN tree AS t2 ON t2.parent_id = t1.uid LEFT JOIN tree AS t3 ON t3.parent_id = t2.uid  WHERE t1.parent_id = 1)
UNION
(SELECT t3.* FROM tree AS t1 LEFT JOIN tree AS t2 ON t2.parent_id = t1.uid LEFT JOIN tree AS t3 ON t3.parent_id = t2.uid  WHERE t1.parent_id = 1);

My solution don't respect DRY principle but this is the only simple solution that I found for now.

If you don't know in advance the maximum depth of your tree please check following questions and links that discuss the subject :

https://stackoverflow.com/questions/5688376/mysql-select-tree-parent-ids
https://stackoverflow.com/questions/18083546/mysql-recursivetree-parent-child-category
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

Hope this help!


After Insert Trigger never fires using Import Wizard Using Import Wizard I tried importing tables tcc_Block and PROJECT_IDENTITY.

These 2 table structures already exist in SQL Server and they are related with ProjectID columns.  PROJECT_IDENTITY has ProjectID as PK and tcc_Block as FK key.

Any time I import tables, ProjectID in parent table is created and incremented, but the one in child table is always Null.

The trigger never fires!?

-- On tcc_Block table I need to do the update (below) AFTER INSERT
-- Tried Trigger, but none would work.

ALTER TRIGGER [dbo].[InsertTest]
ON [dbo].[tcc_Block]
AFTER INSERT
NOT FOR REPLICATION
AS
BEGIN

    DECLARE @proj int;

    SELECT @proj =max(ProjectID)
    FROM PROJECT_IDENTITY ;

    UPDATE tcc_Block
    SET ProjectID = @proj
    WHERE ProjectID is null ;

END;
GO

The SQL Server Import and Export Wizard does not expose a way to enable triggers directly.

You could save the result as an SSIS package, then edit that to enable the FIRE_TRIGGERS option in the component it uses.

Alternatively, and probably more simply, you could choose to use one of the other data import methods that more easily allow you to specify that triggers should be fired.

Related official documentation:

Bulk Import and Export of Data (SQL Server)
Controlling Trigger Execution When Bulk Importing Data


Why am I not seeing any benefits of parallel execution when parallel clause is used? I have these two methods of creating a table and populating with rows. The first one uses a
parallel (degree 3) clause, so I expected the inserts to be much faster. But I timed the time taken for a) and b) and it turns out to be almost the same. There is no indication that method a) is faster, in fact in some of the runs, method b) was faster.

I thought that parallel clause would perform the inserts in parallel and hence be much faster. What am I missing here?

a)

create table TAB1
(col0 number not null,
 col1 number,
 col2 number,
 col3 varchar2(25)) parallel (degree 3)
 storage (initial 100K next 100K pctincrease 0);

begin
 for i in 1..300000 loop
  insert into TAB1 values(i-1,i,300000-i,null);
 end loop;
 commit;
end;
/

b)

create table TAB2
(col0 number not null,
 col1 number,
 col2 number,
 col3 varchar2(25))
 storage (initial 100K next 100K pctincrease 0);

begin
 for i in 1..300000 loop
  insert into TAB2 values(i-1,i,300000-i,null);
 end loop;
 commit;
end;
/

You're not seeing the benefits of parallel execution because both insert methods use single-row inserts, hence parallel does not kick in !

Parallel execution is only available for bulk operations. Lots of tiny operations doesn't qualify as a set operation. You're inserting rows one by one here, you should try it with a bulk operation (INSERT /*+APPEND*/ INTO SELECT, CREATE TABLE AS SELECT ...) and see if you can achieve some performance improvement.

You can tell when Oracle does a parallel execution because the explain plan will display parallel operations.

For further reading:

Parallel introduction in the concepts guide
A short introduction about parallel execution by Tom Kyte


What SQL Server edition supports CDC? Does any one know which SQL Server 2014/2016 edition/version, supports CDC (Change Data Capture)?

Change Data Capture is supported in the Developer and Enterprise Editions of SQL Server up to and including SQL Server 2016 RTM.

SQL Server from 2016 with Service Pack 1 and higher also supports CDC in Standard Edition.

Source: https://www.microsoft.com/en-us/sql-server/sql-server-editions


How critical is it if SYSAUX is full? I recently got an Ora-1688 alert and realized my SYSAUX is 99.7% full.
Is this a critical issue that has to be dealt with immediately?
Will this cause my system to stop?

If your Database has enabled AUDIT and your auditing tablespace SYSAUX gets 100% full,  NONE of database users will be able to log in to database anymore. Your database will not crash but only SYS db user will be able to log in - the first thing you should do in this case is to extend the SYSAUX tablespace (add a data file) and deal later with how SYSAUX got full at the first place.


Trimming down a big SQL Server database I have a large SQL Server 2008 test database (1.9TB) and I want to reduced its size on the disk. I have been deleting unused tables, and purging significant amounts of data, but its size on disk it no reducing. What should I do?

I've looked at the Shrink task in SSMS, but the options are bewildering. Do I shrink the database or the files? And with what options?

Select the second radio button Reorganize pages before releasing unused space, and then in the Shrink file to: option, put the minimum MB specified. You will be corrected if you put a lower MB than that specified. Then click OK.

I have found that using the DBCC commands for these work all the time, as the GUI did not work in some of my cases.

Also note, the extra space is there for a reason. You will need the space for future growth, but then that is a whole new topic in performance.


Compare two Oracle database roles I'm trying to re-engineer my corporates user managament and want to compare all the roles with each other to find duplicate ones. Doing it by hand is really tiring and I need to compare like 150 roles. I wrote the following script:

DEFINE ROLE_1 = &INPUT_1
DEFINE ROLE_2 = &INPUT_2

SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING ON
SET LINESIZE 140

COLUMN PRIVILEGE    FORMAT A20    HEADING "PRIVILEGE"
COLUMN TABLE_NAME   FORMAT A30    HEADING "TABLE_NAME"
COLUMN GRANTABLE    FORMAT A5     HEADING "GRANTABLE"

PROMPT
PROMPT OVERLAPPINGS BETWEEN 1st ROLE AND 2nd ROLE
PROMPT

SELECT
    PRIVILEGE,
    TABLE_NAME,
    GRANTABLE
FROM
    SYS.DBA_TAB_PRIVS
WHERE
    GRANTEE IN ( '&ROLE_1' )

INTERSECT

SELECT
    PRIVILEGE,
    TABLE_NAME,
    GRANTABLE
FROM
    SYS.DBA_TAB_PRIVS
WHERE
    GRANTEE IN ( '&ROLE_2' )
ORDER BY TABLE_NAME, PRIVILEGE ASC
/

PROMPT
PROMPT PRIVILEGES IN 1st ROLE BUT NOT IN 2nd ROLE
PROMPT

SELECT
    PRIVILEGE,
    TABLE_NAME,
    GRANTABLE
FROM
    SYS.DBA_TAB_PRIVS
WHERE
    GRANTEE IN ( '&ROLE_1' )

MINUS

SELECT
    PRIVILEGE,
    TABLE_NAME,
    GRANTABLE
FROM
    SYS.DBA_TAB_PRIVS
WHERE
    GRANTEE IN ( '&ROLE_2' )
ORDER BY TABLE_NAME, PRIVILEGE ASC
/

PROMPT
PROMPT PRIVILEGES IN 2nd ROLE BUT NOT IN 1st ROLE
PROMPT

SELECT
    PRIVILEGE,
    TABLE_NAME,
    GRANTABLE
FROM
    SYS.DBA_TAB_PRIVS
WHERE
    GRANTEE IN ( '&ROLE_2' )

MINUS

SELECT
    PRIVILEGE,
    TABLE_NAME,
    GRANTABLE
FROM
    SYS.DBA_TAB_PRIVS
WHERE
    GRANTEE IN ( '&ROLE_1' )
ORDER BY TABLE_NAME, PRIVILEGE ASC
/

It works all fine, but for some reason it doesn't display the table head.

The real problem why I'm asking is, is there a more comfortable way to automatically compare all roles to one another? Some neat way to do all this? I'm quite new to PL/SQL and don't really know how to loop this thing up. Is there even maybe a possibly way to display the corresponding column of the tables?

Here is a query that finds duplicate role_tab_privs. It uses Oracle LISTAGG aggregate function to create a list of privileges for each role, owner, table.

SELECT arole, brole, aowner, atable, aprivlist
FROM
      --join to each grouped view by role, owner, table
      (
      SELECT A.ROLE AS arole, A.owner AS aowner, A.table_name AS atable,
                  --one field for all privileges concatenated with | separator
                  listagg(a.privilege, ' | ') WITHIN GROUP(ORDER BY a.privilege) AS aprivlist
      FROM role_tab_privs a
      GROUP BY A.ROLE, A.owner, A.table_name
      )
JOIN
      (
      SELECT b.ROLE as brole, b.owner as bowner, b.table_name as btable,
                  listagg(b.privilege, ' | ') WITHIN GROUP(ORDER BY b.privilege) AS bprivlist
      FROM role_tab_privs b
      GROUP BY b.ROLE, b.owner, b.table_name
      )
  -- the roles cannot be equal but the owner, table, and privileges must be
  ON AROLE != bROLE
 AND aowner = bowner
 AND atable = btable
 AND aprivlist = bprivlist
ORDER BY aowner, atable, arole
;

Note that the LISTAGG is used only on the privileges within a role, owner, table group. The result of a `LISTAGG' function must fit within the max varchar(4000) or the error:

ORA-01489: result of string concatenation is too long

This query gives a good view of how the table privileges relate.


When you back up a database and scripts are running at the same time, at what point in time does the backup reflect? If I run a SQL Server database backup via Management Studio and there are scripts running at the same time, does the backup reflect the point in time when the backup was started or when it ended? My db is about 10GB so the backup takes some time, meaning there's lots of things that could go on in the meantime.

I'm using SQL Server 2008 R2

I just posted this question in SO but think this is probably a better place for it?

Assuming we're talking about a full or differential backup, a marker is placed at the start of the backup process. At the end of the backup process, any transactions that committed during the process are rolled forward into the backup. Any transactions that are not completed are marked as rolled back within the backup. So, the short answer to your question is, all completed transactions from the beginning to the end of the backup process. During a restore operation, the final step is the cleanup of these transactions.


Index automation job script Our Production instance is running SQL Server 2014. There needs to be an automated Index defragmentation job that will either reorganize or rebuild the indexes based on the fragmentation level for a specific table in a specific database.
This job needs to be scheduled and ran on a weekly basis due to large data load and the recent performance issues we have been facing.
Unfortunately with the maintenance plan in 2014, I am unable to set the % limit of when the index should be rebuilt or reorganized. I have tried to test our Ola Hallengren script, but that works best with 2016 or 2017.
I have a script that detects the fragmentation level:
SELECT S.name as 'Schema',
 T.name as 'Table',
 I.name as 'Index',
 DDIPS.avg_fragmentation_in_percent,
 DDIPS.page_count
 FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
 INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
 INNER JOIN sys.schemas S on T.schema_id = S.schema_id
 INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
 AND DDIPS.index_id = I.index_id
 WHERE DDIPS.database_id = DB_ID()
 and I.name is not null
 AND DDIPS.avg_fragmentation_in_percent > 0
 ORDER BY DDIPS.avg_fragmentation_in_percent desc

Now all I need to do is to combine the rest of the requirement:
-if avg_fragmentation_in_percent > 5 AND < 30 , then ALTER INDEX REORGANIZE

-if avg_fragmentation_in_percent > 30, then ALTER INDEX REBUILD

Can someone please help me with the query? Thanks in advance.
**Edited/ Update:
When I execute this query:
SELECT S.name as 'Schema',
 T.name as 'Table',
 I.name as 'Index',
 DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
 INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
 INNER JOIN sys.schemas S on T.schema_id = S.schema_id
 INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
 AND DDIPS.index_id = I.index_id
 WHERE DDIPS.database_id = DB_ID()
 and I.name is not null
 and T.name= 'WSSSync_Partition25'
 ORDER BY DDIPS.avg_fragmentation_in_percent desc

This is my output (Take a note of the 99% fragementation) :

Then I run Ola's script:
EXECUTE dbo.IndexOptimize
@Databases = 'WSS_UsageApplication',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@Indexes = 'WSS_UsageApplication.dbo.WSSSync_Partition25'

The script runs perfectly, however when I re run the first query to find the  fragmentation level. The fragmentation remains the same at 99.6%.

I would say that Ola scripts work well for SQL 2014 too.
Here is what you should do:
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30

Anyway I would like to suggest you also to use higher thresholds like 50-80.
With modern storages it is better to focus IO on statistics update.
For your query above...
SELECT S.name as 'Schema',
 T.name as 'Table',
 I.name as 'Index',
 DDIPS.avg_fragmentation_in_percent,
 DDIPS.page_count
 CASE
  WHEN avg_fragmentation_in_percent BETWEEN 5 AND  30  then 'ALTER INDEX REORGANIZE'
  WHEN avg_fragmentation_in_percent > 30, then 'ALTER INDEX REBUILD'
  ELSE ''
 END AS CommandType
 FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
 INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
 INNER JOIN sys.schemas S on T.schema_id = S.schema_id
 INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
 AND DDIPS.index_id = I.index_id
 WHERE DDIPS.database_id = DB_ID()
 and I.name is not null
 AND DDIPS.avg_fragmentation_in_percent > 0
 ORDER BY DDIPS.avg_fragmentation_in_percent desc

Please note that this parameter exists in Ola reindexing scrips:
@MinNumberOfPages

Set a size, in pages; indexes with fewer number of pages are skipped for index maintenance. The default is 1000 pages. This is based on Microsoft’s recommendation.
IndexOptimize checks page_count in sys.dm_db_index_physical_stats to determine the size of the index.


can you use msdb.dbo.sp_send_dbmail to set up calendar appointments? For example, in health care, every employee gets a PPD test (they jab your arm with something) and you have to go back in 48 hours to see if you've sprouted an extra head.

Everybody forgets.

The nurses document in their EHR that they jabbed the patient, and I can send out an email reminding the employee to go check for their extra head, but I'd rather make it a calendar invite.

Question

How would you set up an outlook calendar appointment from SQL Server?

(I dont have outlook to test it .. so the code is not tested by me)

Borrowed from : Send Outlook Calendar Requests in Email

EXEC msdb.dbo.sp_send_dbmail
  @recipients ='<your email address>'
  ,@subject = 'iCalendar Example'
  , @query = 'SET NOCOUNT ON;SELECT ''BEGIN:VCALENDAR''+ CHAR(13)+ ''VERSION:2.0''+ CHAR(13)+ ''METHOD:PUBLISH''+ CHAR(13)+ ''BEGIN:VEVENT''+ CHAR(13)+ ''CLASS:PUBLIC''+ CHAR(13)+ ''DESCRIPTION:This is the iCalendar example Most client application support it''+ CHAR(13)+ ''SUMMARY:iCalendar''+ CHAR(13)+ ''DTEND:20150325T110000''+ CHAR(13)+ ''DTSTART:20150325T120000''+ CHAR(13)+ ''LOCATION:My Office''+ CHAR(13)+ ''END:VEVENT''+ CHAR(13)+ ''END:VCALENDAR'''
  , @attach_query_result_as_file = 1
  , @query_result_header = 0
  , @query_result_separator = 'CHAR(10)+CHAR(13)'
  , @exclude_query_output = 1
  , @query_attachment_filename = '123.ics'


Detecting changes in a SQL Server table In my application, with a DB running on SQL Server 2012, I've got a job (scheduled task) that periodically executes an expensive query and writes the results to a table that can later be queried by the application.

Ideally, I would like to run that expensive query only if something changed since the query has last executed. Since the source tables are very big, I cannot just select a checksum over all candidate columns or something like that.

I've got the following ideas:

Explicitly write a last changed timestamp, a "must be queries" flag, or something like this to a tracking table whenever I change something in a source table.
Use a trigger to do the same.

However, I'd really like to know whether there is a lightweight way to detect changes on a table without me explicitly tracking the writes. Can I, for example, get the "current" ROWVERSION of a table or something like that?

No, there isn't any. Any sort of 'last updated at' tracking would run into a severe performance problem as all updates, from all transactions, would attempt to update the one record tracking the 'last updated at'. This would effectively mean only one transaction can update the table at any moment, and all other transactions have to wait for the first one to commit. Complete Serialization. The number of admins/devs willing to put up with such performance penalty just for the benefit of knowing when the last update occurred is probably small.

So you are stranded to handle it via custom code. That means triggers since the alternative (detecting from log records) is a prerogative reserved only for transactional replication (or it's CDC alter-ego). Be aware that if you try to track it via a 'last updated at' column then you'll be facing exactly the serialization problem mentioned above. If update concurrency is important then you'd have to use a queue mechanism (trigger uses an INSERT and then a process aggregates the inserted values to formulate the 'last updated at'). Do not try to cheat with some 'clever' solution like sneaking at the current identity or looking up sys.dm_db_index_usage_stats. And also an 'updated_at' per-record column, like Rails timestamps have, is not working because it does not detect deletes...

Is there any 'lightweight' alternative? Actually there is one, but it is difficult to say whether it will work for you and is difficult to get it right: Query Notifications. Query Notification does exactly that, it will set up a notification if any data has changes and you need to refresh your query. Although most devs are familiar only with its .Net incarnation as SqlDependency, Query Notification can be used as a long lived, persisted mechanism to detect data change. Compared with true change tracking it is going to be really lightweight, and its semantics are closer to your needs (something, anything, changed, so you need to rerun the query).

But in the end, in your place, I would really reconsider my assumptions and go back to the drawing board. Perhaps you can use log shipping or replication to set up a reporting database, on a different server. What I read between the lines is that you're in need of a proper ETL pipe-line and an analytics data warehouse...


Can SSMS Object Explorer Details configurations be exported? A number of employees make use of SQL Server Management Studio (SSMS). I have a configuration file for all new users for Environment (etc) configurations.

I want to deliver a uniform view for each database object type (tables, stored procedures, security, etc.).

I'm looking for a method to export an Object Explorer Details configuration or a program to configure the Object Explorer Details using a secondary source (text file, database).

I've provided an illustration of two Object Explorer Details configurations.

Object Explorer Details: View 1 (for Tables and Stored Procedures)

Object Explorer Details: View 2 (for Tables and Stored Procedures)

In advance, thank you for your attention.

I don't think so. These settings are stored along with a bunch of other UI configuration in a binary file in your user profile directory.


Best db setup to accomplish yes/no list of clients for daily calls I will preface this by saying I am a relative novice to Access but I have managed to develop some basic databases. I have some working knowledge of expressions and macros but VBA is still a bit of a mystery to me.

I have a problem that is simple to visualise mentally but seems to be quite a challenge to implement in a database (at least for me :)).
I work in a program which involves calling clients on a daily basis who are typically elderly and living alone who have various health issues which render them potentially vulnerable to sudden adverse health events. We need to improve our management of our daily call roster which involves determining each day whether clients need to be called or not based on two basic conditions:

Pre-selected regular days the client has established not to be called. (e.g. no call Mon, Wed, Fri etc.)
Ad-hoc changes to call days (going into hospital, away on holiday from date to date etc.).

I initially started the project by incorporating a multivalue field in the Clients table which makes it easy to select or deselect days, but when it comes to developing an expression to filter the list for any particular day it is proving to be a challenge.

I then considered a "DaysOfWeek" table which could be referenced by a junction table "ClientCallDays". The challenge is in being able to resolve any resulting query to only one result for each client each day.

I am now considering including yes/no fields for each day of the week, which is easy then to deal with in a form or IIF query with OR operators nested (e.g. "If today falls between dateX and dateY or is selected in the Yes/No field corresponding to Today, then don't call, otherwise call") but may violate normalization.

Sounds like you need a proper CRM  (:
For the days of the week I think you have it right, have a column for each day of the week and if a day is a "no call day" you check it off.  Your query would then say, "ok it's Monday who can I call?".

As far as the adhoc days (on vacation etc).  You may just want to have a notes field that shows on each record on the call list.  This can be reviewed before calling and modified or skipped based on the content of the note.


What is the difference between LOB and binary data in SQL Server? What is the difference when speaking about 'LOB' and 'binary' data?

Is it the same, in terms of binary data being stored in a separate filegroup, or are there differences?

According to Technet: Data Types (Transact-SQL)

  In SQL Server, based on their storage characteristics, some data types
  are designated as belonging to the following groups:

  Large value data types: varchar(max), nvarchar(max), and    varbinary(max)
  Large object data types: text, ntext, image, varchar(max),    nvarchar(max), varbinary(max), and xml

So these types are defined as LOB data types.
When you asked about binary data I suppose you are talking about the data of binary data type.

Binary data type can be one of two kinds: fixed-length and variable length.
Fixed-length binary data type or variable-length varbinary data type with the maximum number of characters instead of MAX specifier is always stored in row in the first case and (may be) off-row in the second and is NOT LOB data type.

On the other hand, according to this classification (from the same Technet article):

varchar(max), nvarchar(max), text, ntext data types are CHARACTER strings and not BINARY strings.

So LOB data types include CHARACTER strings that are not binary strings.

Conclusion

BINARY data <> LOB data

And the only case when it's the same is when your data is defined as VARBINARY(MAX)

Back to your question from the comment on how LOB data can be stored.

If the value is one of the old legacy types (text, ntext, image) then it is stored off-row by default.

Off-row means that there is a pointer from the data record which points to the location of the record storing the actual LOB value or the start of the tree that stores the LOB value:

This image is from Pro SQL Server Internals 1st Edition
by Dmitri Korotkevitch book.

But you can force the value to go in-row (obviously with the limit) using text in row option.

Starting with SQL server 2005 new LOB data types were introduced, these new types: varchar(max), nvarchar(max), varbinary(max) are stored in-row by default (of course with the limit of 8Kb)

............................................................

To complete a picture.

There is another kind of data that is stored off-row, that is row-overflow data.

Thanks to this feature, starting with 2005 it's possible not only to DEFINE (as it was in 2000) some variable-length columns with the sum of sizes that exceeds 8060 bytes, but also to INSERT data that will exceed this limit, some of these values will be pushed off-row (i.e. the value will be stored on off-row page and IN ROW there will be only a pointer to the row stored on the row-overflow page):

  A table can contain a maximum of 8,060 bytes per row. In SQL Server
  2008, this restriction is relaxed for tables that contain varchar,
  nvarchar, varbinary, sql_variant, or CLR user-defined type columns.
  The length of each one of these columns must still fall within the
  limit of 8,000 bytes; however, their combined widths can exceed the
  8,060-byte limit. This applies to varchar, nvarchar, varbinary,
  sql_variant, or CLR user-defined type columns when they are created
  and modified, and also to when data is updated or inserted.

  This restriction does not apply to varchar(max), nvarchar(max),
  varbinary(max), text, image, or xml columns

(From Row-Overflow Data Exceeding 8 KB)

  The max specifier expands the storage capabilities of the varchar,
  nvarchar, and varbinary data types. varchar(max), nvarchar(max), and
  varbinary(max) are collectively called large-value data types. You can
  use the large-value data types to store up to 2^31-1 bytes of data.

Back to the origin of this your question, how LOB data can be moved.

When we are talking about moving data to another filegroup, and about difficulties we have with LOB data, we refer to LOB data and NOT to binary data that is stored in binary columns (in-row data).

From What about moving LOB data? by Kimberly L. Tripp

  In versions prior to SQL Server 2012, an index that includes LOB
  columns cannot be built or rebuilt using online operations. However,
  even with OFFLINE index builds or rebuilds, existing LOB data is not
  moved. The one exception is when you change from a partitioned table
  to a non-partitioned table or vice versa. In honesty, the limitations
  and requirements around LOB data are a bit strange. When you create a
  table you can specify TEXTIMAGE_ON. This allows the LOB data for that
  table to reside on a filegroup other than where the data resides.
  However, this option (TEXTIMAGE_ON) is NOT available for the CREATE
  INDEX statement.

  What if you want to move the LOBData to another filegroup? The
  TEXTIMAGE_ON option is not allowed during a CREATE INDEX statement.
  So, if you want to move the LOB data to another filegroup – which is
  different than the data, then “game over” there is NO way to do this
  without exporting/importing or using INSERT/SELECT or SELECT INTO. Of
  these, the best option is INSERT/SELECT as you’ll need to first define
  the table (and you can specific exactly where you want both the data
  and LOB data will reside) before you copy it over. But, this is
  single, large transaction and while you’re migrating there will have
  to be some downtime to switch everything (by dropping the original
  table and renaming the newly created table). And, I haven’t even
  started to mention what happens if this table is being referenced by
  others. That further complicates this process.

And to your last question:

  Is it the same, in terms of binary data being stored in a seperate
  filegroup

Separate filegroup is not a requirement for storing LOB data.
But it's possible to store LOB data separately using TEXTIMAGE_ON option:

  TEXTIMAGE_ON { filegroup| "default" } Indicates that the text, ntext,
  image, xml, varchar(max), nvarchar(max), varbinary(max), and CLR
  user-defined type columns (including geometry and geography) are
  stored on the specified filegroup.

  Varchar(max), nvarchar(max), varbinary(max), xml and large UDT values
  are stored directly in the data row, up to a limit of 8000 bytes and
  as long as the value can fit the record. If the value does not fit in
  the record, a pointer is sorted in-row and the rest is stored out of
  row in the LOB storage space. 0 is the default value. TEXTIMAGE_ON
  only changes the location of the "LOB storage space", it does not
  affect when data is stored in-row. Use large value types out of row
  option of sp_tableoption to store the entire LOB value out of the row.

And the data of fixed-length binary data type just cannot be stored in a separate filegroup. It's impossible because all the data is stored in-row, and it's syntactically impossible as

  TEXTIMAGE_ON is not allowed if there are no large value columns in the
  table

More on TEXTIMAGE_ON here: CREATE TABLE (Transact-SQL)


On DB2, who updated a record and what did the record look like before the update? (SQL Server 2008 R2 Standard, database under full recovery)

I have a table with fields id, firstname, lastname. A statement is executed:

insert into dbo.sometable (id, firstname, lastname) values (1, 'John', 'Smith')

After a few hours another statement is executed:

update dbo.sometable set firstname='Matt' where id=1

Is there a method or a tool that would allow us to see the history of a record (that a record was inserted on X date by Y person and these were the values inserted, and that same record was updated on A date by B person and these were the new values after the update)?

Users familiar with DB2 for i (ie, DB2 on the iSeries) might know of a TAA toolset command called CRTDBFJRN that can dump all transactions on a given table into another table giving a clear picture of the insert, the update and the field that was updated.

SQL Server Enterprise has CDC but we have a Standard edition; I could use triggers to track changed data by storing in an audit table. The use of triggers will require two more objects - a trigger and an audit table, and triggers would be fired on each change. It would be nice if a tool could replay transactions on demand from a transaction file.

What tools do you use or tasks do you perform when an auditor asks for a history of a financial record (inserted and updated on a single day, for example) such as: Who changed this record, what was changed, when was this record originally entered, what was the data when it was original entered etc?

Any help will be appreciated. Thank you.

I've tested two tools that can provide info about transactions including when, who, using which computer and application

ApexSQL Audit creates auditing triggers for you, so if you're not up to coding and creating triggers for each of your tables, this is an option

It has 2 built in reports, and the good thing about it is that it saves all captured transactions into 2 tables, so you can run queries against them and extract any info you need

Unfortunately, it can't replay the transactions

Another one is ApexSQL Log. It reads transaction logs and trn backups, so the database has to be in Full recovery model. Besides the data transactions, it also tracks schema changes (create table, alter function, etc) and it can replay transactions


Newbie IT student. Question about a homework assignment I'm a new student in an IT program, and I'm trying to make sense of what this means:

  Design for the following non-functional business problem:

  Your business delivers Internet services, by deploying compute
  infrastructure into rented data centers, which have transit
  partnerships.  In order to sustain these services, it is important to
  track all of the infrastructure, so that the status of it may be
  communicated to the applications which rely on it, and in order to
  manage it.  It is moving beyond the scope of a single spreadsheet,
  which one person can administer, and needs to go into a database for
  concurrent access before a infrastructure management platform can be
  selected.

  There are going to be up to 10 data centers, each with 2 to 4 transit
  providers.

  Each data center has between 2 and 50 racks, and two core routers.

  Each rack has 2 power supplies, 2 switches, 1 storage shelf (4U) and
  20 spaces for 1U servers.

  Each storage shelf has 48 SAS SSDs, and each 1U server has 12 SSDs.

  Use modeling to conclude a relational database design which can
  migrate this need out of a single spreadsheet.  Provide evidence of
  the design, by submitting the PostgreSQL DDL.  An export of a
  spreadsheet will be provided, and sample python for populating a given
  model.

  A data set for populating your database, can be found at:
  infrastructure.tgzView in a new window

  Of course, it is a compressed tar file which contains the following
  files: uplinks.csv, infrastructure.csv, and media.csv.

  Data model designs with less abstraction will be the easiest to
  populate with these data.  Focus on pulling out a simple sample of the
  data, in order to prove the population of the set of tables by mapping
  the natural keys, to generated primary and foreign keys.

Now, the class is fine and good and the instructor is funny and intelligent, but I'm not sure what to do as the pace moves a bit quicker than a baby programmer is used to. Would the following steps be right?

Go into Violet UML Editor and craft the database
Export this model into something
Go into the Postgres I set up in linux
use the database our instructor set up, and link that to the assignment

I'm not looking for someone to do my homework, but an explanation of the underlying concepts and some links would be amazing.

I'm no expert, but it seems to me you should be giving this feedback to your tutor.

If you feel like you're not sure how to proceed with the work, clearly they are not communicating their expectations  well and they should find that out - they won't if you don't tell them!


Run job in agent based on table data Is there a way to start a SQL Server agent job if a table is not empty?

Basically, first I want to check a table, and if the table is not empty then start the job. If it is empty, then check every 1 hour to see whether it has the required information.

I want to run this job once a day once I have data in the table.

Not directly, no. What you describe is kind of like expecting your e-mail program to know when to open and display new e-mail, even though the program needs to be open already to check for new mail.

You might be able to do this with a trigger on the table itself - however note that triggers can be disabled, and also that some operations (such as BULK INSERT) can intentionally bypass triggers.

CREATE TRIGGER dbo.ShouldIEnableJob
ON dbo.TableName
FOR INSERT
AS
BEGIN
  IF EXISTS (SELECT 1 FROM inserted)
  BEGIN
    UPDATE msdb.dbo.sysjobs
      SET enabled = 1
      WHERE name = N'your job name'
      AND enabled = 0;
  END
END

(This doesn't follow your requirement of checking once an hour, but rather it responds to the first insert. You'll also want to drop the trigger once the job has been enabled, but I don't know that you'll be able to do that from within the trigger.)

You could probably create a different job that wakes up every hour. It would check the data, and if it is time to enable the other job, it would do so (similar to the trigger above) and also disable itself. You could also schedule something similar outside of SQL Server (e.g. using PowerShell, VBScript via Windows Task Scheduler, etc.).

Maybe Event Notifications could assist in a similar way, but I've never used them for this specific purpose.


ERROR WSREP`: gcs/src/gcs.cpp:gcs_open():1379:` Failed to open channel 'test_cluster' I have three server that I want to Install Galera clustering on them, I followed this link to install and config it. but when I want to start the first server I got this error :

root@node107:/home/debian# service mysql start --wsrep-new-clusterping
Job for mysql.service failed. See 'systemctl status mysql.service' and 'journalctl -xn' for details.

and :

root@node107:/home/debian# journalctl -xn
-- Logs begin at Tue 2016-03-01 19:02:04 IRST, end at Tue 2016-03-01 19:39:32 IRST. --
Mar 01 19:39:31 node107 mysqld[5102]: 160301 19:39:31 [ERROR] WSREP: gcs/src/gcs.cpp:gcs_open():1379: Failed to open channel 'test_cluster' at 'gcomm://192.168.120.107,192.168.120.111,192.168.120.11
Mar 01 19:39:31 node107 mysqld[5102]: 160301 19:39:31 [ERROR] WSREP: gcs connect failed: Connection timed out
Mar 01 19:39:31 node107 mysqld[5102]: 160301 19:39:31 [ERROR] WSREP: wsrep::connect(gcomm://192.168.120.107,192.168.120.111,192.168.120.117) failed: 7
Mar 01 19:39:31 node107 mysqld[5102]: 160301 19:39:31 [ERROR] Aborting
Mar 01 19:39:31 node107 mysqld[5102]:
Mar 01 19:39:31 node107 mysqld[5102]: 160301 19:39:31 [Note] WSREP: Service disconnected.
Mar 01 19:39:32 node107 mysqld[5102]: 160301 19:39:32 [Note] WSREP: Some threads may fail to exit.
Mar 01 19:39:32 node107 mysqld[5102]: 160301 19:39:32 [Note] /usr/sbin/mysqld: Shutdown complete
Mar 01 19:39:32 node107 mysqld[5102]:
Mar 01 19:39:32 node107 mysqld_safe[5378]: mysqld from pid file /var/run/mysqld/mysqld.pid ended

these are configuration file for the three servers :

vim /etc/mysql/conf.d/cluster.cnf

Server1 :

[mysqld]
query_cache_size=0
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_type=0
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_provider=/usr/lib/galera/libgalera_smm.so
#wsrep_provider_options="gcache.size=32G"

# Galera Cluster Configuration
wsrep_cluster_name="test_cluster"
wsrep_cluster_address="gcomm://192.168.120.107,192.168.120.111,192.168.120.117"
#wsrep_cluster_address="gcomm://192.168.120.107"

# Galera Synchronization Congifuration
wsrep_sst_method=rsync
#wsrep_sst_auth=user:pass

# Galera Node Configuration
wsrep_node_address="192.168.120.107"
wsrep_node_name="node107"

Server 2:

[mysqld]
query_cache_size=0
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_type=0
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_provider=/usr/lib/galera/libgalera_smm.so
#wsrep_provider_options="gcache.size=32G"

# Galera Cluster Configuration
wsrep_cluster_name="test_cluster"
wsrep_cluster_address="gcomm://192.168.120.107"
#wsrep_cluster_address="gcomm://192.168.120.107,192.168.120.111,192.168.120.117"

# Galera Synchronization Congifuration
wsrep_sst_method=rsync
#wsrep_sst_auth=user:pass

# Galera Node Configuration
wsrep_node_address="192.168.120.111"
wsrep_node_name="node111"

server 3 :

[mysqld]
query_cache_size=0
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_type=0
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_provider=/usr/lib/galera/libgalera_smm.so
#wsrep_provider_options="gcache.size=32G"

# Galera Cluster Configuration
wsrep_cluster_name="test_cluster"
wsrep_cluster_address="gcomm://192.168.120.107,192.168.120.111,192.168.120.117"

# Galera Synchronization Congifuration
wsrep_sst_method=rsync
#wsrep_sst_auth=user:pass

# Galera Node Configuration
wsrep_node_address="192.168.120.117"
wsrep_node_name="node117"

I don't understand what I have missed. what should I do?

We should start the first node without below parameter in my.cnf :
    wsrep_cluster_address="gcomm://192.168.120.107,192.168.120.111,192.168.120.117"

Then start the other nodes normally :

/etc/init.d/mysql start

The first node should start normally like the others.


Create index view with self join I use SQL Server 2008 R2 and know that in Indexed View cant have self join.

I have a tree table that have ID and ParentID Column and I need create indexed view on this table with self join between each record and parent record. can I simulate self join in this view?

EDIT

My table structure is :

 SAM.Detail (DetailID Int, ParentDetailID Int, Quantity Int, ...)

and my query is :

 Select A.DetailID,
       A.Quantity - SUM(B.Quantity) as RemainQuantity,
       COUNT_BIG(*) as CountBig
From SAM.Detail A
inner join SAM.Detail B ON B.ParentDetailID = A.DetailID
Group By A.DetailID, A.Quantity

If all you need is to speed up your query, you can create the following indexed view:

Select ParentDetailID ,
       SUM(Quantity) as RemainQuantity,
       COUNT_BIG(*) as CountBig
From SAM.Detail
Group By ParentDetailID

You can join it you Detail table, and your query might run faster.


How to model a car (year, make, model, trim)? I'm trying to design a database to represent cars (specifically: year, make, model, trim) for a car dealership application. Based on my understanding of the data, I've come up with the two database designs below:

Example data:

Year:  2013
Make:  Nissan
Model: Murano
Trim:  SL

I think the top design is more correct than the bottom design - but I'm not 100% sure.

The plan is that for every vehicle added into the car dealership's inventory, I would assign that inventory item a trim_id (which would have details about the vehicle's standard features, options, etc.). The inventory item would then just need to select which options apply to it, specify odometer reading, condition, price, etc.

The model would also need to be able to support queries like:

List all make for a particular year
List all model for a particular make
List all trim for a particular model

Is the design in the right direction? Normalized?

Database Design Cars-Models / Car Classifieds - this puts the year column with the model. I'm not a car buff, but I don't think this is right. I think the trim is associated with the year?

The first model is more accurate for your business needs. I would go a little bit further and include an index (unique) that includes NAME and YEAR.
If you keep the model as it is; for example, the model doesn't prevent you to have this data:

TRIM:
id     model_id     name     year
==================================
1      1            SL       2013
2      1            SL       2013
3      1            SL       2013

To avoid this situation create one index in TRIM with this:

CREATE UNIQUE INDEX trim_idx ON trim (model_id ASC,name ASC,year ASC);

And do a similar action for MODEL

CREATE UNIQUE INDEX model_idx ON model (make_id ASC,name ASC);


SSRS ReportServer_XXX.log is giving incorrect CPU count I am currently investigating an issue wherein sometimes the SSRS just suddenly stops accepting requests until we do an IISRESET command. This happens after we added an additional processor on the server. No new report is added on that time. Right now we have 2x Quad Core with HT enabled processors. Information in 'sys.dm_os_sys_info' is giving me the correct cpu_count which is 16. However, while looking at the 'ReportServer_XXX.log', I noticed that it's logging incorrect cpu count.

w3wp!resourceutilities!1!3/22/2014-14:29:16:: i INFO: Reporting Services starting SKU: Standard w3wp!resourceutilities!1!3/22/2014-14:29:16:: i INFO: Evaluation copy: 0 days left w3wp!resourceutilities!1!3/22/2014-14:29:16:: i INFO: CPU throttling turned on w3wp!resourceutilities!1!3/22/2014-14:29:16:: i INFO: Running on 127 physical processors, 255 logical processors

I am not certain if this is the cause but either way, how do I correct this issue? The count is way off.

Also, on our other server, the CPU throttling turned on message is not being logged. Could also be this another reason?

SSRS is running under this environment:

Windows Server 2003 R2 32-bit (PAE enabled)
SQL Server 2005 SP4 (AWE enabled)
2xQuad core with HT processors
64GB memory

I think the "CPU Throttling" message indicates that the server has a power setting enabled that will dynamically reduce CPU speed to conserve power (usually the "Balanced" power scheme set in BIOS). Not sure if this is related to your issue, but you probably don't want it enabled on a server.


How to deal with a chain of several linked 1:n:n*m:n*m*k "join" explosion? I have a domain model, where I have a "manager" which has 1:n relation with another resource (A). A has 1:n with B, and B has 1:n with C (it makes sense as a relation).

However I'll need a lot of queries fetching B/C rows based on a manager id which requires a lot of joins. Is that ok, or should I add the manager explicitly to B and C?

It sounds like you're talking about a self-referential relationship, especially with the inclusion of "manager" titles. This is a common pattern, especially with employee tables. While they may implement it differently the main RDBMS vendors typically offer recursive capabilities. This means that with a special syntax you can easily query this kind of a relationship without writing the hundreds of JOINs you would otherwise need. An example for Microsoft T-SQL recursive capabilities is here and focuses on manager/employee relationships.

Every row should have a ManagerID or Parent attribute, and this can be NULL or the Employee's own ID in the case of the leader (e.g. CEO). This allows you to correctly identify your root node. If you need to traverse down from different levels simply change the start point for the recursion.


How can I configure RMAN to delete crontrol files automatically? I am using Oracle Release 12.2.0.1.0 Standard Edition on ODA (Oracle Database Appliance, running linux). My instance "xyz" is running in archivelog mode. RMAN performs backup and unnecessary datafiles are deleted automatically from the backup set.

[oracle@oda ~]$ rman TARGET /
Recovery Manager: Release 12.2.0.1.0 - Production on Fr Aug 31 16:50:05 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
Mit Zieldatenbank verbunden: XYZ (DBID=4079461799)
RMAN> show all;
Kontrolldatei der Zieldatenbank wird anstelle des Recovery-Katalogs verwendet
RMAN-Konfigurationsparameter für Datenbank mit db_unique_name XYZ sind:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/mnt/backup/rman/xyz/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/mnt/backup/rman/xyz/%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u03/app/oracle/fast_recovery_area/XYZ/controlfile/snapcf_xyz.f';

When I check the directory everything looks fine - except for the control files which keep piling up

[oracle@oda1 xyz]$ pwd
/mnt/backup/rman/xyz
[oracle@oda1 xyz]$ l
total 4144832
drwxr-xr-x 2 oracle oinstall       4096 Aug 31 11:00 .
drwxr-xr-x 7 oracle oinstall       4096 Jul  4 14:38 ..
-rw-r----- 1 oracle asmadmin 1072447488 Aug 29 01:02 6ntbnscr_1_1
-rw-r----- 1 oracle asmadmin     943104 Aug 29 01:02 6otbnshm_1_1
-rw-r----- 1 oracle asmadmin   18115072 Aug 29 11:00 6qtbovhi_1_1
-rw-r----- 1 oracle asmadmin   62260224 Aug 30 01:00 6stbqgoj_1_1
-rw-r----- 1 oracle asmadmin 1072734208 Aug 30 01:05 6ttbqgq1_1_1
-rw-r----- 1 oracle asmadmin    1159680 Aug 30 01:05 6utbqh2k_1_1
-rw-r----- 1 oracle asmadmin   10599424 Aug 30 11:00 70tbrjtj_1_1
-rw-r----- 1 oracle asmadmin   42828288 Aug 31 01:00 72tbt54i_1_1
-rw-r----- 1 oracle asmadmin 1073242112 Aug 31 01:02 73tbt54q_1_1
-rw-r----- 1 oracle asmadmin    2316288 Aug 31 01:02 74tbt59l_1_1
-rw-r----- 1 oracle asmadmin   17406976 Aug 31 11:00 76tbu89j_1_1
-rw-r----- 1 oracle asmadmin   10698752 Jun 20 11:11 c-4079461799-20180620-00
-rw-r----- 1 oracle asmadmin   10698752 Jun 20 11:25 c-4079461799-20180620-01
-rw-r----- 1 oracle asmadmin   10698752 Jun 20 13:03 c-4079461799-20180620-02
-rw-r----- 1 oracle asmadmin   10698752 Jun 20 13:48 c-4079461799-20180620-03
-rw-r----- 1 oracle asmadmin   10698752 Jun 20 14:03 c-4079461799-20180620-04
-rw-r----- 1 oracle asmadmin   10698752 Jul 16 10:22 c-4079461799-20180716-00
-rw-r----- 1 oracle asmadmin   10698752 Jul 26 17:32 c-4079461799-20180726-00
-rw-r----- 1 oracle asmadmin   10698752 Jul 27 01:02 c-4079461799-20180727-00
-rw-r----- 1 oracle asmadmin   10698752 Jul 27 11:00 c-4079461799-20180727-01
..

The files beginning with "c" are the control files.

Question: What can I do to delete these files also automatically?

Thank you for your insight!

The RMAN Retention Policy for the control files and backup sets is defined as such:

(emphasis mine)

[CONFIGURE]..

  This is the command to start the configuration of the parameter

... RETENTION POLICY ...

  Specifies a persistent, ongoing policy for backup sets and copies that RMAN marks as obsolete, that is, not needed and eligible for deletion.
  As time passes, RMAN marks backup sets and copies as obsolete according to the criteria specified in the retention policy. RMAN automatically deletes obsolete backup sets and copies in the flash recovery area when space is needed. RMAN does not automatically delete obsolete files outside the flash recovery area: you must manually execute DELETE OBSOLETE to remove them.

... TO REDUNDANCY integer

  Specifies that RMAN should retain integer full or level 0 backups of each datafile and control file. The default retention policy setting is REDUNDANCY 1.
  If more than integer full or level 0 backups of a datafile or control file exist, then RMAN marks these extra files as obsolete. RMAN then determines the oldest of the retained backups and marks all archived redo logs and log backups older than this backup as obsolete. The DELETE OBSOLETE command removes obsolete datafile backups (full or incremental), control file backups, and archived log backups or image copies.

Applying the Above to Your Environment

In your case you have set the following:

CONFIGURE RETENTION POLICY TO REDUNDANCY 3;

Which means:

keep all full and level 0 backups for up to three versions [back]
keep all archive logs in-between these backups as well
keep all control file backups of each backup type as well

full and level 0 backup
archive log backup
when any autobackup of the control file is triggered

As you might notice, you could essentially have the same amount of backed up control files as you have the sum of:

full backup files
level 0 backup files
archive log backup files

...or even more. (Read: Oracle 12c – Automatic Control File Backups for more details)

And if you are not backing up to the flash recovery area of your Oracle instance, then you would have to manually delete these files after backing up your database by issuing the following command:

DELETE OBSOLETE;

You could also test this by first running:

REPORT OBSOLETE;

...to check that the control files in the directory are actually obsolete, and then manually delete them in RMAN with the command I mentioned before this one.

Summary

You could be seeing the following issues:

Everthing is OK, the instance has just created a lot of autobackup-ed control files.
The Control Files are outside the FRE and have to deleted manually
You hit a bug (...read on below).

Because we don't know your exact backup scripts and other settings, it's hard to tell which point you are encountering, but you have a few good starting points.

Apparent Bug

There are cases (apparently) where the control file never gets marked as obsolete:

12.2 controlfile backup never gets marked as obsolete

A bug has been filed, and a fix supplied, but it doesn't seem to work in all cases.

You might have to manually delete the control file backups in RMAN.

DELETE COPY OF CONTROLFILE LIKE '/mnt/backup/rman/xyz/<part_name_of_control_file>%';

Answering Your Question

  What can I do to delete these files also automatically?

Sorry, but that depends which situation you are hitting and can vary from:

Will happen automatically
Can be deleted with REPORT OBSOLETE and DELETE OBSOLETE
Will only happen manually with DELETE COPY OF CONTROLFILE LIKE ...
Might work again after bug fix

References

Deleting RMAN Backups and Archived Redo Logs (Oracle Docs)
Configuring the Backup Retention Policy (Oracle Docs)
Listing Backups and Recovery-Related Objects (Oracle Docs)
Deleting Specified Backups and Copies (Oracle Docs)
12.2 controlfile backup never gets marked as obsolete (PATRICK'S ORACLE DBA BLOG)


Check programmatically if SQL Server needs restarting I have to check programmatically if the SQL Server service needs restarting after series of changes in configuration.

Is there any indicator (e.g. in the system registry) that allows to obtain this information?

You can check for differences between value_in_use and value in sys.configurations for non dynamic changes.

Select * from sys.configurations
where value <> value_in_use and is_dynamic=0

to check for a changed logginmode

declare @tab table (name varchar(max),value varchar(max))
insert into @tab EXEC master.sys.xp_loginconfig 'login mode'
insert into @tab
SELECT 'current',CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN 1 THEN 'Windows NT Authentication'
WHEN 0 THEN 'Mixed'
END

Select
CAST(
Case when (Select value from @tab where name='login mode')<>(Select value from @tab where name='current')
then 1 else 0 end as bit) as loginconfig_changed


Call trigger during SQL Loader insert I'm trying to insert data from a file into an Oracle table using SQL loader. The primary key is populated by a sequence where the nextVal is called from an insert trigger.

When I try using sqlldr, it seems to bypass the trigger as it says I cannot insert a null value into the primary key field.

Is there a way I can use sql loader to call the trigger for each insert?

OPTIONS (direct=false, SKIP=1)
load DATA
INFILE 'bcbsm.ctl'
append into table pprs.x
(
  LAST_NAME POSITION(1:25) CHAR
)

command: sqlldr username/password control=file.ctl

In my trigger there is a "Before Insert on x For Each Row" where I call the nextVal for the sequence then assign it to the primary key column. Since it's a unique identifier and populated by a sequence, it's not on the file that I'm trying to load.

Figured it out, I just have to put the sequence's nextVal in quotes in the control file:

append into [tablename]
(
   [column name] "[sequence name].nextVal"
   ...
)


mysqlslap running very slow on a specific computer I've recently discovered the tool mysqlslap and have run some simple tests with it. I currently have three test environments (2 Win 7, 1 Linux Ubuntu) where I can run these tests. However, running a no-op test on one of the Win 7 machines takes approximately 2 seconds. Running a similar query from for example MySQL Workbench takes no time, just as it should. This must have something to do with the configuration of my machine or MySQL (5.5).

Running a more heavy query takes longer time on the already slow machine, which is at least a little bit logical.

Does anybody have a clue why this happens?

Any help appreciated!

Well, I found out what the simple solution was. I needed to configure mysqlslap with --host and --user options for some reason yet unknown.


Giving permission for each table within a database I want to give different permission for different tables within a single database for a user.For example a user has full permission to a table while for another table only read permission.How it can be done within a database?

In Oracle (and, realistically, most any reasonable database), everything is denied other than what is granted.  So simply grant the user the privileges you want him to have

GRANT select, update, delete
   ON table_owner.table_name
   TO username;

GRANT select
   ON table_owner.another_table_name
   TO username;


how do I determine why I cannot drop a schema A schema was created with the name of a windows user [DOMAIN\Foo]. I receive this error message when attempting to drop the schema.

DROP SCHEMA [DOMAIN\foo]

Msg 15151, Level 16, State 1, Line 8
Cannot drop the schema 'DOMAIN\foo', because it does not exist or you do not have permission.
The statement has been terminated.

I have already made sure that the user does not have authorization on the schema, and transferred ownership to dbo

alter authorization on schema::[DOMAIN\foo] to dbo

My windows login is a member of the server role sysadmin.
My user mapped to the database is a member of the database role db_owner.

The root of the problem is that I have a schema-only database defined that is generated automatically via triggers. When a DDL statement gets replayed by the INSERT trigger of the changelog table, the error occurs when the schema is out of sync (The schema did not exist in my SchemaOnlyDatabase). The error was being thrown in the context of the SchemaOnlyDatabase, not the database that was currently active TheDevDatabase, but that was not clearly indicated by the message.

CREATE TRIGGER [RecordDatabaseChanges]
ON ALL SERVER
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
SET NOCOUNT ON;
--ALL SETTINGS NEEDED FOR XQUERY IN DDL TRIGGERS
--http://tomaslind.net/2013/10/15/zero-footprint-serverdatabase-ddl-trigger/
SET ARITHABORT, CONCAT_NULL_YIELDS_NULL, ANSI_PADDING, ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
DECLARE @data XML;
SET @data = EVENTDATA();
DECLARE @EventType NVARCHAR(30)
SELECT @EventType = @data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(30)')
IF @EventType IN (
N'ADD_ROLE_MEMBER',
N'ALTER_AUTHORIZATION_DATABASE',
N'ADD_SIGNATURE_SCHEMA_OBJECT',
N'DENY_DATABASE',
N'DROP_ROLE_MEMBER',
N'GRANT_DATABASE',
N'REVOKE_DATABASE',
N'UPDATE_STATISTICS'
)
RETURN
INSERT INTO [ChangeLogDb].[dbo].[ChangeLog] ([Event Type], [Database], UserName, SPID, [Schema], Name, [New Name], ObjectType, [New ObjectType], [SQL Used])
SELECT
COALESCE(@data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(30)'),'') AS [Event Type],
COALESCE(@data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(128)'),'') AS [Database],
COALESCE(@data.value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(128)'),'') AS [UserName],
COALESCE(@data.value('(/EVENT_INSTANCE/SPID)[1]','nvarchar(128)'),'') AS [SPID],
COALESCE(@data.value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(128)'),'') AS [Schema],
COALESCE(@data.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(128)'),'') AS [Name],
COALESCE(@data.value('(/EVENT_INSTANCE/TargetObjectName)[1]','nvarchar(128)'),'') AS [New Name],
COALESCE(@data.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(128)'),'') AS [ObjectType],
COALESCE(@data.value('(/EVENT_INSTANCE/TargetObjectType)[1]','nvarchar(128)'),'') AS [New ObjectType],
COALESCE(@data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'),'') AS [SQL Used]
END

CREATE TABLE [ChangeLogDb].[dbo].[ChangeLog]
(
[Changelog_ID] [int] IDENTITY(1,1) NOT NULL,
[Event Type] [nvarchar](30) NOT NULL,
[When] [datetime] NOT NULL CONSTRAINT [DF_ChangeLog_When]  DEFAULT (getdate()),
[Database] [nvarchar](128) NOT NULL,
[UserName] [nvarchar](128) NOT NULL,
[SPID] [nvarchar](128) NOT NULL,
[Schema] [nvarchar](128) NOT NULL,
[Name] [nvarchar](128) NOT NULL,
[New Name] [nvarchar](128) NOT NULL,
[ObjectType] [nvarchar](128) NOT NULL,
[New ObjectType] [nvarchar](128) NOT NULL,
[SQL Used] [nvarchar](max) NOT NULL,
[NTUserName] [nvarchar](128) NOT NULL CONSTRAINT [DF_ChangeLog_NTUser]  DEFAULT (suser_sname()),
[HostName] [nvarchar](128) NOT NULL CONSTRAINT [DF_ChangeLog_HostName]  DEFAULT (host_name()),
[SessionLoginName] [nvarchar](128) NOT NULL CONSTRAINT [DF_ChangeLog_SessionLoginName]  DEFAULT (suser_name()),
CONSTRAINT [PK_ChangeLog] PRIMARY KEY CLUSTERED ([Changelog_ID] ASC)
)

AFTER INSERT AS
BEGIN
DECLARE @sqlbatch NVARCHAR(MAX)
DECLARE @databasename VARCHAR(255)
DECLARE sqlused_cursor CURSOR FAST_FORWARD FOR
SELECT i.[DATABASE], i.[SQL Used] FROM inserted i WHERE i.[Database] = 'TheDevDatabase'
OPEN sqlused_cursor
FETCH NEXT FROM sqlused_cursor INTO @databasename, @sqlbatch
WHILE @@FETCH_STATUS = 0
BEGIN
-- re-execute in the context of the TheSchemaDatabase db.
EXEC TheSchemaDatabase.dbo.sp_executesql @Stmt=@sqlbatch
FETCH NEXT FROM sqlused_cursor INTO @databasename, @sqlbatch
END
CLOSE sqlused_cursor
DEALLOCATE sqlused_cursor
END


Disable nestloops into the Postgresql JDBC connection I'd like to disable nested loops for a specific JDBC connection string (batch java application), using the PostgreSQL community driver

Is it possible to do so?

I tried this way, in vain:

jdbc:postgresql://xxx.yyy.zzz:5432/mydb?options=enable_nestloop=off

You can use the options connection parameter, as explained here:

  options = String

  Specify 'options' connection initialization parameter.

  The value of this property may contain spaces or other special characters, and it should be properly encoded if provided in the connection URL. Spaces are considered to separate command-line arguments, unless escaped with a backslash (\); \\ represents a literal backslash.

So that should look like

options=-c%20enable_nestloop%3Doff

But I share the horse's concern that this may be a bad idea, and you should rather tune your queries.


Using mysqldump on RDS to export a large file in TSV format I would like to export a single, large table (30GB) from an RDS MySQL server on a regular basis into a TSV file, so that we can load the file into Vertica.

However, RDS doesn't provide a way to use mysqldump with the --tab=path option, so we need to parse/convert the INSERT statements to TAB seperated files.

I would like to convert lines from the dump file like this:

INSERT INTO MyTAble VALUES ('data_1','data_2', 'date_3');

to this
    'data_1'   'data_2'     'data_3'

Since I need to parse through a 30G file, I would like to find the most efficient way of doing this?

Thanks for any help.

Assuming RDS with MySQL 5.6 is being used, mydumper may be a better option. The newer versions have the RDS grants limitation into account and you will get all the advantages of mydumper over mysqldump (compression, parallel execution, overall faster speed, etc).

This wouldn't provide TSV data, but by using the --chunk-filesize option you can split the data into multiple files in parallel and then process them in parallel afterwards

option:: --chunk-filesize -F Split tables into chunks of this output file size. This value is in MB


Does SQL Server redistribute data after adding an additional data file? Imagine a database about 30GB in size using 3 data files on the same disk.

Someone told me to have one data file per processor implemented to get full performance on all cpus. So if I now add three more data files, how's the data redistributed to the new files? Do I have to do something?

The way that datafiles work, any new data will, mostly, get put in the new data file you added. Or in your case the two new data files.

However, SQL Server will not try to re balance your data. This would't make sense either as you will be causing a lot of IO / load on a database for, as far as SQL server knows, no reason.
You can however rebalance your data by recreating your indexes on a new filegroup while dropping the old indexes. The advantage being that you're allocating space in small chunks, getting unfragmented indexes and generally have more control.

You will however have to do this largely manually. And you'll probably want to create full backups before and after each operation.


Choosing the correct database type for single type of entry So I'm building an application which constantly gets data from an API, basically the API outputs data through JSON which shows a certain speed for different hosts. What I want to here is query the app every minute and store this data within a database.

The idea is that the app will use the data to build graphs. These graphs will show the collective speeds of all hosts and for every host seperately. Hosts are assigned to a user

1 user has multiple  hosts with each a certain speed. Now considering the data that will be stored will grow over time and the relations between the entities is rather limited I was wondering if I should look at a NoSQL database like MongoDB or DynamoDB.

NoSQL databases are for unstructured data or unpredictably structured data that is often queried using full-text searching.

Capturing time-series statistics for graphing sounds like a pretty well-structured data set to me.  What do you have, three tables?  Users, Servers and Measurements?  It sounds to me like you have data which is perfectly suited to a relational database.

It may be that you need to think about whether your relational database is going to be fully normalized or partially denormalized for reporting performance.  That's something you can decide is necessary once you do some load testing with production volumes in order to see whether denormalization is helpful.


Aggressive Under-Indexing and no data for missing index I know that there are lot of blocking on my database and have tried my best to get this sorted by vendor as this application is supported by them and hasn't produced any successful result yet. Every now and then, we get issue of blocking and this blocking gets so severe and their design is so poor that the whole portal goes down unless and until I kill few SPIDs which is holding exclusive lock(mostly).

I have been using sp_blitzindex from almost an year now and a big fan of First Responder Kit provided by Mr. Brent Ozar and Team. When I execute sp_blitzindex against this database where blocking takes place, it says - "Aggressive Under-Indexing: Total lock wait time > 5 minutes (row + page) with long average waits" with the priority of 10. I checked the URL column and also checked other related pages however couldn't get much assistance.

I know that the tables listed here are under indexed and some more indexes need to be created however when I run the same procedure i.e. sp_blitzindex for these objects individually at table level by using below command:

EXEC dbo.sp_BlitzIndex @DatabaseName='db1', @SchemaName='sch', @TableName='tab1';

I don't get any missing index details at all. All the existing indexes are utilized and read count is lesser than write count, only issue which is highlighted here are in the "Lock Waits" column for Primary key.

I have no clue on which column index needs to be created. I also checked sp_blitzlock to see if I could gather some more info which would help however all I see there is lot of deadlocks and same objects are listed which is figured in aggressive under-indexing.

Also checked output of sp_blitzcache by passing sortorder as "reads" and "duration" in this particular database however no missing index request. There are warnings saying "Unparameterized Query"  and "Unparameterized Query, non-SARGables" and these plans involve different set of tables.

Any help is highly appreciated.

Version: Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Sep 7 2018 01:37:51 Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)

Free Unit

Since you're using the First Responder Kit, I'll stick to using it in my examples to teach you how to diagnose this further.

No Missing Indexes?

Why doesn't SQL Server have any missing index requests in the DMVs or Query Plans?

First, Wait

Use sp_BlitzFirst @SinceStartup = 1;, and check out your wait stats.

If LCK_ waits aren't near the top of your list (top 10 or so), and if the Avg ms Per Wait column isn't showing values over 1-2 seconds for the LCK_ waits, the situation might not be so dire. I'd probably switch my focus elsewhere.

Modifiers

Rather than using sp_BlitzCache to look at Reads or Duration, use it to look at these:

EXEC sp_BlitzCache @SortOrder = 'writes';
EXEC sp_BlitzCache @SortOrder = 'avg writes';

You may find queries that modify a lot of data at once. If you do, changing them to batch modifications can reduce the locking overhead.

The problem with this approach is that it relies on execution plans being in the cache when you're looking for them. They may not be, for various reasons.

Since you're running sp_BlitzCache, pay attention to the line in the Warnings rollup (second output);

Common Problems

Long locks can come from places that end users may not notice, like index maintenance.

If this is the source, you may want to consider scaling back the fragmentation level you perform maintenance at (50-80%), the frequency of the maintenance (weekly or monthly), or just not doing it at all and updating statistics only (much less invasive and less prone to blocking).

Look for code patterns that use BEGIN TRAN, then do a ton of work before committing. This requires you do a code review. There's no shortcut here, and it's typically the kind of thing you want a monitoring tool to do.

Look in sp_BlitzIndex for foreign keys with cascading actions. These can cause a dramatic amount of locking.

Hope this helps!


Is smallest to largest the rule of thumb in a multi-table JOIN? Given this query,

SELECT a.*
FROM a
LEFT JOIN b ON b.a_id = a.id
LEFT JOIN c ON c.a_id = a.id
HAVING COUNT(DISTINCT b.id) < COUNT(DISTINCT c.id)

if b is a significantly larger table than c, will there be performance implications to the order I do the joins? I read somewhere that the rule of thumb is to join smallest to largest, but that might've been referring to INNER JOINs.

I realize that the results will depend on the order of outer joins in many scenarios, but in one like the above where the order doesn't matter for veracity, will it likely affect performance?

SQL is declarative language, query is not executed the way you write it . Most modern RDMS have decent optimizer which is capable of choosing correct join order when relatively small number of tables is involved , statistics is up to date,  data not skewed, appropriate indexes exist, etc.

Query optimization is a very broad topic, and it's quite specific to particular vendor. It may involve adding hints, creating materialized views, using fixed execution plans, caching tables in memory, and many other things. To some extent you may control join order with the way you write query (for instance,  subquery inside WITH  will be more likely executed first compared to the same subquery inside inline view, but again, it's very specific to RDMS you're using.

As I rule of thumb, I'd recommend starting with a query that gives you expected results, and try optimizing it only if it's slow - the optimizer will do the rest.

Side note: COUNT can appear in HAVING clause, not in WHERE ; the query will fail on the most RDMS.


SQL Server shrink log after full and log backup I have an SQL Server database in Full recovery model. The data file is just 1 GB in size, but the log file is 20 GB.

I have a space problem, so I took a full backup and then a log backup with truncate option. Is correct now to shrink the log file? Does anything speak against doing that?

This is a production DB that is supposed to be in full recovery model. Til now no one implemented a backup strategy. The log became very big and they are running out of space. I took a full backup and then a log backup with truncate option using SSMS, in order to avoid the log file increase further. Now I thought that I can schedule daily a full backup and every hour a log backup to avoid that the log file increase over 2Gb. I'm not sure if after I took full + log backup is it possible to shrink the log?

The idea is to reduce the log file from 20Gb to 1 or 2 Gb because the server is running out of space and then take a daily full backup and every hour a log backup to avoid the log file increase too much.

You should be ok, transaction log backup will allow a point in time recovery. You can shrink the tlog file without any issues. Since you already took the tlog backup with truncate, the log space should be empty (unless there's something like replication, log shipping, etc) and you should be able to shrink the tlog.

After that, you can continue with your normal schedule of full backup and tlog backups.

The only situation when tlog shrink is not recommended is when you already know the tlog will grow due the "normal" activity of your database. But from what you said, it should be ok to shrink it.


This is a lot of code with tables selecting on tables and a dynamic pivot statement at the end. Let me know if you have any questions or any things you want me to change.

SELECT  ROW_NUMBER() OVER (PARTITION BY ticketid ORDER BY ownerdate) Ticket_OrderID,
        ticketID,
        [owner],
        ownerdate INTO #tempTable
FROM yourTable
--WHERE [owner] IS NOT NULL

SELECT  A.ticketid,
        'timediff' + CAST(ROW_NUMBER() OVER (PARTITION BY A.ticketID ORDER BY A.ticketID,A.ownerdate) AS VARCHAR(100)) time_diff_owner,
        CAST(DATEDIFF(MINUTE,A.ownerdate,B.ownerdate) AS VARCHAR(100)) + 'min' +
        CAST(DATEDIFF(SECOND,A.ownerdate,B.ownerdate)%60 AS VARCHAR(100)) + 'sec' time_diff_sec
        INTO #tempTable2
FROM    #tempTable A
INNER JOIN #tempTable B
ON  A.Ticket_OrderID = B.Ticket_OrderID - 1
    AND A.ticketID = B.ticketID

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(time_diff_owner)
            FROM #tempTable2
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

SET @query =
'SELECT ticketid,' + @cols + ' FROM #tempTable2 A
PIVOT (
     MAX(time_diff_sec) FOR time_diff_owner IN (' + @cols + ')
) pvt'

EXEC (@query)

DROP TABLE #tempTable
DROP TABLE #tempTable2

Results:

ticketid timediff1      timediff2       timediff3       timediff4       timediff5
---------------------------------------------------------------------------------
1001     1min43sec      5min18sec       107735min59sec  221682min17sec  16min34sec
1002     106599min59sec 34251min23sec   188557min57sec  5234798min59sec 1min48sec
1003     14min5sec      2865min38sec    31394min56sec   NULL            NULL
1004     34196min44sec  NULL            NULL            NULL            NULL
1005     153min14sec    3min9sec        2826min1sec     18245min51sec   11min53sec


I'm more mysql than php but are you binding the $guestid php variable twice?  You need to supply a variable for each ? placeholder in prepared statements and presumably so in mysqli.  In a native prepared statement on the server, the ? is legal within an IF() but then again so is ? = ?, which apparently mysqli doesn't allow.

But also, you could rewrite the query:

IF(r.eventId = e._id AND r.guestId = i.guestId, 1, 0) AS 'RSVP-ed'

We know that i.guestId has to contain the guestId you're interested in, because you've specified it in the where clause so we should be able to compare it this way.

However, there seem to be other issues with your query.

LEFT JOIN rsvpList AS r ON r.eventId = e._id

It seems like this should actually be:

LEFT JOIN rsvpList AS r ON r.eventId = e._id AND r.guestId = i.guestId

...because otherwise you're joining all of the rsvpList entries for that event, not just those for the guest in question.  You may have tried to filter for this in the WHERE clause at some point, which doesn't work if they haven't rsvp'ed.

This change also illustrates that the first comparison in your IF() is actually unnecessary.

IF(r.eventId = e._id AND r.guestId = i.guestId, 1, 0) AS 'RSVP-ed'

We know that r.eventId will match e._id OR will be null if no RSVP, so we don't need to test it, because r.guestId will be what we want or null if no rsvp, so this becomes simply:

r.guestId IS NOT NULL as `RSVP-ed`

the truthiness of the assertion that r.guestId is not null in the joined-up result set is returned as a 1 or 0, and r.guestId will be null with no rsvp because there's no matching row in rsvpList for that guestId.

An equivalent and slightly more intuitive (but redundant) expression of that would be

IF(r.guestId IS NOT NULL,1,0) as `RSVP-ed`


Table data is not getting dumped This is just for test purpose. I'm taking backup (INNODB DB) with --single-transaction and from another terminal I'm running alter table on one of the table of same DB on which dump command is running.Altered table is getting dumped with altered structure but DATA is missing.I repeated this test 3 times and same result is there. Just wanted to know this is the way it works? Please give some explanation. Thanks in advance.

When you use mysqldump --single-transaction, you are not supposed to break the transaction.

I wrote a post 1.5 years ago discussing the SQL that can break transaction : Transactional DDL workflow for MySQL

In the future, never run DDL during a mysqldump because it triggers an implicit commit.


prepared statements are not inserted i am new to JDBC and trying following code. Could not find the error in it

try{
        Connection con = ConnectionProvider.getCon();
        stmt=con.prepareStatement("insert into create_request values((select count(reqno) from create_request)+1,?,?,?,?,?");
        stmt.setString(1,obj_Leaverequest.getUser_name());
        stmt.setString(2,obj_Leaverequest.getLeave_Type());
        stmt.setInt(3,obj_Leaverequest.getLeave_Units());
        stmt.setString(4,obj_Leaverequest.getLeave_Reason());
        stmt.setString(5,"pending");
        count=stmt.executeUpdate();
        if(count>0){
            status=true;
        }
    }

Getters are working fine, i could print their values . i guess i am wrong with the query.Please point out my mistake. Thanks in advance!!

I guess you have forgotten a ')'.

try this:

stmt=con.prepareStatement("insert into create_request values((select count(reqno) from create_request)+1),?,?,?,?,?");