Improving indexing of jsonb data when the json structure is well defined I have a simple table records that contains a primary key (id) and a jsonb field (data). The structure of the jsonb is consistent across all rows. I have created a view (metadata) for this table which extracts a particular value category, which I can then run queries against.
CREATE VIEW metadata AS
SELECT id as id,
       data -> 'some_key' -> 'some_array' ->> 0 as category
FROM records;

(the data structure is not controlled by me)
I can use this VIEW to perform queries like:
SELECT category
FROM metadata
WHERE category = 'category_0';

and it behaves as expected.
I was experiencing slow performance out of the above query (and similar equality queries), so I added an index as follows:
CREATE INDEX metadata_category_idx ON records ((data -> 'some_key' -> 'some_array ->> 0));

This has not improved my performance as I would have expected when I query for a particular category using string equality. It should be noted that for "categories" with a small number of rows, EXPLAIN ANALYZE tells me its using the index, however for "categories" with a large number of rows, it falls back to sequential searching. There are approximately 350k rows in my table. I've tried each type of index (i.e. GIN, GIST) with no measured benefits.
I intend to strictly use string equality when querying this view, and I have little control over the structure of the json object in data.
What would be the best way to construct my index such that the above query can run more efficiently?

If category were a regular column and not an expression, PostgreSQL would choose an index-only scan, which would be much faster if the table has been vacuumed recently. But index-only scans are not supported on expression indexes like that, see the documentation:

In principle, index-only scans can be used with expression indexes. For example, given an index on f(x) where x is a table column, it should be possible to execute
SELECT f(x) FROM tab WHERE f(x) < 1;

as an index-only scan; and this is very attractive if f() is an expensive-to-compute function. However, PostgreSQL's planner is currently not very smart about such cases. It considers a query to be potentially executable by index-only scan only when all columns needed by the query are available from the index. In this example, x is not needed except in the context f(x), but the planner does not notice that and concludes that an index-only scan is not possible. If an index-only scan seems sufficiently worthwhile, this can be worked around by adding x as an included column, for example
CREATE INDEX tab_f_x ON tab (f(x)) INCLUDE (x);

An additional caveat, if the goal is to avoid recalculating f(x), is that the planner won't necessarily match uses of f(x) that aren't in indexable WHERE clauses to the index column. It will usually get this right in simple queries such as shown above, but not in queries that involve joins. These deficiencies may be remedied in future versions of PostgreSQL.

So you would jave to include the JSON column in the index to get an index-only scan. This may be impossible, if the size of the JSON is too big. Otherwise, it may still improve the speed of the query, even though such a large index will we much less efficient.
Essentially, you are suffering from a bad data model. Given that the structure of the JSONs is the same for all rows, that should certainly have been designed using normal table columns and avoiding jsonb. Then the problem would not exist.


Fetching index avg fragmentation for each database i am  beginner and writing a query  to fetch avg fragmentation in percent to rebuild indexex for db i used cursor but  i m able to bring for one database so how to include for all database using outer cursor excluding master database ... please help me to put outer cursor to bring for all database exlcuding  master

my query is  like this

 DECLARE @TableName VARCHAR(255)
DECLARE @schema VARCHAR(50)
DECLARE @indexName VARCHAR(255)
Declare @avgfrag int
DECLARE @sql NVARCHAR(500)

DECLARE TableCursor CURSOR FOR

SELECT schema_name(t.schema_id)                           AS [Schema],
       object_name(ps.object_id)                          AS [Table],
       i.name                                             AS [Index],
       --ps.Index_type_desc                                 AS IndexType,
       convert(TINYINT,ps.avg_fragmentation_in_percent)   AS [AvgFrag%]
       --convert(TINYINT,ps.avg_page_space_used_in_percent) AS [AvgSpaceUsed%],
       --ps.record_count                                    AS RecordCnt,
       --ps.fragment_count                                  AS FragmentCnt
FROM     sys.dm_db_index_physical_stats(db_id(db_name()),NULL,NULL,NULL,'DETAILED') ps -- Faster option: SAMPLED
         INNER JOIN sys.indexes i
           ON ps.object_id = i.object_id
              AND ps.index_id = i.index_id
         INNER JOIN sys.tables t
           ON ps.object_id = t.object_id

WHERE  ps.avg_fragmentation_in_percent >=1

ORDER BY ps.avg_fragmentation_in_percent desc

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @schema,@TableName,@indexname,@avgfrag
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX  '+ @indexname + ' ON ' + @Schema  +'.'+ @tablename + ' REBUILD ' --+' with (online=on)'
print @SQL +'   '+ convert(varchar,@avgfrag)

EXEC (@sql)

FETCH NEXT FROM TableCursor INTO @schema,@TableName,@indexname,@avgfrag
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

This script will loop through all databases and execute the selective reindex script.

SET QUOTED_IDENTIFIER ON
GO

SET NOCOUNT ON;

declare @dbname nvarchar(100)
declare @reindexcommand varchar(max)

DECLARE dbreindex CURSOR
FOR SELECT quotename([name]) from sys.databases
WHERE database_id <>2
AND is_read_only = 0

open dbreindex
while(1=1)
    begin
        FETCH NEXT FROM dbreindex
        INTO @dbname

        IF @@FETCH_STATUS < 0
        BREAK
        SELECT 'Rebuilding/Reorganizing indexes on '+@dbname+' Starting a t'+convert(varchar(50),getdate())+''
        SELECT ''

        set @reindexcommand = '
USE '+ @dbname + ';
SET NOCOUNT ON
DECLARE @reorg_frag_thresh   float   SET @reorg_frag_thresh   = 10.0
DECLARE @rebuild_frag_thresh float   SET @rebuild_frag_thresh = 30.0
DECLARE @fill_factor         tinyint SET @fill_factor         = 80
DECLARE @report_only         bit     SET @report_only         = 0
DECLARE @objectid       int
DECLARE @indexid        int
DECLARE @dbid           int
DECLARE @partitioncount bigint
DECLARE @databasename   nvarchar(130)
DECLARE @schemaname     nvarchar(130)
DECLARE @objectname     nvarchar(130)
DECLARE @indexname      nvarchar(130)
DECLARE @partitionnum   bigint
DECLARE @partitions     bigint
DECLARE @frag           float
DECLARE @command        nvarchar(4000)
DECLARE @intentions     nvarchar(4000)
DECLARE @table_var
TABLE(objectid     int,
indexid      int,
partitionnum int,
frag         float)
SET @databasename = '''+@dbname+'''
SET @dbid = DB_ID()
INSERT INTO    @table_var
SELECT    [object_id] AS objectid,
[index_id] AS indexid,
[partition_number] AS partitionnum,
[avg_fragmentation_in_percent] AS frag
FROM    sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, ''LIMITED'')
WHERE    [avg_fragmentation_in_percent] > @reorg_frag_thresh
AND    index_id > 0

DECLARE partitions CURSOR
FOR    SELECT * FROM @table_var
OPEN partitions
WHILE (1=1)
BEGIN
        FETCH NEXT FROM partitions
        INTO @objectid, @indexid, @partitionnum, @frag
        IF @@FETCH_STATUS < 0
        BREAK
        SELECT      @objectname = QUOTENAME(o.[name]),
                    @schemaname = QUOTENAME(s.[name])
        FROM        sys.objects AS o WITH (NOLOCK)
        JOIN sys.schemas as s WITH (NOLOCK)
        ON s.[schema_id] = o.[schema_id]
        WHERE       o.[object_id] = @objectid
        SELECT      @indexname = QUOTENAME([name])
        FROM        sys.indexes WITH (NOLOCK)
        WHERE       [object_id] = @objectid
        AND         [index_id] = @indexid
        SELECT      @partitioncount = count (*)
        FROM        sys.partitions WITH (NOLOCK)
        WHERE       [object_id] = @objectid
        AND         [index_id] = @indexid
        SET @intentions = @databasename + N''.'' + @schemaname + N''.'' + @objectname + N''.'' + @indexname + N'':''
        SET @intentions = ''====='' + @intentions
        SET @intentions = @intentions + N'' FRAGMENTATION: '' + CAST(@frag AS nvarchar) + N''%''
        IF @frag < @rebuild_frag_thresh
            BEGIN
                SET @intentions = @intentions + N'' OPERATION: REORGANIZE''
                SET @command = N''ALTER INDEX '' + @indexname + N'' ON ''+ @databasename + N''.''+@schemaname + N''.'' + @objectname + N'' REORGANIZE''
            END
        IF @frag >= @rebuild_frag_thresh
            BEGIN
                SET @intentions = @intentions + N'' OPERATION: REBUILD''
                SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @databasename + N''.''+ @schemaname + N''.'' + @objectname + N'' REBUILD''
            END
        IF @partitioncount > 1
            BEGIN
                SET @intentions = @intentions + N'' PARTITION: '' + CAST(@partitionnum AS nvarchar(10))
                SET @command = @command + N'' PARTITION='' + CAST(@partitionnum AS nvarchar(10))
            END
        IF @frag >= @rebuild_frag_thresh AND @fill_factor > 0 AND @fill_factor < 100
            BEGIN
                SET @intentions = @intentions + N'' FILL FACTOR: '' + CAST(@fill_factor AS nvarchar)
                SET @command = @command + N'' WITH (FILLFACTOR = '' + CAST(@fill_factor AS nvarchar) + '')''
            END
        IF @report_only = 0
            BEGIN
                SET @intentions = @intentions + N'' EXECUTING: '' + @command
                PRINT @intentions
                EXEC (@command)
            END
        ELSE
            BEGIN
                PRINT @intentions
            END
        END
CLOSE partitions
DEALLOCATE partitions
'
    --print @reindexcommand
    EXEC (@reindexcommand)
    SELECT'***************************************************************'

    end

close dbreindex
deallocate dbreindex
go


In Oracle multi-tenant architecture, is a PDB a container? Below is an excerpt from Oracle document.

I just want to confirm, is it correct to consider a user-created PDB or a seed PDB as a container? It's a little weird to me. If this is true. Then essentially everything is a container in an Oracle multi-tenant architecture. CDB root, system container, application container, PDBs, seed PDBs are all containers?

Well yes, a PDB is a container for the database objects of this PDB. However container in this context only means that it contains something (schema objects, not PDBs). This is most likely why the official names/descriptions in your listing are not named containers, even if they are. It is not (like the System or Application Container) a „housing“ for a PDB. (And to make things more confusing I would think of the Application Container more like a collection, not a container)


Return row in given date I have a MySQL table named tasks that contains four columns:

id (Integer)
name (Varchar)
task_start (DateTime)
task_finish (DateTime)

How can I write an SQL statement that returns tasks that are running at a specific point in time (e.g. task that are running right now)?

You can do it this way

-- Tasks that were running at specific (e.g. 2014-02-01 12:15:00) point in time
SELECT id, name, task_start, task_finish
  FROM tasks
 WHERE '2014-02-01 12:15:00' BETWEEN task_start AND task_finish;

-- Tasks that are running right now
SELECT id, name, task_start, task_finish
  FROM tasks
 WHERE NOW() BETWEEN task_start AND task_finish;

Here is SQLFiddle demo


SQL Server 2014 SP2, backup and restore with powershell I have a task where i have to take the backup with encryption and restore it to destination using the powershell.

I used the invoke-sql command and use the tsql in it but this command is not reliable for us rather we want to use the SQL powershell i.e backup-sqldatabase & restore sqldatabase.

my questions are what are the process to perform it (please i dont want to use invoke-sqlcmd)

create master using powershell ( i use "create master key encryption
by password = 'Abcd1234!'" in invoke-sqlcommand)
Create certificate using powershell
backup-certificate
create backup using encryption

You can use dbatools which is powershell based.
For your case you need to have a 2 step approach.

use the cmdlets in Backup and Restore – Certificates

Backup-DbaDbCertificate
Backup-DbaDbMasterKey
Restore-DbaDbCertificate

then use Copy-DbaDatabase with -BackupRestore switch. Alternately, you can use Backup-DbaDatabase and then Restore-DbaDatabase as per your needs.


Microsoft Access - performance difference between index and constraint? I've looked around a bit but haven't come across anything talking specifically about MS Access. So: what, if any, is the performance difference between a field with a constraint and a field with an index?

For example, I can define a field F to be non-null in three different ways as far as I can tell:

-- (1)
create table TestTable (
  ID autoincrement primary key
, F int not null
);

or:

-- (2)
create table TestTable (
  ID autoincrement primary key
, F int
, constraint cnnF not null (F)
);

or:

-- (3)
create table TestTable (
  ID autoincrement primary key
, F int
);
create index idxF on TestTable (F) with disallow null;

All three end up disallowing null values in F. But what specifically is happening when I use the first two methods as opposed to the third?

(1) Does Access internally create a constraint here? Or an index?

(2) Does Access internally create an index when I specify the constraint? Does (2) have the same performance as (3) if I'm doing lots of querying e.g. with WHERE clauses on F?

The ultimate reason for this question is laziness: I'm wondering if I can get away with specifying indexes for my fields by just using the CONSTRAINT clause in the CREATE TABLE statement, instead of having to chain together several SQL statements in a VBA sub/function.

Constraints and indexes are two entirely different things.  A constraint defines what can go in the column and an index creates an internal structure to help performance.

-- (1)
create table TestTable (
  ID autoincrement primary key
, F int not null
);

This is part of the table/column definition.  Probably the easiest and most common way to restrict a column from null values.

or:

-- (2)
create table TestTable (
  ID autoincrement primary key
, F int
, constraint cnnF not null (F)
);

This is a constraint.  This will also work but it's really meant for things like making sure a char(1) column only has a Y or an N in it.

or:

-- (3)
create table TestTable (
  ID autoincrement primary key
, F int
);
create index idxF on TestTable (F) with disallow null;

This is an index.  As a side effect it is disallowing null but really it's purpose is is to speed up your queries.

I hate to say it but lazy isn't going to work.  I would put null/not null definitions into the column definition.  Then use constraints on columns where you want to restrict what values go into the column, not if it's null or not.  Then last but not least add indexes where needed for query performance.


How to make sqlcmd return an ERRORLEVEL other than 0 when the .sql script fails? I'm running sqlcmd from a batch file and I was wondering how to make it return an ERRORLEVEL other than 0 when something goes wrong with the backup.

You should use the option -b in sqlcmd.

-b
Specifies that sqlcmd exits and returns a DOS ERRORLEVEL value when an error occurs. The value that is returned to the DOS ERRORLEVEL variable is 1 when the SQL Server error message has a severity level greater than 10; otherwise, the value returned is 0

http://msdn.microsoft.com/en-us/library/ms162773.aspx


How to grant or allow a particular SQL Server Login/User to always view a Column Level Encrypted data? I used Column Level Encryption to encrypt one of the columns in my SQL Server database. Now everytime I want to SELECT to see that encrypted data, I have to open the symmetric key to decrypt the column and then close it.

I want to a particular user or login to always view the encrypted data. Is it possible in Column Level Encryption, if yes, how?

There is no out of the box way to automatically open a key for a particular user. You could create a logon trigger that opens the symmetric key. Something like this:

CREATE TRIGGER open_sec_key
ON ALL SERVER
FOR LOGON
AS
BEGIN
  IF ORIGINAL_LOGIN() = '<user>'
   OPEN SYMMETRIC KEY [key_name] DECRYPTION BY CERTIFICATE [cert_name];
END;

Logon Triggers.


Oracle multi-tenant vs SQL Server I am from SQL Server background and recently getting my hands dirty with Oracle 12c. I came across few features of Oracle called multi-tenant, Container Database and Pluggbale database. Can somebody tell me what are the direct equivalents of these features in SQL Server 2012?

Can somebody tell me what are the direct equivalents of these features in SQL Server 2012?

An Oracle Pluggable Database is similar to a SQL Server User Database.

Before Oracle 12c an Oracle Instance could only have one Database.  With Oracle 12c Oracle added the ability for a single Instance to manage multiple Databases, like SQL Server has always done.

See Introduction to the Multitenant Architecture from Oracle Docs.

And Database Engine Instances from SQL Docs.


SQL select query order by on where in How Can I make the order by based on what I input on where?

example query

select * from student where stud_id in (
'5',
'3',
'4'
)

the result would be

id|   name  |
5 |  John   |
3 |  Erik   |
4 | Michael |

Kindly help me thanks.

There is no such direct language construct. If you want to do an "unnatural order" (based on the data type), then you can either join to some other table that maps the values and has another column to define the order for each value, or you can use CASE in your ORDER BY, such as

CREATE TABLE t(col int IDENTITY)
INSERT INTO t DEFAULT VALUES
GO 8

SELECT *
FROM t
WHERE col IN(5, 3, 4)
ORDER BY
 CASE col
  WHEN 5 THEN 1
  WHEN 3 THEN 2
  WHEN 4 THEN 3
 END

I'm using int above, but that doesn't change the concept. If you don't like repeating the "list of values" in the ORDER BY as well as the WHERE you can skip the WHERE and do a TOP for as many rows that you want. In any case, not neat, but since there isn't such language construct...


Insufficient privileges as a definer of package I cannot run a procedure within a package with a user that also defined it unless I specify "authid current_user" for some reason. Here is my original code that produces insufficient privileges error

create or replace package hr.table_pkg
is
    procedure make ( p_table_name varchar2, p_col_specs varchar2);
end  table_pkg;

create or replace package body hr.table_pkg is

  procedure  make  ( p_table_name varchar2, p_col_specs varchar2) is
    sql_stmt varchar2(32767);
    begin
      sql_stmt := 'Create table '|| p_table_name||' ('||p_col_specs||')';
      dbms_output.put_line(sql_stmt);
      execute immediate sql_stmt ;
  end make ;
end table_pkg;
/

So the user that defines the package is HR. when I query view USER_OBJECTS I can see the package and the body aswell

but when I, as the HR user try to run

execute table_pkg.make('my_contacts','id number(4), name varchar2(40)');

I'm gonna get an error that I cannot create the table. It also does not work when I change the procedure in the body to create the table in a specific schema HR:

sql_stmt := 'Create table hr.'|| p_table_name||' ('||p_col_specs||')';

the call to the procedure only works when I recompile the header of the package to

create or replace package hr.table_pkg
authid current_user
is

Can please anyone explain to me what am I missing, I do not get why the definer ( HR user) cannot run the procedure with definer rights

Thanks a lot!

When you compile a procedure that executes with the current_user's authorisation, then the current user must be able to do everything that the Procedure wants to do.

When you compile a procedure that executes with the definer's authorisation, then the owner of that procedure must be able to do everything that the Procedure wants to do.

Any and all privileges needed by such a procedure must be directly granted to the owning account; they cannot be conferred through a Role, even if that Role is granted "by default".

You probably need to grant "create table" to the account that owns the procedure.

That said, having a procedure to spin up tables at random strikes me as a slightly odd thing to want to do.


Insert/update loop ORACLE function I want to find the best solution for an insert/update oracle function. My task is to collect some date from multiple tables (source tables) and bring them to a single table (destination table). Source tables getting inserts and updates in real time. My function must run ones/day to getting all new data and updated data to destination table. I identify my new / updated data by using a column (date) from source table based on a trigger witch update the date column with systimestamp at every insert/update.
My current code looks like this
for rec in (
select firstname, lastname, email, id
      from people_source
where people_source.date > last_run_date
)

loop

merge into people_destionation dest
  using ( select firstname, lastname, email, id from dual ) src
  on (src.id=dest.id)
 when matched then updated set
 firstname=src.firstname,
 lastname=src.lastname,
 email=src.email
when not matched then
insert
(
firstname,
lastname,
email,
id
)
values
(
rec.firstname,
rec.lastname,
rec.email,
rec.id
);

exception when others ....

This solution make what I want, but I tested for 100k rows. I have source tables with 100 mil. rows and I try to find the best performance solution.
I also read about "Switching between PL/SQL and SQL engines" and I think this applies to me. Will it be a better idea to try to make functions with BULK COLLECT and FORALL?
I have not a very long experience in pl/sql, so I need more opinions and I thing here is a good place. Thanks. Best wishes.

In your code, you are issueing one SQL statement for every row you get from your people_source table. This is called row-by-row (read slow-by-slow) processing. For every query you make you force a context switch from PL/SQL to SQL engine and back which will add up pretty quickly.
As a first step, you should try to implement the functionality using just SQL (in this way, you eliminate expensive SQL/PLSQL context switch altogether). Try to use the merge DML as it was meant to be used:
merge into people_destionation dest
using (
  select firstname, lastname, email, id
    from people_source
   where people_source.date > last_run_date
) src on (src.id = dest.id)
when matched then
  updated set firstname = src.firstname,
              lastname = src.lastname,
              email = src.email
when not matched then
  insert (firstname, lastname, email, id)
  values (src.firstname, src.lastname, src.email, src.id)

In case this is slow, then we need more information (for example how many rows you expect to be merged, how are source/target tables created, are there any triggers, indexes) to be able to propose a way to make it faster (adding index on Id column, parallel execution, partitioning the table, eliminating triggers, disabling/recreating indexes, ...).


psql variable holding text with multiple lines This works as expected,

\set x '''2\n3'''

but this doesn't

\set x '''2
3'''

Is there a workaround for later example so I don't have to use \n instead of newline?

To answer my own question, the \gset command should be used for multiline variable content,

select '''2
3''' x \gset


SQL Query with ODBC, Joins and Group Bys I am having issues with the statement below.  The main purpose of the query is to pull a list of customers into an Access db from ODBC (our customers are stored in DB2 on IBM i).  The query is intended to look for a customer (OM01U1.OM01015) that has not had a delivery (RS2101F.DELDATE) in over 13 months but is not already suppressed (OM01U1.OM01068).  I do need all of the columns in order to research the equipment and financial records so I can't remove them.  The statement is pulling from several tables though.  I believe that the issue may be with the "Group By".  I am also getting duplicate outlets (OM01U1.OM01015) when I do receive results.  I only need to see the outlet one time with it's last delivery date (RS2101F.DELDATE). Please don't beat me up too bad, I did not create this query I just sort of inherited it.  I know it is way off and there is probably a better way for me to get the information I need.  I appreciate any help guys!

SELECT
    OM01U1.OM01041 as Loc,
    OM01U1.OM01015 AS Outlet,
    OM01U1.OM01945 AS OLTyp,
    OM01U1.OM01052 AS Outlet_Name,
    OM01U1.OM01054A AS Street_Number,
    OM01U1.OM01054C AS Street_Name,
    OM01U1.OM01055A AS City,
    OM01U1.OM01055B AS State,
    OM01U1.OM01106 AS Zip,
    OM01U1.OM01058A AS Area_Code,
    OM01U1.OM01058C AS Phone,
    OM01U1.OM01037 AS Channel,
    OM01U1.OM01926 AS USA_Type,
    OM01U1.OM01078 AS Key_Acct,
    OM01U1.OM01110 AS TRDGRP,
    OM01U1.OM01034 AS Trade_Name,
    OM01U1.OM01248 AS DTC,
    OM01U1.OM01073 AS Sales_Route,
    OM01U1.OM01068 AS Sup_CDE,
    OM01U1.OM01982 AS Sup_Reason,
    OM01U1.OM01067 AS Sup_Date,
    OM01U1.OM01065 AS Creation_DT,
    OM01U1.OM01066 AS Update_DT,
    OM01U1.OM01141 AS CAN_NUM,
    CF30_CNSTYP AS CF_CONTYP,
    EC01_EQUNUM AS EC_EQUIP,
    EC01_PRMSLR AS EC_SUPPLIER,
    EC01_EQUOWN AS EC_OWN_CD,
    EC01_EQUACY AS EC_ACCESSORY,
    EC01_EQUSPP AS EC_COMPONENT,
    EC01_SYSSTA AS EC_STATUS,
    EC01_ISTDAT AS EC_INSTALL_DT,
    EC01_RSPPRN AS EC_PERSON,
    EC01_LASMTCDAT AS EC_LAST_MNT_DT,
    OC02_RNTCTCNUM AS OC_CONT_NUM,
    OC02_RNTCTCSTA AS OC_CONT_STATUS,
    OC02_BTONUM AS OC_BILL_TO,
    RS2101F.DELDATE AS RS_LAST_DEL_DT
  FROM
    CF30, EC01, OC02, OM01U1, RS2101F
  WHERE
    OM01U1.OM01015 = RS2101F.OUTNUM

  GROUP BY
    RS2101F.DELDATE,
    OC02_BTONUM,
    OC02_RNTCTCSTA,
    OC02_RNTCTCNUM,
    OC02_OUTNUM,
    EC01_LASMTCDAT,
    EC01_RSPPRN,
    EC01_ISTDAT,
    EC01_EQUOWN,
    EC01_EQUACY,
    EC01_EQUSPP,
    EC01_SYSSTA,
    EC01_PRMSLR,
    EC01_EQUNUM,
    CF30_CNSTYP,
    OM01U1.OM01141,
    OM01U1.OM01066,
    OM01U1.OM01065,
    OM01U1.OM01067,
    OM01U1.OM01982,
    OM01U1.OM01068,
    OM01U1.OM01073,
    OM01U1.OM01248,
    OM01U1.OM01034,
    OM01U1.OM01110,
    OM01U1.OM01078,
    OM01U1.OM01926,
    OM01U1.OM01037,
    OM01U1.OM01058C,
    OM01U1.OM01058A,
    OM01U1.OM01106,
    OM01U1.OM01055B,
    OM01U1.OM01055A,
    OM01U1.OM01054C,
    OM01U1.OM01054A,
    OM01U1.OM01052,
    OM01U1.OM01945,
    OM01U1.OM01015,
    OM01U1.OM01041
  HAVING  RS2101F.DELDATE < 1110809
      AND OM01U1.OM01068<>'S'

You have a working query, but you are selecting:

FROM CF30, EC01, OC02, OM01U1, RS2101F

With no explicit joins and only one implicit join:

WHERE OM01U1.OM01015 = RS2101F.OUTNUM

This is going to lead to problems. Can you find which fields (columns) match to which in each table? You could then say:

FROM OM01U1
INNER JOIN RS2101F
ON OM01U1.OM01015 = RS2101F.OUTNUM

For example.

You may also find it useful to add a derived table:

SELECT OUTNUM, Max(RS2101F.DELDATE) FROM RS2101F

So that would then read:

FROM OM01U1
INNER JOIN (
     SELECT OUTNUM, Max(RS2101F.DELDATE)
     FROM RS2101F
     GROUP BY OUTNUM) As t
ON OM01U1.OM01015 = t.OUTNUM


SQL Server order of execution and query plan I am aware that the order of execution of a query in SQL Server is (excluding TOP, etc):

FROM, JOIN
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

However, I cannot understand how in the query plan the sort (which corresponds to the order by) is done before the select (given that they are read from right to left) if the order of execution is per above => SELECT before ORDER BY.

An simple example is as follows:

I excluded a JOIN on purpose as to avoid the case of having that sort corresponding to the JOIN.

The order of execution you have quoted is a version of the Logical Processing Order of the SELECT statement - as the documentation states. The actual physical execution of the statement is determined by the query processor and the order may vary from this list. Also the SELECT node in the plan isn't an execution plan operator at all. This root node just labels the type of statement.

I.e., the DBMS is free to do it any way it wants, as long at it returns the same result as if it had used the logical execution order.


Does MongoDB auto compacting I copy a collection to another collection. Data is 14GB. I deleted the copied collection. Yet the size of the directory is still 14 GB.

I chose repair but it seems to take forever. Actually inserting the whole data took only 20 minutes and the repair is still running till now.

It's just compacting data.

What happen if I do not repair the database? Will files eventually be compacted?

When you delete data like this, the files are not automatically rewritten to reflect the new size of the data (which is potentially a massive operation).  Instead, the free space in those files is marked as free and will be re-used when you insert new data.

As you have found, the only way to reclaim that space on disk at the moment is a repair - that is not "just compacting data".  In fact a repair rewrites your data files from scratch, essentially creating a brand new copy of them on another part of the disk before removing the old ones.  Depending on how large the current data set is, how much data is left, and how fast your disk is, this can take a long time.

If you removed all data in a database, a faster and more efficient method to remove the data is to simply drop the database, which would not have this issue.


Designing an auto-related table I'm a newbie in database design but I need to design one.
I need to have a record of the devices connected to a network in which certain device could be connected to many devices or any.

In the device's table I've got the following columns:

Device ID(char 25),
IP address (char 25),
SN (PRIMARY KEY char 20)

All that information is taken from two CISCO commands (CDP show neighbors detail and show inventory) the info is placed in two text files.

The first file has the information of the device I'm connecting to via telnet and looks like this:

SN: FDO1129Z9ZJ
Device ID: Barragan_3750
IP: 148.227.2.158

The second file contains information about devices connected to this device, something like this:

Device ID: BIOTERIO
IP address: 148.228.83.189
Interface: GigabitEthernet1/0/6
Port ID (outgoing port): GigabitEthernet0/1
SN: P7K08UQ

Device ID: LAB_PESADO
IP address: 148.228.130.158
Interface: GigabitEthernet1/0/11
Port ID (outgoing port): GigabitEthernet0/1
SN: FNS174202FT

Device ID: Arquitectura_Salones
IP address: 148.228.1420.11
Interface: GigabitEthernet1/0/9
Port ID (outgoing port): GigabitEthernet0/49
SN: FNS14420420

I'm not sure how to represent the relationship because Interface (char 25) and Port ID (char 25) should be relationship's attributes.

Could you help me?

If I were you I would break this into a minimum of three tables:

Devices
Ports
Interfaces

You have already got most of the data you will need, I do suggest also considering the MAC address of different ports where available.

If I were building these out, I would use the following fields:

    CREATE TABLE device {
    serial_num VARCHAR(25) PRIMARY KEY,
    device_id VARCHAR(255) UNIQUE KEY,
    ip_add BINARY(16)
};

CREATE TABLE ports {
    device_serial VARCHAR(25) NOT NULL,
    port_id INT NOT NULL,
    port_type varchar(40),
    port_num varchar(15),
    mac_add BINARY(48),
    PRIMARY KEY (device_serial, port_id),
    UNIQUE KEY (mac_add)
};

CREATE TABLE interfaces {
    interface_id INT NOT NULL PRIMARY KEY,
    interface_name VARCHAR(255),
    port_a INT,
    port_b INT,
    ip_add BINARY(16),
    UNIQUE KEY (port_a, port_b)
};

This would allow you to specify most of the fields with a degree of specificity.

Each "device" could either be a router or a switch, or even an end user device, as far as the network is concerned it's simply something which can be connected. It might benefit you to add an ENUM type to let you easily assign a status to each device as well, however for now you can keep it simple.

Since most ports will have a MAC address, and MAC addresses are globally unique, it would make sense to give each MAC address a unique key, allowing it to be nullable allows you to use serial ports and the like as well which don't have MAC addresses afaik. The point of the composite Primary key in the ports table is to tie each port to a device, that way ports are not allowed to float around on theIr own confusing the net admins.

The Interfaces table is how things tie together, an interface is simplY a connection between two ports. I gave the interface the IP address, as monitoring of a device over TCP/IP tends to start from seeing if the IP address is responding and it makes the most sense, it also helps to make sure only one pair of ports are using an IP address at a time.

A few notes on this design, you will need to use some scripting language to make full use of this, I determined to use a binary datatype for IP and MAC addresses based on a useful answer on our sister site. Likewise, converting a MAC Address back and forth can be done with this PHP function.

There are certainly improvements which could be made to this design, however I think it serves as a good boilerplate for a simple network management DB.


How does Oracle Application Express connect to the database? I'm mocking up some options for a simple, internal reporting front-end for an Oracle database.
I have Oracle XE 11R2 on a separate testing machine, and am going through a tutorial on making a simple report in Application Express. To my surprise, Application Express has magically connected to the XE database. I'm guessing this was so easy because the database and Application Express are on the same machine.
However, I'm wondering how this would work in a production/network environment. My former solution was an MS Access front-end, which used an ODBC connection to connect to the database. But I don't imagine Application Express would use an ODBC connection.
How does Oracle Application Express connect to the database in a production/network environment? Would an equivalent to an ODBC connection need to be deployed on each machine that uses the Application Express report?

Note: I'm well aware that I have no idea what I'm doing. I'm just trying to get a grasp on how this all works, so I can make an informed pitch to my I.T. department. Corrections are welcome.

Application Express, together with either ORDS (Oracle REST Data Services) or a similar web service such as Glassfish, is basically a set of processes that run in the database, usually connecting via internal connections. Application Express basically serves HTML web pages to allow access the database and display reports, and so end users only need a web browser to use an Application Express application, with reports, ability to modify data, etc.  This is a huge simplification, but for your purposes, all you need to know is that end-users just need a fairly current web browser that supports HTML5 to get all the fancy web pages for accessing/modifying the data in the database.  APEX comes with a security framework, or you can also use your own corporate security.


NULL in foreign key column(s) I have been learning about SIMPLE and FULL matching foreign keys in PostgreSQL and I am wondering if the following thought process is correct:

Once at least one of the referencing columns of the foreign key contains a NULL value, there is no link to the referenced table. This is because a NULL is not comparable with another NULL value, as per 3VL. This also means that defined cascading actions (such as ... DO DELETE, ... DO SET NULL, ..) when a referenced row which contains at least one NULL value in the relevant columns, is updated or deleted will never happen, because there is no link to the referenced row. More concrete, assume a foreign key from table A(x, y) to B(x, y). If both A and B contain a row (5, NULL) for x and y, there is no link, because the NULL for the row in A is not equal to the NULL for the row in B.

Is this correct? I am quite new to SQL, so I am wondering if I understood this correctly.

You are quite right. I have created a small example at Fiddle

create table p
( a int not null
, b int
, unique (a,b)
);

create table c
( a int not null
, b int
, constraint fk_p foreign key (a,b)
                  references p (a,b)
                      on delete cascade
                      on update cascade
);

-- insert data in parent:
insert into p (a,b) values (1,null);  -- ok
insert into p (a,b) values (1,null);  -- also ok. The constraint evaluates to null,
                                      -- which is ok. The rule is that it must not
                                      -- evaluate to false

-- insert data in child
insert into c (a,b) values (1,null);  -- ok
insert into c (a,b) values (2,null);  -- also ok, foreign key evaluates to null.
                                      -- So despite there is no 2 in
                                      -- parent this is ok

-- remove data from parent
delete from p;

-- all rows present in child
select * from c;

a   b
1   null
2   null

Null makes even trivial things a bit tricky to grasp. I would not recommend candidate keys that include nulls.


Is there a way to display current noise words in SQL 2000 Full Text Indexing? I know this is ancient technology, but sometimes upgrading is not always an option.

Does anyone now how to list the actual noise words that are currently in use on a SQL 2000 Full Text Index?

We have a recurring problem on a very old SQL 2000 system.  It is a 3 way Windows 2003 cluster.  Once in a while the cluster fails over to a new node, and when that node comes online it seems to be using a different noise word list than what we want.

It seems that various service packs for SQL 2000 leave noise word files, and when the failover happens the first noiseword file found (in any directory) is the one that gets loaded.

You are experiencing a known issue (where the default Windows noise.enu file is used instead of SQL Server's version). According to KB #889708, you need to copy the following registry key from the good node to the bad nodes:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ContentIndexCommon\LanguageResources\Override

Then you may still need to re-populate your FT indexes, not sure, and no 2000 instance handy to test, sorry. Also I don't believe there is any way to query the list of noise words (like you can on more modern versions).


How do I force SQL Server to use my spatial index through a view? I have tables which holds transactions for properties that are stored as lat long pairs. (There are more columns and datapoints then in my example schema).

A common request is to find the transactions that happened within X miles of a particular point, and only retrieve the 5 most recent transactions that happened for each nearby property.

To make this work, I decided to add a view that encapsulates this logic of most recent:

create or alter view dbo.v_example
with schemabinding as
select example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
      ,most_recent= iif(row_number() over (partition by latitude,longitude order by transaction_dt desc) < 5,1,null)
from dbo.example;

So a query might look like this::

select *
from dbo.v_example
where latlong.STDistance(geography::Point(40,-74,4326)) <=1609.344e1
and most_recent = 1

Unfortunately, SQL Server  does not want to use the spatial index when I query through the view. If I remove schemabinding and try to add a hint on the view, I get that the query processor cannot create a plan.

How can I encapsulate the logic and still get it to use my spatial index?

Here's a db<>fiddle with example data and plan shapes.

The table is much larger, and it is much slower to scan it then to do a clustered index seek and then find the near by point.

Window functions & Views

Recently I answered a different question regarding views & window functions but not all answers given there apply here.

Two differences are that this example uses geography datatypes and filters on the window function. The positive part here is that you are not bound to the window function and could use something like CROSS APPLY to get the closest results for one latitude & longitude combination.

The previous question + Answer

In short, the window function is calculated before the filtering is applied.

E.G.

where latlong.STDistance(geography::Point(40,-74,4326)) <=1609.344e1
and most_recent = 1

Extra information can be found in this blogpost  from 2013 by Paul White on window functions and views.

Testing

As a first test, the result is clear when omitting the ROW_NUMBER() from the view

create or alter view dbo.v_example2
with schemabinding as
select example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
from dbo.example;

set statistics xml on;
select *
from dbo.v_example2
where latlong.STDistance(geography::Point(40,-74,4326)) <=1609.344e1

Resulting in the expected performant query plan.

DB<>Fiddle

But you obviously still want the additional filtering

Keeping the view + window function

You could choose to add the window function afterwards like so:

create or alter view dbo.v_example2
with schemabinding as
select example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
from dbo.example;

&

set statistics xml on;
;WITH CTE AS
(
select most_recent= iif(row_number() over (partition by latitude,longitude order by transaction_dt desc) < 5,1,null)
,*
from dbo.v_example2
where latlong.STDistance(geography::Point(40,-74,4326)) <=1609.344e1
)
SELECT
*
FROM CTE
WHERE most_recent is not null;

Again resulting in the expected execution plan.

DB<>Fiddle

Using an inline table valued function  + window function

CREATE FUNCTION dbo.F_Example
(
    @P1 INT

)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
    (
SELECT example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
      ,most_recent FROM
(
select example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
      ,most_recent= iif(row_number() over (partition by latitude,longitude order by transaction_dt desc) < 5,1,null)
from dbo.example
WHERE latlong.STDistance(geography::Point(40,-74,4326)) <= 1609.344e1
) AS A
WHERE most_recent= @P1
);

EXEC SP_EXECUTESQL
N'SELECT * FROM  dbo.F_Example(@P1)',N'@P1 INT',@P1 = 1

Resulting in the query plan you expected.

DB<>Fiddle

Using a CTE + window function

;WITH CTE AS
(
select example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
      ,most_recent= row_number() over (partition by latitude,longitude order by transaction_dt desc)
FROM dbo.example
WHERE latlong.STDistance(geography::Point(40,-74,4326)) <=1609.344e1
)

SELECT example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
      ,most_recent
FROM CTE2
WHERE most_recent < 5;

Using CROSS APPLY and TOP(4) without the window function

SELECT example_id
      ,a.transaction_dt
      ,latitude
      ,longitude
      ,latlong
from dbo.example e1
CROSS APPLY(
SELECT TOP(4) transaction_dt
FROM dbo.example e2
WHERE e1.latitude = e2.latitude  and e1.longitude = e2.longitude
GROUP BY latitude,longitude,transaction_dt
ORDER BY transaction_dt desc
) as a
WHERE latlong.STDistance(geography::Point(40,-74,4326)) <=1609.344e1
AND e1.transaction_dt = a.transaction_dt
ORDER BY transaction_dt desc;

I used top(4) since the resultset was based on < 5 not <= 5

When using the CROSS APPLY method there is one more index to be added, to remove the index spool & sort:

CREATE INDEX IX_latitude_longitude_transaction_dt
ON dbo.example(latitude,longitude,transaction_dt);

It goes without saying that you could add the CROSS APPLY solution to a view and query it the same way as you used to, an example of this here.
All above examples in one DB<>Fiddle here.

End solution comment by MichaelB

  Thanks Randi, I was able to rewrite my logic into a select subquery
  using the idea of your cross apply. As an added bonus, if I don't
  mention the field in the columns I select, I do not get a performance
  hit.

DB<>Fiddle

create or alter view dbo.v_example2
with schemabinding as
select example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
      ,most_recent= (
          select a.transaction_dt
          intersect
          select top 5 b.transaction_dt
          from dbo.example b
          where a.latitude=b.latitude
                and a.longitude=b.longitude
          order by transaction_dt desc
      )
from dbo.example a;

&

select example_id
      ,transaction_dt
      ,latitude
      ,longitude
      ,latlong
from dbo.v_example2
where latlong.STDistance(geography::Point(40,-74,4326)) <=1609.344e1
      and most_recent is not null


mysqldump - does it have non-repeatable DMLs? We are migrating databases using mysqldump, and since this is the first time I'm doing it, I have a couple of questions.

If in the middle of mysqldump the utility crashes(for some reason- latest was global max_allowed_packet error that I had to increase), can I just rerun the dumpfile completely or try to find how much it had progressed and start from there? Is the dumpfile from mysql

2nd question - I use
    mysql -u <> -p<> < dump.sql
method to restore. Any chance we can see how much it has progressed? Is there any utility that would allow me to see the progress since I'm restoring 2 TB databases ?

2TB restore from mysqldump?? How long do you have?

mysqldump  produces logical backup and it'd take longer to restore. Not scaring but I'd not dare to dump a 2TB database (unless you must)! I'd rather consider taking hotbackup.

About your other questions:

mysqldump will generate simple D[MD]L queries... create table, insert, create triggers/routines etc..
If mysqldump stops in between your dump will be incomplete, just like an incomplete SQL file!
You  can always check mysqldump finished correctly by checking last line of the output file, should say something like "Dump complelted..."
You may also check "$?" on shell  to confirm if the mysqldump command completed successfully.
About your progress, mysql internally doesn't report progress but from linux shell you can use (pipe viewer) pv command to know the progress.

Also:

Do refer mysqldump reference from documentation.
There existst mysqldumpsplitter to split from large dumpfiles if required.
If you consider to setup physical backup, setup instructions for xtrabackup+holland framework are there too.


check key if exists in other table without fk constraint Lets say I have:

table a (id_a int);
table b (id_b int, id_a int);

I want to check if b.id_a exists in "a" before a new row in "b" will be inserted. Normally I would just add a fk constraint on b.id_a. The problem is that I'm not allowed to add a pk or uq constraint to "a.id_a" to reference the fk on b.id_a.

Would be nice if somebody has a solution for me.

Edit:
Why not fk constraint - My model consists of an ESRI Arc SDE with spatial tables and simple postgres tables. Some pg tables reference to an sde uq identifier column. By default the SDE adds no pk to a spatial table in postgres (in sql server it does dont ask me why). However if a pk would be added by default by the SDE it wont help me at all, because i would have to change it to an other uq column, because the default pk column keys could change in the future (=WTF). ATM i just added or changed the pk of the SDE table to my uq identifier column, but as i had errors at first when restoring the db (now it works for the moment), ESRI support told me that my change or add of the pk column can cause these errors or maybe cause some in the future (because SDE wont know this pk). In my opinion it the pk should not matter (because SDE wont know and also wont use it) that's why i added it atm and it seems to work. But for the future, i want to be on the safe side and so i have to find an other possibility.

What i want: It would be nice to have the same behaviour like with a fk constraint - but i guess a similar behaviour wont be possible because that's what fks made for.

I have tried to write a before insert trigger but i think its the wrong approach as it seems that i always have to return the new.row and i don't want something to be inserted when the check for the value to be inserted don't exists.

I also could simply use no relationship or check on the fk but this in my opinion would be a high risk for having wrong values in the tbl.

Thanks for helping me...

Possible Solution:

I have tried to add a fk constraint which references on a column with uq index and it works (thx to ypercube). I have taken a look at the doc, but i cant find something about adding a fk constraint referencing on a column with just a uq index (no pk or uq constraint). At least i found a possible explanation in a tutorial which says

  A unique constraint is actually implemented as a unique index in PostgreSQL, just as it is in many databases.

but also

  This index wouldn’t be a good candidate for a foreign key, but it does illustrate how you can create a unique index that you can reference with a foreign key.

I just don't really get it why it shouldn't be a "good candidate" for a fk constraint, when either using a uq constraint or uq index on that column grants you unique values.

from docs pg uq constraint:

  Unique constraints ensure that the data contained in a column or a group of columns is unique with respect to all the rows in the table.

from docs pg uq index:

  Causes the system to check for duplicate values in the table when the index is created (if data already exist) and each time data is added. Attempts to insert or update data which would result in duplicate entries will generate an error.

I do agree with Aaron, that not creating a PK (or unique constraint) is a very strange requirement

But you could do something like this:

insert into b (id_b, id_a)
select 1, 2
where exists (select 1 from a where id_a = 2);

or alternatively:

insert into b (id_b, id_a)
select 1, id_a
from a where id_a = 2

If you want to insert multiple rows, you can use a values clause:

insert into b (id_b, id_a)
select *
from (
   values
      (1,2), (2,3), (3,4)
) as t(id_b, id_a)
where exists (select 1
              from a
              where a.id_a = t.id_a);

This will only make sure that this specific insert will insert correct data. It will not prevent others that don't follow this pattern to insert invalid data. And it is not safe in a multi-user environment where different transactions run the inserts concurrently.


What are the trade offs for incrementing ids vs. fulltext keys for foreign key designs? In a lot of relational database designs there are fields that are referenced in other tables.

For example consider a user table with a unique username and a second table storing address data.

One possible layout, that I would say is the common approach, because I have observed in most software, is to use auto increment ids like this:

Table users
===========
userId int primary auto_increment
userName varchar unique

Table adressdata
==========
userId int references users.userId
adress_type varchar // for example country
address_value varchar // for example US
(you probably also want to put a unique key on (userId,adress_type))

This is how I used to do it and how I have seen it in most cases.

Another way would be:

Table users
===========
userName varchar primary

Table adressdata
==========
userName varchar references users.userName
adress_type varchar // for example country
address_value varchar // for example US
(you probably also want to put a unique key on (userName,adress_type))

Here we store the full username also in the adressdata table.

To me this has the following advantages:

You can select the username right away from the table without the need to join it to another table. In this example this is from an application point of view probably not so relevant, but it is only an example.
It may be easier to scale the database in a master-master replication environment, because there are no auto_increment conflicts.

But also the disadvantages:

The space requirements for the index and data (but more relevant will probably be the index) on the field in the second table are higher.
A change of the username would need to propagate to all tables, which is more resource consuming than just changing it in one table and leave the ids as they are.

In my opinion it is much easier to work with text fields and don't use increment ids, and the trade offs are minimal and in most applications not relevant.

Of course some object ARE identified with an incrementing number by their nature (for example forum posts should receive an incrementing id because there probably is no other unique field like title or so).

But I before I start designing my database layouts in a completely different way I would like to know whether there are things I did not think of.

Are there any best practices?
Are there pros/cons that I did not think and the effect of which may arise at a later poit in time?
How do you personally design databases regarding the points above and why?

I'd suggest to use the id and not the username, because if you start to use the username as a join column in multiple tables, you've to remember to update all of them.

The foreign key for the users table, becomes the primary key of addressdata table and the primary key must remain stable. It's a good practice to not change primary key fields. A primary key must exist when the record is created, and has to remain unchanged for the whole lifetime of the record.

If you'd like further insights The great primary-key debate is a great article.


Mysql not writing to binlog Mysql is not writing events to binlog, I have tried change the binlog configurations, but can't solve.

Conf:

server_id = 2
log-bin = master
binlog_format = row
replicate-do-db = our_db
log-slave-updates = true

'%log%' config:
Seems that log_bin_basename does not exists in 5.5 and log-bin is used to configure the name and automatically turn on the log_bin, but I am still not receiving events in binlog, it just creates the file and write ' master.000919 |   4 | Format_desc |         2 |         107 | Server ver: 5.5.50-0+deb7u2-log, Binlog ver: 4'
    expire_logs_days = 10
    max_binlog_size = 200M
    binlog_do_db = our_db

I also verified that the events are being write to general log

Version: mysql  Ver 14.14 Distrib 5.5.50, for debian-linux-gnu (x86_64) using readline 6.2

The events that I need to be write in binlog are common inserts.

'%log%' config:

Variable_name                            Value
back_log                                 50
binlog_cache_size                        32768
binlog_direct_non_transactional_updates  OFF
binlog_format                            ROW
binlog_stmt_cache_size                   32768
expire_logs_days                         10
general_log                              OFF
general_log_file                         /opt/mysql/SRV01STM085.log
innodb_flush_log_at_trx_commit           1
innodb_locks_unsafe_for_binlog           OFF
innodb_log_buffer_size                   8388608
innodb_log_file_size                     5242880
innodb_log_files_in_group                2
innodb_log_group_home_dir                ./
innodb_mirrored_log_groups               1
log                                      OFF
log_bin                                  ON
log_bin_trust_function_creators          OFF
log_error
log_output                               FILE
log_queries_not_using_indexes            OFF
log_slave_updates                        ON
log_slow_queries                         OFF
log_warnings                             1
max_binlog_cache_size                    18446744073709547520
max_binlog_size                          209715200
max_binlog_stmt_cache_size               18446744073709547520
max_relay_log_size                       0
relay_log
relay_log_index
relay_log_info_file                      relay-log.info
relay_log_purge                          ON
relay_log_recovery                       OFF
relay_log_space_limit                    0
slow_query_log                           OFF
slow_query_log_file                      /opt/mysql/SRV01STM085-slow.log
sql_log_bin                              ON
sql_log_off                              OFF
sync_binlog                              0
sync_relay_log                           0
sync_relay_log_info                      0

From the 5.5 manual:

  Setting this option causes the log_bin system variable to be set to ON (or 1), and not to the base name. This is a known issue; see Bug #19614 for more information.

Newer versions have made changes in this area, but there continues to be a difference between how the VARIABLE log_bin (in my.cnf) and the commandline --log-bin (when invoking mysqld).

From https://dev.mysql.com/doc/refman/5.5/en/replication-features-triggers.html :

  With statement-based replication, triggers executed on the master also execute on the slave. With row-based replication, triggers executed on the master do not execute on the slave. Instead, the row changes on the master resulting from trigger execution are replicated and applied on the slave.


Check is there is an Empty Column Value Let's say we have a Table in MySQL Database called TAB1 that Contains two Columns col1 : col2 .
the question is :
is there a way to check is any of these Columns Contain a Null or Empty Value?
for the Condition WHERE col_Name is null OR col_Name='' , this Query checks one Column, I want to check all the Columns using a simple Query.

You can use ISNULL and NULLIF for this purpose:

SELECT * FROM `TAB1` WHERE ISNULL(col1, '') = '' OR ISNULL(col2, '') = ''

or

SELECT * FROM `TAB1` WHERE NULLIF(col1, '') IS NULL OR NULLIF(col2, '') IS NULL


Is it possible to login to an Oracle schema using a certificate? The question is based on having to deal with lots of automated processing (testing, mostly). As it stands, all the schemas used have the same well known password, which is stored in lots of configuration files, which is bad from a security perspective. Using encrypted passwords does not really cut it.
So the question is: Does Oracle DB allow the use of client certificates for authentication?

I would say that the answer is yes. I am working on encrypting my listener. But it would start with building a wallet. Since I use Linux i use cron and OS authentication for jobs that I want to  run. You can do something similar with Windows. Just make sure that remote OS authentication is turned off.


FILESTREAM is enabled, but I get a message that it's not. Anyone able to help? I have been given the task of setting SharePoint Foundation 2010 up in work.  I have hit quite a few problems (see other questions I have posted), so I decided to start from scratch again.  I have installed SharePoint and SQL Server 2008.  Now I'm at the step of provisioning the BLOB storage and have the following three queries to run:

use [ContentDbName]
if not exists (select * from sys.symmetric_keys where name = '##MS_DatabaseMasterKey##')create master key encryption by password = N'Admin Key Password !2#4'

use [ContentDbName]
if not exists (select groupname from sysfilegroups where groupname=N'RBSFilestreamProvider')alter database [ContentDbName] add filegroup RBSFilestreamProvider contains filestream

use [ContentDbName]
alter database [ContentDbName] add file (name = RBSFilestreamFile, filename = 'c:\Blobstore') to filegroup RBSFilestreamProvider

The first bit executes without a problem, but the second gives the message:

Msg 5591, Level 16, State 3, Line 2
FILESTREAM feature is disabled.

I have went into the Configuration Manager and checked that FILESTREAM was enabled, which it was.  I restarted the service and still get the same message.  Even restarting the server totally does not help.

What should I do?

Thanks!
Mark

I would suggest double checking all the requirements for enabling FILESTREAM as per:

How to: Enable FILESTREAM


Are there any disadvantages to shrinking a database? My production server's database grows unexpectedly and almost I am going to create a job for shrinking the database which runs once in a month. Is it a good idea?

The backups was failed and the time of failure huge transactions were logged which rapidly grows the DB.

I am using SQL Server 2008 R2.

The backups was failed and the time of failure huge transactions were logged which rapidly grows the DB.

If this was a one-time occurrence and your log file (LDF) is grotesquely huge, take a log backup and then shrink the log file (only) to its pre-bloat level just this once.  Otherwise, leave it the way it is.

The reason you don't want to shrink your data file is because it fragments your indexes, which can affect performance. In addition, because your indexes get fragmented when you shrink your database. the next time your re-indexing kicks off, your datafile will grow for rebuilds and your log file will grow for reorgs.

The reason you don't want to shrink your log file to a size smaller than its usual working size is because instant file initialization works for data files, but not for log files, so if the log file is going to grow to that size again it'll be a performance hit while it grows.

There's a lot of information on why shrinking is bad.  Only do it if you have a good reason.


How to find the erroneous code failing the SSDT publish? Every time something fails the publish, assuming it passes the preview generation, the error line and executed script sample are completely useless at helping me trace the source of the error, since the line number is relative to the last GO statement, and the executed script doesn't always appear in the publish script.

In this instance, it fails with this error:

The executed script doesn't exist in the publish script:

And this is where it navigates in the publish script upon double clicking the error message:

Usually I am able to trace it down based on the error information, but this time I'm at a loss because the only relevant keywords I can think of are so common that it seems hopeless.

It looks like logging is hackish and the errors sometime come before the postdeployment scripting (e.g. table triggers executing on incomplete data since publish settings only include an option to disable DDL triggers).

I've tried running SQL Server Profiler during the publish but that tool omits even more crucial debugging information (and gets overwhelmed by my large database project). Perhaps another reason to upgrade to get the Extended Events Profiler?

(I verified this statement was not the culprit)

I would use RAISERROR WITH NOWAIT in the post-deployment script at the start of each block being executed. This way you should be able to see how far the deployment got before getting to the failure point and hopefully have narrowed it down to a particular script.

How I tend to structure my SSDT projects is that I have a single post-deployment script and then include other files using :r and separate them with GO's. This then leaves me with the ideal place to insert the RAISERRORS to pick up on progress. Alternatively you should stick the RAISERROR at the top of each included file.

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql?view=sql-server-2017 key point in the link being:

  RAISERROR can be used as an alternative to PRINT to return messages to calling applications. RAISERROR supports character substitution similar to the functionality of the printf function in the C standard library, while the Transact-SQL PRINT statement does not. The PRINT statement is not affected by TRY blocks, while a RAISERROR run with a severity of 11 to 19 in a TRY block transfers control to the associated CATCH block. Specify a severity of 10 or lower to use RAISERROR to return a message from a TRY block without invoking the CATCH block.

Sorry - this is assuming the error is within your post-deployment script. After re-reading I've realised your error seems to be prior to that.


Best way to backup/restore a very large DB (~2TB) The ibdata1 file has increased to ~2TB and leaves only a few GBs free space in the same disk (this is because I forgot to turn on innodb_file_per_table, before it was too late)

From what I have read in What is the best way to reduce the size of ibdata in mysql? the only way to reduce ibdata1 is to:

  backup -> delete ibdata1 -> restore database.

Now, since the ibdata1 is so large, what's the best way to do it?
And how much time will it take?

I have two other free disks of 2TB size available which can be used for backup.

My thought is as follows:

mysqldump current database into disk-x
restore from disk-x to disk-y
delete original ibdata1 in disk-z

Is this good way and how long will it take?

You may need to think further ahead -- When reloading, will you have enough space?

Setting innodb_file_per_table does not necessarily provide any extra space.

When you do SHOW TABLE STATUS;, the "Data_free" will indicate how much free space there is in ibdata1.  How big is that?  If it is small, then something more needs to be done.

Is it possible in that OS to "extend" your current drive with one of those spare drives?  That is can you, with an OS command and without dumping, extend the current "drive" to 4TB?  If so, that is is probably worth doing to get started.

If you do backup, it will need to be to some other drive, either on the same server or on another server.  (That is, do the dump across the 'network'.)


Migrating from text and image to varchar(max) and varbinary(max) I have a SQL Server database which contains a number of image and text columns, and I'm studying potential problems that might arise from migrating them to their non-deprecated counterparts varbinary(max) and varchar(max).

Aside from changes to application code, my main concern are potential "gotchas" associated with this. For example, is there functionality supported by the older data types but not in the new ones?

Data loss due to truncation at least doesn't seem to be a problem since the new types are at least as large as the old ones.

Just a note: these new data types support the same sizes as the deprecated types they replace, e.g. 2GB of data (which means a different number of characters depending on Unicode and other factors).

One thing for sure is you should parse all of your existing application code, stored procedures, functions etc. for instances of built-ins like UPDATETEXT, READTEXT, TEXTPTR, WRITETEXT, TEXTSIZE and @@TEXTSIZE - all of which will likely have to be changed. You can identify those stored in SQL Server this way:

SELECT s.name, o.name
  FROM sys.sql_modules AS m
  INNER JOIN sys.objects AS o
  ON m.[object_id] = o.[object_id]
  INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id]
  WHERE UPPER(m.definition) LIKE N'%UPDATETEXT%'
     OR UPPER(m.definition) LIKE N'%WRITETEXT%'
     OR UPPER(m.definition) LIKE N'%READTEXT%'
     OR UPPER(m.definition) LIKE N'%TEXTPTR%'
     OR UPPER(m.definition) LIKE N'%TEXTSIZE%';

Note that this could yield false positives (e.g. those terms may be in a comment or naturally occur in an entity name) and it could miss some (e.g. the commands could be constructed using parameters / dynamic SQL). You're on your own for searching your application codebase and/or source control for instances of the same.

Also make sure you find all modules that accept or output parameters of these types:

SELECT DISTINCT s.name, o.name
  FROM sys.parameters AS p
  INNER JOIN sys.objects AS o
  ON p.[object_id] = o.[object_id]
  INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id]
  WHERE system_type_id IN (34,35,99);

You might also want to consider that you may have logic in jobs and other maintenance routines that currently avoid these tables or treat them differently because of the limitations inherent in these data types. When you move to the newer types (and especially on the most modern versions of SQL Server), a lot of these limitations go away.

Finally, other than the syntax above, I can't think of a single feature the old types support that the new types don't.


Error message - view server state permission was denied - when connecting to SQL Server 2014 using SQL Server 2012 Management Studio When right-clicking on a table and selecting "select top 1000 rows", I get this error:

Is there any workaround, other than the obvious upgrade to SQL Server 2014?

The least intrusive way to get this working is to either use the following command or have the DBA issue this command for you:

use master
go
GRANT VIEW SERVER STATE TO <user_name>


SQL Server 2005 Cluster Service pack 4 upgrade failure at remote user account I built SQL Server 2005 ENT Cluster on Windows Server 2008 R2. I need to upgrade it to SQL Server 2005 SP4. However, I get this error at the Remote User Account screen

  Enter the remote user account name and password below.
  User name, Password and domain fields.
  Your account information could not be verified.
  Press Ok to return to Authentication mode screen to determine the reason for failure.
  For setup to verify your credentials the services must be startable, connectable, and you must be a SQL Server Administrator

I am using  an domain account that has access and local admin rights to both servers. The SQL Server and agent run as this domain account and sysadmin .

I got connected using the active node name and also using the virtual name and I get the same error. I also made sure no one is connected to the passive node.

This is my exact error message on the link. But none helped.
http://answers.flyppdevportal.com/categories/sqlserver/sqlsetupandupgrade.aspx?ID=89159030-1f3d-4f13-a693-8122b6bb6aae

The link below from discusses "To work around this problem, use the NetBIOS name of the domain instead of the FQDN" but I do not know where I would be using .
http://support.microsoft.com/default.aspx?kbid=932870

Thanks

I faced the same problem with installing sql server 2005 service pack 3 on windows 2008 R2 cluster. On node1, i brought the sql server group online and pause the Node2 in cluster Manager and then ran the set up on node1. Once setup completed, i resume the node2 and failed over the SQL Server group on node2. Now on Node2, i pause Node1 in cluster manager and run the setup on node2. Once setup was completed, i resume Node1 and rebooted both nodes. I verified the version by failing over the sql server on both nodes.


SQL Server - Adding non-nullable column to existing table - SSDT Publishing Due to business logic, we need a new column in a table that is critical to ensure is always populated. Therefore it should be added to the table as NOT NULL. Unlike previous questions that explain how to do this manually, this needs to be managed by the SSDT publish.

I have been banging my head against the wall for a while over this simple-sounding task due to some realizations:

A default value is not appropriate, and it cannot be a computed column. Perhaps it is a foreign key column, but for others we cannot use a fake value like 0 or -1 because those values might have significance (e.g. numeric data).
Adding the column in a pre-deployment script will fail the publish when it automatically tries to create the same column, a second time (even if the pre-deployment script is written to be idempotent) (this one is really aggravating as I can otherwise think of an easy solution)
Altering the column to NOT NULL in a post-deployment script will be reverted each time the SSDT schema refresh occurs (so at the very least our codebase will mismatch between source control and what is actually on the server)
Adding the column as nullable now with the intention of changing to NOT NULL in the future does not work across multiple branches/forks in source control, as the target systems will not necessarily all have the table in the same state next time they are upgraded (not that this is a good approach anyway IMO)

The approach I have heard from others is to directly update the table definition (so the schema refresh is consistent), write a predeployment script that moves the entire contents of the table to a temporary table with the new column population logic included, then to move the rows back in a postdeployment script. This seems risky as all hell though, and still pisses off the Publish Preview when it detects a NOT NULL column is being added to a table with existing data (since that validation runs before the predeployment scripting).

How should I go about adding a new, non-nullable column without risking orphaned data, or moving data back and forth on every publish with lengthy migration scripts that are inherently risky?

Thanks.

I'll share how I have done this in the past.  It is designed to solve the
specific limitation of pre-deployment scripts that you call out in your second
point:

  Adding the column in a pre-deployment script will fail the publish when it
  automatically tries to create the same column, a second time (even if the pre-
  deployment script is written to be idempotent)

Why pre-deployment scripts don't work for this

When you deploy an SSDT project, the way it stitches thing together is like
this (a bit simplified, but in general):

Do the "schema compare" between the source (dacpac file) and the target
(database)
Generate a deployment script based on the results of that compare
Process any pre-deployment scripts in the dacpac (doing token replacement,
etc.) and insert the contents into the beginning of the deployment script
Do the same for post-deployment scripts, appending to the end of the
deployment script

When a new column exists in the dacpac and not in the target database, step #2
will generate code to add that column.  So if the pre-deployment script adds
this column, the main part of the script will fail (because it assumes the
column doesn't exist, based on the results of the schema compare in step #1)

Solution: pre-SSDT script

Martin Smith mentioned this option in a comment, and it's the solution that
has worked best for me so far:

  We use premodel scripts in our deplyment pipeline. This is not something
  that is part of SSDT but a step that runs before the dacfx publish. So in
  this case the premodel script could add the column with the desired values
  and make it not null and by the time the publish happens it is already in
  the state expected by SSDT so it doesnt have anything to do. I'm still yet
  to find much use for predeploy scripts. – Martin Smith
  Jun 1 at 21:45

The steps to implement this solution in general are:

Create a script in the SSDT project to hold your "pre-SSDT" T-SQL code

Depending on how your deployment process works, code in these files
should probably be idempotent

Make sure to set this script to "Build Action=None" and "Copy to Output
Directory=Copy always"

the "copy always" option is especially important, as the deployment
process needs to be able to find this script in your
deployment artifacts

In your deployment process, locate and run this script (or scripts)
before the SSDT schema compare occurs
Once that script has been executed successfully, you can engage DacServices
/ DacFx / whatever to complete your deployment as usual

In the end, this allows you to add the column using whatever custom code you
like, populated using complex business logic, in the pre-SSDT script.

You also add the column definition in the SSDT project (so source control
still matches the real life state of the database).  But when the schema
compare runs, it sees no changes related to that column (because you've
already deployed it).

Other uses of pre-SSDT

I often find when testing deployments that SSDT performs a "table rebuild"
operation* when it's completely unnecessary.  This is where a new table is a
created with the updated schema, all data is copied to that table, the old
table is dropped, and the new table is renamed to replace the old table.

This can lead to massive transaction log file growth and other problems if
the table is large. If I notice that a schema change is causing this, I'll
instead make the change myself in pre-SSDT (which is usually a simple
ALTER TABLE statement) and avoid the table rebuild.

Is this a good idea?

I think so.  If you read Critiquing two different approaches to
delivering databases: Migrations vs state by Alex Yates, this is
essentially combining the two approaches a bit.  SSDT is state based, but we
incorporate a migration step (before SSDT) to handle some of the more complex
scenarios that SSDT just has no way of dealing with in a general way.

In doing some searching while writing this answer, this is actually a very
common approach discussed in the SSDT user community once you know what to
search for.  I've seen it called:

pre-compare
pre-model
pre-DAC
pre-SSDT

Etc.  Here's a great article that covers a lot of the points that I mentoned
above:

Pre-Compare & Pre-Deployment Scripts to SSDT

And one from Red Gate (in the #4 – Changing from system type to user
defined type section) that also refers to this as pre-compare:

How to Fix Ten SSDT Deployment Snags, With or Without ReadyRoll

So what's the point of pre-deployment scripts?

Martin points out that he hasn't found "much use for predeploy scripts."  I
tend to feel the same way.  But there are scenarios where they can be useful.

One example a coworker pointed out to me was storing some data in a temp table
to be used in the post deployment script (say you're moving a column from one
table to another).

*The table rebuild looks like this, which is horrifying, right?

GO
PRINT N'Starting rebuilding table [dbo].[MyTable]...';

GO
BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

CREATE TABLE [dbo].[tmp_ms_xx_MyTable] (
    [Id] BIGINT IDENTITY (1, 1) NOT NULL,
    -- etc, other columns
);

IF EXISTS (SELECT TOP 1 1
           FROM   [dbo].[MyTable])
    BEGIN
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_MyTable] ON;
        INSERT INTO [dbo].[tmp_ms_xx_MyTable] ([Id], ...)
        SELECT   [Id],
                 -- etc, other columns
        FROM     [dbo].[MyTable]
        ORDER BY [Id] ASC;
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_MyTable] OFF;
    END

