User can't stop Oracle listener (TNS-01190) I have Oracle 10g installed on RedHat Enterprise Linux 5.

I have 2 users that use Oracle:

uid=501(user1) gid=502(group1) groupes=500(oinstall),501(dba),502(group1)

and

uid=500(oracle) gid=500(oinstall) groupes=500(oinstall),501(dba),502(group1)

When a user tries to stop a listener that was started by the other user, I get this error:

TNS-01190: The user is not authorized to execute the requested listener command

How can I allow both users to stop the listener, regardless of who started it?

The description for the message you're seeing is:

  Cause: Most of the listener administrative commands are only intended
  to be issued by privileged users, for example DBAs or system
  administrators. If the listener password is not set, then the listener
  only accepts administrative requests from LSNRCTL running with the
  same OS credentials, or running as a local administrator (also
  referred to as super user).

  Action: If an authorized user is attempting the command, then make
  sure that LSNRCTL is executed with the same OS user credentials as the
  running listener, or as a local administrator.

Unless you want to get into setuid wrappers, you need to have a password set for your listener. There's a good summary of how to set that up here, if you haven't already done so.

Then if you start the listener as normal as oracle, you can shut it down as user1 by going into the interactive mode of the controller, enter command 'set password', and enter the password for the listener:

$ lsnrctl

LSNRCTL for Solaris: Version 11.2.0.2.0 - Production on 12-NOV-2012 14:55:29

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> stop
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
TNS-01190: The user is not authorized to execute the requested listener command
LSNRCTL> set password
Password: <secret>
The command completed successfully
LSNRCTL> stop
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
The command completed successfully
LSNRCTL>

... and it should shut down. I know this is a different O/S and version but the principle is the same. And the reverse is true; if you start it as user1, you need to set password to be able to shut it down as oracle.

However, this is deprecated from 11gR2. The manual mentons using ssh or OEM, but you could also just make user1 switch to the oracle account to start and stop the listener via su. Obviously that gives them full DBA access, it isn't restricted to the listener, which is the case for ssh too.

I'm not sure why you'd want anyone you didn't trust to administer the database to be able to mess with the listener, or why you need to shut it down and start it up often enough for this to be an issue.

Login as sysdba
and run:

alter system set LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))' scope=both;

alter system register;

It worked form me.


restoring database using RMAN If I have new data like new users and tables created after backing up  database with RMAN , in case of restoring database will the operation affect my new data made after taking the backup?

No. RMAN is aware of that possibility, hence why it creates a snapshot controlfile before performing backup. Rman needs to access control file to map objects to be backed up, and as controlfile is always in use, snapshot is used for that specific point in time. When you restore a backup, you're simply telling oracle to place those files from the backup back, so you don't need to worry about your data getting overwritten.


SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER' Whats wrong with this statement?
why it gives below error message

Error Message:

  SELECT failed because the following SET options have incorrect
  settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for
  use with indexed views and/or indexes on computed columns and/or
  filtered indexes and/or query notifications and/or XML data type
  methods and/or spatial index operations.

Statement

  SELECT  ISNULL(STUFF((SELECT ', ' + u.TITLE + ' ' + u.FirstNAME  + ' ' + LastNAME   [text()]
                                         FROM table1 c
                                         INNER JOIN table2 u ON c.user = u.user
                                         AND c.Task = @task
                                         AND c.Type = 'Assign'
                                         FOR XML PATH(''), TYPE)
                                        .value('.','NVARCHAR(MAX)'),1,2,' ')
                                 ,'') UNAMESLIST

As documented in SET QUOTED_IDENTIFIER (Transact-SQL)

  SET QUOTED_IDENTIFIER must be ON when you invoke XML data type
  methods.

A simple test

set quoted_identifier off

DECLARE @xmlRecords XML
SET     @xmlRecords = '<records><record orderId="1" refCode="1234"></record></records>'

SELECT  records.record.value('(@orderId)[1]', 'INT') AS orderId
FROM    @xmlRecords.nodes('/records/record') records(record)

  Msg 1934, Level 16, State 1, Line 8 SELECT failed because the
  following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
  Verify that SET options are correct for use with indexed views and/or
  indexes on computed columns and/or query notifications and/or xml data
  type methods.

You are using FOR XML PATH

SET QUOTED_IDENTIFIER off

DECLARE @T TABLE (id VARCHAR(5),col1 XML)

INSERT INTO @t (id,col1) VALUES ('1','<node1>one</node1>')

SELECT ISNULL(STUFF((
                SELECT ', ' + id
                FROM @t
                FOR XML PATH('')
                    ,TYPE
                ).value('.', 'NVARCHAR(MAX)'), 1, 2, ' '), '') UNAMESLIST

  Msg 1934, Level 16, State 1, Line 8 SELECT failed because the
  following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
  Verify that SET options are correct for use with indexed views and/or
  indexes on computed columns and/or query notifications and/or xml data
  type methods.

Check results from command:

DBCC useroptions

There are other very important settings to observe here too:

language    us_english
dateformat  mdy
datefirst   7

Here in Australia am using settings as per:

language    British
dateformat  dmy
datefirst   1

Strangely, my process worked on a dev level DB but not SIT level DB, I resolved this issue by adding this line to my stored proc that performs the XML import:

SET QUOTED_IDENTIFIER ON

I also found that this has to be added above the CREATE PROCEDURE statement !
i.e. as per this example:

SET ANSI_WARNINGS ON
GO
SET ANSI_PADDING ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.P427007ImportAHSAHospitalDetails
    @Path       VARCHAR(255),
    @FileName   VARCHAR(255),
    @OperatorID VARCHAR(10),
    @BranchID   VARCHAR(10),
    @TillID     VARCHAR(10)
