When no index is faster than having a clustered index? I ran the same query, which involves joining two tables A and B, when B had no index at all and then added a PK clustered index on the ID column used for joining to it. The time elapsed when there was no index was actually faster?
No index result:
Table 'Table_A'. Scan count 5, logical reads 1764, ...
Table 'Table_B'. Scan count 10, logical reads 16976, ...
 SQL Server Execution Times:
   CPU time = 859 ms,  elapsed time = 269 ms.

Added PK clustered index to Table_B
Table 'Table_A'. Scan count 1, logical reads 445, ...
Table 'Table_B'. Scan count 2, logical reads 17026, ...
 SQL Server Execution Times:
   CPU time = 1046 ms,  elapsed time = 1053 ms.

Even though the IO numbers are higher when Table_B had no index, how could it be faster still? And how could a table with index slower than without? Any logical explanation?

Looking at the ratio of elapsed time to CPU time for both queries, we can see that the "no index" query benefited from parallelism - CPU time was about 3 times greater than elapsed time.
After adding the clustered index to Table_B, you got a serial version of that execution plan (CPU time and elapsed time were about equal).  Or maybe an entirely different serial execution plan, but the fact that Table_B was scanned twice as often as Table_A in both queries makes me think the general plan shape could be the same.
With the clustered index in place, perhaps the optimizer reasoned that it could take advantage of the sorted nature of the clustered index, and thus didn't need to incur the overhead of parallelism.
In this case, that seems like a good decision by the optimizer overall: the second query used less CPU cores, read less data pages, at the cost of a small increase in elapsed time
If you edit your question to include more details (actual execution plan, table definitions, sample data), then someone might be able to provide an answer more specific to your situation.


Tables not showing up when linking SQL Server to Access using ODBC connection I am learning to link the back end of a database in SQL Server express 2016 to the front end in Access 2016. The tables were originally in Access and then I exported them to SQL Server. Now I am attempting to link them to the front end in Access but the problem is that they are not showing up in the possible tables that I can link. The answers I found when searching this up was that I simply did not have permission, but I am on my laptop and there are no other users and I am using Windows Authentication. I am doing this using the Northwind sample database. The following shows that I only see the system tables as options when trying to link not the desired tables.

This shows the settings of my data source.

I know this was 8 months ago. Your problem may be that you did not select a default database. When linking, select the options button and select the database that is not appearing. I hope this helps.


How to trace the exact query fired in sql server I am facing a problem with performance tuning. We are using an application called ARAS (one of the leading PLM software) which is using Sql server as the back end.

Every time we find some data using Aras interface it interacts with SQL server and give us Response.

But I am unable to find which query is being sent to SQL Server. I tried to find it using this query

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC.

This was also not helping me properly.

We are using SQL Server 2008 Express and ARAS 9.3

Though SQL Profiler is not available in Express Edition, still you can leverage trace functionality with any of the below mentioned approaches :

ExpressProfiler available at CodePlex
Or You can install developer Edition and then use profiler to trace the sql statements.
Use Trace Flag 4032 that will log all the sql from all clients to the errorlog.

Note : This will log all the sql from all the clients to the error log, so better turn it off once you are done with tracing. The downside of this trace flag is that it has to be applied at the startup using ;-T4032 and sql server has to be restarted.

    DBCC TRACEON(4032,3605,-1)

4. You can initiate server side trace  as shown below :

/***********************************************/
/* Server Side Trace                           */
/***********************************************/
-- Declare variables
DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxFileSize bigint
DECLARE @fileName NVARCHAR(128)
DECLARE @on bit

-- Set values
SET @maxFileSize = 5
SET @fileName = N'C:\SQLTrace'
SET @on = 1

-- Create trace
EXEC @rc = sp_trace_create @TraceID output, 0, @fileName, @maxFileSize, NULL

-- If error end process
IF (@rc != 0) GOTO error

-- Set the events and data to collect
EXEC sp_trace_setevent @TraceID, 41,  1, @on
EXEC sp_trace_setevent @TraceID, 41, 12, @on
EXEC sp_trace_setevent @TraceID, 41, 13, @on
EXEC sp_trace_setevent @TraceID, 41, 14, @on
EXEC sp_trace_setevent @TraceID, 41, 15, @on
EXEC sp_trace_setevent @TraceID, 41, 16, @on
EXEC sp_trace_setevent @TraceID, 41, 17, @on

-- Set Filters
-- filter1 include databaseId = 6 .. **Change your database ID *** HERE !!
EXEC sp_trace_setfilter @TraceID, 3, 1, 0, 6
-- filter2 exclude application SQL Profiler
EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'

-- Start the trace
EXEC sp_trace_setstatus @TraceID, 1

-- display trace id for future references
SELECT TraceID=@TraceID
GOTO finish

-- error trap
error:
SELECT ErrorCode=@rc

-- exit
finish:
GO

-- to find trace ID

SELECT * FROM :: fn_trace_getinfo(default)

--- to stop the trace

sp_trace_setstatus traceid, 0 --- here 0 is the trace ID which we got from above query

--Load the trace file into a new table in sql server

SELECT * INTO sqlTableToLoad
FROM ::fn_trace_gettable('C:\SQLTrace\trace.trc', DEFAULT)


Converting SQL to Relational Algebra / Calculus I have designed a schema and generated a few SQL queries. I'm using PostgreSQL.

For example:

CREATE TABLE train
(
  train_code SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE journey
(
  journey_id SERIAL PRIMARY KEY,
  int INTEGER,
  train_code REFERENCES train(train_code)
);

CREATE TABLE price
(
  journey_id REFERENCES journey(journey_id),
  price INTEGER
);

Give me all train names that commence from train_code NYC or SFO that are priced $50 or more. (assume the price table is in $ already).

SELECT train.name
JOIN   train
JOIN   journey on train.train_code = journey.train_code
JOIN   price on price.journey_id = journey.journey_id
WHERE  price.price >= 50
AND    journey.train_code IN ('NYC', 'SFO')
AND    journey.int = 1 -- (first train of the journey)

I am unsure how to convert this into a relational algebra and/or calculus query. I have looked at a few converters e.g. http://dbis-uibk.github.io/relax/calc.htm but in this calculator 'join' for example and 'in' is not allowed.

You have a number of "mistakes" in your SQL, that should be addressed before you translate it to relational algebra.

You don't have a FROM in your SQL. You shouldn't start by JOINing.
You seem to assume that train_code is a text, yet you define it as an integer.
Your table definitions don't define a type for train_code in the journey table, nor for journey_id in the price one.
The tool you're using doesn't understand SERIAL (which is not standard SQL). Just don't use it. Use INTEGER instead.

So, your table definitions just should be:

CREATE TABLE train
(
  train_code TEXT PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE journey
(
  journey_id INTEGER PRIMARY KEY,
  int INTEGER,
  train_code TEXT REFERENCES train (train_code)
);

CREATE TABLE price
(
  journey_id INTEGER REFERENCES journey (journey_id),
  price INTEGER
);

This is translated by RelaX to:

group: joanolo (imported from SQL)

train = {
    train_code:string, name:string
}

journey = {
    journey_id:number, int:number, train_code:string
}

price = {
    journey_id:number, price:number
}

On the query side, you need to add a FROM and you just change x IN (a, b) to (x = a OR x = b). You'll end up having the following query:

SELECT
    train.name
FROM
    train
    JOIN  journey on train.train_code = journey.train_code
    JOIN  price on price.journey_id = journey.journey_id
WHERE
    price.price >= 50
    AND (journey.train_code = 'NYC' OR journey.train_code = 'SFO')
    AND  journey.int = 1

This format is understood by RelaX, and will give you the result you're looking for:

  π train.name σ price.price ≥ 50 and (journey.train_code = 'NYC' or journey.train_code = 'SFO' ) and journey.int = 1 train ⨝ train.train_code = journey.train_code journey ⨝ price.journey_id = journey.journey_id price

That is you have one projection (π), equivalent to your SELECT one selection (σ) that filters with the condition in your WHERE clause, and two joins (⨝) which are equivalent to SQL JOIN.


REGEXP_REPLACE is matching but not replacing with empty string Given a table with a column called files_name, and you want to match everything after the match with regex, and replace it with nothing.

I have this code but it matches everything, but it's always changed 0.

UPDATE files
SET files_name = REGEXP_REPLACE (files_name, 'S[0-9]?[0-9]?[0-9]E[0-9]?[0-9]\K.*', '')
WHERE type = 5;

A online regex tester says my regex is correct, and is matching as desired, yet no matter what I put into the second '' it never replaces the match.

I'm using mariadb 10.3.17

UPDATE files
SET files_name =
    REGEXP_REPLACE(
       files_name,
       '(S[0-9]?[0-9]?[0-9]E[0-9]?[0-9])([^0-9].*)',
       '\\1'
    )
WHERE type = 5;

fiddle

UPDATE

  I executed this command, with redacted info of course...

mariadb -h ip -P port -D database -e "UPDATE files SET files_name = REGEXP_REPLACE (files_name, '(S[0-9]?[0-9]?[0-9]E[0-9]?[0-9])([^0-9].*)', '\\1') WHERE type = 5;" -pPASSWORD

You need to quote a slash:

in command line
in SQL query

So you would use '\\\\1'.

You may avoid using slash by using CONCAT(CHAR(92), '1') instead of '\\1' in the query text - in that case no slaches in SQL and quoting don't need in.


Standalone Backup and Restore Tools We plan to have our build server perform a backup (mongodump) from our Production environment and restore it (mongorestore) to the Test environment for each new release to Test.

Are there mongodump and mongorestore standalone tools that one could use for this or is it best to install the entire MongoDB package on our build server (which feels like an overkill)?

Executing mongodump without the full MongoDB installation:

We are using Windows 10 & MongoDB 3.2.

As at MongoDB 3.2, there is no option for the Windows MSI to only install the mongodump.exe and mongorestore.exe command line tools so a default install is the most straightforward option.

  Are there mongodump and mongorestore standalone tools that one could use for this or is it best to install the entire MongoDB package on our build server (which feels like an overkill)?

If you install from a standard (non-Administrator) command prompt, the installer will not be able to create a Windows Service definition so the only overhead will be some extra binary files. In this case you can also use the MSI installer to uninstall or upgrade to newer versions of MongoDB. You will also have additional command line tools available if you want to monitor a deployment (mongostat.exe, mongotop.exe) while you are backup/restoring, and will have mongoimport.exe and mongoexport.exe if you want to import/export JSON or delimited text.

If you want a more minimal install only including specific binaries, you can copy the binaries and the two OpenSSL DLLs which can be found in the bin directory of a full install of MongoDB: ssleay32.dll and libeay32.dll. The DLLs will need to either be in your path or the same directory as the MongoDB .exe files you are running.


How to combine concatenate operator on JSON values with AS I'm trying to combine multiple JSON objects into one big object in Postgres.

I could do this with an extra SELECT row_to_json (as in my first example), but this way it creates a extra object, which I don't want because this object is already created by array_agg which is wrapped around it. However, array_agg doesn't accept multiple columns. So I tried to solve it with the concatenation operator, but I can't use AS with it, which is necessary to give the objects the right keys. (And the data type becomes text, which to_json function escapes, which is not perfect but I can live with it).

SELECT to_json(array_agg(g)) FROM (SELECT
        (SELECT row_to_json(e) FROM
            (SELECT ST_AsGeoJSON(ST_Union(cargogeom))::json AS geometry,
            (SELECT row_to_json(d) FROM
               (SELECT cargoid AS voyageid, voyages, cargovalues) d) AS properties,
            to_json('Feature'::text) AS type) e) AS "ThisKeyShouldBeGone"
    FROM (SELECT
            cargoid,
            cargogeom,
            sum(cargonumvoyages) AS voyages,
            sum(cargovalues) AS cargovalues
        FROM "bgbCargoMinardSplit"
        GROUP BY cargoid, cargogeom)
    AS x
    WHERE cargoid = 1000
    GROUP BY cargoid, voyages, cargovalues
    ORDER BY cargoid ) AS g;
            cargoid,
            cargogeom,
            sum(cargonumvoyages) AS voyages,
            sum(cargovalues) AS cargovalues
        FROM "bgbCargoMinardSplit"
        GROUP BY cargoid, cargogeom)
    AS x
    WHERE cargoid = 1000
    GROUP BY cargoid, voyages, cargovalues
    ORDER BY cargoid ) AS g;