DROP TABLE [dbo].[MyTable];

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_MyTable]', N'MyTable';

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;


Login failed for newly created user I just created my first database yesterday. It is now set up and I can write to it from my C# application using the admin user login. I would now like to use a limited write only user so my admin credentials are not stored in my application.

I have created a second login on master using query1

CREATE LOGIN writeOnlyLogin WITH password='myPassword';

I created a user on master using query2

CREATE USER writeOnlyUser FROM LOGIN writeOnlylogin;

I then added this user to the database that has the table I want this user to write to by using query2 with a connection string targeting the new database instead of master.

I granted insert access for this user only on a single table within the new database using query3

GRANT INSERT ON myTable TO writeOnlyUser

The user has no other roles or permissions.

In my c# application I have a datastructure that I want to write to this table. I create the connection string like this and write like this. With the admin credentials the open succeeds. With my new user the open fails.

public void SendToDatabase()
{
    string serverName = "myServer.database.windows.net";
    string databaseName = "myDatabase";
    string tableName = "myTable";
    string user = "writeOnlyUser";
    string password = @"myPassword";
    string sqlConnectionString = $"Data Source={serverName};Initial Catalog={databaseName};User id={user};Password={password};";
    using (SqlConnection sqlConnection = new SqlConnection(sqlConnectionString))
    {
        sqlConnection.Open();
        SqlCommand sqlCommand = HelperMethods.CreateSqlCommandFromObject(this, tableName, sqlConnection);
        int rowsAffected = sqlCommand.ExecuteNonQuery();
    }
}