AS
BEGIN

    SET ANSI_WARNINGS ON
    SET ANSI_PADDING ON
    SET QUOTED_IDENTIFIER ON

    DECLARE @DebugModeFlag CHAR
    SELECT @DebugModeFlag = 'N'
    ----------------------------------------------------------------------------------------
    -- Empty the [ImportAHSAHospitalDetails] table
    IF @DebugModeFlag = 'Y' PRINT 'Empty the [ImportAHSAHospitalDetails] table';
    ----------------------------------------------------------------------------------------

    DELETE FROM [ImportAHSAHospitalDetails];

    DBCC CHECKIDENT ('ImportAHSAHospitalDetails', RESEED, 0);

    DELETE
    FROM dbo.ImportProviderWarnings
    WHERE ImportType = 'Z'

    DECLARE @ProcessID    INT,
        @EffDateText  VARCHAR(10),
        @EffDate      DATETIME,
        @FileVersion  VARCHAR(10),
        @InsertCount  INT,
        @UpdateCount  INT,
        @InvalidCount INT,
        @ProcessStartTime DATETIME,
        @ProcessEndTime DATETIME,
        @ProcessDuration INT

    DECLARE @HOSDetailsRaw TABLE (HOSDetailsData XML)
    DECLARE @HOSDetailsXML XML

    SELECT  @ProcessStartTime=GETDATE()

    ----------------------------------------------------------------------------------------
    -- Importing Raw Data File
    IF @DebugModeFlag = 'Y' PRINT 'Importing Raw Data File';
    ----------------------------------------------------------------------------------------
    INSERT INTO @HOSDetailsRaw EXEC ('SELECT * FROM OPENROWSET ( BULK ''' + @Path + @FileName + ''', SINGLE_CLOB) AS xmlData')
    SELECT @HOSDetailsXML = HOSDetailsData FROM @HOSDetailsRaw;

    ----------------------------------------------------------------------------------------
    -- Get the Effective Date
    IF @DebugModeFlag = 'Y' PRINT 'Get the Effective Date';
    ----------------------------------------------------------------------------------------
    WITH XMLNAMESPACES( 'http://www.ahsa.com.au/schema/hospitaldetails' AS ns)
    SELECT
        @EffDateText = @HOSDetailsxml.value('(/ns:HospitalDetails/@effectiveDate)[1]', 'VARCHAR(10)' );

    SELECT @EffDate = DATEFROMPARTS (SUBSTRING(@EffDateText, 1, 4), SUBSTRING(@EffDateText, 6, 2), SUBSTRING(@EffDateText, 9, 2));

    ----------------------------------------------------------------------------------------
    -- Get the File Version
    IF @DebugModeFlag = 'Y' PRINT 'Get the File Version';
    ----------------------------------------------------------------------------------------
    WITH XMLNAMESPACES( 'http://www.ahsa.com.au/schema/hospitaldetails' AS ns)
    SELECT
        @FileVersion = @HOSDetailsxml.value('(/ns:HospitalDetails/@fileVersion)[1]', 'VARCHAR(10)' );


Is it better a composite index (item, item_type) or single-column index on item_type_1, item_type_2, ...? We have items of three types to be stored and queried according to some business logic.

create table a_table
(
   item_a   varchar2(30),
   item_b   varchar2(16),
   item_c   varchar2(2),
   -- other columns
);

And the indexes

create unique index idx_1 on a_table (item_a);
create unique index idx_2 on a_table (item_b);
create unique index idx_3 on a_table (item_c);

Data for a specific item of a specific type would be read simply as:

-- reading item_a
select ... from a_table where item_a = '...';

-- reading item_b
select ... from a_table where item_b = '...';

-- ...

If an item is of type a, it can't be of type b, so each row must have only one of item_a, item_b or item_c with a value, the others must be null. (This can be enforced in any ways, it does not matter if it impacts insertions.)

In theory the number of type of items could grow (maybe a fourth item type could be added somewhere in the future).

This fact pushed towards another solution which also avoid the ugly mutually exclusive (unchecked in the given example) columns:

create table a_table
(
   item       varchar2(30) not null,
   item_type  varchar2(10) not null,
   -- other columns
);

create unique index idx_1 on a_table (item_type, item);

-- reading item_X
select ... from a_table where item='...' and item_type='item_X';

Disregarding the obvious disadvantage of the first solution when the number of item types increases and other possible design flaws, is there any important performance difference between accessing (select of a row using indexed columns) a table as done in the first case and accessing a table as done in the second one?

The cardinality of item_a and item_b can be millions, the cardinality of item_c is surely under 200.

Notes

Assume that there will be an equal number of selects for item_a, item_b and item_c
In the first solution, consider a composite index (item_c, item_a, item_b) iff it would be better (performance-wise) than three separate indexes, but do not forget that the comparison must be done with the second solution where both indexed columns will be used in the where condition: assume the use of the best “correct” index/indexes for both cases.

I think, from performance perspective, it will be very close.

But some thoughts:

One column indexes do not store NULL values. So individual indexes will be smaller. Composite index (item_type, item ) size will be bigger than sum of individual indexes. In extreme cases even the height of indexes may differ.
Two of three individual indexes will be unique, so it takes one less consistent read compared to nonunique index.
For item_c column oracle can build precise histogram.
In the case of (item_type, item ), oracle will correctly calculate cardinalities for queries with item types A and B, but for "item_type='C' and item=:X" it can underestimate. This is may be not a problem, but in complex queries with joins can give ineffective plan. So you need to check.
About to compare index (item_a,item_b,item_c) with (item_type, item ) - like others said it will work almost only with queries like "item_a=:X", and will not work when "item_b=:Y". So using index (item_a,item_b,item_c) for first case is just a bad idea when we have equal number of queries on item_b and item_c columns.

    create table hr.tt nologging
    as
    select case when mod(rownum,3) = 0 then rownum end AS item_a,
           case when mod(rownum,3) = 1 then rownum end AS item_b,
           case when mod(rownum,3) = 2 then round(dbms_random.value(1,200)) end AS item_c,
           o.*
     from all_objects o,
    (select * from dual connect by level < 100) d
     where rownum <= 5e6;

    create unique index hr.item_a_idx on hr.tt(item_a) nologging;

    create unique index hr.item_b_idx on hr.tt(item_b) nologging;

    create index hr.item_c_idx on hr.tt(item_c) nologging;

    create table hr.tt2 nologging
    as
    select case when mod(rownum,3) in (0,1) then rownum
                else round(dbms_random.value(1,200))
                end AS item,
           case when mod(rownum,3) = 0 then 'A'
                when mod(rownum,3) = 1 then 'B'
                when mod(rownum,3) = 2 then 'C' end AS item_type,
           o.*
     from all_objects o,
    (select * from dual connect by level < 100) d
     where rownum <= 5e6;

    create index hr.item_item_type_idx on hr.tt2(item_type, item) nologging;

some queries

SYS@mydb> select * from hr.tt where item_a = 300;

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |   197 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TT         |     1 |   197 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | ITEM_A_IDX |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
...
          4  consistent gets

SYS@mydb> select * from hr.tt2 where item_type = 'A'  and item = 300;

1 row selected.

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |   115 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TT2                |     1 |   115 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | ITEM_ITEM_TYPE_IDX |     1 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          5  consistent gets

Below is an example where optimizer calculations are wrong (oracle 11.2).
Optimizer thinks there will be only one row in the "INDEX RANGE SCAN".
Actually we see 8438 rows. Extended optimizer statistics on these two columns may be helpful, may be not.

SYS@mydb> select * from hr.tt2 where item_type = 'C'  and item = 150;

8438 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4063424880

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |   115 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TT2                |     1 |   115 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | ITEM_ITEM_TYPE_IDX |     1 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   2 - access("ITEM_TYPE"='C' AND "ITEM"=150)

Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
       8618  consistent gets
       . . .
       8438  rows processed

And below is the same case for individual item_c column.
Here optimizer calculations are correct - 8133 rows estimated, 8290 rows selected.

SYS@mydb> select * from hr.tt where item_c = 150;

8290 rows selected.

Execution Plan
----------------------------------------------------------
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |  8133 |   826K|  7737   (1)| 00:01:33 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TT         |  8133 |   826K|  7737   (1)| 00:01:33 |
|*  2 |   INDEX RANGE SCAN          | ITEM_C_IDX |  8133 |       |    19   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   2 - access("ITEM_C"=150)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       8499  consistent gets
       . . .
       8290  rows processed

The composite index would be better than three separate indexes because it would be able to use it for all three columns but you will want to pay attention to the order of the columns because it DOES matter.  Could you not test and compare in dev/test environment?
  See quote below.

RedGate:

"For composite indexes, take into consideration the order of the columns in the index definition. Columns that will be used in comparison expressions in the WHERE clause (such as WHERE FirstName = ‘Charlie’) should be listed first. Subsequent columns should be listed based on the uniqueness of their values, with the most unique listed first."


Query to identify people exceeding a particular amount within an interval of 6 months I have tried searching in Google but am just not finding the right post.
I have this table with fields trans_date, CID (which is customerid), and amount.

How do I find the CID exceeding an amount of say $1000 within any time period of 6 months or 180 days (eg. Jan-June or Feb-July, or November 2014-April 2015 (periods overlapping years too)) starting from the beginning of time?

trans_date             CID        amount
22-Jun-2015            123        550
24-Aug-2015            999        200
15-Aug-2015            123        500
24-nov-2014            321        200
08-Jan-2015            321        900
24-oct-2015            999        200

In the result CID 123 and 321 should be the output both exceeding the total amount 1000 in a span of 6 months.

Any response would be highly appreciated.

The part "within any time period of 6 months" makes things quite tricky. It is very easy to check this for a specific period:

select cid,
       sum(amount)
from transfer
where trans_date >= date '2014-11-01'
  and trans_date < date '2015-06-01'
group by cid
having sum(amount) >= 1000;

But this is not what you want.

You want a "rolling running total" that covers the last 6 month relative to the the trans_date value of each row. This can easily be achieved using a window function (aka "analytical function")

select trans_date,
       cid,
       amount,
       sum(amount) over (partition by cid order by trans_date range between interval '6' month preceding and current row) as running_total
from transfer
order by trans_date;

The "trick" is the "range between ..." part: it will only evaluate the running total for the "last 6 month" relative to the month of the "current row"

The above returns the following result for your sample data:

TRANS_DATE          | CID | AMOUNT | RUNNING_TOTAL
--------------------+-----+--------+--------------
2014-11-24 00:00:00 | 321 |    200 |           200
2015-01-08 00:00:00 | 321 |    900 |          1100
2015-06-22 00:00:00 | 123 |    550 |           550
2015-08-15 00:00:00 | 123 |    500 |          1050
2015-08-24 00:00:00 | 999 |    200 |           200
2015-10-24 00:00:00 | 999 |    200 |           400

Now to get the customers you are interested in, you can wrap this query into a derived table and then get only those CIDs that have at least one running total that exceeds 1000:

select distinct cid
from (
  select trans_date,
         cid,
         amount,
         sum(amount) over (partition by cid order by trans_date range between interval '6' month preceding and current row) as running_total
  from transfer
) t
where running_total > 1000;

The distinct is necessary because a customer can have more than one "6 month period" where the total amount exceeds 1000.

This is probably not the most efficient query though - but currently I can't think of anything better.

Here is a SQLFiddle example: http://sqlfiddle.com/#!4/1e26f/2

You can write such a function :

create or replace function get_period(v_date date)
return varchar2
is period varchar2(10);
yr varchar2(4);
mnt number;
begin
     yr := to_char(v_date ,'yyyy');
     mnt := to_number(to_char(v_date,'mm'));
     if (mnt > 0 && mnt < 7) then
         period := concat(yr ,'_1');
     end if;
     if (mnt > 6 && mnt < 13) then
         period := concat(yr ,'_2');
     end if;
     return period;
end;

then in query :

select CID , get_period(trans_date) , sum(amount)
from table_name
group by CID , get_period(trans_date)
having sum(amount) > 1000


How does SQL*Plus connect to Oracle without a listener? sqlplus can be used even if there is no listener in the system, or if it isn't running.

How does SQL*Plus connect to Oracle without a listener?

I can connect any user using SQL*plus even with no listener running.

As @BalazsPapp mentioned, the oracle db can be connected locally even without a listener. In this case, the BEQ (Bequeath) protocol is used, instead of the tcp-based normal oracle communication.

Although on Balazs's link there is nothing about the details of the BEQ. On unix, it probably uses unix domain sockets. On windows, it uses probably some local rpc.

You only bypass the listener when you connect as sysdba*. Ordinary connections through SQLPlus still use the listener. As for how it does it, there is a special mechanism for this kind of connection, which allows a database to be administered without a working listener. I do not believe that the workings of this mechanism are published by Oracle. They want you to use the listener.

Or as sysoper*.
**And only if you don't specify a database in your connect string.


MySQL replication slave hangs after encountering SET @@SESSION.GTID_NEXT= 'ANONYMOUS'; I recently installed two identical default installations of MySQL 5.7 under Ubuntu Server 16.04, and configured them to do a binary log replication. Until now this has been working fine, but suddenly the replication stops continuing and the slave query thread runs at 100% CPU without doing any work.

After some searching I found that the slave status tells that it is way behind master. Using mysqlbinlog on the binlog file indicated by Relay_Master_Log_File and position Exec_Master_Log_Pos, I found out that the statement executed at this position is:

SET @@SESSION.GTID_NEXT= 'ANONYMOUS';

Somehow the slave hangs when trying to execute this statement, sending the CPU load to 100% (which is how I discovered the situation in the first place).

Except by having the slave skip that statement using SET GLOBAL sql_slave_skip_counter=1 it is unclear to me what is the actual cause of this issue, and how I should solve this.

Any help would be really appreciated!

I had same issue. Just skipped it, and all became good.

stop slave; set global sql_slave_skip_counter = 1; start slave;


Oracle Text - Changing SYNC to MANUAL not working always Oracle version is 11g. When the Oracle Text index is created, SYNC option is given as "ON COMMIT". Before inserting large number of records, SYNC is changed to "Manual" for performance reason. Below query is run using CallableStatement for this purpose

{call CTX_DDL.REPLACE_INDEX_METADATA('IXFTS_ABC_TABLE','REPLACE METADATA SYNC (MANUAL)')}

In some environments this works fine but in production environments the SYNC option is still "ON COMMIT". No error is thrown when this is not updated. The index is not a partitioned index. Any pointers to troubleshoot further will be helpful.

[Update] I could reproduce this in my local machine rarely. Looks like running the same sql 2 or 3 times is working. Not sure why this fails the first time.

Try this alter option.

alter index IXFTS_ABC_TABLE parameters ('REPLACE METADATA SYNC(Manual)');


Query against sys.schemas and sys.synonyms runs very slow for one user Scenario: SQL Server 2014 (v12.0.4100.1)

.NET Service runs this query:

SELECT name, base_object_name
FROM sys.synonyms
WHERE schema_id IN (SELECT schema_id
                    FROM sys.schemas
                    WHERE name = N'XXXX')
ORDER BY name

...which returns about 6500 rows but it often times out after 3+ minutes. The XXXX above is not 'dbo'.

If I run this query in SSMS as UserA, the query returns in less than a second.

When run as UserB (which is how the .NET service connects), the query takes 3-6 minutes, and has the CPU% at 25% (of a 4 cores) the entire time.

UserA is a Domain Login in the sysadmin role.

UserB is a SQL Login with:

EXEC sp_addrolemember N'db_datareader', N'UserB'
EXEC sp_addrolemember N'db_datawriter', N'UserB'
EXEC sp_addrolemember N'db_ddladmin', N'UserB'
GRANT EXECUTE TO [UserB]
GRANT CREATE SCHEMA TO [UserB]
GRANT VIEW DEFINITION TO [UserB]

I can duplicate this in SSMS by wrapping the above SQL in an Execute as...Revert block, so the .NET code is out of the picture.

The execution plan looks the same. I diff'ed the XML and there are only minor differences (CompileTime, CompileCPU, CompileMemory).

IO Stats all show no physical reads:

Table 'sysobjvalues'. Scan count 0, logical reads 19970, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 9122, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysclsobjs'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

XEvent waits status (for a ~3min query) are:

+---------------------+------------+----------------------+------------------------------+-----------------------------+
|      Wait Type      | Wait Count | Total Wait Time (ms) | Total Resource Wait Time (ms) | Total Signal Wait Time (ms) |
+---------------------+------------+----------------------+-------------------------------+-----------------------------+
| SOS_SCHEDULER_YIELD |      37300 |                  427 |                            20 |                         407 |
| NETWORK_IO          |          5 |                   26 |                            26 |                           0 |
| IO_COMPLETION       |          3 |                    1 |                             1 |                           0 |
+---------------------+------------+----------------------+-------------------------------+-----------------------------+

If I rewrite the query (in SSMS, I don't have access to the App Code) to

declare @id int
SELECT @id=schema_id FROM sys.schemas WHERE name = N'XXXX'
SELECT a.name, base_object_name FROM sys.synonyms a
WHERE schema_id = @id
ORDER BY name

then UserB runs at the same (fast) speed as UserA.

If I add db_owner to UserB, then, again, the query runs < 1 sec.

Schema created via this template:

DECLARE @TranName VARCHAR(20)
SELECT @TranName = 'MyTransaction'

BEGIN TRANSACTION @TranName
GO

IF NOT EXISTS (SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
        WHERE SCHEMA_NAME = '{1}')
BEGIN
    EXEC('CREATE SCHEMA [{1}]')
    EXEC sp_addextendedproperty @name='User', @value='{0}', @level0type=N'Schema', @level0name=N'{1}'
END
GO

{2}

COMMIT TRANSACTION MyTransaction;
GO

And {2} is, I believe, a list of Synonyms created in that schema.

Query Profile at two points into the query:

I've opened a ticket with Microsoft.

Also, we tried adding UserB to db_owner, and then DENYing all of the privileges we know that are associated with db_owner.  The result is a fast query.  Either we missed something (entirely possible), or there is a special check for the db_owner role.

You may want to re-write your query as follows (I'm using dbo rather than XXXX so that I do find some synonyms on my testing database). This is similar to the re-write you found to be more efficient, but avoids the need to declare a variable and use two queries.

SELECT name, base_object_name
FROM sys.synonyms
WHERE schema_id = SCHEMA_ID(N'dbo')
ORDER BY name

This yields a plan like the following:

One very interesting thing about the Filter operator in this plan is that it has a predicate that performs an internal has_access() check. This filter removes any objects that the current account does not have sufficient permissions to see. However, this check is short-circuited (i.e., completes much more quickly) if you are a member of the db_owner role, which may explain the performance differences you are seeing.

Here is the query plan for your original query. Notice that all synonyms on the database (1,126 in my case, but likely many more in your case) pass through the very expensive has_access() filter, even though only 2 synonyms match the schema. By using the simplified query above, we can ensure that has_access() is only invoked for the synonyms that match your query rather than for all synonyms in the database.

Using sys.dm_exec_query_profiles to explore further

As Martin suggests, we can confirm that the has_access() check is a significant bottleneck by using sys.dm_exec_query_profiles on SQL Server 2014+. If I run the following query using a db_owner account on a database with ~700K objects, the query takes ~500ms:

SELECT COUNT(*)
FROM sys.objects

When run with an account that is not a db_owner, this same query takes about eight minutes! Running with actual plan on and using a p_queryProgress procedure that I wrote to help parse sys.dm_exec_query_profiles output more easily, we can see that almost all of the processing time is spent on the Filter operator that is performing the has_access() check:

Using trace flag 9481 seems to resolve this issue for me.

The CE (Cardinality Estimator) in 2014 (compat 120) changed from 2012 and 2008, and using TF 9481 forces use of the 2012 CE.

See The SQL 2014 Cardinality Estimator Eats Bad TSQL for Breakfast by Kendra Little.


Exclude composite key from select I have 2 identical DBs, one of the tables has C1, C2, C3 , composite key.
I'm trying to get the rows from B that are not present in DB A.

I have tried many approaches, but at this time I even can't re-use this suggestion due too the late hour.

I think it should be by something like

Select * from B
where  not exists
     (SELECT a.C1, a.C2, a.C3
      from A.table as a, b.table as b
      where a.C1=b.C1
          , a.C2=b.C2
          , a.C3=b.C3)

But I know that I'm totally wrong.
What is the proper way to do this?

I think you need this:

Select * from b.table as b
where  not exists
     (SELECT 1
      from A.table as a
      where a.C1=b.C1 and a.C2=b.C2 and a.C3=b.C3 ) ;

You shouldn't add b.table in the subquery's from clause, because you already have it under the b alias in the main query.
You don't need to specify columns in the subquery's select clause, because that doesn't have any effect. "Is there a row satisfying the where clause?" – that's the question that the exists predicate is meant to answer.
You need AND between the conditions in WHERE, not commas.

This might be the simplest code to be written

select *
from b
where b.c1+b.c2+b.c3 not in (select a.c1+a.c2+a.c3 from a)

If column values are int try using convert for them:
convert(varchar,b.c1)


MySQL - Connection blocked because of many connection errors - How to find root cause? I am developing a web application and am running into the dreaded host xxx blocked because of many connection errors message. There have been numerous questions on SO pertaining to this error and common wisdom is to flush hosts to clear the error and then bump up MySQL's max_connections setting in order to avoid this issue in the future. A summary of the issue from the MySQL manual is as follows:

  The number of interrupted connect requests allowed is determined by
  the value of the max_connect_errors system variable. After
  max_connect_errors failed requests, mysqld assumes that something is
  wrong (for example, that someone is trying to break in), and blocks
  the host from further connections until you execute a mysqladmin
  flush-hosts command or issue a FLUSH HOSTS statement. See Section
  5.1.3, “Server System Variables”.

I'd like to go a step further and determine the root cause of the issue for my application. I am relatively new to MySQL and would like to know what is the best way to trap such errors - does MySQL provide any logs or information as to which connections or queries failed or are the any tracing tools I can use to ascertain this information?

Thank you

JP

The advice you've seen about max_connections is a distraction from the issue you're seeing, because it has nothing to do with connection errors.

You can log the failed connections to the mysql error log by issuing this:

SET GLOBAL log_warnings = 2;

You can also make this a permanent setting in my.cnf so that it persists after a restart.

http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_log-warnings

If connections are left open and not killed by application they would also use up resources but would reach a limit of you defined max connections.

mysql> show processlist;

This command will show you if there are dead connections with time specified, you can kill those manually by kill command followed by id or use this script below to kill them with a cronjob.

SECONDS_TOO_LONG=500
QUERIES_RUNNING_TOO_LONG=`/path/mysql -u user -p'pass' -ANe"SELECT COUNT(1) FROM information_schema.processlist WHERE user<>'system user' AND time >= ${SECONDS_TOO_LONG}"`
if [ ${QUERIES_RUNNING_TOO_LONG} -gt 0 ]
then
    KILLPROC_SQLSTMT="SELECT GROUP_CONCAT(CONCAT('KILL QUERY ',id,';') SEPARATOR ' ') KillQuery FROM information_schema.processlist WHERE user<>'system user' AND time >= ${SECONDS_TOO_LONG}"
    /path/mysql -u user -p'pass' -ANe"${KILLPROC_SQLSTMT}" | /path/mysql -u user -p'pass'
fi;

But you must fix your application as this is a temporary fix.


What is an "Obfuscation key" in SQL Server? Jokes aside, what is an "Obfuscation key"? I'm just going off the docs.

sys.sysclsobjs Exists in every database. Contains a row for each classified entity that shares the same common properties that include the following:
...
Obfuscation key.

This seems to involve

Content/binn/sqllang.dll
Content/binn/sqlmin.dll

I would like to know what the key does and what is being obfuscated, and if that's available for the user.

I would guess they mean rows created for symmetric keys.

create symmetric key KeyTest with
  algorithm = aes_128,
  identity_value = '123456'
  encryption by password = '123456!Q';

go

-- Needs a DAC connection
select *
from sys.sysclsobjs
where Name = 'KeyTest'

Result:

class id    name      status  type intprop  created                 modified
----- ----- --------- ------- ---- -------- ----------------------- -----------------------
56    259   KeyTest   0       A1   128      2017-12-22 08:43:33.170 2017-12-22 08:43:33.170

I think this is the internal infrastructure behind "Dynamic Data Masking" which seems to be relatively new thing,

  Traditionally, the logic of obfuscating sensitive data has been developed in the application layer, requiring it to be duplicated across all modules and applications accessing the data.  [...] SQL Server 2016 and Azure SQL DB now offer a built-in feature that helps limit access to those particular sensitive data fields: Dynamic Data Masking (DDM).

That's the only link to the word "obfuscating" that I see though. To the user interface, this is called "Masking", and it's accessible to users with,

ALTER TABLE [SalesLT].[Customer]
ALTER COLUMN [Phone] ADD MASKED WITH (FUNCTION = 'default()')

After the column is masked, it requires 'UNMASK` privilege,


Can I tell SQL Server not to place data in the buffer cache? One of the servers I support has an ETL job that runs every morning. This isn't an SSIS package, just a series of stored procedures executed via SQL Server Agent. The job takes several hours to run, and regularly brings the server's page life expectancy down under 100, which isn't surprising as it hits pretty much every table in the main operational database.

Most of the data it's reading isn't needed during regular operational use, so it's just wasting space in the buffer cache until the job is done and the cache can start filling back up with data that's actually useful. Is there any mechanism through which I can tell SQL Server not to replace what's in the buffer cache when running these stored procedures?

Can I tell SQL Server not to place data in the buffer cache?

No. SQL Server must bring pages into memory in order to work with them. There are no configuration options to directly affect how long pages remain in memory, or the various policies used to manage buffer pool usage.

If your process involves changing data, you may be able to influence the impact on the buffer pool to some extent (ha!) by issuing a manual checkpoint. This will force modified (dirty) pages to be written to disk, freeing them up for reuse.

Resource governor currently only supports limiting workspace memory (used by sorts, hashing operations, and parallel buffers). It does not provide a way to limit the use of the buffer pool for cached data and index pages.

Generally, you will have to rely on SQL Server making good decisions around space usage in the buffer pool. The ETL job(s) likely benefit from the current arrangement, so you might well find a negative performance impact were you able to limit its use of the buffer pool.

If you are certain that the vast majority of the buffer pool is being consumed by finished ETL operations, you might find it worthwhile to explicitly flush dirty pages and clear buffer pool at the end of the ETL process:

CHECKPOINT;
DBCC DROPCLEANBUFFERS;

This is usually not indicated for a production system, but you may find it helps in this specific situation. With no dirty pages and an empty buffer pool, SQL Server will begin to acquire pages for the new workload at its maximum rate.

In particular, the buffer pool being lower than the server memory target will enable ramp-up reads - where whole extents are read in whenever a page is requested from persistent storage. This is effective by default in Enterprise Edition and can be enabled for other editions (even Express) using global trace flag 840, see:

FIX: An application query that reads lots of data may be slow, and the SQL Server service may take a long time to start in SQL Server 2005

From that link:

  This hotfix exposes trace flag 840. When trace flag 840 is turned on, SQL Server can perform larger I/O extent reads to populate the buffer pool when SQL Server starts. The larger I/O extent reads populate the buffer pool faster. Additionally, the larger I/O extent reads improve the initial query compilation and the response time when SQL Server starts.

Although that text mentions SQL Server starting, it also applies to buffer pool repopulation as I described above.

You can of course also help this process along by running dummy queries against structures you want to bring into memory at the end of the ETL process.

Seems like you could just use resource governor:

https://msdn.microsoft.com/en-us/library/bb933866%28v=sql.110%29.aspx

That way you can just put a heavy memory limit on one user, like the user that runs the agent jobs, or the agent account itself.


trim is not working for few string I want to trim dot (.) from a column value. Just say string is "Mr." and I need to trim the dot from the string. I am expecting "Mr"

Lets say,

I have two fields in my table

 id   |  value
 1       Mr.
 2       Ms.
 11      Mr.
 12      Ms.

But while running below query, not getting as i expected.

 select trim(value, '.') from mytable

 value
 (text)
 Mr
 Ms
 Mr.
 Ms.

And noticed, for id 1 & 2 values is coming as expected. but for id 11 & 12 still the dot is coming. I really could not understand what is the issue.

(Using Postgresql 9.1)

I looked at the trim function and it removes the character from the beginning or the end of the string.  I'd bet that the two where it's not working have spaces after the ".".  Try trimming spaces off the end first then trim the "."

select trim(trim(value,' '), '.') from mytable

I haven't used Postgresql any, but generally the syntax for trim follows:
TRIM( [ [{LEADING | TRAILING | BOTH}] [removal_char]
FROM ]   target_string    [COLLATE collation_name])

TRIM  ('.' FROM 'STRING')  would be more like what you'd want, think you have it reversed.


Postgres Slow ROW_NUMBER() function I have created the following table in postgres.

test=# \d leaderboard_scores;
                                        Table "public.leaderboard_scores"
   Column   |            Type             | Collation | Nullable |                    Default
------------+-----------------------------+-----------+----------+------------------------------------------------
 id         | integer                     |           | not null | nextval('leaderboard_scores_id_seq'::regclass)
 user_id    | character varying(45)       |           | not null |
 score      | integer                     |           |          |
 created_at | timestamp without time zone |           |          |
Indexes:
    "leaderboard_scores_pkey" PRIMARY KEY, btree (id)
    "leaderboard_scores_user_id_key" UNIQUE CONSTRAINT, btree (user_id)
    "score_back_user_id" btree (score DESC) INCLUDE (user_id)

My table size is 2M rows

My Use cases are:

To fetch users with max scores (this is running fast)
To get the rank of a particular user

To acheive number 2 I was hoping to use row_number window function but that is running very very slow.

The query I am using

select user_id, row_number() over (order by score desc) from leaderboard_scores order by score desc offset 500000 limit 20;

This query is taking around 900 ms which is too much to get a rank for a given user.

                                                                                QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=52083.47..52085.56 rows=20 width=49) (actual time=1074.712..1074.755 rows=20 loops=1)
   ->  WindowAgg  (cost=0.43..208332.50 rows=1999999 width=49) (actual time=0.086..1043.536 rows=500020 loops=1)
         ->  Index Only Scan using score_back_user_id on leaderboard_scores  (cost=0.43..178332.52 rows=1999999 width=41) (actual time=0.074..807.340 rows=500021 loops=1)
               Heap Fetches: 500021
 Planning Time: 0.097 ms
 Execution Time: 1074.783 ms
(6 rows)

How can I optimize to get a user's rank?

You say you want the rank of a particular user, but that is not what your query does.

  Heap Fetches: 500021

Vacuum your table to reduce the heap fetches.  If the table is updated a lot, you may need to make changes to its autovac parameters to keep autovacuum running on it often enough.

For example,

alter table leaderboard_scores set (autovacuum_vacuum_scale_factor =0);
alter table leaderboard_scores set (autovacuum_vacuum_threshold =<rel_pages / 10>);

Where <rel_pages / 10> needs to manually computed and the numeric value plugged in.

create a temp table

The cost is clearly high, cause the high number of processed records (rows=500021). I think it wouldn't be much slower even without the index. Without the LIMIT the total time of the following would be ... lets say 2-3 sec?

DROP TABLE IF EXISTS temp_leaderboard_ranks;
SELECT user_id, row_number() OVER (ORDER BY score DESC)
  INTO temp_leaderboard_ranks
  FROM leaderboard_scores ORDER BY score DESC;
CREATE INDEX temp_lb_ranks_idx on temp_leaderboard_ranks(row_number)

or something

Another option is using a MATERIALIZED VIEW, mostly its the same as above, like you can create indices on it, but its easier if needs refresh and safer to use.

add the rank as a column

If the original table is changing fast and you need more realtime data, another option is to make the rank table permanent and use triggers keep it up to date.

insert and delete records when the leaderboard_scores changes
update all the affected records in the rank table if the scores column changes most of the time its only a few, which has a score between the NEW.score and OLD.score

If you decide for the trigger thing, you have to be careful with the change of ranks: if the score increases you must decrease the rank by one for all the IDs, which has less score then NEW.score and has higher rank then the previous rank of our ID, and also increase the rank of this ID (one or more ... or zero). If the score can decrease too in this case you have to do the opposite. Although it seems to be simple (and now i feel also, i explain it too mutch), this can be tricky with concurrent changes and multirow updates. If you put the rank in the leaderboard table, you can even create nice deadlocks too.


sp_updatestats doesn't seem to work for non-sysadmin logins Microsoft SQL Server 2017 (RTM-CU16)

When trying to update statistics under a user which is a member of db_owner database role via

EXEC sp_updatestats;

SQL Server throws an error:

  Msg 15247, Level 16, State 1, Procedure sp_updatestats, Line 15 [Batch
  Start Line 0] User does not have permission to perform this action.

Even though MS here states that "ownership of the database (dbo)" is enough seems that it is not.

You misunderstood the phrase

  Requires membership in the sysadmin fixed server role, or ownership of
  the database (dbo)

"ownership of the database" means that this login owns this database.

This is not the same as "is a member of db_owner database role"

Owner of a database is one single login, while there may be as many db_owners as you want.

And in fact if you execute

exec sp_helptext 'sp_updatestats';

in the first rows you'll find:

declare @dbsid varbinary(85)

select @dbsid = owner_sid
    from sys.databases
    where name = db_name()

-- Check the user sysadmin
if not is_srvrolemember('sysadmin') = 1 and suser_sid() <> @dbsid
begin
    raiserror(15247,-1,-1)
    return (1)
end

This controls if the executor is a member of sysadmin server role or he owns this database: suser_sid() <> @dbsid where @dbsid is owner_sid of current database.

To find out the owner of some database you should execute

select owner_sid
from sys.databases
where name = 'some_db';

And you also can find it using SSMS:

Occurs that this sp_updatestats behaviour is some kind of a bug.
One way to solve it is to use another sp and then grant the user execute permission (if not db_owner):

CREATE PROCEDURE dbo.updstatistics
WITH EXECUTE AS 'dbo'
AS
     EXEC sp_updatestats;
GO
GRANT EXECUTE ON dbo.updstatistics TO [<user>];
GO


Why Snapshots of the model, master, and tempdb databases are prohibited I have been researching on snapshots and found at many places including Microsoft

  Snapshots of the model, master, and tempdb databases are prohibited.

My question is why?

Edit:

Actually because at one of our client's server I saw backup statements with Snapshot every hour for each DB at server, I am just wondering if this is useful and what will be the purpose of this

Backup statement

BACKUP DATABASE [master] TO VIRTUAL_DEVICE='{886}4' WITH SNAPSHOT,BUFFERCOUNT=1,BLOCKSIZE=1024,COPY_ONLY

BACKUP DATABASE [msdb] TO VIRTUAL_DEVICE='{886}4' WITH SNAPSHOT,BUFFERCOUNT=1,BLOCKSIZE=1024,COPY_ONLY

taking snapshot on hourly manner is very useful for active databases and based on RTO (Recovery Time Objective) and RPO (Recovery Point Objective) which is less than 1 hour.

Model, master, and tempdb is passive default MSSQL databases.These database doesn't required to snapshot hourly as there wont be much changes occur every hour.One time daily backup is sufficient enough to rescue the database in the event of disaster.

Moreover, snapshopt will use up spaces and available resource (RAM and CPU).


How to repair, or drop/create a corrupted table in mysql? Situation: I have a MySQL database with 104 tables, running locally on XAMPP on windows 7. All the tables have InnoDB engine. XAMPP MySQL is running as windows service. As front end I have PHP (CodeIgniter).

Problem: Last night as usual I stopped working on the project and shutdown the windows normally. Today in the morning I got error table MY_TALBE does not exists in the browser. show tables on console shows the table name, but desc MY_TABLE and select * from MY_TABLE says unknown table. I tried create table MY_TABLE(...) but I had error as table MY_TABLE already exists, so I tried drop table MY_TABLE but it said Unknown table MY_TABLE! I tried mysqlcheck.exe for repair and check, they failed both, but after that, show tables does not show the table name any longer. I need to mention I did not change/move/delete/rename any of mysql files.

Question: Is it possible to recover/repair MY_TABLE, or at least how to drop and create it again (without dropping the database and re-crteating it again)?

This situation sounds a lot like a post I answered two years ago (InnoDB table SELECT returns ERROR 2006 (HY000): MySQL server has gone away (after power outage))
First, here is the InnoDB Architecture

In that post, I basically point out that the metadata of the table is still present inside the system tablespace file ibdata1.
When you ran SHOW TABLES;, all mysqld did was report the .frm files. It is possible to create the table as MyISAM because it has no attachments to ibdata1 or any other metadata. Yet, you cannot convert it to InnoDB because the metadata somehow imagines that the .frm and some tablespace_id is attached to the table name. Based on my old post, I would just mysqldump all the other tables and call you table a lost cause. However, Percona has the Data Recovery Toolkit for just such an occasion.
In the webpage about the Data Recovery Toolkit says

The Percona Data Recovery Tool for InnoDB is a free toolkit for recovering lost or corrupted data from InnoDB tables. It provides a way to extract InnoDB's data from files or raw devices in a tab-separated format when standard InnoDB recovery cannot be used for some reason such as a dropped table or severe corruption that prevents InnoDB from recognizing the data. It can be used for MySQL recovery to retrieve deleted tables, recover previous versions of updated rows, or fix InnoDB file corruption after a hardware failure such as a memory fault or bad cabling.
Percona has used these tools to perform MySQL data recovery for many customers, including extremely large datasets. Although the tools are powerful, performing InnoDB data recovery correctly requires expert knowledge of the InnoDB storage format and source code plus experience, and intuition. Percona can help with our data recovery service.

I have not used it, but if Percona made it, you should give it a try.
If you have a backup of that lost table (in the form of a mysqldump), you should carry out the steps from my old post and then load the backup of the lost table.
SUGGESTION
Once you get this situation squared away, you should tune InnoDB to flush changes to disk.
[mysqld]
innodb_fast_shutdown=0
innodb_max_dirty_pages_pct=0
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1

That way, when you shutdown mysql, InnoDB can more thoroughly flush changes.
If you want even better assurance of InnoDB being flushed to disk, you should use MySQL in Linux.

You can try creating a new table according to old one's structure. Not sure if this will realy help as you say that your original table is corrupted:

CREATE TABLE `new_table` LIKE `MY_TABLE`;

If new table is ok, you can drop old one:

DROP TABLE `MY_TABLE`;

Otherwise you will have to drop the table (with the command above) and recreate it manually or from a backup.


How to check foreign keys related to a table How to see foreign keys related to a table in MySql?

Background : I wanted to drop a table in MySql which has a foreign key constraint. When I do it I get this:

Error Code: 1217. Cannot delete or update a parent row: a foreign key constraint fails

  How can I drop foreign keys related to the table leaving others.

Firstly, find out your FOREIGN KEY constraint name in this way:

SELECT
  TABLE_NAME,
  COLUMN_NAME,
  CONSTRAINT_NAME,   -- <<-- the one you want!
  REFERENCED_TABLE_NAME,
  REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_NAME = 'My_Table';

You can also add (to the WHERE clause) if you have more than one table called My_Table in different schemas.

AND TABLE_SCHEMA = 'My_Database';

And then you can remove the named constraint in the following way:

ALTER TABLE My_Table DROP FOREIGN KEY My_Table_Constraint;

References: 1 & 2.

You can directly execute this query's output to drop the constraint of referenced table.

select concat('ALTER TABLE ' ,CONSTRAINT_SCHEMA,'.',TABLE_NAME,' DROP FOREIGN KEY ' ,CONSTRAINT_NAME ,';' )
from  information_schema.REFERENTIAL_CONSTRAINTS
where REFERENCED_TABLE_NAME='<<table to be dropped >> ';


DB collation is used for comparison instead of column collation I am moving data from one SQL Server to another. lets call them source and destination SQL server.

The source SQL Server has collation SQL_Latin1_General_CP1_CI_AS and the destination has collation Hebrew_CI_AS.

The destination's SQL Server's tables and their columns have collations SQL_Latin1_General_CP1_CI_AS - which is good.

I asked source SQL Server to generate script for moving some tables. When I run that script on the destination SQL Server I get an error:

  Cannot resolve the collation conflict between
  "SQL_Latin1_General_CP1_CI_AS" and "Hebrew_CI_AS" in the equal to
  operation.

Why is it that the collation Hebrew_CI_AS is used (SQL Server Collation) and not the collation of the column?

Thanks

If you do not specify a collation when creating a table (regular or temporary or variable), then the database's default collation is used. When creating a temporary table (not table variable), it exists in tempdb, so tempdb's default collation is used. TempDB's default collation is derived from the server default since the server default was used to create the model database which is in turn used to create all new databases, including tempdb.

Fortunately, you can use the COLLATE clause to specify which collation to use for the column(s), regardless of any database's default collation. And if you are creating temporary tables in databases that have different collations and you don't always know ahead of time what the collation will need to be, then you can specify DATABASE_DEFAULT which will use the current database's default collation instead of tempdb's default collation.

You can see this behavior by running the following tests. The setup creates a database with a collation that I assume is not your server's default (so that it can be obviously different from tempdb).

Test Setup

USE [master];
IF (DB_ID(N'CollateEBCDIC') IS NULL)
BEGIN
  CREATE DATABASE [CollateEBCDIC]
    COLLATE SQL_EBCDIC277_CP1_CS_AS;
END;
GO

Test Queries

1) Creating a table without specifying the COLLATE clause will use that database's default collation:

USE [CollateEBCDIC];

CREATE TABLE dbo.TestCollate (Col1 NVARCHAR(50));

SELECT name, collation_name
FROM   sys.columns col
WHERE  col.[object_id] = OBJECT_ID(N'dbo.TestCollate');

Returns:

name    collation_name
Col1    SQL_EBCDIC277_CP1_CS_AS

2) Creating a temporary table without specifying the COLLATE clause will use tempdb's default collation:

-- DROP TABLE #TestCollate1;
CREATE TABLE #TestCollate1 (Col1 NVARCHAR(50));

SELECT name, collation_name
FROM   tempdb.sys.columns col
WHERE  col.[object_id] = OBJECT_ID(N'tempdb.dbo.#TestCollate1');

Returns:

name    collation_name
Col1    SQL_Latin1_General_CP1_CI_AS

3) Creating a temporary table with specifying the COLLATE clause will use the specified collation. If you don't always know what that collation will be, you can specify DATABASE_DEFAULT which will use that database's default collation:

-- DROP TABLE #TestCollate2;
CREATE TABLE #TestCollate2 (Col1 NVARCHAR(50) COLLATE DATABASE_DEFAULT);

SELECT name, collation_name
FROM   tempdb.sys.columns col
WHERE  col.[object_id] = OBJECT_ID(N'tempdb.dbo.#TestCollate2');

Returns:

name    collation_name
Col1    SQL_EBCDIC277_CP1_CS_AS

4) Declaring a table variable without specifying the COLLATE clause will use the current database's default collation:

SELECT name, collation_name
FROM   sys.dm_exec_describe_first_result_set(N'
DECLARE @TestCollate3 TABLE (Col1 NVARCHAR(50));
INSERT INTO @TestCollate3 (Col1) VALUES (NCHAR(42));
SELECT * FROM @TestCollate3;
', NULL, NULL);

Returns:

name    collation_name
Col1    SQL_EBCDIC277_CP1_CS_AS

Test Cleanup

DROP DATABASE [CollateEBCDIC];

Contained Database Wackiness

When working within a Contained Database, there are two differences from the behavior noted above:

Temporary Tables use the Database's default Collation (i.e. DATABASE_DEFAULT) for string columns that are not declared using the COLLATE keyword. Test #2 shows Temporary Tables using the Instance's default Collation.
Database-level meta-data (e.g. names of objects, Users, Indexes, columns, etc) always has a Collation of Latin1_General_100_CI_AS_KS_WS_SC (i.e. CATALOG_DEFAULT) regardless of the Database's default Collation.

You can use COLLATE database_default to solve this types of problem

Collate
Is a clause that can be applied to a database definition or a column definition to define the collation, or to a character string expression to apply a collation cast.

Syntax

COLLATE { <collation_name> | database_default }

collation_name
    Is the name of the collation to be applied to the expression, column definition, or database definition. collation_name can be only a specified Windows_collation_name or a SQL_collation_name. collation_name must be a literal value. collation_name cannot be represented by a variable or expression.

database_default
    Causes the COLLATE clause to inherit the collation of the current database.

SELECT * FROM test1 INNER JOIN test2  on test1.col COLLATE
database_default = test2.col COLLATE database_default

For more about https://msdn.microsoft.com/en-us/library/ms184391.aspx


Is it good to create new indexes for better performance - SQL Server Hi I have a SQL Stored Proc that used to take 45 seconds to execute.

Then I run the Execution Plan under SQL and saw some high cost steps:

Then I right click that and select:

And finally I create the indexes that the Execution Plan recommend me.

Now the Query takes 2 seconds that is acceptable.

Is that right what I did? Does it come with extra cost?

  Any advice is well appreciated

I always take careful consideration when creating indexes on production systems, always try to create in DEV first. There are several reasons, but the top three for me are. First, there is a cost associated to indexes, essentially more overhead, so depending on your table the cost can be quite high. Second, I am never always privy to all the functionality that could be accessing that table/dataset, so I have seen negative results in other parts of an application due to adding indexes for other reasons. Third, if this is a data warehouse or a table that experiences frequent writes/loads, then you may actually negatively affect performance (unless you drop and add the indexes before and after).

AFAIK, this will (possibly) only affect INSERT/UPDATE tasks on the table, and rebuilding of indexes.  I would confirm that any INSERT/UPDATE Tasks (Procedures) still run efficiently.


I can't connect to my servers SQL database via an IP Address I have setup a server that runs Windows Server 2008, and has SQL Server 2008 Express installed.

I can connect to the machine's SQL Server Express database via the MACHINENAME/SQLEXPRESS.

However when we come to connecting through any software or script using an IP Address it won't allow the connection.

I have tried:

Turning off the Firewall.
Allowing Remote Connections for the SQL Database.
Enabling TCP/IP within the SQL Configuration.

When we attempt to connect via the software 'SQL Server Management Studio', we get the following message:

Error Message:

  An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (Microsoft SQL Server, Error: 10061)

  A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An established connection was aborted by the software in your host machine.) (Microsoft SQL Server, Error: 10053)

Can you please let me know when your free so we can take a look because I seem to be getting know where, I’ve amended the details as per some information UK Fast sent me but they have said "It’s not within the support remit", so they can’t help any further.

I look forward to hearing from you.

Your SQL Server is installed as named instance, so first of all try connecting to your server using the following server name: IP Address\SQLEXPRESS.
When you install SQL Server as named instance it uses dynamic TCP/IP ports by default, so it is not possible to connect to it whitout specifying instance name (just IP address). If you need to connect to your server without using instance name you have to reconfigure your server to use static TCP port. To do it please perform the following:

open SQL Server Configuration Manager;
switch to the SQL Server Network Configuration | Protocols for SQLEXPRESS;
double-click the TCP/IP protocol;
select the Yes value in the Enabled field;
switch to the IP Addresses tab;
find the IPAll section;
clear the TCP Dynamic Ports field in that section;
specify the 1433 value in the TCP Port field:

restart your server
Try to connect to your server using just its IP address.

Just adding on to what has been previously said. I had the same issue of not being able to connect to the server (client and server on the same desktop) and I ran through all the steps listed here trying to fix it. However, I eventually found out what the issue was. All I had to do was go into SQL server management studio and click "SQL server services" and then single-click the server I needed to connect to. Then click the green 'run' button. I'm not sure if this was specific to me since my computer was both running and accessing the server, hope it helps anyone else out there though!


MySQL UUID() behavior 4 years ago, I answered a question on SO, "how to set an UUID for each row in bulk".
I answered, after trying it,
UPDATE table SET uuidcol = UUID();

It worked for me, but about half (it seems) people report that it doesn't work, saying the ID is identical on all rows.
So today, I tried again, dropping each time the column, and creating a new one with a new type, like
text, char(36), varchar(36)

and it worked again, making a unique UUID per row, each time.
What could make that it does not work for other people?
The engine for instance? ( using InnoDB on MySQL 5.7 )
edit
Did another test by

creating a simple table t of a uu varchar(36)
inserting 10,000,000 lines with uu being null
did the UPDATE t SET uu=UUID();

and all of the 10M lines have a different, unique UUID.

I'm having this exact same problem right now.
I have written a sample java Application to demonstrate the problem:
MariaDBTestUUID.java:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MariaDBTestUUID
{
    private Connection connect;
    private Statement statement;
    private ResultSet resultSet;

    public void updateUUID() throws Exception
    {
        try
        {
            // This will load the MySQL driver, each DB has its own driver
            Class.forName("org.mariadb.jdbc.Driver");
            // Setup the connection with the DB
            connect = DriverManager
                    // AWS Aurora (MySQL server 5.6.10) - found with SQL "show variables like '%version%';"
                    .getConnection("jdbc:mariadb://<hostname>:3306/<DB name>?user=<user>&password=<password>");
//                     // local MySQL server (5.7.22)
//                    .getConnection("jdbc:mariadb://localhost:3306/licenceserver4?user=root&password=<password>");
//                     // docker container MySQL server (5.7.30)
//                    .getConnection("jdbc:mariadb://localhost:3307/testDB?user=root&password=<password>");

            // PreparedStatements can use variables and are more efficient
            PreparedStatement preparedStatement =
                connect.prepareStatement("UPDATE testUUID SET new_id = uuid(), new_id2 = REPLACE(uuid(), '-', ''), new_id3 = UNHEX(REPLACE(uuid(), '-', ''))");
            preparedStatement.executeUpdate();

            queryStrings("select ID, new_ID, new_ID2, hex(new_ID3) as new_ID3 from testUUID");
            queryStrings("select count(distinct(new_ID)) as distinct_rows from testUUID");
            queryStrings("select count(distinct(new_ID2)) as distinct_rows2 from testUUID");
            queryStrings("select count(distinct(new_ID3)) as distinct_rows3 from testUUID");
        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            close();
        }
    }

    private void queryStrings(String sql) throws SQLException
    {
        System.out.println("Running \"" + sql + "\"... ");
        statement = connect.createStatement();
        // Result set get the result of the SQL query
        resultSet = statement
            .executeQuery(sql);
        writeResultSet(resultSet);
    }

    private void writeResultSet(ResultSet resultSet) throws SQLException
    {
        // ResultSet is initially before the first data set
        while (resultSet.next())
        {
            // we can only do this because we know they are all strings
            for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++)
            {
                String value = resultSet.getString(i);
                System.out.print(resultSet.getMetaData().getColumnName(i) + ": " + value + ", ");
            }
            System.out.println();
        }
    }

    // You need to close the resultSet
    private void close()
    {
        try
        {
            if (resultSet != null)
            {
                resultSet.close();
            }

            if (statement != null)
            {
                statement.close();
            }

            if (connect != null)
            {
                connect.close();
            }
        }
        catch (Exception e)
        {
            System.out.println("Error closing:");
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws Exception
    {
        new MariaDBTestUUID().updateUUID();
    }
}

The version of the client library is set here:
build.gradle:
plugins {
    id 'java'
}

sourceCompatibility = 11

repositories {
    mavenCentral()
}

dependencies {
    // DB driver
    runtimeOnly 'org.mariadb.jdbc:mariadb-java-client:2.2.4'
}

To run the test application, just:
1 - connect to each of the DBs and run the following queries:
create table testUUID (ID int, new_ID varchar(100), new_ID2 varchar(100), new_ID3 BINARY(16));
insert into testUUID (id) values (1),(2),(3);

2 - replace the connection info masked for security reasons and run it on each of the DBs.
3 - set the version of mariadb client to use in the test in build.gradle
4 - run main()
If the output shows 3 for each of the distinct queries, the update stmt created distinct values in each column.
If it shows 1, the update set the same value to all rows for that column.
The queries should all return 3.
What I saw, for all DBs:

With mariadb client 2.2.3, it returns 3 for all queries.
With mariadb client 2.2.4, it returns 1 for the second and third queries (new_ID2 and new_ID3).

So, my findings so far:

Everything worked without issues (all columns in every row were set with distinct values) until version 2.2.3 of the mariadb client library.
From version 2.2.4, it started setting identical values on every row for columns we were setting to replace(uuid(),'-', '') using a single update statement.
This happens with several further mariadb client versions as well, including the latest (2.7.0).

Either the issue is related to the REPLACE() function, or to the nesting of function calls in the update. It is definitely not caused by the function UUID().

The issue happens equally in MySQL 5.7.22 (locally installed in my env), MySQL 5.7.30 (test docker container), and more importantly, Aurora DB (MySQL 5.6.10).

----------------------------------------------------
EDIT
----------------------------------------------------
For me, this was caused by a conjunction of:

the database charset
a change in MariaDB J/Connector starting at version 2.2.4
the combination of UUID() and String processing functions (like REPLACE())

Adding CONVERT() to the picture solved it:
UPDATE sometable SET uuid=UNHEX(REPLACE(CONVERT(UUID() using utf8mb4), '-', '')) WHERE uuid IS NULL;
For more details (and an upvote): https://stackoverflow.com/a/51393124/5154619

The probLem with the implementation and definition  of uuid() in mysql is; that it is nOt truly randoM generated, so that collisions can occir especually when doing it in a bulk.
Better is to use uuid v4, which guarantees randomness throughout see an implementation https://stackoverflow.com/a/32965744/5193536 it is a bit slower.


Script to Delete records in multiple table I would like to delete a specific record from two or more tables which are linked to one another.

For Example: I have two tables, Students and winners I would like to delete the names Roy and Peter from both the tables in one shot.

table: Students

> ID     name      class
> 1      Roy         2
> 2      James       3
> 3      Carl        4
> 4      Peter       4
> 5      Alice       5

table: Winners

St_ID          achievement
1              1
2              1
3              3
4              5
5              5

I have more than 100 tables with 50 specific records to be deleted from all the tables.

You have to achieve this using Dynamic SQL Query

1- First you have to list all Tables With there Corresponding Databases in a Temp Table
2- Building Query for databases that contains Students and Winners dataTable

Your script should look like

--Get all Databases With corresponding Database

declare @SQL nvarchar(max)
DECLARE @strQuery AS NVARCHAR(MAX)

SET @strQuery = ''

CREATE TABLE #TblTemp (DatabaseName Varchar(255), Tablename Varchar(255))

set @SQL = (select 'union all
select '''+D.name+''' as DatabaseName,
       T.name collate database_default as TableName
from '+quotename(D.name)+'.sys.tables as T
'
from sys.databases as D
for xml path(''), type).value('substring((./text())[1], 13)', 'nvarchar(max)')

--print @SQL
INSERT INTO #TblTemp
exec (@SQL)

-- Building Queries

SELECT @strQuery = @strQuery + 'Delete T1 from [' + name  + '].dbo.Students As T2
                        Inner join  [' + name  + '].dbo.Winners as T1
                        On T1.[st_ID] = T2.[ID]
                        Where    T1.[name] = IN(''Roy'',''Peter'')  ;

DELETE FROM [' + name  + '].dbo.Students WHERE [name] = IN(''Roy'',''Peter'') ;

'

 from sys.databases
 WHERE EXISTS (SELECT 1 FROM #TblTemp WHERE #TblTemp.DatabaseName = name AND #TblTemp.TableName = 'Students') AND
 EXISTS (SELECT 1 FROM #TblTemp WHERE #TblTemp.DatabaseName = name AND #TblTemp.TableName = 'Winners')

--VIEW QUERIES (you can copy result and execute it manually)
SELECT @strQuery

--EXECUTE QUERIES
EXEC(@strQuery)

--DROP Temp Table
DROP TABLE #TblTemp

This Will Result a query like the following (If these databases contains Students and Winners Table)

Delete T1 from [master].dbo.Students As T2   Inner join  [master].dbo.Winners as T1    On T1.[st_ID] = T2.[ID]    Where    T1.[name] = IN('Roy','Peter')  ;
DELETE FROM [master].dbo.Students WHERE [name] = IN('Roy','Peter') ;
Delete T1 from [tempdb].dbo.Students As T2   Inner join  [tempdb].dbo.Winners as T1    On T1.[st_ID] = T2.[ID]    Where    T1.[name] = IN('Roy','Peter')  ;
DELETE FROM [tempdb].dbo.Students WHERE [name] = IN('Roy','Peter') ;
Delete T1 from [model].dbo.Students As T2   Inner join  [model].dbo.Winners as T1    On T1.[st_ID] = T2.[ID]    Where    T1.[name] = IN('Roy','Peter')  ;
DELETE FROM [model].dbo.Students WHERE [name] = IN('Roy','Peter') ;
Delete T1 from [msdb].dbo.Students As T2   Inner join  [msdb].dbo.Winners as T1    On T1.[st_ID] = T2.[ID]    Where    T1.[name] = IN('Roy','Peter')  ;
DELETE FROM [msdb].dbo.Students WHERE [name] = IN('Roy','Peter') ;
Delete T1 from [AdventureWorks2008R2].dbo.Students As T2   Inner join  [AdventureWorks2008R2].dbo.Winners as T1    On T1.[st_ID] = T2.[ID]    Where    T1.[name] = IN('Roy','Peter')  ;     DELETE FROM [AdventureWorks2008R2].dbo.Students WHERE [name] = IN('Roy','Peter') ;     Delete T1 from [DbMail].dbo.Students As T2   Inner join  [DbMail].dbo.Winners as T1    On T1.[st_ID] = T2.[ID]    Where    T1.[name] = IN('Roy','Peter')  ;

I don't Now if that is what you was asking for in your question below (Your question was not specific like this one, so my answer was providing the dynamic sql logic in general)

Truncate with where clause

From SQL DELETE

  Most database management systems allow you to create a foreign key constraint so that if you delete a row in a table, the corresponding rows the related table are also removed automatically. This ensures the integrity of the data.

AND

Answered in SO. (Must be adapted to your situation)

  Can't you just separate them by a semicolon?

Delete from messages where messageid = '1';
Delete from usersmessages where messageid = '1'

  OR

  Just use INNER JOIN as below

DELETE messages , usersmessages  FROM messages  INNER JOIN usersmessages
WHERE messages.messageid= usersmessages.messageid and messages.messageid = '1'

EDIT due to @NIC's comment (and DV?):

First option: table_constraint (Transact-SQL)

  ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } Specifies
  what action happens to rows in the table that is altered, if those
  rows have a referential relationship and the referenced row is deleted
  from the parent table. The default is NO ACTION.

  ...

  CASCADE Corresponding rows are deleted from the referencing table if that row is deleted from the parent table.

  ...

and an example in SO

Second option:

prepare tables

DROP TABLE IF EXISTS dbo.Student, dbo.Winner

CREATE TABLE dbo.Student
(
    ID          INT         NOT NULL,
    name        VARCHAR(30) NOT NULL,
    class       VARCHAR(30) NOT NULL
);
CREATE TABLE dbo.Winner
(
    stID        INT         NOT NULL,
    achievement INT         NOT NULL
);
GO

INSERT INTO dbo.Student(ID, name, class)
VALUES
    (1, 'Roy', '2'),
    (2, 'James', '2'),
    (3, 'Carl', '2'),
    (4, 'Peter', '2'),
    (5, 'Alice', '2')

INSERT INTO dbo.Winner(stID, achievement)
VALUES
    (1, 1),
    (2, 1),
    (3, 3),
    (4, 5),
    (5, 5)

Deleting rows

DELETE FROM Student WHERE ID = 1;
DELETE FROM Winner WHERE stID = 1

Tested in SQL Server 13.0.1722.0
note: DROP TABLE IF EXIST may not work in older versions


postgresql: permission denied for sequence _i_seq I have a schema of a table in PostgreSQL that looks like:

CREATE TABLE IF NOT EXISTS  data.pmacct (

    i                       BIGSERIAL PRIMARY KEY,

And there are users:

A: superuser privileges
B: read/write privileges
C: read privileges

And I have made the following modifications in regards to the privileges (as user A)

CREATE SCHEMA data ;
ALTER DATABASE ip_spotlight SET search_path TO data ;
REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA data FROM PUBLIC ;
ALTER DEFAULT PRIVILEGES IN SCHEMA data GRANT SELECT ON TABLES TO C ;
ALTER DEFAULT PRIVILEGES IN SCHEMA data GRANT ALL ON TABLES TO B ;

as user postgres

GRANT USAGE ON SCHEMA data TO netops ;
GRANT USAGE ON SCHEMA data TO netopsapp ;
GRANT SELECT ON ALL TABLES IN SCHEMA data TO C ;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA data TO C ;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA data TO C ;
GRANT ALL ON ALL TABLES IN SCHEMA data TO B ;
GRANT ALL ON ALL SEQUENCES IN SCHEMA data TO B ;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA data TO B ;

But I am getting the following error message as user B

  ERROR:  permission denied for sequence pmacct_i_seq

Could you please enlighten me what I have done wrong and user B is not able to do an INSERT statement into the data.pmacct table ?

Try to make changes  on all tables
Answer from Alex Soto

Since you're changing the ownership for all tables, you likely want views and sequences too.  Here's what I did:

Tables:

for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" YOUR_DB` ; do  psql -c "alter table \"$tbl\" owner to NEW_OWNER" YOUR_DB ; done

Sequences:

for tbl in `psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" YOUR_DB` ; do  psql -c "alter table \"$tbl\" owner to NEW_OWNER" YOUR_DB ; done

Views:

for tbl in `psql -qAt -c "select table_name from information_schema.views where table_schema = 'public';" YOUR_DB` ; do  psql -c "alter table \"$tbl\" owner to NEW_OWNER" YOUR_DB ; done

You could probably [DRY][1] that up a bit since the alter statements are identical for all three.


How can I eliminate false "connection string format is not valid" messages from SSIS log? I have a project that uses the SSIS catalog with project-level connection managers. The connection managers' connection strings are overridden at the server using a project parameters file, to enable easily repointing the whole project to different data sources by setting different connection strings.

Here's the issue:

It works. No real connection problems or failures.
It bloats the log
    tables horribly in the catalog because upon each package execution I
    get:

  120    30  Error: The connection string format is not valid. It must
  consist of one or more components of the form X=Y, separated by semicolons.
  This error occurs when a connection string with zero components is set on
  database connection manager.

For every connection manager, in every package. I think this has to be a false error that gets triggered when the connection string is overridden with the (correct) values in the param file.

Is there something wrong here? Some way to suppress those messages?

If you use the Excel connection manager for example, you should set the ExcelFilePath property as an Expression instead of the ConnectionString !

If you are using XML file package configuration, then make sure you type the values proper within  tags. There shouldn't be missing semicolon within the string of subtags. And,there should not be extra spaces.
For e.g  if you are using like below in the ConfiguredValue tag, see the semicolons between each properties.

 Data Source=SERVERNAME;Initial Catalog=DatabaseName;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;

If you miss the semicolon like below :(between Data Source and Initial Catalog properties)

Data Source=SERVERNAME Initial Catalog=DatabaseName

Then it will show error.


Efficient way to move rows across the tables? This is somewhat long question as I would like to explain all details of the problem.

System Description

We have a queue of incoming messages from external system(s).
Messages are immediately stored in the e.g. INBOX table. Few thread workers
fetch the job chunk from the table (first mark some messages with UPDATE,
then SELECT marked messages). Workers do not process the messages,
they dispatch them to different internal components (called 'processors'),
depending on message command.

Each message contains several text fields (longest is like 200 varchars),
few ids and some timestamp(s) etc; 10-15 columns total.

Each internal component (i.e. processor) that process messages works differently.
Some process the message immediately, others triggers some long operation,
even communicating via HTTP with other parts of the system. In other
words, we can not just process message from the INBOX and then remove it.
We must work with that message for a while (async task).

Still, there are not too many processors in the system, up to 10.

Messages are all internal, i.e. it is not important for user to browse
them, paginate etc. User may require list of processed relevant messages,
but that's not mission-critical feature, so it does not have to be fast.
Some invalid message may be deleted sometimes.

Its important to emphasize that expected traffic might be quite high - and we don't
want bottlenecks because of bad database design. Database is MySql.

Decision

The one of the decisions is not to have one big table for all messages, with some flags column that will indicate various messages states. Idea is to have tables per
processors; and to move messages around. For example, received messages will be stored in INBOX, then moved by dispatcher to some e.g. PROCESSOR_1 table, and finally moved to ARCHIVE table. There should not be more then 2 such movements. W

While in processing state, we do allow to use flags for indicating processing-specific states, if any. In other words, PROCESSOR_X table may track the state of the messages; since the number of currently processing messages will be significantly smaller.

The reason for this is not to use one BIG table for everything.

Question

Since we are moving messages around, I wonder how expensive this is with high volumes. Which of the following scenarios is better:

(A) to have all separate similar tables, like explained, and move complete messages rows, e.g. read complete row from INBOX, write to PROCESSOR table (with some additional columns), delete from INBOX.

or

(B) to prevent physical movement of the content, how about to have one big MESSAGES table that just stores the content (and still not the state). We would still have other tables, as explained above, but they would contain just IDs to messages and additional columns. So now, when message is about to move, we physically move much less data - just IDs. The rest of the message remains in the MESSAGE table unmodified all the time.

In other words, is there a penalty in sql join between one smaller and one huge table?

Thank you for your patience, hope I was clear enough.

I think its faster (feel free to test) to keep the record in the same table while processing, but be sure to set an index on the column(s) that track which processor is using them.

Also use MyISAM tables if you don't require InnoDB's features. The ARCHIVE table can best be set to the archive type, so it does not make space for updates (if you don't need to write to it)


The column order of composite clustered primary key on a partitioned table? I have the following table.

create table T (K1 date, K2 int references S(Id), ....) on partitionScheme(K1)

The table will be partitioned by K1 (K1 has low selective. the data will be appended in the order by K1). Which of the following primary keys (the column order is different) is preferred?

alter table T add primary key clustered (K1, K2)
alter table T add primary key clustered (K2, K1)

Or should the PK be non-clustered and create another clustered index?

A lot of the queries look like:

select ....
from T join S on S.Id = T.K2
where ....

Which of the following primary keys (the column order is different) is preferred?

Like all indexing decisions, much depends on how the table will be queried.

All partitioned indexes (for SQL Server 2008 and later) have the partition ID (not partitioning key value) as a hidden leading key column in each partitioned index, so the effective competing definitions are:

PartitionID, K1, K2
vs.
PartitionID, K2, K1

This affects the utility of each index for different types of queries, as one would expect. The main extra consideration is that inequality seeks on the first real key (K1 or K2) are still supported, regardless of any inequality seek and/or partition elimination operations on the PartitionID column.

For example, the (K1, K2) index specification can seek to a range of partitions, and a range of K1 values simultaneously:

SELECT T1.*
FROM dbo.T1 AS T1
WHERE 1 = 1
AND T1.K1 >= CONVERT(date, '20080711', 112)
AND T1.K1 <= CONVERT(date, '20100711', 112);

The same query on a table with (K2, K1) as the clustered index key could seek find the range of partitions, but it would have to fully scan each qualifying partition to locate rows that match the K1 predicates exactly. To be clear, the test of K1 values would be applied as a residual predicate, not as a seeking operation.

This will show as a Clustered Index Scan in showplan, with a partition-eliminating seek, and residual predicate on K1 values:

A subtle point when using the date data type as the partitioning key is that you must be careful to use explicit date types in your queries if you expect partition elimination to occur reliably. Using other types, such as datetime, is easily done (by accident) but will often prevent elimination, where it would logically be expected.

For example, this query will touch all partitions:

DECLARE @dt datetime = '20080711';

SELECT *
FROM dbo.T1 AS T1
WHERE T1.K1 = @dt;

Whereas this query will touch only a single partition:

DECLARE @dt date = '20080711';

SELECT *
FROM dbo.T1 AS T1
WHERE T1.K1 = @dt;

Both queries look superficially identical in graphical showplan (a clustered index seek). You need to check the operator properties in detail to check if static or dynamic partition elimination is being applied.

For the example join query given in the question: Both indexing strategies include the K2 column, but neither can generally provide rows in K2 order without a sort. As a result, either index is equally good for a hash or nested loops join, but neither can provide the input order required for a merge join on K2.

This might seem counter-intuitive for the (K2, K1) index, but remember the leading PartitionID key. Each partition has rows in (K2, K1) order. Unless exactly one partition is specified in the query, a sort will be necessary to return rows in K2 order. The (K1, K2) index could only return rows in K2 order for a single partition and a single given value of K1.

The proposed clustered primary key (K1, K2) has the potential advantage of minimizing base table page splits if appended data is actually sorted by the clustering key during the insert operation. For the (K1, K2) index, this would mean rows sorted by (PartitionID, K1, K2). For (K2, K1), it would be (PartitionID, K2, K1).

Related reading: Query Processing Enhancements on Partitioned Tables and Indexes

if you have identity field, PK should be on 'K1' and 'id_filed'

create table  T (...)
on partitionscheme(K1)

alter table T add primary key clustred (K1,id_field) on partitionscheme(K1)

if not

alter table T add primary key clustred (K1) on partitionscheme(K1)

,but the clustered index always must be on same file group as table.


ISNULL() OR Is NULL in UPDATE statement I have to run an UPDATE statement on live online database with 500k records. I want to know which statement will run faster:

Update Table set REC_ID = isnull(REC_ID,'')

Update Table set REC_ID = '' where REC_ID is null

i am using Microsoft SQL Server 2012

Those queries won't do the same thing.

Update Table set REC_ID = isnull(REC_ID,'')

This one will update each record and if REC_ID is NULL it will set it to ''.

Update Table set REC_ID = '' where REC_ID is NULL

This one will only update records containing a null value in REC_ID, and set it to ''.
While both of them will end up giving the same result, the second one will be executed on less records (except if every REC_ID is NULL), it should be faster.

The WHERE clause in your UPDATE statement could be used to speed the search for matching rows when the field in question is part of an index. In this example, if REC_ID is indexed and the selectivity of the field is good, the WHERE clause will help, but if it's poor, a table scan will likely be required anyway and both statements will last almost the same.


How to deal with very large transaction logs in SQL Server Here is my situation: I have being asked to deal with a SQL Server 2008 R2 database that has a transaction log that is several times bigger than the database itself. The database is in full recovery mode and the transaction log has never been backed up.

I intend to run the transaction log backup every 15 minutes but I am afraid that the first backup may take a very very long time (hundreds of GBs of data).

What is the right course of action here?

I'm a developer by training and not a DBA so I would appreciate a more detailed answer. Thanks in advance.

I would take the first transaction log backup by hand (not as a scheduled task) and during hours when a performance hit will be acceptable to your users.

Then take a second immediately so that the working area of the log file moves back to the start of the file.

Then shrink the log file.

Then start your scheduled transaction log backups.

Full recovery model keeps all transactions in log file till backup log happens.

Here are your steps

Backup the Database (just in case)
Change recovery model to simple
Truncate log file
Change recovery model back to full


Does the order I declare foreign key constraints in matter? Does it matter what order I add foreign key constraints to a table in, or what order I write them in a CREATE TABLE statement? MySQL clearly at least remembers the order; if I run the DDL statements below...

CREATE TABLE foo(id INT PRIMARY KEY);
CREATE TABLE bar(id INT PRIMARY KEY);
CREATE TABLE baz (
  foo_id INT,
  bar_id INT,
  FOREIGN KEY (bar_id) REFERENCES bar (id),
  foreign key (foo_id) REFERENCES foo (id)
);
CREATE TABLE qux (
  foo_id INT,
  bar_id INT,
  FOREIGN KEY (foo_id) REFERENCES foo (id),
  FOREIGN KEY (bar_id) REFERENCES bar (id)
);

... and then run SHOW CREATE TABLE on each of baz and qux...

mysql> SHOW CREATE TABLE baz\G
*************************** 1. row ***************************
       Table: baz
Create Table: CREATE TABLE `baz` (
  `foo_id` int(11) DEFAULT NULL,
  `bar_id` int(11) DEFAULT NULL,
  KEY `bar_id` (`bar_id`),
  KEY `foo_id` (`foo_id`),
  CONSTRAINT `baz_ibfk_1` FOREIGN KEY (`bar_id`) REFERENCES `bar` (`id`),
  CONSTRAINT `baz_ibfk_2` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE qux\G
*************************** 1. row ***************************
       Table: qux
Create Table: CREATE TABLE `qux` (
  `foo_id` int(11) DEFAULT NULL,
  `bar_id` int(11) DEFAULT NULL,
  KEY `foo_id` (`foo_id`),
  KEY `bar_id` (`bar_id`),
  CONSTRAINT `qux_ibfk_1` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`id`),
  CONSTRAINT `qux_ibfk_2` FOREIGN KEY (`bar_id`) REFERENCES `bar` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

... then we can see that MySQL remembered that the foreign key pointing to foo comes first in baz, but second in qux.

It's possible to imagine this order making a difference; for instance, if MySQL, upon insert into a table with foreign keys, takes out locks on referenced tables in the order that the foreign keys are declared, then simultaneous inserts into baz and qux in my example above would have the potential to deadlock, and so it would be a best practice to ensure that foreign keys were always declared in the same order on all tables (e.g. by alphabetical order of the referenced table name). Do any such considerations exist in reality, or does the order of foreign key constraints have no effect on anything?

The order of FK Definition does not matter. When a transaction on table with FKs completes, it will release the lock on referenced tables simultaneously.


How does "Be made using the login's current security context" pass the users password to remote server Say you have a SQL Server with a "Linked Server" to another server - said "Linked Server" is set to use "Be made using the login's current security context" as its authentication model.

How does the SQL Server provide the correct password (when dealing with non-windows credentials) to the other server when all the server has is a Hash.

I have transferred logins from server to server before and i know that the hash alone is stored in SQL and this is often presented as a hex value hex when creating logins with passwords, for example:

create login test with password = '0x22A9AE652CFC38938D56A9C3872B266B192D16E4' hashed

If SQL only has a hash available - can it logon to the remote server using the hash? Or is the original (un-hashed) password kept in the user’s session / memory for the entire duration of their connection which SQL can then retrieve and pass to the remote server for login?

If the server can login with a hash instead of the password – can this be done in normal logins or is it purely an internal feature of linked servers?

Purely a curiosity question – like to understand how these things work.

Whilst liasing with Microsoft on a different issue (paid support request) i happened to ask them about this & they confirmed the unhashed password is passed to the remote server but the mechanism in which the SQL Engine does this is "hidden and cannot be captured" - but suffice to say, the login is not done using the hash.

Here is there full response:

  How does the SQL Server provide the correct password (when dealing with non-windows credentials) to the other server when all the server has is a Hash?

  Answer: If connected to the local server using SQL Server
  Authentication, login name and password will be used to connect to the
  remote server. In this case a login with the exact same name and
  password must exist on the remote server.

  Be made using the login's current security context:

  Specify that a connection will be made using the current security context of the
   login for logins not defined in the list. If connected to the local
   server using Windows Authentication, your windows credentials will be
   used to connect to the remote server. If connected to the local server
   using SQL Server Authentication, login name and password will be used
   to connect to the remote server. In this case a login with the exact
   same name and password must exist on the remote server.

  How does the SQL Server provide the correct password (when dealing with non-windows credentials) to the other server when all the server has is a Hash?

  Answer: Login name and password is passed to the remote server, this
  mechanism/task is handled by the SQL Engine which is hidden and cannot
  be captured.

  If SQL only has a hash available - can it logon to the remote server
   using the hash? Or is the original (un-hashed) password kept in the
   user’s session / memory for the entire duration of their connection
   which SQL can then retrieve and pass to the remote server for login?
   If the server can login with a hash instead of the password – can this
   be done in normal logins or is it purely an internal feature of linked
   servers?

  Answer: HASH cannot be used to login to the SQL server, SQL Engine is
  responsible for handling login process and we don’t have much
  documentation around this.

You have to give username password(non hashed) of remote server when setting up the linked server.

When giving non windows username, password should be same on both servers to work!