Which results in

[
  {
    "ThisKeyShouldBeGone": {
      "geometry": {
        "type": "MultiLineString",
        "coordinates": [
          [
            [..]
          ]
        ]
      },
      "properties": {
        [..]
      },
      "type": "Feature"
    }
  },
  {
    "ThisKeyShouldBeGone": {
      "geometry": [..]

So to get rid of the "ThisKeyShouldBeGone"-key I tried using concatenate operators, but this way I can't use AS:

SELECT to_json(array_agg(g)) FROM (SELECT
            (SELECT ST_AsGeoJSON(ST_Union(cargogeom))::json  || ',' ||
            (SELECT row_to_json(d) FROM
                (SELECT cargoid AS voyageid, voyages, cargovalues) d)  || ',' ||
            to_json('Feature'::text) AS type)
    FROM (SELECT
            cargoid,
            cargogeom,
            sum(cargonumvoyages) AS voyages,
            sum(cargovalues) AS cargovalues
        FROM "bgbCargoMinardSplit"
        GROUP BY cargoid, cargogeom)
    AS x
    WHERE cargoid = 1000
    GROUP BY cargoid, voyages, cargovalues
    ORDER BY cargoid ) AS g;

So is there a way I can combine the concatenate operator with AS, or is there maybe a better way to do this, since the concatenate operator isn't perfect either.

Not exactly sure what you are asking, but in Postgres 9.3 or later you can use json_agg() to translate a whole table into a JSON array of records. Prepare the rows in a single subquery and feed whole rows to json_agg() using the table alias:

Multiple layers of nested subselects like you display seem uncalled for.

Simple example:

CREATE TABLE tbl (
  tbl_id serial PRIMARY KEY,
  foo    text
);

INSERT INTO tbl VALUES
  (1, 'First' )
, (2, 'Second');

Query:

SELECT json_agg(sub)
FROM  (
   SELECT tbl_id        AS some_id
        , foo           AS bar
        , tbl_id || foo AS some_derived_column
   FROM  tbl
   ) sub;

Result:

[{"some_id":1,"bar":"First","some_derived_column":"1First"},
 {"some_id":2,"bar":"Second","some_derived_column":"2Second"}]

SQL Fiddle.


Reconnect mirroring server SQL Server 2008 R2 The failover server has status (Mirror, Disconnected, In recovery) and principal server has status (Principal, Disconnected).

What is the process to reconnect these servers?

Sometimes restarting the mirroring endpoint on the principal server will resolve this.

To do this use the following T-SQL.

ALTER ENDPOINT Endpoint_Name STATE = STOPPED

ALTER ENDPOINT Endpoint_Name STATE = STARTED

To get the name of the endpoint.

SELECT * FROM sys.endpoints
WHERE type = 4

See KB 2490051 for more details.


Is MariaDB a suitable drop in replacement for MySQL on a standard LAMP stack? I've been wondering lately what (if any) are the improvements available in MariaDB over 'conventional' MySQL?

I understand that where platform interoperability and/or backwards compatibility may be an issue then sticking with the tried and trusted MySQL is best. But for a stand-alone DB on a stand-alone web site/application are there any benefits to be had by using Maria?

Will Maria work with common web platforms such as WordPress, Drupal, Joomla etc.?

I expect that some of this is going to come down to choice/preference of storage engines, but to be honest I still don't know half the time if/when I should use MyISAM, InnoDB or any of the others! Which is better or faster or whatever?

The only thing I get is that if I want 'true' table relationships (i.e. foreign keys etc.) I use InnoDB.

Thanks for any help or clarity people can offer me.

One valid reason to move to another MySQL flavour is performance. The latest MariaDB versions 5.3 and 5.5 (which are drop in replacemants of MySQL 5.1 and 5.5 respectively) included major improvements in query optimization. You can read more on their site: What is MariaDB 5.3

Examples on improvements/features are "Index Condition Pushdown", "Cost-based choice of range vs. index_merge", "Subquery Materialization", "Subquery Cache" and new joins methods, like "Hash Join". Several of the features affect performance of (statements with) subqueries, derived tables and views.

Details can be found at the Optimizer Feature Comparison Matrix page.

Other additional features like microsecond support for time datatypes and improvements on the replication and binary logging.

The previous versions (5.1 and 5.2) also have some features that are not found in (mainstream) MySQL, like [PERSISTENT] VIRTUAL columns.

Of course, any migration of your server from MySQL to MariaDB should be examined and tested by you as whether these features are useful in your databases/applications and whether the difference in performance (if any) is big enough for the troubles you may have (as with any migration).


How do i track usage of a heap table ? I would like to look at something similar to sys.dm_db_index_usage_stats (This DMV is only for tables with indexes I believe) for the Heap tables? Any idea?

Heaps are tracked in sys.indexes and sys.dm_db_index_usage_stats with index_id=0.  Try

select
   db_name(database_id) database_name,
   object_name(object_id,database_id) table_name,
   s.*
from sys.dm_db_index_usage_stats as s
where index_id = 0

You should see your heap tables.


What are the parameters to install oracle 11g r2 command line? I'm trying to install oracle 11g R2 using a responsefile. I do like this:

./runInstaller -responseFile /home/oracle/database/response/db_install.rsp

But when I execute the command, it always open my Windows X (I'm using moba Extern). How can I install Oracle 11G R2 in command line?

Note: I want to install software only!


Differences Between Two Different Create Index Commands Are there differences between these two scripts?  Or would all of the extra tokens/attributes (ie: NONCLUSTERED, WITH..., etc...) for the 1st script be defaults in SQL Server 2008 for the 2nd script?

1st script:

CREATE UNIQUE NONCLUSTERED INDEX [DEID_MAP_IDX1] ON [dbo].[DEID_MAP]
(
    [VISIT_NUM] ASC
) WITH
  (PAD_INDEX  = OFF,
   STATISTICS_NORECOMPUTE  = OFF,
   IGNORE_DUP_KEY  = OFF,
   ALLOW_ROW_LOCKS = ON,
   ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

2nd script:

CREATE UNIQUE INDEX [DEID_MAP_IDX1] ON [DEID_MAP]
(
    [VISIT_NUM] ASC
);

FYI: there is ETL code that drops the index with this script before doing a bulk data load, and then finally applying re-creating the index with the 2nd script above.

DROP INDEX [deid_map_idx1] ON [deid_map] WITH ( ONLINE = OFF );

EDIT:

After applying the simple index above (2nd script), I got this:

SQL Server Management Studio > expanded table > expanded folder "Indexes" > right click index > selected "Script Index as.." > selected "CREATE TO" > selected "New Query Editor Window" > got the following.

CREATE UNIQUE NONCLUSTERED INDEX [DEID_MAP_IDX1] ON [dbo].[DEID_MAP]
(
    [VISIT_NUM] ASC
) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

So it appears that these are added in addition by running the simple statement:

SORT_IN_TEMPDB = OFF

DROP_EXISTING = OFF

ONLINE = OFF

It boils down to looking what the default values are.  Lets break this down:

CREATE UNIQUE NONCLUSTERED INDEX [DEID_MAP_IDX1] ON [dbo].[DEID_MAP]

nonclustered is specified here.  The default (i.e. nothing specified) is nonclustered.  So unless clustered is specified it will default to nonclustered.  So that's the same in both scripts.

[dbo] is specified here explicitly.  As for the second un-specified CREATE INDEX then it all depends on what the current user's default schema is.  Only you can answer that at the moment, so that may or may not default to dbo.

WITH (
    PAD_INDEX  = OFF,
    STATISTICS_NORECOMPUTE  = OFF,
    IGNORE_DUP_KEY  = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]

PAD_INDEX: the default is OFF, so unspecified will be the same in the second script as it is in the first.

STATISTICS_NORECOMPUTE: the default is OFF, so the second script unspecified has the same value.

IGNORE_DUP_KEY: the default is OFF, so the second CREATE INDEX is identical with this parameter.

ALLOW_ROW_LOCKS: the default is ON, so the second CREATE script has the same behavior.

ALLOW_PAGE_LOCKS: the default is ON...the second script has identical behavior.

... ON [PRIMARY]: just like the default schema one, this all depends on what your default filegroup is.  If PRIMARY is the default filegroup, your second CREATE INDEX script will also create the index on PRIMARY.  If PRIMARY is not the default filegroup, then it will be a different filegroup, as an unspecified filegroup will go to the default filegroup.

All of this information and default values can be found on this BOL reference here.


Installation problem in SQL server 2005 Does SQL Server 2005 Express have a problem with installing on single user systems?

Sql Server 2005 will install just fine on a single user system. A better question is why you would want to. Most of the time when I've seen people want to do this, they're using it as a simple data store for a desktop application they are building. If that's what you're doing, you should probably choose an in-process database like Sqlite, Sql Server Compact Edition, or even MS Access instead. Of course there are exceptions, but really if this is part of an app that must coexist with other apps on a machine that will serve as some user's primary desktop, you should really avoid trying to push a whole server at them.


How to use COLUMNS_UPDATED to check if any of certain columns are updated? I have table with 42 columns and a trigger which should do some stuff when 38 of these columns are updated. So, I need to skip the logic if the rest 4 columns are changed.

I can use UPDATE() function and create one big IF condition, but prefer to do something shorter. Using COLUMNS_UPDATED  I can check if all of certain columns are updated?

For example, checking if column 3, 5 and 9 are updated:

  IF
  (
    (SUBSTRING(COLUMNS_UPDATED(),1,1) & 20 = 20)
     AND
    (SUBSTRING(COLUMNS_UPDATED(),2,1) & 1 = 1)
  )
    PRINT 'Columns 3, 5 and 9 updated';

So, value 20 for column 3 and 5, and value 1 for column 9 because it is set in the first bit of the second byte. If I change the statement to OR it will check if columns 3 and 5 or column 9 is/are updated?

How can apply OR logic in the context of one byte?

You can use CHECKSUM() as a fairly simple methodology for comparing actual values to see if they were changed.  CHECKSUM() will generate a checksum across a list of passed-in values, of which the number and type are indeterminate.  Beware, there is a small chance comparing checksums like this will result in false negatives.  If you cannot deal with that, you can use HASHBYTES instead1.

The example below uses an AFTER UPDATE trigger to retain a history of modifications made to the TriggerTest table only if either of the values in the Data1 or Data2 columns change.  If Data3 changes, no action is taken.

USE tempdb;
IF COALESCE(OBJECT_ID('dbo.TriggerTest'), 0) <> 0
BEGIN
    DROP TABLE dbo.TriggerTest;
END
CREATE TABLE dbo.TriggerTest
(
    TriggerTestID INT NOT NULL
        CONSTRAINT PK_TriggerTest
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , Data1 VARCHAR(10) NULL
    , Data2 VARCHAR(10) NOT NULL
    , Data3 DATETIME NOT NULL
);

IF COALESCE(OBJECT_ID('dbo.TriggerResult'), 0) <> 0
BEGIN
    DROP TABLE dbo.TriggerResult;
END
CREATE TABLE dbo.TriggerResult
(
    TriggerTestID INT NOT NULL
    , Data1OldVal VARCHAR(10) NULL
    , Data1NewVal VARCHAR(10) NULL
    , Data2OldVal VARCHAR(10) NULL
    , Data2NewVal VARCHAR(10) NULL
);

GO
IF COALESCE(OBJECT_ID('dbo.TriggerTest_AfterUpdate'), 0) <> 0
BEGIN
    DROP TRIGGER TriggerTest_AfterUpdate;
END
GO
CREATE TRIGGER TriggerTest_AfterUpdate
ON dbo.TriggerTest
AFTER UPDATE
AS
BEGIN
    INSERT INTO TriggerResult
    (
        TriggerTestID
        , Data1OldVal
        , Data1NewVal
        , Data2OldVal
        , Data2NewVal
    )
    SELECT d.TriggerTestID
        , d.Data1
        , i.Data1
        , d.Data2
        , i.Data2
    FROM inserted i
        LEFT JOIN deleted d ON i.TriggerTestID = d.TriggerTestID
    WHERE CHECKSUM(i.Data1, i.Data2) <> CHECKSUM(d.Data1, d.Data2);
END
GO

INSERT INTO dbo.TriggerTest (Data1, Data2, Data3)
VALUES ('blah', 'foo', GETDATE());

UPDATE dbo.TriggerTest
SET Data1 = 'blah', Data2 = 'fee'
WHERE TriggerTestID = 1;

SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerResult

If you're insistent on using the COLUMNS_UPDATED() function, you should not hard-code the ordinal value of the columns in question, since the table definition may change, which may invalidate hard-coded value(s).  You can calculate what the value should be at runtime using the system tables.  Be aware that the COLUMNS_UPDATED() function returns true for the given column bit if the column is modified in ANY row affected by the UPDATE TABLE statement.

USE tempdb;
IF COALESCE(OBJECT_ID('dbo.TriggerTest'), 0) <> 0
BEGIN
    DROP TABLE dbo.TriggerTest;
END
CREATE TABLE dbo.TriggerTest
(
    TriggerTestID INT NOT NULL
        CONSTRAINT PK_TriggerTest
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , Data1 VARCHAR(10) NULL
    , Data2 VARCHAR(10) NOT NULL
    , Data3 DATETIME NOT NULL
);

IF COALESCE(OBJECT_ID('dbo.TriggerResult'), 0) <> 0
BEGIN
    DROP TABLE dbo.TriggerResult;
END
CREATE TABLE dbo.TriggerResult
(
    TriggerTestID INT NOT NULL
    , Data1OldVal VARCHAR(10) NULL
    , Data1NewVal VARCHAR(10) NULL
    , Data2OldVal VARCHAR(10) NULL
    , Data2NewVal VARCHAR(10) NULL
);

GO
IF COALESCE(OBJECT_ID('dbo.TriggerTest_AfterUpdate'), 0) <> 0
BEGIN
    DROP TRIGGER TriggerTest_AfterUpdate;
END
GO
CREATE TRIGGER TriggerTest_AfterUpdate
ON dbo.TriggerTest
AFTER UPDATE
AS
BEGIN
    DECLARE @ColumnOrdinalTotal INT = 0;

    SELECT @ColumnOrdinalTotal = @ColumnOrdinalTotal
        + POWER (
                2
                , COLUMNPROPERTY(t.object_id,c.name,'ColumnID') - 1
            )
    FROM sys.schemas s
        INNER JOIN sys.tables t ON s.schema_id = t.schema_id
        INNER JOIN sys.columns c ON t.object_id = c.object_id
    WHERE s.name = 'dbo'
        AND t.name = 'TriggerTest'
        AND c.name IN (
            'Data1'
            , 'Data2'
        );

    IF (COLUMNS_UPDATED() & @ColumnOrdinalTotal) > 0
    BEGIN
        INSERT INTO TriggerResult
        (
            TriggerTestID
            , Data1OldVal
            , Data1NewVal
            , Data2OldVal
            , Data2NewVal
        )
        SELECT d.TriggerTestID
            , d.Data1
            , i.Data1
            , d.Data2
            , i.Data2
        FROM inserted i
            LEFT JOIN deleted d ON i.TriggerTestID = d.TriggerTestID;
    END
END
GO

--this won't result in rows being inserted into the history table
INSERT INTO dbo.TriggerTest (Data1, Data2, Data3)
VALUES ('blah', 'foo', GETDATE());

SELECT *
FROM dbo.TriggerResult;

--this will insert rows into the history table
UPDATE dbo.TriggerTest
SET Data1 = 'blah', Data2 = 'fee'
WHERE TriggerTestID = 1;

SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerResult;

--this WON'T insert rows into the history table
UPDATE dbo.TriggerTest
SET Data3 = GETDATE()
WHERE TriggerTestID = 1;

SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerResult

--this will insert rows into the history table, even though only
--one of the columns was updated
UPDATE dbo.TriggerTest
SET Data1 = 'blum'
WHERE TriggerTestID = 1;

SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerResult;

This demo inserts rows into the history table that perhaps should not be inserted.  The rows have had their Data1 column updated for some rows, and have had the Data3 column updated for some rows.  Since this is a single statement, all rows are processed by a single pass through the trigger.  Since some rows have Data1 updated, which is part of the COLUMNS_UPDATED() comparison, all rows seen by the trigger are inserted into the TriggerHistory table.  If this is "incorrect" for your scenario, you may need to handle each row separately, using a cursor.

INSERT INTO dbo.TriggerTest (Data1, Data2, Data3)
SELECT TOP(10) LEFT(o.name, 10)
    , LEFT(o1.name, 10)
    , GETDATE()
FROM sys.objects o
    , sys.objects o1;

UPDATE dbo.TriggerTest
SET Data1 = CASE WHEN TriggerTestID % 6 = 1 THEN Data2 ELSE Data1 END
    , Data3 = CASE WHEN TriggerTestID % 6 = 2 THEN GETDATE() ELSE Data3 END;

SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerResult;

The TriggerResult table now has some potentially misleading rows that look like they don't belong since they show absolutely no changes (to the two columns in that table).  In the 2nd set of rows in the image below, TriggerTestID 7 is the only one that looks like it was modified.  The other rows only had the Data3 column updated; however since the one row in the batch had Data1 updated, all rows are inserted in the TriggerResult table.

Alternately, as @AaronBertrand and @srutzky pointed out, you can perform an comparison of the actual data in the inserted and deleted virtual tables.  Since the structure of both tables is identical, you can use an EXCEPT clause in the trigger to capture rows where the precise columns you are interested in have changed:

IF COALESCE(OBJECT_ID('dbo.TriggerTest_AfterUpdate'), 0) <> 0
BEGIN
    DROP TRIGGER TriggerTest_AfterUpdate;
END
GO
CREATE TRIGGER TriggerTest_AfterUpdate
ON dbo.TriggerTest
AFTER UPDATE
AS
BEGIN
    ;WITH src AS
    (
        SELECT d.TriggerTestID
            , d.Data1
            , d.Data2
        FROM deleted d
        EXCEPT
        SELECT i.TriggerTestID
            , i.Data1
            , i.Data2
        FROM inserted i
    )
    INSERT INTO dbo.TriggerResult
    (
        TriggerTestID,
        Data1OldVal,
        Data1NewVal,
        Data2OldVal,
        Data2NewVal
    )
    SELECT i.TriggerTestID
        , d.Data1
        , i.Data1
        , d.Data2
        , i.Data2
    FROM inserted i
        INNER JOIN deleted d ON i.TriggerTestID = d.TriggerTestID
END
GO

1 - see https://stackoverflow.com/questions/297960/hash-collision-what-are-the-chances for a discsussion of the vanishingly small chance that the HASHBYTES calculation may also result in collisions.  Preshing has a decent analysis of this problem as well.


How to optimize NOT IN statement Why does NOT IN performs INDEX SCAN instead of INDEX SEEK?

Here's my current index on the table,

CREATE NONCLUSTERED INDEX [idx_dmcasarms_CourseList_cDesc]
ON [dbo].[courselist]
(
    [c_desc] ASC,
    [c_code] ASC
)

SQL Statement,

SELECT  c_code CourseCode
FROM    courselist
WHERE   c_desc = 'BACHELOR OF SCIENCE IN INFORMATION TECHNOLOGY'
GO

SELECT  c_code CourseCode
FROM    courselist
WHERE   c_desc NOT IN ('PRE SCHOOL', 'BASIC EDUCATION', 'SCIENCE HIGH SCHOOL')
GO

and Execution Plan,

Is there anyway I can optimize this?

It performs a scan because it has to find all values not in the one listed.
Imagine looking up in a phone book and finding all last name values that aren't 'Smith'.
Then you'll have to scan the entire book. If you instead had to find only those with the last name value 'Smith', then you could just seek to that section.

As for how to optimize, it would unfortunately be to rework the logic to avoid NOT IN if the scan is too costly to accept.
So if you somehow can change your NOT IN to an IN or = it would be better for the database engine.
Of course this can be impossible at time, and then you'll have to weigh the cost of the query vs. the cost of reworking the logic.


Database backup over sqlcmd call from batch file I'm trying to do a database backup by sqlcmd called from batch file but I can't achieve that. I'm getting error "Sqlcmd: Error: Connection failure. SQL native Client is not installed correctly. To correct this, run SQL Server Setup". I was trying this : https://stackoverflow.com/questions/5083189/sqlcmd-wont-run-is-this-a-config-issue
but every attempt of changing PATH environmental variable gives me another error :"command sqlcmd is not recognized as an internal or external command, operable program or batch file".

Can someone prompt me what I'm doing wrong ? Do I need to modify my PATH variable in some particular way ?

Batch file content :

  sqlcmd.exe -S .\EXPRESS -E -Q "USE MyDatabase GO EXEC
  sp_BackupDB"

Stored proedure which makes backup - works fine from Management Studio

USE master
go

BEGIN TRY
    SET NOCOUNT ON;

    DECLARE
        @path VARCHAR(100)

    SELECT @path = 'c:\MyDatabase.bak'
    --SELECT @path

    -- BACKUP --
    BACKUP DATABASE MyDatabase TO DISK = @path WITH INIT;
END TRY

BEGIN CATCH
    PRINT 'error';
END CATCH
GO

PATH content :

  %SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;c:\Program
  Files (x86)\Microsoft SQL Server\100\Tools\Binn\;c:\Program
  Files\Microsoft SQL Server\100\Tools\Binn\;c:\Program Files\Microsoft
  SQL Server\100\DTS\Binn\;C:\Program Files\Microsoft\Web Platform
  Installer\;C:\Program Files\Microsoft SQL
  Server\120\Tools\Binn\;C:\Program Files\Microsoft SQL Server\Client
  SDK\ODBC\110\Tools\Binn\;C:\Program Files (x86)\Microsoft SQL
  Server\120\Tools\Binn\;C:\Program Files\Microsoft SQL
  Server\120\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL
  Server\120\Tools\Binn\ManagementStudio\;C:\Program Files
  (x86)\Microsoft SQL Server\120\DTS\Binn\;C:\Program Files
  (x86)\Windows Kits\8.1\Windows Performance Toolkit\;C:\Program
  Files\Microsoft DNX\Dnvm\;C:\Pro-face\Pro-Server EX\

Do this:

1.> Open a Command Prompt window and type:
cd\
dir sqlcmd.exe /s

Our Command Prompt window returned the following:
C:\Program Files\Microsoft SQL Server\100\Tools\Binn

Note: if no sqlcmd.exe is found something happened with your SQL install.
To get you up and running I'd simply download the utilities themselves here:

https://www.microsoft.com/en-us/download/details.aspx?id=36433

2.> From the Command Prompt window command menu Mark and highlight that path that was returned and press Enter to put it in the paste buffer.

3.> Edit the System path (not the user path) within Windows and paste the path you just found just after all the %SYSTEMROOT% path references and click OK. You may need to restart your server for this to take affect.


Can't restore master/slave replication from Percona backup (slave server) A slave is restored from a master backup created with Percona xtrabackup
On the master binlog is set to expire in 14 days and this backup is less than 24 hours old
my.cnf (master)
expire_logs_days   = 14

restore script (slave)
service mysql stop

rm -r /var/lib/mysql/*

# backup is unzipped into /var/lib/mysql
xtrabackup --prepare --target-dir=/var/lib/mysql
chown -R mysql.mysql /var/lib/mysql

service mysql start

# get gtid
line=$(head -n 1 "/var/lib/mysql/xtrabackup_binlog_info")
gtid=$(echo $line | awk -F '[ ,]' '{print $3}')

mysql -u root -pPASS -e "reset master; SET GLOBAL gtid_purged='$gtid'; CHANGE MASTER TO MASTER_HOST='$master_host', MASTER_USER='repl', MASTER_PASSWORD='$master_pass', MASTER_AUTO_POSITION=1; start slave"

mysql -u root -pPASS -e "show slave status\G;"

Status
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: server.com
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File:
          Read_Master_Log_Pos: 4
               Relay_Log_File: rep-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File:
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB: mydb
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 0
              Relay_Log_Space: 154
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires. Replicate the missing transactions from elsewhere, or provision a new slave from backup. Consider increasing the master's binary log expiration period. The GTID sets and the missing purged transactions are too long to print in this message. For more information, please see the master's error log or the manual for GTID_SUBTRACT.'
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: fe51e8df-b7c4-11e9-be21-4061862b8d34
             Master_Info_File: /mnt/HC_Volume_8355755/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp: 210211 10:50:32
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set: 3ccc2392-45ed-11e7-bc98-4061862b8d34:1-35942477,
94bb04d3-6c4e-11eb-8631-96000080ccbb:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:

/var/lib/mysql/xtrabackup_binlog_info
mysql-bin.000025    217141189   3ccc2392-45ed-11e7-bc98-4061862b8d34:1-35942477,
fe51e8df-b7c4-11e9-be21-4061862b8d34:1-25059775

The gtid value extracted from the file: 3ccc2392-45ed-11e7-bc98-4061862b8d34:1-35942477
update
Have also tried to fetch the second uid in /var/lib/mysql/xtrabackup_binlog_info by doing
gtid=$(tail -n 1 "$mysql_dir/xtrabackup_binlog_info")

Nothing works.. Same error

I found the solution here
https://www.percona.com/doc/percona-xtrabackup/2.3/howtos/setting_up_replication.html
I was missing the xtrabackup --move-back command


CASE_SENSITIVE on ORACLE DB I have a ORACLE DB and when i try inserting "INDIA" and "india" as values into column which is carrying unique constraints on it , it is allowing the value to insert .

It looks like case sensitive options should be enabled in the Db , But not able to figure out where to do that so that it will not allow values with the same name either small / capital letters.

Regards
Chenna B.

But I have many columns which need to be updated in the same , so instead of that , can i change parameter at DB level which will not allow of that sort in any columns

Use a unique function-based index:

CREATE UNIQUE INDEX countryname_unique ON country (LOWER(countryname));

Much better than messing with collations.


SQL Profiler plus Include Actual Execution Plan Is there any option to show "Include Actual Execution Plan" plus query cost and recommended indexes in SQL Profiler?

I see Showplan XML, but it shows only the execution plan without recommended indexes :/

It's working perfect when I run the query from SQL Server Management Studio and Include Actual Execution Plan.

FWIW, the missing index info is there is is just Profiler that doesn't bother to show it. If you dig into the underlying XML you will see it. Ans since Profiler has been deprecated and MS haven't done any work on it since 2010, we can't expect any change here. If you use Extended Events, the "new" (if we consider 2008 being new) trace engine, then you will find that the missing index is there as well. But the GUI for XE doesn't show the graphical representation for the plan in the first place, only the raw XML. I suggest you follow Erik's recommendation, most probably a more productive path.


Count Total Transaction each specific row even if 0 I have a 3 Columns namely:

Payment Channel
Payment Name
Total Transaction

The Total Transaction will be the COUNT of each Payment Channel and Payment Name.

In the Payment Channel, values have PC01-PC09.

I want my result to display all the Payment Channel even if no transactions.

Here is my SQL Script:

SELECT
    B2C_BUY_LOG.PG_CHANNEL AS Payment_Channel,
    COMM_CODE.CODE_NAME AS Payment_Name,
    COUNT(B2C_BUY_LOG.PAY_ID) AS Total_Transaction
FROM B2C_BUY_LOG
INNER JOIN B2C_BUY_HIST ON B2C_BUY_LOG.PAY_ID = B2C_BUY_HIST.PAY_ID
INNER JOIN COMM_CODE ON B2C_BUY_LOG.PG_CHANNEL = COMM_CODE.CODE
WHERE B2C_BUY_LOG.RET_CODE = 1
AND B2C_BUY_LOG.PAY_ID LIKE '190220%'
AND COMM_CODE.CODE IN('PC01', 'PC02', 'PC03', 'PC04', 'PC05', 'PC06', 'PC07', 'PC08', 'PC09')
GROUP BY Payment_Channel, Payment_Name;

Here is the result of my Query:

Payment_Channel   Payment_Name    Total_Transaction
PC01              Name-1          14
PC02              Name-2          2
PC03              Name-3          7
PC04              Name-4          9
PC06              Name-6          21
PC08              Name-8          18
PC09              Name-9          95

This query returns only the match values between the joined tables because it is INNER JOIN and the PC05 and PC07 is missing on the result because it has no transaction. I also tried different JOINS.

How can I display the PC05 and PC07 with a count of 0 if no transaction?

Thanks!

SELECT
    channels.channel AS Payment_Channel,
    COMM_CODE.CODE_NAME AS Payment_Name,
    COUNT(B2C_BUY_LOG.PAY_ID) AS Total_Transaction
FROM (           SELECT 'PC01' channel
       UNION ALL SELECT 'PC02'
       UNION ALL SELECT 'PC03'
       UNION ALL SELECT 'PC04'
       UNION ALL SELECT 'PC05'
       UNION ALL SELECT 'PC06'
       UNION ALL SELECT 'PC07'
       UNION ALL SELECT 'PC08'
       UNION ALL SELECT 'PC09') channels
LEFT JOIN B2C_BUY_LOG ON B2C_BUY_LOG.PG_CHANNEL = channels.channel
LEFT /* or INNER */ JOIN B2C_BUY_HIST ON B2C_BUY_LOG.PAY_ID = B2C_BUY_HIST.PAY_ID
INNER /* or LEFT */ JOIN COMM_CODE ON B2C_channels.channel = COMM_CODE.CODE
WHERE B2C_BUY_LOG.RET_CODE = 1
AND B2C_BUY_LOG.PAY_ID LIKE '190220%'
GROUP BY Payment_Channel, Payment_Name;


Best data modelling approach to handle redundant foreign keys in a database about Surveys, Questions and Responses I am looking for advice on the best relational modelling approach to store surveys, questions, and responses.

I am looking for which of the two approaches below looks best, or an alternative approach to either.

I have at least these entities:

question
survey
person

And at least these relationships:

Each survey has 1 or more questions.
Each question may be used in 0 or more surveys.
Each person may take 0 or more surveys.

Here is where I run into trouble: how to model the responses to survey questions made by a person.

Here are two approaches I've considered, neither of which seems very good to me.  The diagrams here are greatly simplified to illustrate the issue.

Approach 1:

What I don't like about this approach:

The survey_person_question_response table has two different columns that refer to a survey: survey_question_survey_id and survey_person_survey_id

It would be an error to have different survey_id's referenced in one row for these two columns.  The survey_question must be from the same survey as the person took in survey_person. I can't see a good way to enforce this.

It seems like what I am doing here is making a relationship between two relationships.  That feels wrong to me for some reason.

Approach 2:

Try to avoid two FKs from approach 1 that should refer to the same value...

What I don't like about this approach:

There is no enforcement that the question_id and survey_id FKs are from a valid survey_question pair
There is no enforcement that the survey_id and person_id FKs are from a valid survey_person pair

Any advice on:

Whether one of these approaches is a typical approach
The pros and cons of one of these approaches over the other
A better way to arrange this data entirely

Would be greatly appreciated!

As per my understanding of your specifications, your business environment involves a conceptual-level ternary relationship. In this regard, you need to define:

the relationship  (or association) type between the entity types Person and Survey;
the relationship type between Survey and Question;
the relationship type that establishes the connection between the two aforesaid relationship types and, as a consequence, between Person, Survey and Question, i.e., Response (a shorter name that simplifies interpretation, from my point of view).

So, I consider that you are on the right track with your Approach 1, although it requires some small (yet important) refinements in order to make it more accurate. I will detail such refinements and other relevant considerations in the following sections.
Business rules
Let us expand the applicable business rules a bit and reformulate them in the following way:

A Person registers in zero-one-or-many Surveys
A Survey gets the registration of zero-one-or-many Persons
A Survey is integrated by one-to-many Questions
A Question integrates zero-one-or-many Surveys
A Question receives zero-one-or-many Responses
A Response is provided by exactly-one Person in the context of exactly-one Survey

Expository IDEF1X diagram
Then, I have created the IDEF1Xa diagram that is presented in Figure 1, which synthesizes the business rules formulated above:

a Integration Definition for Information Modeling (IDEF1X) is a highly recommendable modeling technique that was established as a standard in December 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on theoretical work authored by the sole founder of the relational model, i.e., Dr. E. F. Codd and also on the entity-relationship view developed by Dr. P. P. Chen.

The PersonSurvey relationship
As I see it, the PersonSurvey relationship is required to provide a means of authorization so that a Person can take part in a given Survey. In this way, once a certain Person has been registered in a specific Survey, he or she is authorized to provide Responses to the Questions that integrate the respective Survey.
The SurveyQuestion relationship
I assume that the property (or attribute) called suvery_question.question_number in your diagram is used to represent the Order of presentation of a given Question instance with respect to a particular Survey. As you can see, I have denoted such property as SurveyQuestion.PresentationOrder, and I think that you should prevent that (i) two or more Question.QuestionNumber values share (ii) the same PresentationOrder value in (iii) the same SurveyQuestion occurrence.
To portray that need, I have included a composite ALTERNATE KEY (AK) in the box representing this entity type, which is comprised of the combination of properties (SurveyNumber, QuestionNumber, PresentationOrder). As you are well aware, a composite AK can be declared in a logical DDL design with the aid of a multi-column UNIQUE constraint (as I exemplified in the SurveyQuestion table that is part of the expository DDL layout expounded a few sections below).
The Response entity type
Yes, with the Response entity type I am depicting a relationship between two other relationships; it may seem awkward at first glance but there is nothing wrong with this approach, as long as it (a) represents the features of the business context of interest accurately and (b) is represented properly in a logical-level layout.
Yes, you are totally correct, it would be an error to portray that part of the scenario at the logical level of abstraction by means of two Response.SurveyNumber (or, say, Response.SurveyId) values referenced from two different columns in the same Response row.
Derived logical SQL-DDL layout
-- You should determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on your business context characteristics.

-- As one would expect, you are free to make use of
-- your preferred (or required) naming conventions.

CREATE TABLE Person (
    PersonId        INT      NOT NULL,
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    GenderCode      CHAR(3)  NOT NULL,
    BirthDate       DATE     NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Person_PK PRIMARY KEY (PersonId),
    CONSTRAINT Person_AK UNIQUE      (
        FirstName,
        LastName,
        GenderCode,
        BirthDate
    )
);

CREATE TABLE Survey (
    SurveyNumber    INT       NOT NULL,
    Description     CHAR(255) NOT NULL,
    CreatedDateTime DATETIME  NOT NULL,
    --
    CONSTRAINT Survey_PK PRIMARY KEY (SurveyNumber),
    CONSTRAINT Survey_AK UNIQUE      (Description)
);

CREATE TABLE PersonSurvey (
    PersonId           INT      NOT NULL,
    SurveyNumber       INT      NOT NULL,
    RegisteredDateTime DATETIME NOT NULL,
    --
    CONSTRAINT PersonSurvey_PK         PRIMARY KEY (PersonId, SurveyNumber),
    CONSTRAINT PersonSurveyToPerson_FK FOREIGN KEY (PersonId)
        REFERENCES Person (PersonId),
    CONSTRAINT PersonSurveyToSurvey_FK FOREIGN KEY (SurveyNumber)
        REFERENCES Survey (SurveyNumber)
);

CREATE TABLE Question (
    QuestionNumber  INT       NOT NULL,
    Wording         CHAR(255) NOT NULL,
    CreatedDateTime DATETIME  NOT NULL,
    --
    CONSTRAINT Question_PK PRIMARY KEY (QuestionNumber),
    CONSTRAINT Question_AK UNIQUE      (Wording)
);

CREATE TABLE SurveyQuestion (
    SurveyNumber       INT      NOT NULL,
    QuestionNumber     INT      NOT NULL,
    PresentationOrder  TINYINT  NOT NULL,
    IsMandatory        BIT      NOT NULL,
    IntegratedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT SurveyQuestion_PK PRIMARY KEY (SurveyNumber, QuestionNumber),
    CONSTRAINT SurveyQuestion_AK UNIQUE      (
        QuestionNumber,
        SurveyNumber,
        PresentationOrder
    ),
    CONSTRAINT SurveyQuestionToSurvey_FK   FOREIGN KEY (SurveyNumber)
        REFERENCES Survey   (SurveyNumber),
    CONSTRAINT SurveyQuestionToQuestion_FK FOREIGN KEY (QuestionNumber)
        REFERENCES Question (QuestionNumber)
);

CREATE TABLE Response (
    SurveyNumber     INT      NOT NULL,
    QuestionNumber   INT      NOT NULL,
    PersonId         INT      NOT NULL,
    Content          TEXT     NOT NULL,
    ProvidedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Response_PK                 PRIMARY KEY (SurveyNumber, QuestionNumber, PersonId),
    CONSTRAINT ResponseToPersonSurvey_FK   FOREIGN KEY (PersonId, SurveyNumber)
        REFERENCES PersonSurvey   (PersonId, SurveyNumber),
    CONSTRAINT ResponseToSurveyQuestion_FK FOREIGN KEY (SurveyNumber, QuestionNumber)
        REFERENCES SurveyQuestion (SurveyNumber, QuestionNumber)
);

Two composite FOREIGN KEYs in the Response table
This is, probably, the most important point to discuss: the references made from a given Response row to

SurveyQuestion.SurveyNumber, and
SurveyPerson.SurveyNumber

must have matching values. As far as I am concerned, the best option to enforce this condition in a declarative way is by making use of two composite FOREIGN KEYs (FKs).
As shown in the DDL design, the first FK is making a reference to the PersonSurvey table PRIMARY KEY (PK), i.e., (PersonId, SurveyNumber), and is conformed by the columns Response.PersonId and Response.SurveyNumber.
The second FK is pointing to the SurveyQuestion table PK, i.e., (SurveyNumber, QuestionNumber), and is, accordingly, made up of the columns Response.SurveyNumber and Response.QuestionNumber.
In this way, the Response.SurveyNumber column is quite instrumental since it is used as part of a FK reference in two different constraints.
With this method, one ensures database management system-guaranteed referential integrity from

(a) Response to the PersonSurvey;
(b) Response to the SurveyQuestion; and
(c) each of the tables representing an associative entity type to the tables standing for independent entity types, namely Person, Survey and Question.

Derived data to avoid update anomalies
I have noticed in your diagram two elements that I esteem are worth mentioning. These elements are related to two PersonSurvey columns that can (should) be derived.
In that regard, you can derive the PersonSurvey.IsStarted datum by querying if a given Person occurrence has provided one or more Responses to Questions that integrate an exact Survey via the SurveyQuestion table.
And you can also obtain the PersonSurvey.IsCompleted data point by determining if a given Person instance has supplied a Response to all the Questions that cointain a value of 'TRUE' in the IsMandatory column in a specific SurveyQuestion row.
By way of the derivation of these values, you are preventing some update anomalies that would have eventually arisen in case you had kept such values in the SurveyQuestion column.
Important consideration
As @Dave rightly points out in his comment, if you face a future requirement demanding the management of different kinds of responses that imply managing dates, numeric values, multiple choice, and another possible aspects, you will have to extend this database layout.


MySQL: Can't find .ibd files after turning on innodb_file_per_table Question:

This a question about MySQL.  Let me give the specifics of my setup:

Mac OSX 10.6.8
MySQL 5.5.28
Workbench 5.2.44 for all administration
InnoDB engine exclusively

In order to manage limited hard drive space on my laptop, I'd like to turn on the innodb_file_per_table option so that each table gets its own .ibd file instead of all data going into the ibdata1 file.  I researched various sources, including Stackoverflow, and they all suggest the same basic steps:

export existing tables
turn off MySQL server
add innodb_file_per_table under the [mysqld] heading of the my.cnf file
delete ibdata1, ib_logfile0, and ib_logfile1 - which for me are located in /usr/local/mysql/data
restart MySQL server
at this point, newly created tables or tables imported from the original dump should each get there own .ibd file.

I followed the steps and there appears to be no problem.  Various checks that I did within Workbench all indicate that the innodb_file_per_table option is turned on.   However, when I create new tables or import the old tables, I'm not seeing any new .ibd files, and it seems like ibdata1 is growing.

When I create/import a myschema, the following directory is created:

/usr/local/mysql/data/myschema

It is my assumption that if I create myschema.table1 that a the file table1.ibd will be added to the directory above, and that all the data associated with that table will be stored in this file.  However, I can't check this assumption because in both Console and Finder, I get "permission denied" messages when I try to look inside the myschema directory.  Moreover, in Finder, the folder icon has a red/white minus sign on it.

Resolution & Observations:

It turns out that my error was a simple one, and that the steps above to get innodb_file_per_table are correct. My issue was simply that I didn't have permissions to /usr/local/mysql/data/myschema - this was indicated by the red minus sign on the folder icon - which is easily resolved in Finder (for details just Google "folder with red minus sign mac").

Here are some things I learned along the way:

Even with innodb_file_per_table turned on, ibdata1 does still grow (and doesn't shrink) as you add data, but at a much slower rate that the actual table.  This makes sense because the Innodb engine still needs this file, even when innodb_file_per_table is turned on, so things should be written to it.
My implementation works even though I don't include the line innodb_data_home_dir = /usr/local/mysql/data in the my.cnf file.  However, I do have datadir = /usr/local/mysql/data - but this must have been a default since I didn't set it.

The .ibd files will be under the innodb_data_home_dir under a subdirectory, the subdirectory name matching the database name.

Given innodb_data_home_dir=/etc/local/mysql/data and innodb_file_per_table is set:

 SHOW VARIABLES LIKE 'innodb_data_home_dir'
 SHOW VARIABLES LIKE 'innodb_file_per_table'

then when table test.mytable is created, the .ibd file will be located in /etc/local/mysql/data/test/mytable.ibd

(It's possible that the ibdata1 file will grow to accommodate rollback for transactions. When a transaction is run, pre-change copies of blocks get recorded, in case you issue a ROLLBACK, and also for the benefit of concurrent transactions (with an appropriate isolation levels), that need to reference only committed changes, and not any uncommitted changes.)


MongoDB deployment best practices I had a few questions around deployment best practices and how folks are using mongodb today. Feel free to add any other comments you can think off.

No of databases per mongod instance. Is the recommendation to create multiple databases in a single mongod instance or to run many mongod's on the same box for the different DBs. Also how are folks running doing this for dev and prod envs?
Ideal number of shards per cluster. How does one decide the ideal number?
Recommended replicaset. 3 or more and how does one decide?
Best way to backup and restore. Is it to use mongodump or is something else? Also do you usually do it on the cluster/instance level vs database level?
Cloning of databases or clusters. Does anyone actively clone and do you clone a db or the whole cluster/instance?

In order :

There is one reason to have multiple mongod instances in one machine (other than machine efficient repset setups) and that one reason is the process wide write lock. If you run into a situation where you're bottlenecked by the write lock rather than any hardware resource bottleneck you can go for multiple mongod instances. In all other situations it's usually more practical to have one mongod process, especially given it's memory mapped based storage engine.
There's no ideal number. Adding shards (almost) linearly scales up write and read performance for sharded collections. It's a basic horizontal scaling solution and follows the rules for that sort of scaling.
Replica sets always consist of at least 3 members although 1 of them can be an arbiter (votes but does not replicate). If you add more you gain read performance if your application uses slaveOk flagged queries. Again, ideal number depends on your use case.
Depends. Most people use a hot backup scheme of some sort. There's no difference between MySQL backup conventions and MongoDB here.
Depends on whether or not you want to clone a single database or the entire cluster? Both are well documented.


SQL 2008/R2 Extended Events - Row Count I need to setup a sql audit to capture all queries running on a server from any unauthorized locations, and one of the columns that is required is Row Count.

In sql trace, this is possible. But I can't seem to find corresponding column in extended events. I looked into these 2 DMVs:

select * from sys.dm_xe_object_columns
where object_name = 'sql_statement_completed'

select pkg.name as PackageName, obj.name as ActionName
from sys.dm_xe_packages pkg
inner join sys.dm_xe_objects obj on pkg.guid = obj.package_guid
where obj.object_type = 'action'
order by 1, 2

But all they have is reads/writes, but not actual number of rows.

Is it possible to get this info in SQL 2008 or SQL 2008 R2 ?

That's a bit insane to capture all running queries no? Queries from a specific user or including the ones from sproc, ETL, etc? If they are coming from an unauthorized location, that tells me that your DB is not locked down enough. If one instance has 20 DBs, each of the service accounts, domain users or even SQL login should only been given access to the database pertain to them. The people who are the owner of these accounts (who knows the password) are usually gone trusted and have gone through background checks.

Otherwise, they are 3rd party SQl tools that like idera dm , SQL Sentry and even pefromance dashboard that comes with SSMS 2016 that keep track of queries applied to the database.


Subquery or Inner Join? I have this simple query that finds all transactions from accounts belonging to a specific login, with some extra conditions on the transactions.

SELECT t.id, t.date, t.amount, t.description FROM transaction t
INNER JOIN account ac ON t.account = ac.id AND ac.login_id = ${loginId}
WHERE t.processed = false AND t.updated_by_user = false
AND t.category = 'uncategorized' ;

Will this query perform faster with a subquery on accounts, for example:

SELECT t.id, t.date, t.amount, t.description FROM transaction t
INNER JOIN (SELECT id FROM account WHERE login_id = ${loginId}) ac ON t.account = ac.id
WHERE t.processed = false AND t.updated_by_user = false
AND t.category = 'uncategorized' ;

I'd appreciate some insightful comments on this, thanks in advance!

Use the INNER JOIN whenever possible because it's easier to read. It otherwise should not make a difference.


UTF8 vs ASCII or custom binary format: high-performance optimization on very big tables The summary of my question is whether there are advantages, even if minimal, to use ASCII, or even a smaller format designed yet created specifically for the situation, instead of UTF-8 for strings.

Is possible to use webserver who will access the data translate one ASCII string to a more compact format.

On this question, just 1-3 tables on database handle a massive amount of data, so any byte can make difference if could fit on memory instead of access disk. The information will be accessed via a RESTfull service

Read vs Writes:

Project need much more read than writes. But writing has a peculiarity: every 10 seconds an amount between 40-300 new rows are inserted into the main table. These could be written in parallel, since do not depend of each other.

Memory vs Disk usage:

Recent inserted rows, that will be used immediately, will be inserted also on a cache for the webservice use, so no need to read they again. But for search on old records, the database will need, and it should be fast.

This is why I suppose that use less bytes to store some fields will make diference: even for larger amount of data, will be more easy for fit on memory.

If I cannot fit data on memory, and database cannot abstract to me some speed, or I will need to force slow table/partition scans each 10 seconds for just one user, or I will be forced to do single select and cache it on Webserver, but this break concept of "stateless" from REST concept.

Characters that must be supported

0-9,A-Z, "-", "_". Maybe will need "a-z". Just 38 or 64 caracters, and never more than this.

For now, most columns are

CHAR(3), CHAR(6), VARCHAR(8), VARCHAR(10).

Examples:

ABC, XYZ
AB-EFG, XY-XPT
ABC123457
E47F6C, 34210A, E48D37 (hex strings, maybe exist a format specific to this?)

Technologies used

Database will be MariaDB. Maybe part of RAW data will be on some NoSQL database. The language of webservice does not really makes diference here, but will be PHP 5.4 with framework Phalcon PHP.

Different types of cache could be used, from Varnish-cache to APC/Mencached until caches inside database.

My initial hypothesis:

One field with VARCHAR(10) needs 80bits. If I use one custom table to use just 64 or less instead 255 characters, I save around 4 times, so instead of 80bits will use just 20bits.

Your list of characters that must  be supported clearly indicates you need nothing more than plain ascii.

If you want to stored this as text, then this ascii is your most compact way. But here are a few clarifications:

VARCHAR(10) does not "need" 80 bits. It may need 80 bits, if all characters are used, under ascii character set. If you only store 3 characters (e.g. 'abc'), then it only needs 24 bits.
utf8 does not store more space than ascii when ascii characters are used. 'abc' on both utf8 and ascii encoding are 3 bytes long. That's why it's called utf-8: it attempts to only use 8 bits when possible.
However on temporary tables (vanilla MySQL; solved on Percona Server; I'm not sure about MariaDB) a utf8 character will take 3 bytes no matter what; same for MEMORY tables. So best use ascii if it fits your needs.
You could compress further. You can use the COMPRESS() function, for example, or encode via your own method (if you only need 64 different characters, this means you're using 6 bits. This means for every 3 bytes (24 bits) you use today, you could squeeze in another byte (using 2 buts from each of the 3 bytes). So you can certainly compress by 25%, and possibly more. But this leaves you with BINARY/VARBINAY types, which are not as easy to work with: you'll have to always compress/decompress, you will not be able to index the text (alphabetically, that is; you can certainly put indexes on the column).
The rest of the tools you mentioned are imho irrelevant; by the time your data reaches varnishd, your texts are uncompressed. Possibly so for PHP as well.


Help designing tables for storing lab test results: values include numbers, number We're using MySQL 5.5 and attempting to design tables to store the results from tests run in a lab. The results of some of the tests can be either a plain number, e.g. "5", or a range such as "< 5" or "> 25", and this is where we're getting stuck. The < and > values are due to the limits of the sensitivity of the tests; above or below a certain threshold for each test the exact amount can't be detected. Values right at the threshold are possible, so for the example above, both < 5 and 5 would be valid values for that particular test, and we need to be able to distinguish between these values.

There's a set list of tests, so needing to add new ones easily isn't a concern. Our preliminary design has a TestResults table and each test is a column in that. We were thinking of maybe adding a column for the tests that require < and > just to hold those values or possibly having "flag" values in the application code that signals a value is below/above the threshold and having the thresholds in a reference table. Neither of these solutions seems that great though. We don't want to just store the results as strings because we'll need to be able to get statistics, averages, etc. on the results.

Anyway we were hoping someone out there had done something like this and had some ideas on the best way to do it. We've been Googling (e.g. on LIMS) and haven't been able to find anything on database designs, but it seems like it wouldn't be that uncommon of a problem... Any ideas on the most appropriate way to model this type of data would be greatly appreciated.

At first glance, it seems like the most appropriate thing to do would be to make the column storing the test result value nullable, and declare an additional column, perhaps an ENUM, for the out-of-range condition.  For example

CREATE TABLE ...
   ...
   result_value DECIMAL(6,3) DEFAULT NULL,
   out_of_range ENUM('low','high') DEFAULT NULL,
   ...

When a test comes back with an out-of-range result, it would seem like you wouldn't want to store anything in the `result_value` column, because when it comes to averages or other statistics the low or high threshold value would completely skew your average... but when you take the AVG() of a data set that includes NULL the denominator used to calculate the average is the total number of not-NULL values... so AVG(5,NULL,NULL,10,NULL) would be 7.5.

Setting a flag that indicates that your result was, instead, out of range, would allow you to easily tally those values separately, and an ENUM column with a small number of possible values requires only 1 byte of storage per row... the "low" or "high" would actually be stored as the byte 0x01 or 0x02, with the corresponding labels stored in the table definition only.

When the minimum threshold is "5" the number "5" is not a meaningful number when it comes to analysis, so I would think that you wouldn't likely want to store that in the `result_value`.  If you did, your analytic queries would have to go to extra steps to exclude those from calculations.

I would suggest that you do need a table that specifies the high and low boundaries for each test, because such a table could be used to constrain the data going into the results table via triggers, blocking the insertion of out-of-range data, in addition to being valuable information when doing analysis.

"But we already know those values."  The database also needs to "know" those values, because subsequent analysis will be more straightforward if those values can be joined and understood by a query.

Also, I would suggest that if there is any possibility that the sensitivity, selectivity, granularity, precision, range, or whatever the proper term might be, of any test could change -- due to improved technology or whatever the reason, that you consider each test to actually be a 1 of N possible subtypes of a type of test (where initially, N is 1).  If the possible range (or other property) of a result is different, then that's technically, arguably, a different test, even though it's measuring the same thing... and these are the kinds of things that are a small hassle now, and a much bigger hassle later.

  needing to add new ones easily isn't a concern.

Famous last words.  Speaking of bigger hassles later, that's generally not a good position to hold, even if it seems unshakable now.

A final word about a common design error I sometimes encounter: If you are using the FLOAT or DOUBLE data types, reconsider that decision, because they are imprecise.  A DECIMAL column has a fixed precision and stores exactly the value you insert.  Data stored in FLOAT and DOUBLE columns are stored as approximate values which can lead to problems with comparisons.  This limitation is one of floating point arithmetic in general, and not of MySQL in particular; it is something with which you may already be familiar but I thought it worth mentioning.


Is REINDEX dangerous? I've been trying to COUNT(*) a table with 150,000 rows that has a Primary key. It tool about 5 minutes, so I figured out this is an indexing problem.

Citing the PostgreSQL manual:

  REINDEX is similar to a drop and
  recreate of the index in that the
  index contents are rebuilt from
  scratch. However, the locking
  considerations are rather different.
  REINDEX locks out writes but not reads
  of the index's parent table. It also
  takes an exclusive lock on the
  specific index being processed, which
  will block reads that attempt to use
  that index (...) The subsequent CREATE INDEX
  locks out writes but not reads; since
  the index is not there, no read will
  attempt to use it, meaning that there
  will be no blocking but reads might be
  forced into expensive sequential
  scans.

From your own experience, can you tell:

is REINDEXING dangerous? Can it harm the data consistency?
Can it take a lot of time?
Is it a probable solution to my scenario?

Update:

The solution that worked for us was recreating the same index with a different name, then deleting the old index.

The index creation is very fast, and we've reduced the index size from 650 MB to 8 MB. Using a COUNT(*) with between takes only 3 seconds.

Reindexing is not dangerous and can not harm data consistency.  However, if you have time critical writes, you may loose data if the table is locked and the DML is aborted.

Reindexing should not take a lot of time, but will usually involve reading the whole table, sorting the index fields and writing a new index.  Given the time for COUNT(*) it will likely take five minutes or more.

It is unlikely this is an indexing problem.  COUNT(*) should use a table scan in which case no index is read.  I would expect you have an IO problem of some sort.

Try using COUNT(1) or COUNT(pk_field) which may use the index.

If you are running on a Unix or Linux platform you may want to monitor disk activity with sar.  You might also have a failing disk which can cut IO rates dramatically.

Tables with large objects can also increase IO significantly to construct the records for COUNT(*).


Cancel Query in remote ssms from ssms in my pc We connect to our customer's database server with vpn. Sometimes we manage Sql server to connect database server or to connect with ssms in our pc's.
I executed a query with ssms in database server, then the connection has gone down. I have tried to connect many times but failed. I have to cancel this query.
I can connect with ssms in my pc's.
Can I cancel query with ssms in my pc?

Step 1:  Get query session id using below query

SELECT sqltext.TEXT,
req.session_id,----- SPID
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time,req.start_time,db_name(database_id) DatabaseName
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
where [status] <> 'background'
and command<> 'TASK MANAGER'

Step 2: use kill command to kill the process execution

Kill(53) -- KIll (SPID)


MongoDB - Adding a new server to an existing replica set with auth enabled Short version: Is there a way to add a new SECONDARY server to an existing production MongoDB replica set without downtime, and without temporarily disabling the "auth" and "keyfile" config settings on the existing servers?

Long version: My current production environment consists of three Windows 2012 R2 DataCenter servers running MongoDB version 2.4.9 as a service:

PRIMARY   - ServerA.example.org:27017
SECONDARY - ServerB.example.org:27017
ARBITER   - ServerC.example.org:27017

I have two MongoDB user accounts in the environment:

{ "_id" : ObjectId("XXXXXXXXXX"), "pwd" : "XXXXXXXXXX", "user" : "mongo-admin", "roles" : [  "userAdminAnyDatabase" ] }
{ "_id" : ObjectId("XXXXXXXXXX"), "pwd" : "XXXXXXXXXX", "user" : "mongo-cluster-admin", "roles" : [  "clusterAdmin" ] }

And the config file on the existing servers looks like this:

logpath=C:\mongo\logs\mongo.log
dbpath=C:\mongo\data
replSet=rs
oplogSize=700
port=27017
auth=true
keyFile=C:\mongo\config\keyfile.txt

I want to add ServerD and ServerE as new SECONDARY servers.

I've tried the following from my workstation and from the local console on ServerA, with and without the "auth" and "keyfile" in the config on ServerD, but I get the same result in all cases:

D:\MongoDB\bin>mongo ServerA.example.org:27017
MongoDB shell version: 2.4.7
connecting to: ServerA.example.org:27017/test
> use admin
switched to db admin
> db.auth("mongo-cluster-admin", "XXXXXXXXXX")
1
rs:PRIMARY> rs.add("ServerD.example.org:27017")
Mon May 19 11:41:42.050 count failed: { "ok" : 0, "errmsg" : "unauthorized" } at src/mongo/shell/query.js:180

The only way I've been able to get this working in a test environment is if I disable the "auth" and "keyfile" settings on all five servers, then add the new servers to the replica set using "rs.add()" on the PRIMARY, and finally re-enable "auth" and "keyfile" on all servers. This requires a couple of restarts of the MongoDB service on each box to update the config settings, which I really want to avoid.

Is there a way to add the new SECONDARY servers to the replica set without changing the "auth" and "keyfile" settings on the existing servers first?

Just add the new secondary with auth enabled and using identical keyfiles from the start.  If you do that, they will connect, use the keyfile to authenticate, and then sync from scratch - there is no reason to change the original members of the set, just make sure that the new ones you add have the correct (identical) keyfile as the original members.

The other thing to make sure of is that the user you are authenticated as has the correct permissions to do the additions (since turning off auth would solve that issue also).  For reference, the built in role with the relevant permissions is clusterManager for 2.6+ - clusterAdmin should also work, and has the advantage of existing in 2.4.

Per the comments discussion, the caveat with clusterAdmin in 2.4 is that although you have permission to run the relevant commands, you will need to have read privileges on the local database (per the Combined Access section of the 2.4 docs) to use the rs.conf() command.  It's not strictly required but without it you will have to construct the entire config document from scratch for a reconfiguration command which is certainly inconvenient.


Returning case-sensitive input of case-insensitive array match Taking a JSON table with some simple data:

CREATE TABLE jsonthings(d JSONB NOT NULL);
INSERT INTO jsonthings(d) VALUES('{"id":1,"email":"Test1@test.com"}');
INSERT INTO jsonthings(d) VALUES('{"id":2,"email":"Test2@test.com"}');
INSERT INTO jsonthings(d) VALUES('{"id":3,"email":"Test3@test.com"}');

I have a requirement to obtain the rows which case-insensitively match a set of email addresses:

SELECT d FROM jsonthings
WHERE (d->>'email')::CITEXT = ANY(ARRAY['TEST1@TEST.COM','test3@test.com']::CITEXT[]);

However, the data I return should contain the email addresses in the case in which they were passed to the query rather than the case in which they are stored within the database. For example, the above query provides the results:

{"id": 1, "email": "Test1@test.com"}
{"id": 3, "email": "Test3@test.com"}

But I need it to return the results:

{"id": 1, "email": "TEST1@TEST.COM"}
{"id": 3, "email": "test3@test.com"}

How can I obtain the matching item in the array and use it in the results?

First of all, you should mention that you are using the additional module citext that provides the case insensitive text data type citext, which is not strictly needed here. The solution below only requires standard Postgres 9.4.

Key elements are jsonb_each_text() to unnest jsonb values into key/value pairs and json_object_agg() to re-assemble the JSON value with changed parts:

SELECT j.id, json_object_agg(d.key, CASE WHEN d.key = 'email'
                                         THEN t.email ELSE d.value END)::jsonb AS d
FROM   jsonthings j
JOIN   unnest('{TEST1@TEST.COM,test3@test.com}'::text[]) t(email)
                                                ON lower(t.email) = lower(j.d->>'email')
     , jsonb_each_text(j.d) d
GROUP  BY 1;

Result:

id | d
---+---------------------------------------
1  | {"id": "1", "email": "TEST1@TEST.COM"}
3  | {"id": "3", "email": "test3@test.com"}

How?

(0.) Of yourse your table has a primary key you did not mention. Let's call it id.

Unnest the text array into individual rows and JOIN to the 'email' value of the jsonb column using lower() on both sides to make it case insensitive.
Decompose the jsonb column (only identified rows!) into key/value pairs with jsonb_each_text().
Replace original 'email' values with the search string with a CASE statement.
Finally aggregate everything back to a json object with json_object_agg() and cast to jsonb.

Aside: For a simple structured form like you present in the example, a normalized schema with scalar Postgres data types would be much more efficient:

How to perform update operations on columns of type JSONB in Postgres 9.4


Are relations slower than a big, inefficient table? I've been asked in my job to violate the first normal form (repeating groups across columns, using empty / null values) several times, "for the sake of computer processing power". In a nutshell, a "student" table should have at least 8 empty fields (e.g telephones: telephone1, telephone2, telephone3...) instead of my suggestion - a "telephone" table which holds a telephone number (and possible other metadata) and the foreign key is the student id number. My boss says that it's better to store them that way because "there are less CPU cycles and that matters in web platforms", instead of using relations. I say that, in the worst of cases, it's negligible.

In that example, using relations (suppose that the tables are filled with lots of records in a medium-sized webapp) is notably slower than using that kind of table schema?

I don't see how anyone could make such a statement without having some actual facts to back it up. If your queries are CPU bound, then you should look to find ways to reduce that bottleneck.

It sounds as if your boss feels that a denormalized database will perform best, but I don't know enough about your application to say if that is right or not. What will be the expected number of deletes, updates, and inserts for this table?

I would expect that such a denormalized design may result in a reduced amount of CPU time but would expect that your disk I/O would increase. And physical reads from disk will be much more expensive than a CPU cycle, so perhaps your boss has a very specific metric to meet (CPU) and as a result wants a very specific design? If so, I would simply build what is asked for and keep metrics on CPU cost for the queries being run. If you see an increase in time then you may want to suggest some design changes.

In fact, it is probably a good idea to get a list of all the metrics your boss wants to see, and track those over time.


What are the 'free to use' workload generators available for Oracle? A couple that I know about are

http://www.dominicgiles.com/swingbench.html and
http://hammerora.sourceforge.net/

Are there any others? I am looking for tools what can generate random workload (based on some parameters) that can be saved (possibly as an SQL file) and run against an Oracle 11gR2 db separately.

Lazer,

Here are what I have found: Load and Performance Tools. This is a huge list of tools, you can find out what product is better for you, but I could not see any tool that has been created specially for Oracle databases (on my first sight). For Sql Server

An information resource for software testers

I hope it will help you.

ADDED:

The best generator that you made yourself.


Copy SSRS Subscriptions from one SSRS server to another I am having to migrate reports and subscriptions from one SSRS server to another. I have already migrated the RDL and Datasources. I only have the subscription information to move. I am trying to use the ReportingServicesTools (https://github.com/microsoft/ReportingServicesTools) and an example from here http://wragg.io/managing-sql-server-reporting-services-subscriptions-with-powershell/ to accomplish this.  I exported the subscription information using Get-RsSubsctiption | Export-RsSubscriptionXml to a file.
I am trying to import it using Import-RsSubscriptionXml | Set-RsSubscription. When this executes, I get no messages, error or informational. When I look in the SSRS database, I don't see the subscription.
Here are the commands:
Get-RsSubscription -ReportServerUri 'http://sql2017/reportserver' -RsItem '/Cooperative Technologies/1035/1035YellowPages/Billing Reports/LookupActivityReport' | Export-RsSubscriptionXml "c:\users\logsdone\desktop\SSRS Subscriptions\1.xml"

Import-RsSubscriptionXml "c:\users\logsdone\desktop\SSRS Subscriptions\1.xml"  -ReportServerUri 'http://sql2017/reportserver'  | Set-RsSubscription -ReportServerUri 'http://sql2017/reportserver'

Thanks for any suggestions or additional documentation or examples.
Eric

you are using this PowerShell module from the gallery.
You need to use Copy-RsSubscription if the subscription on the destination does not already exist. Set-RsSubscription updates only already created subscriptions.
Get-RsSubscription -ReportServerUri 'http://sql2017/reportserver' -RsItem '/Cooperative Technologies/1035/1035YellowPages/Billing Reports/LookupActivityReport' | Export-RsSubscriptionXml "c:\users\logsdone\desktop\SSRS Subscriptions\1.xml"
Import-RsSubscriptionXml "c:\users\logsdone\desktop\SSRS Subscriptions\1.xml"  -ReportServerUri 'http://sql2017/reportserver'  | Copy-RsSubscription -ReportServerUri 'http://sql2017/reportserver' -RsItem '/Cooperative Technologies/1035/1035YellowPages/Billing Reports/LookupActivityReport'


practical effect of not validating FKs in Postgres? I need to add a foreign key definition to a very large table in Postgres.  (Let's assume for the moment that I'm positive that every FK value is actually valid.)
I know that I can specify NOT VALID in order to prevent Postgres from taking aggressive locks on the table while validating the foreign key.  Then I can go back "at leisure" and run VALIDATE CONSTRAINT as a cleanup.
What if I never bothered going back to run VALIDATE CONSTRAINT?  Is there any practical effect of not doing so?

It is conceivable that the planner could use valid foreign key constraints to optimize query plans.  I don't think that it does that today, though. I know people had been working on such things, but I don't think anything has been accepted into released code, or is likely to be in the near future.


SQL Server 2017 & External Python Modules I'm doing some PoC work with Python integration in SQL Server 2017.

I have got the basic steps working, & have completed this example successfully:
https://docs.microsoft.com/en-us/sql/advanced-analytics/tutorials/run-python-using-t-sql

For the PoC I'm trying to complete, I need to us some external python modules (for example tensorflow) that don't seem to come with the standard python libraries that are bundled with SQL Server.

In a standard python IDE, I would just do install with pip or git clone. If I do this on the server that is running SQL Server it completes successfully, but I can't seem to use the external modules from TSQL.

Error message is:

ImportError: No module named 'tensorflow'

Does anyone know if it is possible to do this?

I've tried googling, but not much coming back. I guess as this is such a new feature that there isn't a huge amount of knowledge in the community.

Just in case someone finds this question when searching the net

I logged an advisory case with MS as I needed an answer on this. They have come back to me with the following method that works:

Open a command prompt (as administrator) and navigate to the python directory (and inside the scripts directory)

cd c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\Scripts\Python

pip install <packagename>

Nb: There is a cleaner way to do this with R packages, but this functionality is not there for python yet. I would assume in later versions that a similar process will be available for python: https://docs.microsoft.com/en-us/sql/advanced-analytics/r/install-additional-r-packages-on-sql-server


SQL Server 2008 R2 High CPU We are having a SQL Server R2 Web Edition database, based on Windows Server 2008 R2 with a strong CPU and 32GB RAM DDR3.

Our database works very hard (we run around 600,000 queries in 1 minute) and its CPU is going crazy (25% to 90%).

I was looking after slow queries, found some, fixed them, some other I just canceled to check if it will help, but it didnt, CPU is still very high.

I was wondering what else could it be? I am not a DBA, but I am working with a very experiense one, he create all the indexes and re-configured the DB, but CPU is still very high, and this DBA (with more than 20 year of experiense) is kind of hopeless and dont really know how to find the problem.

I was wondering, could it be caching problem? like that queries are not cached?

Anyone know what can it be? what could we do? how can we investigate it?

Maybe its just the high amount of queries?

I'd love to get every small piece of information you can give!

I would recommend starting with a methodology like Waits and Queues. But at 10k queries per second you should expect some CPU to burn. Whether it's more than it should be is really hard to tell just from the info you gave. The methodology linked will at least help you find bottlenecks.

The biggest bang for the buck would probably be... not to send 10k queries a second. Cache in the client. Look at How StackOverflow Scales with SQL Server and see how SE caches as much as possible in the web tier layer before hitting the server.

Just in case, do a couple of DBCC SQLPERF ('SPINLOCKSTATS') and see if anything stands out.


Suggestions for SQL query repair tool or tips on building one? Not sure if this is ontopic so I figure I would ask here and if its not liked then we can close it.

Our hosting provider for a legacy application that I inherited is no longer supporting Oracle 10 and are requiring us to move to Oracle 11g.  I didn't think this would be a problem until nearly half of the SQL queries started failing.  Upon research I found that previous versions of Oracle had a bug that didn't throw an exception at malformed ANSI SQL queries, and that bug was fixed in 11g.

http://boardreader.com/thread/Ora_00918_After_Upgrade_to_11g_lgv5X1bw1c.html
https://forums.oracle.com/forums/thread.jspa?messageID=4298123

To make a long story short, the brilliant minds that came before me wrote some awful queries and only basic integration tests, and I now have a few weeks to fix about 1000 malformed queries or we are dead in the water.

I will literally set myself on fire if I have to manually fix all of these, and was wondering if you guys knew of some tools or techniques for hunting down these offending queries to make this slightly less painful.  Perhaps a special code profiler, or even a brilliant regex that can help me locate column references without an alias?

Argh! I wanted to open a chat panel to ask you some more questions, but actually inviting doesn't work unless you are already chatting. Oh well.

You have indicated that these are java strings, which means that there are two problems.

Parsing the SQL and making decisions (mark ambiguous items for user)
Finding the SQL inside the java code

Hopefully the SQL should be fairly recognizable. It exists in a string, and sql follows a fairly ridged format. Also, your non-aliased items will follow a simple format too. They will be in columnname AS aliasname. Skip those, and only add aliases for columnnames not in that format. If you load in the db schema, you even get all valid columnnames to look for. Finally, if there is an ambiguous item, then write some context to stdout, and ask the user for input.

I would suggest using python and the PLY library. You get a full blown lex/yacc combo, with an easier language than C. Open a new question on SO if you want more help/or to scream at me. ;)


How to fail over availability groups on multiple remote servers We have many SQL Server installations with availability groups. During our maintenance window, we will need to fail over all the HA groups to secondaries, patch the primary servers, then fail back, patch secondary servers. It's a tedious process to log on to each server and do the failover/failback job.

I am looking for a way to automate this process. Is there any way we can run  failover statement from our central management server to multiple linked servers, with high availability group name and remote server name as variable. Tried this and it doesn't work:

declare
@remote_server [char](24),
@hag_name  nvarchar(24),

set @hag_name  = 'group1'
set @remote_server = 'Server1'

execute ('ALTER AVAILABILITY GROUP ? FAILOVER', @hag_name) at @remote_server

This solution doesn't use a CMS, but I think it'll work.  It's a little script I put together for searching through a comma delimited list of servers for SQL Operators that should be changed as DBAs are swapped in and out.

I modified the script for multiple AG failovers. You can adapt it to suit your specific environment.

You have to build a comma delimited list of of the secondary servers and availability groups:
server1,ag1,server2,ag2,....
...and plug that into the code.

This script's output is the executable code. The output from this sample script should look like this:

(1 row affected)
:CONNECT server1
ALTER AVAILABILITY GROUP [L1] FAILOVER;
GO

:CONNECT server2
ALTER AVAILABILITY GROUP [L2] FAILOVER;
GO

:CONNECT server3
ALTER AVAILABILITY GROUP [L3] FAILOVER;
GO

:CONNECT server4
ALTER AVAILABILITY GROUP [L4] FAILOVER;
GO

Then you copy/paste this into a new SSMS window, remove the non-TSQL lines like "(1 row affected)", switch to SQLCMD mode in SSMS, and execute.

What this script really needs is an intelligent way of knowing which server is secondary, so you don't have to know it for the CDL. I'll try to put that together and post an edit here.

Of course, test this on a couple guinea pig servers.

/*
This script will create a TSQL script that you can run separately in SQLCMD mode.

Steps:
1. Edit @Servers with a comma delimted list of your servers.
2. Edit @SearchVariable1 and @SearchVariable2 with the search variables.
        If you want to search for only 1 string, then make both variables that string.
        For example 'kanjana', which is part of the name of the DBA who had this set of servers before me.
    You can add more SearchVariableN variables. Edit the dynamic SQL statement.
3.  Edit the dynamic SQL to do whatever you need.
4. Copy and paste the result into a new window, specify Query | SQLCMD mode, and execute.
    Remove any non-TSQL lines, such as '(1 row affected)'
5. If logical, you can re-run the script to check the results.

*/

USE MSDB
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'SplitString')
    AND xtype IN (N'TF'))
DROP FUNCTION SplitString
-- SELECT * FROM sysobjects WHERE id = object_id(N'SplitString')
GO

CREATE FUNCTION [dbo].[SplitString]
(
      @Input NVARCHAR(MAX),
      @Character CHAR(1)=','
)
RETURNS @Output TABLE (
      Item NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT

      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END

      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)

            INSERT INTO @Output(Item)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
      END

      RETURN
END
GO

-- User configuration variables:
DECLARE @Servers VARCHAR(MAX) = ''

-- ************ Edit these variables:
SET @Servers = @Servers +
    'server1,L1,server2,L2,server3,L3,server4,L4'

-- ************

-- other variables:
DECLARE @ThisServer VARCHAR(100)
DECLARE @ThisL VARCHAR(100)
DECLARE @SQL VARCHAR(MAX) = ''
DECLARE @B CHAR(2) = CHAR(13) + CHAR(10)

DECLARE MyCursor CURSOR
    FOR SELECT * FROM dbo.SplitString(@Servers,',')
OPEN MyCursor

FETCH NEXT FROM MyCursor INTO @ThisServer
FETCH NEXT FROM MyCursor INTO @ThisL

WHILE @@FETCH_STATUS = 0
BEGIN
    -- PRINT @ThisServer
    SET @SQL = @SQL + ':CONNECT ' + @ThisServer + @B
    SET @SQL = @SQL + 'ALTER AVAILABILITY GROUP [' + @ThisL + '] FAILOVER;'
    SET @SQL = @SQL + @B + 'GO' + @B + @B

    FETCH NEXT FROM MyCursor INTO @ThisServer
    FETCH NEXT FROM MyCursor INTO @ThisL
END

PRINT @SQL

CLOSE MyCursor
DEALLOCATE MyCursor

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'SplitString')
    AND xtype IN (N'TF'))
DROP FUNCTION SplitString


Find out recovery model changes I'd like to work around instances where a database were changed to simple and then reverted back to full or bulk logged recovery models. This is for my backup maintenance plan, as obviously any following log/differential backups will fail. Are there any DMOs available which monitor recovery model changes?

You can see this information in the SQL Server Log.  This link has a query text that shows an example of searching for that specific item.

https://www.sqlrx.com/find-out-who-changed-the-database-recovery-model/

For your convenience, I have included the script in the above link here.  This is not my script, but the one from the site above, with some formatting fixes.

DECLARE @tracefile VARCHAR(500);
DECLARE @ProcessInfoSPID VARCHAR(20);

CREATE TABLE [dbo].[#SQLerrorlog]
    (
        [LogDate] DATETIME NULL
        , [ProcessInfo] VARCHAR(10) NULL
        , [Text] VARCHAR(MAX) NULL
    );

/*

Valid parameters for sp_readerrorlog

1 – Error log: 0 = current, 1 = Archive #1, 2 = Archive #2, etc…

2 – Log file type: 1 or NULL = error log, 2 = SQL Agent log

3 – Search string 1

4 – Search string 2

Change parameters to meet your needs

*/

--Read error log looking for the words RECOVERY

--and either FULL, SIMPLE or BULK_LOGGED indicating a change from prior state
INSERT INTO #SQLerrorlog
EXEC sp_readerrorlog 0, 1, 'RECOVERY', 'FULL';

INSERT INTO #SQLerrorlog
EXEC sp_readerrorlog 0, 1, 'RECOVERY', 'SIMPLE';

INSERT INTO #SQLerrorlog
EXEC sp_readerrorlog 0, 1, 'RECOVERY', 'BULK_LOGGED';

UPDATE #SQLerrorlog
SET ProcessInfo = SUBSTRING(ProcessInfo, 5, 20)
FROM #SQLerrorlog
WHERE ProcessInfo LIKE 'spid%';

-- Get path of default trace file
SELECT @tracefile = CAST(value AS VARCHAR(500))
FROM sys.fn_trace_getinfo(DEFAULT)
WHERE traceid = 1
    AND property = 2;

-- Get objects altered from the default trace
SELECT IDENTITY(INT, 1, 1) AS RowNumber
       , *
INTO #temp_trc
FROM sys.fn_trace_gettable(@tracefile, DEFAULT) g -- default = read all trace files
WHERE g.EventClass = 164;

SELECT t.DatabaseID
       , t.DatabaseName
       , t.NTUserName
       , t.NTDomainName
       , t.HostName
       , t.ApplicationName
       , t.LoginName
       , t.SPID
       , t.StartTime
       , l.Text
FROM #temp_trc t
    JOIN #SQLerrorlog l ON t.SPID = l.ProcessInfo
WHERE t.StartTime > GETDATE() - 1 -- filter by time within the last 24 hours
ORDER BY t.StartTime DESC;

DROP TABLE #temp_trc;
DROP TABLE #SQLerrorlog;
GO