The Exception message is, "Login failed for user 'writeOnlyUser'."

I am failing to find anything useful through google, however that could be because I don't know what I'm looking for. How can I fix this issue? In general, how can I debug login details?

My issue was that I misunderstood how logins work in SQL. I was using the USER name and the LOGIN password instead of the LOGIN name and the LOGIN password. It was a noob problem. Hopefully this saves someone else the time.


Is there a way to edit the System Database Directory for DB2 to remove a database? We have a case where we want to try to get rid of a database. The database still exists on disk and when we catalog it, it re-appears in the Database Manager. But when we try to do a DROP on it, it complains that it cannot find the alias. If we do uncatalog it and try to re-create it, it complains that the database name or alias already exists. So, we are stuck where we can't drop it and we can't recreate it.

I believe the SQLDBDIR file is the System Database used by DB2. Is there a way to edit this safely to get the entry out of there? Or is there another file we could edit, or a command we could run (outside of the standard DROP/CATALOG/UNCATALOG commands) that would get us in the clear?

Any help here would be great as we've been coming across this one more and more with our installed apps. (It arises from disk errors on laptops and we go to try to reinstall the database on a different section of disk.)

So far how we have gotten around this: We have uncatalogued the database. Then we rename the node locally (ie, C:\DB2, the default in the windows version). Then we need to uncatalogue the node. Then we can rebuild our databases with no problems.


Calculating overlap between groups I have a table with two columns of interest, item_id and bucket_id.  There are a fixed number of values for bucket_id and I'm okay with listing them out if I need to.

Each item_id can appear multiple times, but each occurrence will have a separate bucket_id value.  For example, the item_id of 123 can appear twice in the table, once under bucket_id of A, once under B.

My goal is to determine how much overlap exists between each pair of bucket_id values and display it as an N-by-N matrix.

For example, consider the following small example table:

item_id     bucket_id
=========   ===========
111         A
111         B
111         C

222         B
222         D

333         A
333         C

444         C

So for this dataset, buckets A and B have one item_id in common, buckets C and D have no items in common, etc.

I would like to get the above table formatted into something like the following:

        A       B       C       D
===================================
A       2       1       2       0
B       1       2       1       1
C       2       1       3       0
D       0       1       0       1

In the above table, the intersect of a row and column tells you how many records exist in both bucket_id values.  For example, where the A row intersects the C column we have a 2, because there are 2 records that exist in both bucket_id A and C.  Because the intersection of X and Y is the same as the intersection of Y and X, the above table is mirrored across the diagonal.

I imagine the query involves a PIVOT, but I can't for the life of me figure out how to get it working.

Note: the actual data I'm working with has a few hundred million entries but only a dozen or so buckets.  I'd rather not join the table to itself if at all avoidable.

First, we need to get a list of all buckets. if you already have a table buckets with all bucket_id (as PK), this part is not needed:

with
  buckets (bucket_id) as
  ( select distinct bucket_id
    from table_x
  ) ,

Then we get all bucket couples and the count of their common items - through a self-join:

  couples (bucket_a, bucket_b, cnt) as
    ( select a.bucket_id, b.bucket_id, count(*)
      from table_x a
        join table_x b
          on  a.bucket_id <= b.bucket_id
          and a.item_id = b.item_d
      group by a.bucket_id, b.bucket_id
    )

Then we can show our results:

select a.bucket_id      as bucket_a,
       b.bucket_id      as bucket_b,
       coalesce(cnt, 0) as common_items
from buckets a
  join buckets b
    on a.bucket_id <= b.bucket_id
  left join couples c
    on  a.bucket_id = c.bucket_a
    and b.bucket_id = c.bucket_b ;

The above will show, for your sample table:

bucket_a  bucket_b  common_items
================================
A         A         2
A         B         1
A         C         2
A         D         0
B         B         2
B         C         1
B         D         1
C         C         3
C         D         0
D         D         1

If you want the pivoted output, you'll need one more processing.

If the number of buckets is very low as you say, you may try this one as well:

with
  buckets (bucket_id) as
  ( select distinct bucket_id
    from table_x
  ) ,
select a.bucket_id      as bucket_a,
       b.bucket_id      as bucket_b,
       ( select count(*)
         from table_x aa
           join table_x bb
             on  aa.item_id = bb.item_d
         where a.bucket_id = aa.bucket_id
           and b.bucket_id = bb.bucket_id
       ) as common_items
from buckets a
  join buckets b
    on a.bucket_id <= b.bucket_id ;


Need a better approach in how to design this relation I have a table fluxo, and fluxo have many fluxo_acao, but a entry in fluxo_acao can have three types, and each type have they particularities. to acomplish this, i created more three tables(fluxo_acao_mensagem, fluxo_acao_pergunta and fluxo_acao_escolher_fila), connected to fluxo_acao.
The problem is i can't select any of the three tables, from fluxo, because first i need to check wich type is and them i do the query.
I need a better approach in how to acomplish this relation or connect fluxo_acao to the three tables. Even why i need create another two more. and this is the perfect time to change.

someone just told me Inheritance may help me. i'm searching how.

So I can't see exactly what your primary keys are, but it sounds like your issue isn't with subtypes themselves, but more likely the choice of primary key and path dependence.
So if fluxo has a primary key id_fluxo, and the relation to fluxo_acao is one to many AND duplicates aren't too tightly controlled, you can set up your primary key as:

id_fluxo_acao - bad, just a row pointer
(id_fluxo, id_fluxo_acao) - okay, still a row pointer but no notion of what is unique
(id_fluxo, ordem)  (flow, order?) - better, but per your comment it could update frequently and would require updates to the related subtype.
(id_fluxo, timestamp) - best, provides uniqueness and supplies valuable metadata

Then the primary key of each subtype will contain id_fluxo and you can join directly from fluxo to any of the subtypes.
Your comment:

But lets say that i want to select all the entries in the three tables
from fluxo how can i do this with joins. After addind this other pk in
fluxo_acao

This you can do easily enough with a simple query that can be hardened into a view later:
SELECT
  fluxo.id_fluxo
 ,fluxo.descricao
 ,fluxo.id_status
 ,fluxo.data_criacao
 ,fluxo.id_empresa
 ,fluxo.tipo
 ,fluxo_acao.tipo AS acao_tipo
 ,fluxo_acao.timestamp
 ,fluxo_acao.ordem
 /* columns from each subtype */
FROM
  fluxo fluxo
INNER JOIN
  fluxo_acao fluxo_acao
    ON fluxo_acao.id_fluxo = fluxo.id_fluxo
LEFT JOIN
  fluxo_acao_mensagem fluxo_acao_mensagem
    ON fluxo_acao_mensagem.id_fluxo = fluxo_acao.id_fluxo
        AND fluxo_acao_mensagem.timestamp = fluxo_acao.timestamp /* Not sure what the Portuguese would be for "created at" or "CreateTimestamp" */
LEFT JOIN
  fluxo_acao_pergunta  fluxo_acao_pergunta
    ON fluxo_acao_pergunta.id_fluxo = fluxo_acao.id_fluxo
        AND fluxo_acao_pergunta .timestamp = fluxo_acao.timestamp
LEFT JOIN
  fluxo_acao_escolher_fila  fluxo_acao_escolher_fila
    ON fluxo_acao_escolher_fila.id_fluxo = fluxo_acao.id_fluxo
        AND fluxo_acao_escolher_fila.timestamp = fluxo_acao.timestamp

If there is a need for only one of the subtypes (and don't need the column ordem) you can just query the individual table directly.


Using alias in Group by I am running into a problem where I have to use an alias (Ordernum) in a group by. I have found that this could be done somehow with subquery but I don't have any success with that and also those are not so familiar. Any kind of advice at the moment would be a major help.
SELECT a.ma   as  Document
     , a.ca  as  type
     , SUM(a.num) as Value1
     , SUM(a.num2)as Value2
     , coalesce(a.ba, MAX(a.ba) OVER (partition by a.ca)) as OrderNum  <-- Required in group by
     , a.ti as Year
FROM Orders a
WHERE ti = 2020
GROUP BY a.ma
       , b.ca
       , a.ti

You can use a outer SELECT or A cte to accomplish that
SELECT
    Document
    ,type
    ,SUM(Value1) AS Value1
    ,SUM(Value2) AS Value2
    ,OrderNum
    ,Year
FROM
    (SELECT
        a.ma   as  Document
        ,a.ca  as  type
        ,SUM(a.num) as Value1
        ,SUM(a.num2)as Value2
        ,coalesce(a.ba, MAX(a.ba) OVER (partition by a.ca)) as OrderNum
        ,a.ti as Year
    FROM Orders a
    WHERE ti = 2020) t1

Group by
    Document
    ,type
    ,OrderNum
    ,Year


SSRS 2014 Report - looking up single row from dataset I have a table that I'm trying to report out on in SSRS-2014.

A cut down version of the table:-

TABLE_NAME  FEE_NAME    FEE_VALUE
PDE         1st year    100.00
PDE         2nd year    120.00
PDE         3rd year    130.00
PDE         4th year    140.00
PDE         5th Year    150.00
PGB         1st year    500.00
PGB         2nd year    520.00
PGB         3rd year    530.00
PGB         4th year    440.00
PGB         5th Year    850.00
PUS         1st year    100.00
PUS         2nd year    120.00
PUS         3rd year    130.00
PUS         4th year    140.00
PUS         5th Year    150.00
PCA         1st year    100.00
PCA         2nd year    120.00
PCA         3rd year    130.00
PCA         4th year    140.00
PCA         5th Year    150.00
PJP         8th year    600.00
PJP         10th year   820.00
PJP         11th year   40.00
PJP         12h year    140.00
PJP         14th Year   750.00

How can I display the data so that it's :-

Table Name  1st year    2nd year    3rd year    4th year    5th year    6th year    7th year    8th year    9th year    10th year   11th year   12 year 13th year   14th year
PDE         100.00      120.00      130.00      140.00      150.00
PGB         500.00      520.00      530.00      440.00      850.00
PJP                                                                                600.00                    820.00        40.00   140.00              750.00

And so on.

The year columns will range from 1 to 20 and there wont always be a value for every year.

I've tried using :-

=Lookup(ReportItems!Textbox3.Value, Fields!FEE_NAME.Value, Fields!FEE_DIS1.Value, "Fees")

to use the year in the header row of the SSRS table to lookup the FEE_NAME from the dataset, but I get a row for every entry in the dataset but each row shows the same (correct) value.

I've tried using CASE in the SQL :-

CASE WHEN FEES.FEE_NAME = '3rd year' THEN FEE_VALUE END AS [3rd year],

Again, I get the value I want and then empty rows for the rest of the TABLE_NAME rows.

I also looked at Pivot (SQL) and a Matrix (SSRS) table but I just want to return the corresponding fee value and display it in the relevant year column whereas from what I've read so far Pivot and SSRS need an aggregate.

Hopefully this makes sense.

Cheers.

You can do this in either the SQL or using a matrix table within SSRS. Typically, I prefer to do it in the SQL, but that's not often the most convenient for others. You are correct in that you need to use an aggregate function with any kind of pivot functionality. In your case, you should use a sum() aggregate.

While this sounds slightly counter intuitive, it will work fine as there is only a single entry here per fee_name. What you end up with is a list of values, where the only thing in the list is the desired value. A list such as, [10] will sum to 10.


Duplicate Oracle Database from .BKP files - getting "multitenant container database not set up properly" I have a regular Oracle 12c database already created, named ECLS. I've been given a pile of .BKP files, not from this database, and have been told to replace everything in ECLS with the contents of these files.

With the files, I was also told to use this command to do it:

RMAN> duplicate database to ECLS backup location '/u01/dumpfilesgohere';

No other directions were provided.

I found that my database needs to be an auxiliary with nothing mounted. So I did the following:

$ sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup nomount;
SQL> exit
$ rman auxiliary /
RMAN> duplicate database to ECLS backup location '/u01/dumpfilesgohere';

This looks like it's working properly for the first few seconds... it says that it's able to restore the control files from the BKP files. But then it ends up failing. Here's the last few lines before the failure:

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''ECLS'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
}
executing Memory Script

sql statement: alter system set  db_name =  ''ECLS'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/30/2017 14:59:06
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-65093: multitenant container database not set up properly

What am I doing wrong? What files do I need to change to get this error message to stop occurring?

Alternatively... if my whole database install isn't going to work, can someone tell me what I need to do differently when reinstalling the database?

65093, 00000, "multitenant container database not set up properly"

  Cause:  An attempt was made to open a multitenant container database without
              the correct parameter set for a multitenant container database in
              the initialization parameter file.

  Action: Set the 'enable_pluggable_database=true' parameter for the
              multitenant container database in the initialization parameter file
              and restart the database.

By default ENABLE_PLUGGABLE_DATABASE is set to FALSE. We need to set this parameter to TRUE before creating CDB.

RAMN>duplicate database to ECLS
    SPFILE
    set enable_pluggable_database=true
 backup location '/u01/dumpfilesgohere';


Is outputting to both a result set and a temporary table possible? I have a stored procedure that I need to both output the result set to the user and use a piece of information in the result set to possibly output other result sets.

Here is a basic schema of what I have

create table FooQueue(FooId int, IsQueued bit)
create table Foo(FooId int primary key, BarId int, FooData varchar(50))

create table BarQueue(BarId int, IsQueued bit)
create table Bar(BarId int primary key, BarData varchar(50)

Here is a very simplified version of the stored procedures.

create procedure GetFoos as
begin
    select FooId, FooData
    from Foo
    where FooId in (select FooId from FooQueue where IsQueued = 1)

    update BarQueue
    set IsQueued = 1
    where BarId in (select BarId
                    from Foo
                    where FooId in (select FooId from FooQueue where  IsQueued = 1))

    exec GetBars

    delete FooQueue where IsQueued = 1
end
GO

create procedure GetBars as
begin
    select BarId, BarData
    from Bar
    where BarId in (select BarId from BarQueue where IsQueued = 1)

    delete Bar where IsQueued = 1
end
GO

And the first procedure is called like the following

update top 10000 Foo set IsQueued = 1
exec GetFoos

I am about to modify a large number of the stored procedures so it does not hit the Foo table twice to something like the following

alter procedure GetFoos as
begin
    select FooId, BarId, FooData
    from Foo
    into #t
    where FooId in (select FooId from FooQueue where IsQueued = 1)

    select FooId, FooData from #t

    update BarQueue
    set IsQueued = 1
    where BarId in (select BarId from #t)

    exec GetBars

    delete FooQueue where IsQueued = 1
end
GO

Would this be the correct approach to doing this or is there a better way to both return the resultset (without BarId included) and update the 2nd table based on the returned rows?

you can alter the column in select or insert list as you need.

    alter procedure GetFoos as
    BEGIN
        CREATE TABLE #t (FooId INT, BarId INT, FooData SYSNAME)

        select FooId, BarId, FooData
        OUTPUT INSERTED.FooId, INSERTED.BarId,INSERTED.FooData INTO #t(FooId, BarId, FooData )
        from Foo
        where FooId in (select FooId from FooQueue where IsQueued = 1)

        update BarQueue
        set IsQueued = 1
        where BarId in (select BarId from #t)

        exec GetBars

        delete FooQueue where IsQueued = 1
    end
    GO


How? SQL Cluster with multiple active nodes (not failover) I'm building a new SQL 2008 R2 (Enterprise) Cluster that is backed by an EqualLogic iSCSI SAN.  Instead of building a failover cluster (which I have done), I would instead like to setup several nodes that are all active simultaneously.  I want to spread out the CPU/RAM load across the nodes.

Can anyone point me towards a document on this?  I'm only finding info on HA/Failover clusters.

You don't get "load balancing" with SQL Server. There are a lot of "High Availability" features including clustering.

That is, you can't have read/write on the same data with multiple SQL Server instances.

You can off load reporting onto a snapshot, or replicate data between Instances, or "shared scalabl" but none is full OLTP on all data on every node.

You scale up, not out, for SQL Sever

SO: SQL Server load balancing
SF: How to load-balance SQL Server 2008 for high-usage ASP.NET applications?