DLL Error While Creating an In-Memory Table in SQL Server 2014 While trying to create an in-memory table on a pre production box we get the following error.

CREATE TABLE [stage].[MyData]
(
   [ID] [VARCHAR](50) COLLATE Latin1_General_100_BIN2 NOT NULL INDEX imxMyID NONCLUSTERED,
   [Col1] [VARCHAR](15) NULL,
   [Col2] [VARCHAR](5) NULL,
   [Col3] [NUMERIC](12,2) NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)

  Msg 41309, Level 16, State 1, Line 0 Unable to load the compiled DLL
  for database ID 9.

I understand the architecture of in-memory tables is such that SQL Server generates a DLL out of the in memory object which it then loads into memory.  My guess is that operation is the source of this error - however, either my bing-fu skills are failing me, or there is really nothing in the wild that might lead me to resolution for this problem.

My gut is that it's some type of group policy or security issue as this is a pre-production stage environment with anti-virus, parity, the works - just like production has - but need to solve as this is meant for production some day.  The only GPO mentioned in documentation is 'Instant File Initialization' for performance reasons.

Anyone deal with this error before or understand the internals of in-memory objects enough to point me in the right direction?  Exact same setup, database, statement works on my own local machine.

We have an internal "parity" server to protect our production and pre-production environments.  After some research and help from the #sqlhelp tag on twitter we found the location of the DLLs that SQL Server generates prior to loading into memory.  Once those were loaded into the parity server as approved locations for DLLs and EXEs - things were all good.  So this was completely an environmental error - nothing to do with SQL Server per se.


Populating bridge table with conditions I have a bridge table trajectory_detail that I am supposed to populate with millions of records from the corresponding parent tables. The problem is that every value from parent table trajectory_id is supposed to have at least 3 values of activity_id from parent table activities and the date for all these values must be less than 3 months apart.
            create table medical_trajectory
        (
            traject_id int identity(1,1) not null,
            patient_id int not null,
            PRIMARY KEY(traject_id)

        )

        create table activities (
        activity_id int identity(1,1) not null,
        name varchar(256) not null,
        PRIMARY KEY(activity_id)
    )

        create table trajectory_detail ( -- bridge table
            traject_id int not null,
            activity_id int not null,
            date_performed date not null,
            FOREIGN KEY(traject_id) REFERENCES medical_trajectory(traject_id),
            FOREIGN KEY(activity_id) REFERENCES activities(activity_id)
)

The desired outcome would look like this:

I have somehow managed to resolve this using nested while loops but the performance is just terrible and I am kinda stuck and would really appreciate any help.

I'm going to assume there is a table with the activities and dates, and you are only inserting the conforming records into your table.  I'm going to call that data_source.
So we need to do a few things here:

Establish intervals of three months around traject_id for each date_performed.
Find the number of activities that occurred within those intervals, and a distinct count of activity_id.
Remove intervals that do not contain at least three distinct activities.
Ensure the intervals do not overlap then join to the original data.

So here goes:
WITH traj_interval AS
(
  SELECT
    interval.traject_id
   ,interval.start_date
   ,CASE
      WHEN interval.end_date < LEAD(interval.start_date,1,'9999-12-31') OVER (PARTITION BY interval.traject_id ORDER BY interval.start_date) THEN interval.end_date
      ELSE DATEADD(DAY,-1,LEAD(interval.start_date) OVER (PARTITION BY interval.traject_id ORDER BY interval.start_date))
    END AS end_date
  FROM
    (
      SELECT DISTINCT
        traject_id
       ,date_performed AS start_date
       ,DATEADD(MONTH,3,date_performed) AS end_date
      FROM
        data_source
    ) interval
  INNER JOIN
    data_source data_source
      ON data_source.traject_id = interval.traject_id
           AND data_source.date_performed >= interval.start_date
           AND data_source.date_performed <= interval.end_date
  GROUP BY
    interval.traject_id
   ,interval.start_date
   ,interval.end_date
  HAVING
    COUNT(DISTINCT data_source.activity_id) >= 3
)
SELECT
  data_source.traject_id
 ,data_source.activity_id
 ,data_source.date_performed
FROM
  data_source data_source
INNER JOIN
  traj_interval traj_interval
    ON traj_interval.traject_id = data_source.traject_id
         AND traj_interval.start_date <= data_source.date_performed
         AND traj_interval.end_date >= data_source.date_performed

So this handles gaps and overlapping intervals, which is the major headache from before.
This does not make the prettiest execution plan, but it has to be faster than performing loops.


Add computed column with "IN" syntax I have a query in SQL Server 2008R2 that I want to run against a computed column that in turn I am going to index - in order to speed things up.

The column is of type int right now.

I cannot get the syntax right.

alter table dbo.tOutput Add StateNew as State IN (0, 10)

I get an error

Incorrect syntax near the keyword 'IN'.

The query, coming from C# entity framework, should then later on use the index and - well - be fast.

The C# seems to be converted to the IN usage, the original is like this:

tOutput.Where(x => (x.State == 0) || (x.State == 10))

The generated SQL reads

.. FROM tOutput WHERE State IN (0, 10) ..

I do not think that you want a computed column here. It looks like what you really need is a filtered index (introduced in SQL Server 2008), using WHERE [State] IN (0, 10) as the filter. Once this index is created, queries that have that same predicate should use this index. Please take a look at:

Create Filtered Indexes
CREATE INDEX

Just FYI: a computed column is an expression that needs to return a scalar value. State IN (0, 10) is not a scalar expression, which is why you are getting the error. You could modify it to be something like CONVERT(TINYINT, CASE [State] WHEN 0 THEN 1 WHEN 10 THEN 1 ELSE 0 END), but then indexing that will still include all rows. Using a filtered index will only include the matching rows, which will be smaller and faster :-).


Distinct result with row_id for limit query There are 2 tables:

CREATE TABLE sample1 (
  id int,
  name varchar,
  score int,
  mode int
)

CREATE TABLE sample2 (
  id int,
  mode int
  ...
)

I need to select distinct name with MIN(score) and ROW_NUMBER(). Must sort by ROW_NUMBER() ASC. The mode must exist in table sample2 for a row from sample1, so there is INNER JOIN.

ROW_NUMBER() is needed for sequential LIMIT queries so I can select data in batches. I don't care about consistency of that LIMIT+ROW_NUMBER() trick.

My query:

WITH sample1 AS (
    WITH sample1 AS (
        SELECT a.name, MIN(a.score) s
        FROM sample1 a
            JOIN sample2 USING (mode)
            GROUP BY a.name
    )
    SELECT *, ROW_NUMBER() OVER (ORDER BY s ASC) AS n
    FROM sample1
) SELECT * FROM sample1 WHERE n > $1 LIMIT $2

I am concerned about efficiency of that select, in fact it's 3 level deep subquery. Perhaps there are less ugly and more efficient ways to achieve same result?

The number of rows in this table will be approximately 1,000,000 and distinct set is expected to be 100,000. Queries are frequent, actually it's a hot table for select, only score column will be updated in batches of the same size as limit select.

Would seem like you do not need any CTE, subquery or even JOIN at all. Just an EXISTS semi-join and an additional OFFSET:

SELECT name
     , MIN(score) AS s
     , ROW_NUMBER() OVER (ORDER BY MIN(score)) AS n  -- still needed?
FROM   sample1 a
WHERE  EXISTS (SELECT FROM sample2 s2 WHERE s2.mode = a.mode)
GROUP  BY name
ORDER  BY s
OFFSET $1
LIMIT  $2;

A large OFFSET is always costly, as all the leading, discarded rows have to be considered in the ordering first.

If it's a read-only table, you could add a MATERIALIZED VIEW with an index on the row number and query with SELECT * FROM mv WHERE n > $1 AND n <= $1 + $2.
Would help most for big $1 and small $2.


Error when trying to deploy SSIS package I am trying to deploy my 2012 SSIS project to the Integration Services catalog on the SSIS server (also 2012).  I am getting the error below.  I checked that there is plenty of space in SSISDB and msdb databases as well as on the disk.  Any ideas what might be causing this?

I experienced this problem, and was able to resolve it by disabling a DDL trigger on the SSISDB database for DDL_DATABASE_LEVEL_EVENTS.  The trigger was trying to write info into another Database.


Is storing redundant data part of the (CouchDB) self-contained data concept? The CouchDB concept speaks about self-contained documents. In other words: ALL related data is stored in a single concept, instead of splitting them in multiple tables and reference each other using FKs.

  An invoice contains all the pertinent information about a single transaction the seller, the buyer, the date, and a list of the items or services sold.

But this means that we end with redundant data in our database. Let's consider the invoice example: I buy 10 products over a period of time, which results in 10 invoices. Now several information are redundant, e.g. my billing address, delivery address and so on. That's at least a waste of space.

Considering other use-cases, updating data can be very complex. If we have a blogging-cms like WordPress, every post-document contains metadata like author name/email and so on. If those information changes, we've to update a lot of documents. And we have to know the structure from all of them. E.g. the email could be used in posts, comments, log entrys and so on.

When you think that the CouchDB allow a different form for any document, updating such information looks like a horrible tasks. I used MySQL as relational database for years and it seems much more meaningful to normalize such information, put e.g. users in a user table/collection, so that we can refer to some kind of PK.

What is the purpose behind those concept in CouchDB?

So my question is: Did I understood the concept of CouchDB properly, that they drop normalisation completely? I see the (theoretically?) benefit of having a better performance when no joins are required. Is the concept that we get a database with high throughput, but have to live with a (possible massive) amount of redundant data and we have to deal with a complex update-process when related metadata changes?

You understood correctly.  NoSQL databases like Couch trade normalization for fast reads and better availability.

Like you, I have some aversion to inconsistency/denormalization.  It's a trade off in which problems you want to be mindful of when you develop.


Tables with MD_ prefix were found in my Oracle DB - is there any info about them? Recently, during up-gradation of my Oracle DB, I have found many tables whose names are starting with MD_, which are empty.

Is there any info about them?
Can I drop them?

These tables use for Migration workbench.
Please see this link for more information


Trigger update does not seem to finish I'm new to PostgreSQL triggers & functions and such.

I've got a table: keywords and each keyword should be unique. So when I try to insert a keyword that already exists, it should simply update the already existing keyword.

With the trigger below, inserting works fine, updating however takes very long - and I've always canceled it after a few minutes so I'm not sure what it would output.

Table: keywords

   Column   |            Type             |                       Modifiers
------------+-----------------------------+-------------------------------------------------------
 id         | integer                     | not null default nextval('keywords_id_seq'::regclass)
 keyword    | character varying(255)      | not null
 views      | integer                     | not null
 visits     | integer                     | not null
 created_at | timestamp without time zone | not null
 updated_at | timestamp without time zone | not null

CREATE FUNCTION update_if_exists() RETURNS trigger AS $keyword$ DECLARE result INTEGER;
BEGIN
    -- Check whether the keyword already exists.
    result = (SELECT count(keyword) FROM keywords WHERE keyword = new.keyword);

    -- If we've got a match run an update statement.
    IF result = 1 THEN
        UPDATE keywords
        SET
            views       = views + new.views,
            visits      = visits + new.visits
        WHERE
            keyword     = new.keyword;

        RETURN NULL;
    END IF;

    RETURN new;
END; $keyword$ LANGUAGE plpgsql;

CREATE TRIGGER id BEFORE INSERT OR UPDATE ON keywords
    FOR EACH ROW EXECUTE PROCEDURE update_if_exists();

You caused an endless loop by making the trigger BEFORE INSERT OR UPDATE . Use instead:

CREATE FUNCTION update_if_exists()
  RETURNS trigger AS
$keyword$
BEGIN

-- Check whether the keyword alrady exists.
UPDATE keywords
SET    views  = views + NEW.views
      ,visits = visits + NEW.visits
WHERE  keyword = NEW.keyword;

IF FOUND THEN
   RETURN NULL;
ELSE
   RETURN NEW;
END IF;

END
$keyword$ LANGUAGE plpgsql;

CREATE TRIGGER id BEFORE INSERT ON keywords     -- not OR UPDATE!
FOR EACH ROW EXECUTE PROCEDURE update_if_exists();


Database Tasks in C# We have 2 database environments a "Production" Database and a "Reporting" Database located on different physical windows server 2003 boxes. The reporting Db is derived off the Production database and contains some modified tables schema's and additional "Pre Cooked" Data tables which would be too costly to otherwise process on the fly.

What I had originally created was a database script (MS SQL) on our Reporting Server to grab the Live Production database at 1AM and restore it in our Reporting environment by running all the scripts to get it up and going such as the tables restores, and modification of the data. The problem with this approach is that I quickly began to realize a) how long this approach takes (there is no option of putting a 'modifieddate' on any production records) and b) how a junior DB developer (me) should probably not dive into this stuff yet because he's more of a programmer :).

Would it be part of the 'best practice methodology' to create a windows service which basically runs the restore at 1AM and sends the email if the process failed? I'm not sure if writing a c# component is going to allow me the level of granularity that MS SQL Scripts allow. So by using a programming language to accomplish this task - is this nuts and should this all be done in the database?

How would you recommend I run the syncing of the two environments?

Let me know what you think!

For those kind of tasks, you should use the tools you are most comfortable with.

A DB would of course run a scheduled database script or use SSIS. A C# dev creates a small C# tool and a sysadmin creates a PowerShell script.


how do I know whether I am currently in chained or unchained mode In Sybase server-side language how can my code test whether it's running in chained or unchained (auto-commit) mode?

Examining the status of @@TRANCOUNT is not really conclusive.

I gather that there must be a way as for instance, JDBC provides getAutoCommit which is able to detect whether the connection is in auto-commit (unchained) mode or not. But how do I do that from within a stored procedure?

I've found this question but it seems to answer for SQL Server only.

Sybase ASE's default mode is unchained, as I suspect you already know.

You can use SELECT @@tranchained; to see the status for the current session's transaction chaining mode.

The result is either 0 or 1, per the following:

0 = unchained
1 = chained

Transaction chaining mode can be modified with SET CHAINED ON; or SET CHAINED OFF;


User has more privileges in redshift than shown by relevant query Working in Redshift
dev=# SELECT table_catalog, table_schema, table_name, privilege_type
FROM   information_schema.table_privileges
WHERE  grantee = 'myser';
 table_catalog | table_schema | table_name | privilege_type
---------------+--------------+------------+----------------
(0 rows)

However the user is able to perform select on several randomly tested pg_catalog schema tables, e.g. SELECT * FROM pg_class_info;, SELECT * FROM pg_class; etc.
The only GRANT command I run after creating myser was:
GRANT SELECT ON stl_connection_log  TO myuser;

after setting
SET search_path TO pg_catalog;

what am I missing?

information_schema.table_privileges only shows the privileges that were directly granted to a role (user), not the privileges inherited through role membership.
Now most catalog tables have SELECT privileges granted to the special role PUBLIC, which stands for “everybody”, so every user can SELECT from catalog tables.
And if you feel tempted to change that, don't. It will affect the usability of your database.


Enforcing Password Policy on an Instance Is there a way that I can enforce password policy on an entire instance so that I wouldn't have to do it on every login.

You can script it like this for every login, which is already there.

USE [master]
GO

SELECT 'ALTER LOGIN ['+name+'] WITH CHECK_POLICY=ON;'
,* FROM sys.sql_logins
WHERE is_policy_checked = 0
GO

Copy from the results what you want, and execute it in another query window.


Credentials for mysql fail for mysqldump in bash script I am having a very strange problem in a script to back up MySQL schemas.  The script creates a list of schemas and then runs the mysqldump command for each of them. The credentials are stored in a separate file db_credentials.

The relevant lines of code are...

. /home/backupuser/db_credentials
user=$USERNAME
password=$PASSWORD
dbList=$(mysql -u$user -p$password -Bse "SHOW DATABASES")
for db in $dbList
do
   mysqldump –u$user -p$password --single-transaction --routines  $db > $dumpdir/${db}_${timestamp}.sql
   echo $db
done

The output looks like this...

mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect
db_1
mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect
db_2
mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect
db_3
mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect
...

This shows that the $dblist array variable is filled correctly, but for some reason, the myslqdump command is not using the credentials supplied.

I am running the script from the command line using sudo, but I have no idea where it is getting user=root or the password from.

I just checked the backup directory, and the relevant files are being created, but they are empty.

MySQL version 5.1
O/S Centos 6.8
Bash version 4.1.2

Any help would be much appreciated

Apologies, I made a really amateurish error. Apparently, a few lines of code were at some point copied and pasted from a windows machine, so had CR/LF as the end-of-line character.
Changed this to LF and it worked fine.

I would still be interested in why it decided to use root@localhost (and what password it was using), so feel free to comment here with an explanation.


Select and update specific mysql data in single query I am trying to first select data from a row which contains certain characters. I would then like to update and append new data into another row of those items that were selected. This is my current solution I came up with when trying to combine the two tasks into a single query.

SELECT *
FROM `parts`UPDATE  `parts` SET
`keywords` = CONCAT(`keywords` ,  'Phillips' )
WHERE INSTR(`name`, 'PHI') > 0

You can make a SELECT as part of an UPDATE statement, but you can't make an UPDATE part of a SELECT statement. However, based on your example you don't need to, instead you can just do the following:

UPDATE parts
SET keywords = CONCAT(keywords,'Phillips')
WHERE name LIKE '%Phi%'

If you want to retrieve the results after you've updated them just execute the following afterwards:

SELECT *
FROM parts
WHERE name LIKE '%Phi%'


Where can I find the restore duration? I can't seem to find the same level of granularity for SQL Server restores that we can for backups?  I see the restore start date/time in msdb.dbo.restorehistory but not start and stop date and times.  Does it exist?

Please use this with caution. Especially  reading registry via tsql is not allowed in many company policy, or recommended in many case. This script assumes you have enough SQL error log retention.  Especially the error log from the time you database restore was done.  I cannot remember if found this query from a open source or I wrote this. If you find it somewhere else let me know and I will add the source.

USE [MSDB];
GO
SET NOCOUNT ON;
DECLARE @sqlversion VARCHAR(10) ,
    @restore_finish_time DATETIME ,
    @reccount INT ,
    @maxerrorlogtoscan INT ,
    @Lognumber TINYINT;

--Checking how many error log is retained in this instance by reading registry
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs',  @maxerrorlogtoscan OUTPUT;

--If registry value is null  set it to 6
IF @maxerrorlogtoscan IS NULL
    SET @maxerrorlogtoscan = 6;

SET @reccount = 0;
SET @Lognumber = 0;
SET @sqlversion = RTRIM(LTRIM(SUBSTRING(@@version, 22, 5)));

IF OBJECT_ID('tempdb..#dbrestored') IS NOT NULL
    BEGIN
        DROP TABLE #dbrestored;
    END;

CREATE TABLE #dbrestored
    (
      RowID INT IDENTITY
                PRIMARY KEY ,
      logdate DATETIME ,
      dbname VARCHAR(40) ,
      infotext VARCHAR(4000)
    );

IF @@version LIKE '%2000%'
    OR @@version LIKE '%2005%'
    BEGIN
        PRINT 'SQL 2000 and 2005 are not supported';

    END; -- SQL 2000
ELSE -- SQL 2005 or higher
    BEGIN

        WHILE @Lognumber <= @maxerrorlogtoscan
            BEGIN
                INSERT  INTO #dbrestored
                        EXEC xp_readerrorlog @Lognumber, 1,
                            N'Restore is complete',
                            N'The database is now available.';
                SET @Lognumber = @Lognumber + 1;
            END;

        UPDATE  a
        SET     a.dbname = SUBSTRING(b.infotext, 34,
                                     ( CHARINDEX('''', b.infotext, 34) - 34 ))
        FROM    #dbrestored a
                JOIN #dbrestored b ON a.RowID = b.RowID;

    END;

SELECT  AA.destination_server_name ,
        AA.destination_database_name ,
        AA.restore_start_time ,
        AA.restore_finish_time ,
        LEFT(CONVERT(VARCHAR(20), AA.restore_finish_time
             - AA.restore_start_time, 108), 8) AS [RestoreDur hh:mm:ss] ,
        AA.source_server_name ,
        AA.source_database_name ,
        AA.backup_start_date ,
        AA.backup_finish_date
FROM    ( SELECT    @@ServerName AS destination_server_name ,
                    #dbrestored.[dbname] AS [destination_database_name] ,
                    rs.[restore_date] AS restore_start_time ,
                    #dbrestored.logdate AS restore_finish_time ,
                    bs.[server_name] AS [source_server_name] ,
                    bs.[database_name] AS [source_database_name] ,
                    bs.[backup_start_date] ,
                    bs.[backup_finish_date] ,
                    ROW_NUMBER() OVER ( PARTITION BY [destination_database_name] ORDER BY restore_date DESC ) ROWNUM
          FROM      [dbo].[restorehistory] rs
                    JOIN [dbo].[backupset] bs ON rs.[backup_set_id] = bs.[backup_set_id]
                    JOIN [dbo].[backupmediafamily] bmf ON bs.[media_set_id] = bmf.[media_set_id]
                    JOIN #dbrestored ON #dbrestored.dbname = rs.destination_database_name
          WHERE     bs.type = 'D'
                    AND rs.restore_date < #dbrestored.logdate
        ) AA
WHERE   ROWNUM = 1
ORDER BY restore_start_time DESC;


Use array expression from subquery in ANY() condition I have a relatively complicated query with a subquery fetching an array like so:

...
ARRAY(SELECT category_id FROM category_schedule_con con
      WHERE s.id = con.schedule_id ORDER BY category_id) AS cats,
...

and would like to use the array 'cats' in a later WHERE condition like

...
WHERE 4 = ANY(cats)
...

But this does not work as it states that the column 'cats' doesn't exist.
c/p'ing the subquery into the ANY clause yields the expected result.

Explanation

By definition in the SQL standard (which Postgres implements) you can reference output columns in ORDER BY or GROUP BY, but not in the WHERE or HAVING clause. The manual:

  An output column's name can be used to refer to the column's value in
  ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses;
  there you must write out the expression instead.

Related:

PostgreSQL reusing computation result in select query
PostgreSQL Where count condition
GROUP BY + CASE statement

Obviously, your subquery is a correlated subquery expression in the SELECT list (which is hidden in the question due to over-simplification).

To avoid repeating lengthy / expensive expressions in the WHERE clause you can use a subquery in the FROM list. A correlated subquery in the SELECT list cannot be referenced by alias in the WHERE clause, that's just an output column like any other.

Chances are, your query can be much more efficient ...

Better query

Applying the above, this query would work:

SELECT s.*, con.cats
FROM   some_table s  -- guessing the missing query
JOIN  (
   SELECT schedule_id
        , array_agg(category_id ORDER BY category_id) AS cats
   FROM   category_schedule_con
   GROUP  BY 1
   ) con ON con.schedule_id = s.id
WHERE  3 = ANY(cats);

Now you can reference the column alias cats in the WHERE clause. But this query is terribly inefficient for big tables for multiple reasons. Most importantly, the predicate is not sargable.

Using GROUP BY and the aggregate function array_agg() instead of the ARRAY constructor because we are producing multiple arrays in a single query.

You can apply an ORDER BY clause to almost any aggregate function, but a sorted subquery typically performs better:

SELECT s.*, con.cats
FROM   some_table s
JOIN  (
   SELECT id, array_agg(category_id) AS cats
   FROM   (
      SELECT schedule_id AS id, category_id  -- alias id for convenience
      FROM   category_schedule_con
      ORDER  BY 1, 2  -- to get ordered list per schedule_id
      ) con
   GROUP  BY 1
   ) con USING (id)
WHERE  3 = ANY(cats);

More importantly, pull the predicate (the WHERE condition) down into the subquery to make it possible to use an index and exclude irrelevant rows early. Much faster with big tables:

SELECT s.*, con.cats
FROM   some_table s
JOIN  (
   SELECT id, array_agg(category_id) AS cats
   FROM  (
      SELECT schedule_id AS id, category_id
      FROM   category_schedule_con c
      WHERE  EXISTS (
         SELECT 1 FROM category_schedule_con
         WHERE  schedule_id = c.schedule_id
         AND    category_id = 3
         )
      ORDER  BY 1, 2
      ) con
   GROUP BY 1
   ) con USING (id);

Depending on data distribution, a LATERAL join (requires Postgres 9.3+) may be more efficient:

SELECT s.*, con.cats
FROM   some_table s
     , LATERAL (
   SELECT ARRAY (
      SELECT category_id
      FROM   category_schedule_con
      WHERE  schedule_id = s.id
      ORDER  BY 1
      ) AS cats
   ) con
WHERE  EXISTS (
   SELECT 1 FROM category_schedule_con
   WHERE  schedule_id = s.id
   AND    category_id = 3
   );

About LATERAL:

What is the difference between LATERAL and a subquery in PostgreSQL?

But it should be the fastest to invert the logic: start by finding schedule_id that have category_id = 3, self-join to category_schedule_con and aggregate before joining to the other table:

SELECT s.*, con.cats
FROM  (
   SELECT id, array_agg(c.category_id) AS cats
   FROM  (
      SELECT schedule_id AS id
      FROM   category_schedule_con
      WHERE  category_id = 3
      ) x
   JOIN   category_schedule_con c USING (id)
   GROUP  BY id
   ) con
JOIN  some_table s USING (id);

Index

Be sure to have a multicolumn index like:

CREATE INDEX category_schedule_con_foo_idx ON category_schedule_con
(schedule_id, category_id);

For the last query, we'd need the inverted sequence of columns:

CREATE INDEX category_schedule_con_bar_idx ON category_schedule_con
(category_id, schedule_id);

and another one with two columns switched back again. Two indexes on just (category_id) and (schedule_id) would work fast, too;

A PK or UNIQUE constraint on both columns serves as well.


How to implement business logic permissions in PostgreSQL (or SQL in general)? Let's assume I have a table of items:
CREATE TABLE items
(
    item serial PRIMARY KEY,
    ...
);

Now, I want to introduce the concept of "permissions" for each item (please note, I'm not talking about database access permissions here, but business logic permissions for that item). Each item has default permissions and also per-user permissions that may override default permissions.
I tried to think of several ways to implement this and came up with the following solutions, but I'm not sure on which is the best and why:
1) The Boolean Solution
Use a boolean column for each permission:
CREATE TABLE items
(
    item serial PRIMARY KEY,

    can_change_description boolean NOT NULL,
    can_change_price boolean NOT NULL,
    can_delete_item_from_store boolean NOT NULL,
    ...
);

CREATE TABLE item_per_user_permissions
(
    item int NOT NULL REFERENCES items(item),
    user int NOT NULL REFERENCES users(user),

    PRIMARY KEY(item, user),

    can_change_description boolean NOT NULL,
    can_change_price boolean NOT NULL,
    can_delete_item_from_store boolean NOT NULL,
    ...
);

Advantages: Each permission is named.
Disadvantages: There are dozens of permissions which increases the number of columns significantly and you have to define them twice (once in each table).
2) The Integer Solution
Use an integer and treat it as a bitfield (i.e. bit 0 is for can_change_description, bit 1 is for can_change_price, and so on, and use bitwise operations to set or read permissions).
CREATE DOMAIN permissions AS integer;

Advantages: very fast.
Disadvantages: You have to keep track of which bit stands for which permission in both the database and the front-end interface.
3) The Bitfield Solution
Same as 2), but use bit(n). Most likely the same advantages and disadvantages, maybe slightly slower.
4) The Enum Solution
Use an enum type for the permissions:
CREATE TYPE permission AS ENUM ('can_change_description', 'can_change_price', .....);

and then create an extra table for default permissions:
CREATE TABLE item_default_permissions
(
    item int NOT NULL REFERENCES items(item),
    perm permission NOT NULL,

    PRIMARY KEY(item, perm)
);

and change the per-user definition table to:
CREATE TABLE item_per_user_permissions
(
    item int NOT NULL REFERENCES items(item),
    user int NOT NULL REFERENCES users(user),
    perm permission NOT NULL,

    PRIMARY KEY(item, user, perm)
);

Advantages: Easy to name individual permissions (you don't have to handle bit positions).
Disadvantages: Even when just retrieving the default permissions, it requires accessing two additional tables: first, the default permissions table, and second, the system catalog storing the enum values.
Especially because the default permissions must be retrieved for every single page view of that item, the performance impact of the last alternative might be significant.
5) The Enum Array Solution
Same as 4), but use an array to hold all the (default) permissions:
CREATE TYPE permission AS ENUM ('can_change_description', 'can_change_price', .....);

CREATE TABLE items
(
    item serial PRIMARY KEY,

    granted_permissions permission ARRAY,
    ...
);

Advantages: Easy to name individual permissions (you don't have to handle bit positions).
Disadvantages: Breaks the 1st normal form and is a bit ugly. Takes up a considerable number of bytes in a row if number of permissions is large (about 50).
Can you think of other alternatives?
Which approach should be taken and why?
Please note: this is a modified version of a question posted earlier on Stackoverflow.

I know that you are not asking about database security per se, but you can do what you want using database security. You can even use this in a web app. If you don't want to use database security, then the schemas still apply.

You want column-level security, row-level security, and probably hierarchical role management. Role-Based security is much easier to manage than User-Based security.

This example code is for PostgreSQL 9.4, which comes out soon. You can do it with 9.3, but there is more manual labour required.

You want everything to be indexable if you are concerned with performance †, which you should be. This means that bit-mask and array fields probably won't be a good idea.

In this example, we keep the main data tables in the data schema, and corresponding views in public.

create schema data; --main data tables
create schema security; --acls, security triggers, default privileges

create table data.thing (
  thing_id int primary key,
  subject text not null, --or whatever
  owner name not null
);

Put a trigger on data.thing for inserts and updates enforcing that the owner column is the current_user. Perhaps allow only the owner to delete his own records (another trigger).

Create a WITH CHECK OPTION view, which is what users will actually use. Try really hard to make it updatable, otherwise you'll need triggers/rules, which is more work.

create view public.thing with(security_barrier) as
select
thing_id,
subject,
owner,
from data.thing
where
pg_has_role(owner, 'member') --only owner or roles "above" him can view his rows.
WITH CHECK OPTION;

Next, create an access-control list table:

--privileges r=read, w=write

create table security.thing_acl (
  thing_id int,
  grantee name, --the role to whom your are granting the privilege
  privilege char(1) check (privilege in ('r','w') ),

  primary key (thing_id, grantee, privilege),

  foreign key (thing_id) references data.thing(thing_id) on delete cascade
);

Change your view to account for ACLs:

drop view public.thing;

create view public.thing with(security_barrier) as
select
thing_id,
subject,
owner
from data.thing a
where
pg_has_role(owner, 'member')
or exists (select 1 from security.thing_acl b where b.thing_id = a.thing_id and pg_has_role(grantee, 'member') and privilege='r')
with check option;

Create a default row privileges table:

create table security.default_row_privileges (
  table_name name,
  role_name name,
  privilege char(1),

  primary key (table_name, role_name, privilege)
);

Put a trigger on insert on data.thing so that it copies default row privileges to security.thing_acl .

Adjust table-level security appropriately (prevent inserts from unwanted users). No one should be able to read the data or security schemas.
Adjust column-level security appropriately (prevent some users from seeing/editing some columns). You can use has_column_privilege() to check that a user can see a column.
Probably want security definer tag on your view.
Consider adding grantor and admin_option columns to acl tables to track who granted the privilege, and whether the grantee can manage privileges on that row.
Test lots

† In this case pg_has_role is probably not indexable. You would have to get a list of all superior roles to current_user and compare to the owner/grantee value instead.


MySQL - Fastest restore of 1TB database I have MySQL Database and its 1.5TB. I need to setup replication for this. I have 32core CPU and 250GB Memory. The problem restore is taking more time even I used mydumper/myloader to backup and restore.

Here is my MySQL Setting:

innodb_buffer_pool_size =235G
bulk_insert_buffer_size = 5G
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_read_io_threads = 8
innodb_write_io_threads = 22
innodb_adaptive_hash_index = 0
innodb_old_blocks_pct = 45
tx_isolation = READ-UNCOMMITTED
innodb_purge_threads = 2
innodb_change_buffering = inserts
innodb_change_buffer_max_size = 30
query_cache_size = 0

In myloader:

120 threads running to restore.
100000 queries per transaction.

But still its resorting more than a day. Is there any possibilities to restore

If you have "too many" threads doing the restore, they will stumble over each other and might run slower than if you used a fewer number of threads.

Add on innodb_buffer_pool_instances=16

I assume you are using SSDs?  Otherwise, some of the settings are too high.

If you are I/O-bound, then probably there is nothing you can do to speed it up.  Except...

In the future, consider setting up LVM so that both dump and restore can be on the order of one minute, even for multi-TB dumps.  This is because of the "copy of write" technology at the block level of the OS and disk.


SSMS 2012 / 2014 / 2016 Object Explorer hangs when Expanding Databases list on a server with many DBs When accessing SQL Server with SSMS versions (2012/2014 and 2016), opening the databases list from the object explorer is really slow.
If I try it using SSMS 2008 R2 on the same instance the result is => instant display.

Note : The account is sysadmin, all databases have autoclose disabled, and I am not connecting to a remote server. It's on my local machine!

I noticed that there is a small difference comparing the queries generated from SSMS 2008 and 2012/2014/2016 when opening the databases list.

All of them are using has_dbaccess function, except SSMS 2008.

For instance:

SELECT
dtb.name AS [Name],
dtb.database_id AS [ID],
CAST(has_dbaccess(dtb.name) AS bit) AS [IsAccessible]
FROM
master.sys.databases AS dtb
ORDER BY
[Name] ASC

There is a huge performance difference if I remove the function.
I guess they should add a condition in the begging of the query where in case it's a sysadmin account, just don't check permissions in every single database.
Does it make any sense?

This issue is finally fixed in the new SSMS 17.9.

You can get it here.


how to track progress of a large postrgresql dump Is there a way to see the progress of a pg_dump operation on a big db (ie > 1GB)? adding the -v option just dumps text on the screen, but doesn't give me much meaningful tracking information.

You can see a rough progress using the TOC list.

First, get the TOC list of objects to be restored:

pg_restore -l -f list.toc db.dump

Then, you can see the TOC list line by line and compare the output of verbose or query pg_stat_activity to see where in the TOC list is pg_restore in.

It is just a rough estimate though. First because each item from the TOC list may take really different time to load (for instance, schemas are fast, but loading data of big tables and building indexes are not), and if you use -j you'll have an item being restored before a previous one has finished. Also, I'm not 100% sure if pg_restore follows TOC list precisely if you don't use -L, but I think it does.


SQL Server 2008 - get current LOCK_ESCALATION on particular table Is there a way to find the current setting for LOCK_ESCALATION on particular table? Can it be found through system views?

As far as I know, you'd have to look using SQL Server Profiler, the Lock:Escalation event

Esclation information isn't in sys.dm_tran_locks or it's predecessors


creating a multi table "identity" column (faux identity) I am currently designing a job execution engine and have run into a problem I am looking for some advice on.

I am converting some processes to a new system and need to define the table structure.  The process consists of 3 steps with different data for each step (3 different webservice calls).  Currently a flat file is generated with a RowID and the data for all 3 steps on the same row.  The new design is for each process will pull directly from tables and each step will have its own table with only the data fields needed for each step.

The problem is that the rows between the tables need to be linked so that after step 1 completes successfully we can act on step 2 with the proper data.  since none of the step data will be guaranteed to be unique (through business rules) I need to have an ID to link the tables together.  The basic table structure will be as follows:

CREATE TABLE Step1
(
BatchID INT,
BatchRowID ???,
ProcessData
)

CREATE TABLE Step2
(
BatchID INT,
BatchRowID ???,
ProcessData
)

CREATE TABLE History
(
BatchID INT,
BatchRowID ???,
StepID INT,
Result BIT --True False flag
)

I am looking for a solution to create a faux multi table IDENTIY column.  so that Process2 that uses a step 4 will have gaps where BatchRowID's are used for Process1.  The first thing that comes to mind is to use a GUID but as the clustered PK on the tables will be BatchID, BatchRowID I want to try and avoid a GUID if I can.   This is for multiple reasons, 1) Clustered GUIDs suck for inserts (however this is overcome slightly by including BatchID) and 2) The additional space used over an INT in the base table and in every index added to the table.

If any one has implimented something similar I would love to hear your solutions.

Currently the host server has not been determined so SQL Version could be 2008 or 2012.

If the version is at least 2012 use a SEQUENCE


Create a Constraint such that only one of two fields must be filled I have a table in SQL server where the users need to enter data in either of two columns. That is, One of the two must have data inputted but at the same time i don't want to allow users to input in both columns. It's either or but one is a must.

You need to use a check constraint:

create table kevin
(
  one   integer,
  other integer,
  constraint only_one_value
        check (        (one is null or other is null)
               and not (one is null and other is null) )
);

This ensures that at least one of the columns has a value and that not both have a value.

If those are varchar columns you might want to check for empty values ('') as well. To do that use nullif() which converts a value to null if it is equal to the second argument of the function.

create table kevin
(
  one   integer,
  other integer,
  constraint only_one_value
        check (        (nullif(one,'') is null or nullif(other,'') is null)
               and not (nullif(one,'') is null and nullif(other,'') is null) )
);


Migrating 2+ GB Oracle CLOB column to SQL Server VARCHAR(MAX) I have a column on an Oracle db containing HTML data with embedded images that result in some records being over 2GB in size for that column. I have successfully migrated the rest of the database to SQL Server except for this CLOB column. With SQL Server varchar(max) unable to handle anything over 2GB in size, what are my options?

Normally I would recommend using the XML data type to store HTML but it too has the 2GB limit. This then leaves you with two options: FILESTREAM and FILETABLES. Be aware though, that both of these options require some additional configuration within Sql Server and your database before you are able to use them.

File tables are built on-top of FILESTREAM and offer some benefits if you need to access the files using standard Windows API calls. File Tables are generally transparent to the consumer and easier to integrate into your application but lack transactional access. This means that the files can be removed from outside of the Sql Server context which may be undesirable.

FILESTREAM however must be done under a transaction context and therefore prevents anyone (with exception of administrators/people granted explicit permission to the FILESTREAM folder) from removing them outside of Sql Server. They do not have the 2GB limit which will allow you to get around the problem that you are currently facing; however, accessing the data can be a little more complicated. Luckily, the documentation does a pretty decent job of explaining how to perform CRUD operations with FILESTREAM data.

If I had to pick one, based on what you have provided above, I'd go with FILESTREAM as this will offer you better consistency and control over your data.


Forget login from connection drop down menu Recently I logged myself as a different user (testUser) but now that user seems to be persisted in the drop-down menu of connection dialogue box and I couldn't find a way of getting rid of it. Even worse, it seems to be the default login as whenever I change my connection I have to go to the drop-down menu and choose my usual user otherwise the connection will be made in testUser's context.
How can I get rid of testUser from connection dialogue box?
Thank you.

In SSMS 2008, I believe you need to delete the file C:\Users\%username%\AppData\Roaming\Microsoft\SQL Server Management Studio\%Version%\SqlStudio.bin

This will, however, clear ALL of the logins and saved servers.  You may be able to edit the file using an editor, but I believe it would just be easier to start from scratch.

If you are using SSMS 2012 or greater, you can simply delete the server from the dropdown and that will allow you to start fresh with just that server by highlighting it in the dropdown and hitting Delete

See below link for reference to Aaron Bertrand's answer:

https://stackoverflow.com/questions/10993861/how-to-remove-cached-server-names-from-the-connect-to-server-dialog


to ensure that physically corrupt data is not read Can you please explain what they mean by 'physically corrupt data'?

Read uncommitted (the lowest level where transactions are isolated
only enough to ensure that physically corrupt data is not read)

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms189122(v=sql.105)?redirectedfrom=MSDN

I imagine that physically corrupt data means that whatever physical container holds the data - typically blocks or pages - always deliver a sound and well formatted structure, no matter what failures happen.
The lowest storage unit of a relational database is traditionally called a page or a block. A page is usually formed by multiple disk I/O units (disk blocks or sectors). The I/O subsystem guarantees that a sector is always written as an atomic I/O unit: it either succeeds or fails.
However, if (as is pretty much always the case) a page is formed by multiple sectors, then changes to that page need to be written in multiple sectors, as a consistent unit: either all sectors are written - or none is written. The OS I/O subsystem does not automatically guarantee that:
the lower level of the RDBMS (the I/O manager generally) must guarantee that instead.
This is what that sentence means: even when I use the read uncommitted isolation level, there is still a minimal consistency level that guarantees that the physical storage units of the database (the pages) are always consistent at the physical level.


What do we call the equivalent of CSS "Specificity" in SQL? In CSS, the more HTML elements you specify, the more precedence the selector gets. This is known as "specificity." For example,

div p span {...}

Is more specific than, and thus would override:

span {...}

In SQL, one can start a query like:

SELECT database.table.columnOne, database.table.columnTwo FROM ...

The same query can be expressed, less specifically, as:

SELECT columnOne, columnTwo FROM ...

Is there a proper term in SQL for being more specific in queries, or is it just called "specificity" like in CSS?

In CSS, the more HTML elements you specify, the more precedence the selector gets. This is known as "specificity." For example,

That's not true. CSS specificity is very complex because it's trying to apply conflicting rules rather simply disambiguate. While I understand what you're talking about SQL suffers no such problem with identifiers as queries are either executed with certainty or an error is thrown.

Is there a proper term in SQL for being more specific in queries, or is it just called "specificity" like in CSS?

The intent here is different, "specifity" doesn't exist.

In CSS, the goal is to establish a ruleset to apply conflicting rules.
In SQL, the goal with the syntax you've mentioned is to merely disambiguate.

In the context given we would say <catalog>.<schema>.
.<column> is "fully qualified", as in it leaves no further room for ambiguity. This definition comes from ISO9075-1.,

fully qualified (of a name of some SQL object) With all optional components specified explicitly
NOTE 3 — A fully qualified name does not necessarily identify an object uniquely. For example, although a fully qualified specific name, consisting of a catalog name, a schema name and a specific name, uniquely identifies a routine, a fully qualified routine name doesn't necessarily do so.

NOTE 3 is referring to routines being typed, catalog.schema.myRoutine(text,text) can be different from catalog.schema.myRoutine(int,int).
This is massively different from "specificity" which implies cascading mutability -- if we say "show all animals" and "hide all dogs" and "show collies" what should happen?

.animals { display: block }
.dogs { display: none }
.collies { display: block }

In CSS, you have to know if multiple rules can apply to the same identifier, which one has precedence in the event they conflict. In SQL, the rule-set is much simpler. If there is any ambiguity, things will throw a fatal error. All databases that I can think of forgo any attempt at guesswork. Preventing that fatal error from being thrown requires qualification of your "identifiers".
For instance, the following creates a result set that returns two x (some databases would likely throw an error here),
SELECT *
FROM ( VALUES (1) ) AS t(x)
CROSS JOIN ( VALUES (2) ) AS g(x);
 x | x
---+---
 1 | 2
(1 row)

but the second you say you want x with SELECT x rather than SELECT * all databases that I know of throw an error,
ERROR:  column reference "x" is ambiguous
LINE 1: SELECT x FROM ( VALUES (1) ) AS t(x) CROSS JOIN ( VALUES (2)...

Logically you may ask what the equivalent of SQL "fully qualified" identifiers in CSS. That would be a simple identifier (id attribute selectors). The spec requires identifiers be absolute, thus there is no ambiguity when you specify
#Lassie { color: blue }

It's infinitely (fully-) (globally-) "specific" (qualified), and far more specific than
.animals.dogs.collies { color: white }

Actually there is a small rabbit hole I'm excluding and that's XHTML namespaces and Foreign Elements. which do in fact provide for multiple #Lassies (that is to say identifiers aren't always globally unique)


Adding TIME data to a DATE type column in time series table, and what sort of foreign key I've got a v. simple table currently containing millions of data points of price on a given date:

CREATE TABLE data_point (
    id INT PRIMARY KEY,
    symbol_id INT,
    x DATE,
    y DOUBLE
)

This is a timeseries database and for this release I'll keep data with a daily (or less frequent) period but if I need to add a time component, would it be better to change the data_point table and convert x to DATETIME?

If this did happen, initially all of the existing data would remain unchanged, it would not be adjusted - only future data (and not all of it) would be saved with time.

Or would it be better to add a second table with a one-to-one relationship to data_point, which held the time data?

Performance is more critical than space.

This leads on to the associated question about this table. Which is better in terms of performance for huge amounts of data, when all the queries on data_point will be joins from symbol? (Assuming INT for a primary key will get filled up within the app's expected lifetime)

CREATE TABLE symbol (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE data_point (
    id BIGINT PRIMARY KEY,
    symbol_id INT,
    x DATE,
    y DOUBLE,
    CONSTRAINT FOREIGN KEY (symbol_id)
        REFERENCES symbol(id)
);

or

CREATE TABLE data_point (
    symbol_id INT,
    x DATE,
    y DOUBLE,
    CONSTRAINT PRIMARY KEY (symbol_id, x),
    CONSTRAINT FOREIGN KEY (symbol_id)
        REFERENCES symbol(id)
);

When in doubt, keep it simple - just use DATETIME.  I can think of two reasons to flake TIME off into another table, but neither are commonly applicable:

Your table is very large and in use constantly, so you can't afford to block it while changing a data type.  I'd still work around this by adding a new DATETIME column, populating it gradually, and then dropping the old DATE field when possible.
It's very important that you keep your table narrow, and you'll need the time values very rarely.

You've stated that performance is more important than space, but I really wonder if six bytes for DATETIME2(0..2) is really going to make much difference over three bytes for DATE.  You need to value your own time, and the time of everyone who tries to understand your system in the future.

Your question about the PK really should be asked separately.  However, I concur with Rick James that, in this case, you probably don't need a synthetic key (your id field); just index on symbol_id and date.  Assuming you're clustering on the PK, consider putting date first, so you can easily query date ranges.  If you primarily filter (not just join) by symbol_id, then put it first in the index instead.  If you do both, cluster on { date, symbol_id } and index on symbol_id.


Question about the workflow/design process/tools used by professional database designers I hope this in the right place, as it's really more a question about database design software than the philosophy and practise of database administration. I'm a masters student doing an "introduction to databases" subject; the course leans more towards giving us a grounding in theory, less towards discussing how things are "really done". Working in Oracle SQL developer, we started off learning how to create databases by typing out schema files. Subsequently, we were introduced to the modelling tools in SQL developer, and shown how a logical model can be transformed ultimately into a set of schema files, or even just converted straight into a data dictionary.

While we were working in the 'raw' schema files, the general impression given was that this was how databases were designed by professionals, and that GUI tools were more suited to 'DIY' databases (e.g filemaker). Having seen how powerful (and time-saving) the GUI tools in SQL developer are, I'm not sure I believe this 100%.

The question I'm asking is: If a professional is building a new database from scratch, do they generally design and model, and then manually construct the schema files to implement? Or do designers/teams use tools like SQL developer from the beginning of the project to the end, using the software to design and implement the database?

In my experience it varies from one company/situation to the next.

As a dba I loved toad and sql developer because of the ease of use.

However there are times where you're required to script things using the lowest common denominator. For example writing installation or upgrade scripts that may be delivered to a number of customers. Some may have sophisticated tools others may not. But they all have SQL or PL/SQL.

My advice is do whatever you feel is right for you. Keep focused on your objective. Is the end result more important than how you get there? Or is the quality of your code and learning experience more important?


SQL Server - Company wants HA for single instance with 1 userDB - sanity check and questions I am new to SQL Server (2 months) and learning at a gov company (europe).

The job is to make a HA solution for this 1 productive instance (1 database).

Following is given

SQL Server 2012
Windows Server 2008 R2
single instance with single user database
the instance uses an Agent job for backups (once per day)
24h dataloss is OK
Disaster backup is not my job
RTO is the typical ASAP but IMO 1 min is ok (which is insanely high?)
Local Logins (is in Domain, but not AD)
max 300 clients (Citrix Xen Mobile)
Database is 300MByte with only 30 clients currently
all local storage at the moment
most likely more clusters for other applications in the future

What are my choices here and why is this one better?

I know about FCI and AG but I can't wrap my head around some things.

what are the downsides with doing an WSFC without AD?
whats the downside in using a SMBShare instead of Fibrechannel/IScsi?

The possibilities I see (up/downsides would be awesome for these):

put application server(s) into AD (we do have one),get SAN storage, install FCI

use WSFC to add future clustergroups for other applications + SQLServer with the 3 wizards in no time
downside I see is cost, upside is reusability and support is best

same as aboth with SMB which is way easier and cheaper
use AG without AD and replicate Logins, Agent Jobs between nodes manually (same with AD)

I'm kinda lost after stumbling into the topic.

Thanks to everybody helping!

If your RTO is ~ 1 minute and your RPO is 1 day of data loss then you could use log shipping. This would not require AD, a Windows Cluster or Availability Groups.

If you shipped your logs every 15 minutes the recovery time needed to bring the secondary should be well under a minute (Needs testing but I would be surprised if it wasn't from what you have explained above).

The only thing you would need to deal with would be how the application manages the connection to the secondary when the primary is down. This could be handled in the application, a load balancer (with static routes that would need to be altered on failure of the primary) or a clustered name resource (would probably need to be manually failed over).

A FCI would mean you wouldn't need to handle and monitor all the log backup/copy/restores of log shipping and your RTO would be lower. This is not possible without AD. Also you can't have Availability Groups without a Windows cluster which also requires AD.


What is the best way to compare values of whole tables between many databases? We are trying to compare the values of whole tables between many databases.
The user can enter in input the table names and the columns names, and the database names he wants to compare.

They can enter as many databases, tables and columns they wish. We want to compare rows per rows, only for the specified columns.

ex:
DatabaseA, DatabaseB, DatabaseC
Table1, Col1|Col2|Col3|Col4
Table2, Col1|Col4|Col5|Col6|Col20
...

So for an example, if I have:

DatabaseA
Table1, Col1|Col2|Col3|Col4 = 'Apple', 1, 10, 'ABC'

DatabaseB
Table1, Col1|Col2|Col3|Col4 = 'Banana', 1, 10, 'ABC'

There's a difference.

At first I was considering usign the CHECKSUM on each rows if each tables (excluding columns that where not specified by the user) and compare the CHECKSUM but I've read that it's not always unique.

Now I was thinking of using HASHBYTES instead.
Either by doing this:

    SELECT HASHBYTES('sha2_512', CONVERT(NVARCHAR(MAX), ISNULL(col1,'')) +
           HASHBYTES('sha2_512', CONVERT(NVARCHAR(MAX), ISNULL(col2,'')) +
           HASHBYTES('sha2_512', CONVERT(NVARCHAR(MAX), ISNULL(col3,'')) +
           HASHBYTES('sha2_512', CONVERT(NVARCHAR(MAX), ISNULL(col4,'')) +
    FROM Table1

Or by doing this:

   With Vals AS
   (
       SELECT CONVERT(NVARCHAR(MAX), ISNULL(col1, '')) +
              CONVERT(NVARCHAR(MAX), ISNULL(col2, '')) +
              CONVERT(NVARCHAR(MAX), ISNULL(col3, '')) +
              CONVERT(NVARCHAR(MAX), ISNULL(col4, ''))  AS Val
       FROM Table1
   )
   SELECT HASHBYTES ('sha2_512', Val) FROM Vals

What do you think? How would you do it? What would be the best way?

Thanks

This may not be exactly what you are looking to do, but you could use the EXCEPT statement to get the rows that are different:

SELECT Column1, Column2 FROM DatabaseName1.dbo.TableName1
EXCEPT
SELECT Column1, Column2 FROM DatabaseName2.dbo.TableName2

If you do it this way though, make sure you also do the opposite EXCEPT so you know the rows that don't exist in the other database's table:

SELECT Column1, Column2 FROM DatabaseName2.dbo.TableName2
EXCEPT
SELECT Column1, Column2 FROM DatabaseName1.dbo.TableName1

Hopefully that helps you a little.


Mongodb ubuntu - config server and shard together I have the following services on a single shard server:

sudo mongod --dbpath /data/db/ --port 31001
sudo mongod --configsvr --dbpath /data/configdb/ --port 21001

The same server is using for shard and config.
for now I am testing the environment so I am running these rows manually.
I want to run it as a service. I did not event tried it, adding dbpath twice to the config file will not do the work, can you suggest please a config in order to make these lines run as a service?
thanks

An easy way to achieve that is to use cron's @reboot feature:

> crontab -e -u mongodb
@reboot /usr/bin/mongod --configsvr --dbpath /data/configdb/ --port 21001 --logpath /var/log/mongodb/configsvr.log --fork
@reboot /usr/bin/mongod --shardsvr --dbpath /data/db/ --port 31001 --logpath /var/log/mongodb/mongod.log --fork

However, you have to make sure that /etc/security/limits.conf holds the correct values.


Proper way to create logins on Always on What is the proper way to create logins on Always on? An application database  requires an active directory login and I created that on the primary node. The database is still not in the availability group  yet. I plan to move it there once the application is installed. Do I need to create the same login on the secondary node as well ? Or would the login sync across the secondary node once the database is moved into the AG? The only permission which was requested was db_owner . Are there any other permissions which I need to give to the login to properly work within the availability group ? Please let me know. Thanks !

Logins and users work the same way they do with DBM or log shipping. If using SQL Server authentication, you have to sync the SIDs manually (or manually create the login with the right SID) assuming the login is already created on the other instance.

Either way, this is not automatically done. You need to create the login at the instance level to match the DB user. If using Windows authentication, it should have the same SID, but you still need to create the login at the instance level.

Remember, the instance-level login sits outside the AG, so the AG won't do anything there. Not sure why you thought the AG would work this way; it never has, just like DBM and log shipping.

Also, there is no feature called Always On. Please do not refer to AGs that way. http://sqlha.com/2013/04/29/alwayson-is-the-new-activepassive-and-activeactive/


SQL Anywhere Long Varchar/Binary Column Length Limited to 32767 Attempting to set up a database table (Sybase SQL Anywhere 11.0.1) to store user uploaded documents as either BLOBs or base64 encoded strings, but running into an issue where the length of my LONG BINARY and LONG VARCHAR columns is being limited to 32767.
Using the following create table statement:

CREATE TABLE blobTest (
blobCol LONG BINARY,
vcharCol LONG VARCHAR
)

The table is added to the database with no issue, however, running a query of the syscolumns table:

SELECT cname,coltype,length
FROM sys.syscolumns
WHERE tname LIKE 'blobTest'
ORDER BY cname ASC

Yields the result:

blobCol, long binary, 32767

vcharCol, long varchar, 32767

Storing a value in either of these columns shows that this is indeed the length of the columns, with anything larger than 32Kb* getting truncated.

Looking through the SAP documentation, it's my understanding that both of these data types should have a limit of roughly 2Gb. I've checked into the server/database configuration options but I can't find anything that looks like it would be limiting the length of these columns, am I missing something?

-edit-

The amount that the data was being truncated was not 32000, but 64000, per the admin datasource settings. Further testing showed that the results of:

SELECT LEN(vcharCol)
FROM blobTest

Would be the correct number of characters expected for an uploaded file.

Found the answer:

The issue was not with SQL Anywhere, but rather in our server datasource administration settings.

We use ColdFusion 10 on our web server, and the datasources have a Long Text Buffer setting which was limiting the size of the string being returned from a query of the database to 64000

Changing this value to 2000000 solved the issue.


How to run SQL Profiler to monitor 1 specific stored procedure? I have an issue where a stored procedure will run properly when executed from SQL Management Studio, but when the stored procedure is called from an application, it doesn't return the results to the application.  This application regularly utilizes stored procedures as part of it's operation, so why this specific SP isn't returning results is odd.

Since the application works fine otherwise and since the stored procedure works just fine when run in SQL, what I'd like to do is setup sql profiler to monitor just that stored procedure, while I try to execute/call it from the application.

I'm hoping to avoid both the extra load on the system and sifting through a ton of irrelevant data that a generic profiler run would provided on a busy database like this one.

Thank you in advance for any advice on this.

Following code will do trace one stored procedure. Check for folder location, duration and like statement for the name of your stored procedure. I also excluded any replication related stored procedure in case you have replication.  Let me know if you run into any issues.

-- Variables
DECLARE @rc       INT,
        @TraceID  INT,
        @filename NVARCHAR(256),
        @maxsize  BIGINT,
        @on       BIT
DECLARE @Folder VARCHAR(1000)

SET @Folder = 'D:\Proc_Trace'

-- Check for other running traces
SELECT @rc = Count(DISTINCT traceid)
FROM   ::fn_trace_getinfo(DEFAULT)

IF ( @rc > 1 )
  GOTO error

-- Create the holding folder
IF RIGHT(@Folder, 1) <> '\'
    SET @Folder = @Folder + '\'

SELECT @Folder = @Folder +
    'output_' +
    CONVERT(VARCHAR(8), GETDATE(), 112) + '_' +
    LEFT(REPLACE(CONVERT(VARCHAR(20), GETDATE(), 108), ':', ''), 4)

EXEC master.dbo.xp_create_subdir @Folder

SELECT @filename = @Folder + '\' + REPLACE(@@SERVERNAME, '\', '_') + '__sp_trace'
SELECT @maxsize=50, @on=1

-- Create a Queue
EXEC @rc = sp_trace_create @TraceID OUTPUT, 2, @filename, @maxsize, NULL
IF (@rc != 0) GOTO error

-- Set the events
EXEC sp_trace_setevent @TraceID, 11, 2, @on  -- Needed for 2008 RML

EXEC sp_trace_setevent @TraceID, 11, 3, @on
EXEC sp_trace_setevent @TraceID, 11, 7, @on
EXEC sp_trace_setevent @TraceID, 11, 15, @on
EXEC sp_trace_setevent @TraceID, 11, 8, @on
EXEC sp_trace_setevent @TraceID, 11, 1, @on
EXEC sp_trace_setevent @TraceID, 11, 14, @on

EXEC sp_trace_setfilter @TraceID, 1, 0, 6, N'%p_FoldersApplicants_sel_All_Candidates_OBy_CreatedDate_DESC%'
EXEC sp_trace_setfilter @TraceID, 6, 0, 7, N'%repl%'

-- Set the trace status to start
EXEC sp_trace_setstatus @TraceID, 1

-- Wait 10 min
WAITFOR DELAY '08:00:00'

-- Stop/Delete the trace

EXEC sp_trace_setstatus @traceID, 0
EXEC sp_trace_setstatus @traceID, 2

GOTO finish

ERROR:
SELECT ErrorCode=@rc

FINISH:

--getting result

SELECT
      StartTime,
      HostName,
      SUBSTRING(TextData, CHARINDEX('EXEC', TextData), 1000) AS ProcCall,
      SUBSTRING(TextData, 103, 1000) AS Parameters
FROM ::fn_trace_gettable('D:\Proc_Trace\output_20111010_1009\filename.trc', DEFAULT)
WHERE EventClass = 11
ORDER BY StartTime


Database design: How do i connect these two tables of my database in a normalisation way I want to let a  user create a food schedule(schema table) let him create his own food (userProduct) or choise from the supermarkets(supermarketproduct)

the user should be able to make many food schedules(schemas) and should be able to include many userproducts or many supermarketproducts. Both of them can be in one food schedule but, i do not know how to implement an many to many relationship with this because if i include both userproduct and supermarket product one of them will always be null.

Solution:

I would have a combined Product table with all common elements (name, protein, etc) and this would tie to the food schedules.  Then there would be two additional tables for ProductUser and ProductMarket that would refer back to the main Product table.  No nulls needed and it's nice and normalized.


How to store iterations of similar datasets I have a dataset that is stored across 20 or so tables, most of which have a few million rows.  This data will be generated fresh each month, and compared to past datasets.

I'm pretty new to dealing with datasets of this size, so I'm unsure what would be good practice in storing these past datasets.  I need the data to still be accessible to queries, so dumping the database to backup files won't be enough.  Is there some feasible way to move each dataset into a new database once I get a new one?  Or should I just put timestamps on each row and keep everything in one giant dataset?

Sorry if this is too vague, I'm not sure how exactly to ask this question. I'd be open to changing it if there are any suggestions.

Extending comment to answer:

MySQL can handle lots of rows. So making separate schema for each month gives no benefits unless those datasets differ in schema definition.

With some millions of rows per month you have to design your schema and index carefully enough to make querying fast. When you do it so dataset_id (some month identifier or something) is first in primary key for each table, then (at least in InnoDB by default) your data are clustered by it, so they are kept close together in table structure and disk access is not wasted.

If some tables are going to be really big, then you can use partitioning by dataset/month (probably one partition per month, partitioning key has to be part of primary key and each unique index - not sure if it has to be last in index or anywhere..). That way each partition is like physical table, your queries always access specific dataset so can only work on one such "subtable" instead of all data, and you can easily drop old partitions to reclaim some space (probably after moving the data into some archive).


Drive with ldf files failed and some databases left in recovery pending status We are keeping mdf and ldf files on seperate drives of a physical machine. One of the disks forming the drive for ldf files failed. Some of the databases that had their ldfs on that drive are now in "Recovery pending" status.

It is not a production environment so some data loss is acceptable but losing entire databases is not. Restoring from backups is not a viable option at this time.

I tried to:

1) Detach databases, but I got following error:

  File activation failure. The physical file name "D:\LDFfiles\DatabaseName.ldf" may be incorrect.
  The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

  Msg 5181, Level 16, State 5, Line 1 Could not restart database
  "DatabaseName". Reverting to the previous status.

  Msg 5069, Level 16,
  State 1, Line 1 ALTER DATABASE statement failed.

  Msg 823, Level 24,
  State 2, Line 1 The operating system returned error 2(The system
  cannot find the file specified.) to SQL Server during a read at offset
  0000000000000000 in file 'D:\LDFfiles\DatabaseName.ldf'. Additional
  messages in the SQL Server error log and system event log may provide
  more detail. This is a severe system-level error condition that
  threatens database integrity and must be corrected immediately.
  Complete a full database consistency check (DBCC CHECKDB). This error
  can be caused by many factors; for more information, see SQL Server
  Books Online.

2) Bring them online, but I got following error:

Could you please advise what are my options to recover the database? Best to my knowledge mdf file is healthy and can be reused with rebuild log query. The main problem is that it is currently attached. Any advice will be welcome.

You have two options (if restoring from backup isn't an option), and both of them require the database to be attached - your problems are even more serious if you database is no attached (that's the reason why SQL Server prevents you from detaching. Lucky you - older versions didn't look after you like this.

You can either run CHECKDB and allow data loss, or you can ALTER DATABASE REBUILD LOG - but REBUILD LOG is iffy and might not work if you database is in pending recovery anyway.

You need to run CHECKDB. The syntax is as follows:

ALTER DATABASE [fluffedDB] SET EMERGENCY;
ALTER DATABASE [fluffedDB] SET SINGLE_USER;

DBCC CHECKDB ([fluffedDB], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;

Warning: You will get data loss, and none of this is guaranteed.
Read this SQL Skills post by Paul Randal about EMERGENCY-mode repair

Assumption:
The logical disk where the log file is has been repaired/restored.

Query: Why are restoring from backups not viable? In any environment where you even remotely care about the data, always have a backup, as a restore from backup is always usually the easiest way to solve problems like this.


Executing stored procedures asynchronously using tasks in SSIS I have a stored procedure which is calling 20-30 stored procedures. I need these stored procedures to run asynchronously. From what I have read, it seems like executing those stored procedures using SQL tasks in SSIS is the appropriate way to do it.

I have installed required software but I am completely new to it.Can I please get some directions on how to do it? i.e run stored procedures asynchronously using SQL tasks in SSIS,please? Since I am completely new to it,please try to elaborate as much as possible as I have no prior knowledge of  this. It will be a great help.

Thanks

Instead of SSIS I think you might want to look into SQL Server Service Broker. This lets you send messages asynchronously. See here or here for a start.

MSMQ or RabbitMQ may also be options you should consider.


For each value in field A, select the row with some special value in field B, while respecting further criteria Disclaimer: While the basis of my question—the greatest-n-per-group question—has been asked and also answered so many times before, I am facing a seemingly hard issue due to extra conditions that I have to apply.

In case I really missed the answer to my exact question that was given somewhere, I am sorry. I searched, read, tried, a lot, and I spent already half my day on this.

I tried to describe the abstract problem in the title: for any other field A value, get the row with some special field B value, and at the same time, respect further conditions.

Now, my concrete problem is as follows...
I have the following table:

CREATE TABLE IF NOT EXISTS `mdl_datasets` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `cmid` bigint(10) NOT NULL DEFAULT '0',
  `userid` bigint(10) NOT NULL DEFAULT '0',
  `timecreated` bigint(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

For each user (i.e., userid) in one specific module (i.e., cmid), I want to get the most recent row (i.e., maximum timecreated), before a given timestamp (i.e., again timecreated). The latter part makes it hard.

I don't see how I can do this without using at least three (sub)queries, and even then I'm unable to make it work properly in any case.

So far, I only managed to get it partially working, for example, only for users with at least two rows. This is wrong, of course. If there is a user with only a single row, which has a correct timecreated value, then I want to get this row as a result. For any other user, I want to get the one row with the greatest timecreated value that is below the given maximum date (as Unix epoch timestamp).

Is there anything else that I should mention? Tell me.

I think that is exactly the same case of the max-per-group but with some extended conditions and can be solved by single subquery:

SELECT IF( b.id IS NULL, NULL, a.id ) AS id -- an opposite to COALESCE()
     , a.cmid
     , a.userid
     , b.maxts -- can be NULL
  FROM table AS a
  LEFT JOIN ( SELECT id -- if you need no rows for users have no rows before
                        -- the given timestamp replace the LEFT JOIN
                        -- by plain JOIN
                   , cmid
                   , userid
                   , MAX(timecreated) AS maxts
                FROM table
               WHERE cmid = given_cmid
                 AND timecreated < given_timecreated -- additional condition
               GROUP BY userid
            ) AS b ON b.userid = a.userid
                  AND b.cmid   = a.cmid
                  AND b.maxts  = a.timecreated
  WHERE a.cmid = given_cmid
;

This query produce the result for each userid in the table including those having no rows before the given_timecreated but with maxts, id and cmid set to the NULL.

Here condition WHERE cmid = given_cmid is used twice to reduce the size of the subquery result.

Sure you need an index (cmid, userid, timecreated) for acceptable performance. The order columns listed in the index can be different according to the specific data you have.


Find application password expiration date I changed password of my application on one sql server?
when I am using Login property command I am getting last login date.
I want to know when my application password is going to expired in SQL server.
Please help me.
Thank You all.

Use the LOGINPROPERTY function by specifying the DaysUntilExpiration property:

select loginproperty('YourLoginName', 'DaysUntilExpiration');

Reference: BOL reference on LOGINPROPERTY


SQL Server - login failure every day at same time with the service account I have dozens of SQL Servers, on many different versions. Many of them have an error in the SQL Server error log EVERY DAY at just about 2 am.

Login failed for user 'TheDomain\SQLServerAgentServiceAccount'

Some show this reason:

Reason: Token-based server access validation failed with an infrastructure error.
Check for previous errors. [CLIENT: <local machine>]

Error: 18456, Severity: 14, State: 11.

The rest show this:

Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
Error: 18456, Severity: 14, State: 5.

Our network team doesn't see anything suspicious in this time frame. The second reason makes sense and is true - there ISN'T a matching login for the service account. I don't know why there would be an explicit one created for a service account.

What could the servers be trying to do at the same time every day? There are no Agent jobs, backups, maintenance plans, or any other known services that would do anything.

Using an Extended Events session I was able to track down the issue and see that it came from the syspolicy_purge_history job that SQL Server creates by default to clean up Policy Management records. Step 3 is "Erase Phantom System Health Records". This contains a PowerShell script that tries to connect to OTHER instances on the machine, causing the login failures. This is a known bug and only affects machines with more than 1 SQL Server instance.  https://connect.microsoft.com/SQLServer/feedback/details/606801/syspolicy-purge-history-job. So when you see login failures on an instance, they are not that instance trying to connect to itself, but another instance on the same machine.

The link here explains further and the code below will update the job step to connect to only the instance the job is running on.

https://www.codykonior.com/2015/05/31/login-errors-with-syspolicy-purge-history/

  Exec  msdb.dbo.sp_update_jobstep @job_name = 'syspolicy_purge_history', @step_name = 'Erase Phantom System Health Records.', @step_id = 3,
      @command=N'$applicationName = "SQLPS ($env:USERNAME@$env:COMPUTERNAME)"
  $SQLServerConnection = New-Object System.Data.SqlClient.SqlConnection
  $SQLServerConnection.ConnectionString = "Data Source=$(ESCAPE_NONE(SRVR));Initial Catalog=master;Integrated Security=SSPI;Application Name=$applicationName"
  $PolicyStoreConnection = New-Object Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection($SQLServerConnection)
  $PolicyStore = New-Object Microsoft.SqlServer.Management.Dmf.PolicyStore ($PolicyStoreConnection)
  $PolicyStore.EraseSystemHealthPhantomRecords()
  '

OR, copy and paste the below into the job step:

$applicationName = "SQLPS ($env:USERNAME@$env:COMPUTERNAME)"
$SQLServerConnection = New-Object System.Data.SqlClient.SqlConnection
$SQLServerConnection.ConnectionString = "Data Source=$(ESCAPE_NONE(SRVR));Initial Catalog=master;Integrated Security=SSPI;Application Name=$applicationName"
$PolicyStoreConnection = New-Object Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection($SQLServerConnection)
$PolicyStore = New-Object Microsoft.SqlServer.Management.Dmf.PolicyStore ($PolicyStoreConnection)
$PolicyStore.EraseSystemHealthPhantomRecords()


Does MySQL's MyISAM use cache before insert? I want to test two different disks, one of them is HDD and another is SSD.

I use exactly the same information and app for both servers. They are exactly the same, just one of them is SSD and another is HARD.

When I run a simple script that inserts 1,000,000 rows into the database they take a similar time to run. Sometimes HARD is faster!

I want to know does MySQL's MyISAM use any caching, so I can get the same time to insert or I have same disk?

If they use cache before insert how can I disable that to test disk speed?

MyISAM does not cache data at all. It caches only indexes ( See my post What are the main differences between InnoDB and MyISAM? )
You could just set key_buffer_size to something absurbly small, like 8 (the minimum allowed). You may as well disable the query cache while you're at it (Setting query_cache_size to 0).
Just add these lines to /etc/my.cnf
[mysqld]
key_buffer_size = 8
query_cache_size = 0

and run service mysql restart
This will effectively disallow caching anything for MyISAM.
Give it a Try !!!
UPDATE 2013-02-19 13:37 EDT
You just commented:

Also if I understand correctly , you want to say InnoDB use Both INDEX AND Data in memory , so it should have faster speed than MyISAM , but I saw MyISAM insert speed is 10 time faster than InnoDB, why ?

Believe it or not, it is possible to tune MyISAM to outperform InnoDB

Jul 06, 2012 : MySQL table with 100,000 records queried often
May 03, 2012 : Which is faster, InnoDB or MyISAM?
Sep 20, 2011 : Best of MyISAM and InnoDB


stored procedure with multiple statements does not execute all statements when run from job ** see updates below

I have a stored procedure (SQL Server 2012) that deletes old records from a table, then inserts new records from a view to synchronize the table with the view. When I execute the procedure from the command line, it works as intended, so I put it in a job to run every morning. When the procedure is run from the job, no errors arise, the job completes "successfully" in a fraction of the time I would expect, but no records have been inserted into the table. The delete works, but nothing gets inserted.

In trying to figure what is happening, I added some print messages on either side of each statement, "delete started", "delete ended", etc - and all of the messages show up in the job log - so the code is stepping through, it's just not inserting any records into the table.

My first inclination was it was some kind of permissions problem, but the delete statement works fine, and I have other jobs that are configured with the same owner that insert with no problem. I double checked the "mirror" schema, and the owner listed on the job has insert, update, delete, select privileges all enabled. (Still might be a permissions problem, I haven't done much configuration on that front, insights welcome.)

The actual statement is quite long (though uncomplicated), so here's a shortened version of what it's doing with identical syntax. I actually don't think the problem is with the code, as when executed from the command line, it works fine, but here it is anyway - maybe it will prompt just the right Q/A that I need.

Any ideas as to where to look would be appreciated:

**** 2014-03-27 a few updates: it occurred to me to try removing the delete part and just have an insert, just to isolate the problem. Same result, insert didn't happen, job succeeded. So I guess it's not about having more than one statement, it's the insert that is not working, flat out.

Also tried changing owner to myself as well as another user, but those resulted in failed job due to

"Unable to determine if the owner (PRELIANT\reports) of job CRM Reporting Dataset Updates has server access"

For reference, here is the exact message from the job history message window when the job "succeeds":
Date        3/27/2014 7:33:11 AM
Log     Job History (CRM Reporting Dataset Updates)

Step ID     2
Server      ULYSSES
Job Name        CRM Reporting Dataset Updates
Step Name       tblPhoneCall Insert
Duration        00:00:54
Sql Severity    0
Sql Message ID  0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted   0

Message
Executed as user: PRELIANT\reporting. begin insert [SQLSTATE 01000] (Message 0)  finish insert [SQLSTATE 01000] (Message 0).  The step succeeded.

/* remove all records from mirror that are not 'complete' (complete records are read only and will not change) */
    delete MyDB.mirror.tblPhoneCall
    where statecode <> 1 ;

/* remove records from mirror that were deleted from CRM */
    delete tbl
    from
        MyDB.mirror.tblPhoneCall tbl left join
        AnotherDB.dbo.PhoneCall vw on vw.ActivityId = tbl.activityid
    where
        vw.ActivityId is null;

/* insert all records from CRM where there is no match on mirror table, which now includes new records, or records that were not previously completed */
insert MyDB.mirror.tblPhoneCall
(
    activityid
    , field1
    , field2
)
select
    vw.activityid
    , vw.field1
    , vw.field2
from
    AnotherDB.dbo.FilteredPhoneCall vw left join
    MyDB.mirror.tblPhoneCall tbl on tbl.activityid = vw.activityid

where
    tbl.activityid is null;

The answer, it turned out, is something no one in forum land would have been able to guess at, no matter how good with SQL Server they were. It was application-level security that no one here was aware of.

The CRM database which was the source of the data for insert, contains a table of system users managed on the front end of the application. The vendor who developed the software set it up, but as they left no documentation, no one here was aware of the security layer on the back-end. It was confusing, largely, because the security only affects certain views - so all those other jobs that were working were calling for information from tables and views that were not influenced by the security.

When testing logged in as me, the procedure worked because I am set up in the CRM table as an administrator, but the credentials used to run SQL Server agent don't even exist in the CRM user table.

Had nothing to do with SQL Server security, but everything to do with permissions (didn't see that coming)...


what is difference between last_worker_time and last_elapsed_time in DMV sys.dm_exec_query_stats? what is meaning of last_worker_time and last_elapsed_time in DMV sys.dm_exec_query_stats and what is differnce between them?

when I fire below query

    SELECT TOP 20
qs.last_worker_time, qs.last_worker_time/1000000 last_worker_time_in_S,
qs.last_elapsed_time, qs.last_elapsed_time/1000000 last_elapsed_time_in_S
FROM sys.dm_exec_query_stats qs
order by qs.last_worker_time desc

I get reult like below.

On thing i notice was either both are equal or elapsed time is more than worker time.I would like to unsderstand significany of both so it also might help me in performance tunning.

Worker time is the time the task(s) was effectively active, occupying a scheduler and running code (ie. not suspended). Elapsed time is clock time. On a DOP 1 query the worker time will be at most the elapsed time, less if the task was suspended at any moment during execution (thus the clock time would advance, but the worker time not). For DOP > 1 the worker times aggregate so they can exceed elapsed time, while still subject to suspension.

A significant difference between worker time and elapsed time is indicative of blocking, consider the 682616 worker vs. 11509766 elapsed: this a request that blocked for 11 seconds waiting on something (probably a lock).


Statistics and row estimation Hope someone can help me to learn how SQL Server uses stats to estimate the number of records.

Test Scripts

USE [tempdb]

GO

CREATE TABLE t1
(
a INT NOT NULL,
b INT NOT NULL,
c INT CHECK (c between 1 and 50),
CONSTRAINT pk_a primary key(a)
);

GO

INSERT INTO t1(a,b,c)
SELECT number, number%1000+1, number%50+1
FROM master..spt_values
WHERE type = 'P' AND number BETWEEN 1 and 1000;

GO

CREATE STATISTICS s_b ON t1(b);
CREATE STATISTICS s_c ON t1(c);

GO

Sample Query

DECLARE @c INT=300
SELECT * FROM t1 WHERE b>@c
SELECT * FROM t1 WHERE b>300

Query 1

Estimated Number of rows 300

Query 2

Estimated Number of rows 700

Questions

Why such a huge difference between 1st and 2nd query row estimation.
2nd Query how SQL estimating to 700 from the Stats
Any good article to learn these.

The queries give different estimates because the first one does not benefit from the Parameter Embedding Optimization. From the point of view of the optimizer the query might as well be:

SELECT * FROM t1 WHERE b>???;

To get an idea of how cardinality estimates might work for simple queries, sometimes I think about what I would do if I had to program the query optimizer to produce an estimate. Often those thoughts don't exactly match what SQL Server does but I find it helpful to keep in mind that the optimizer is almost always working with imperfect or limited information. Let's go back to your first query:

DECLARE @c INT=300;
SELECT * FROM t1 WHERE b>@c;

Without a RECOMPILE hint the optimizer doesn't know the value of the @c local variable. There isn't enough information here to do a proper estimate, but the optimizer needs to create an estimate for all queries. So what can a programmer of the optimizer do? I suppose that a sophisticated algorithm that makes a guess based on the distribution of values in the column would be possible to implement, but Microsoft uses a hardcoded guess of 30% for an unknown inequality. You can observe this by changing the value of the local variable:

DECLARE @c INT=999999999;
SELECT * FROM t1 WHERE b>@c;

The estimate is still 300 rows. Note that the estimates do not need to be consistent between queries. The following query also has an estimate of 300 rows as opposed to 1000 - 300 = 700:

DECLARE @c INT=300;
SELECT * FROM t1 WHERE NOT (b>@c);

Conceptually, it can be rewritten as

DECLARE @c INT=300;
SELECT * FROM t1 WHERE b<=@c;

Which will have the same hardcoded 30% estimate.

Your second query does not use a local variable, so the optimizer can directly use the statistics object that you created on the column to aid with estimation. Histograms can only have up to 201 steps and your table has 1000 distinct values so there are some filter values in which the optimizer does not have complete information. In those cases it needs to make more of a guess. Here's how you can view the histogram for the b column:

DBCC SHOW_STATISTICS ('t1', 's_b') WITH HISTOGRAM;

On my machine I got lucky in that 300 is the high value for one of the steps of the histogram:

You can find an explanation of the different columns in the documentation. Going forward I'm going to assume that you know what they all mean. The query requests rows with b > 300, so the histogram with a RANGE_HI_KEY of 300 is irrelevant, as are all of the steps with a lower RANGE_HI_KEY. For this query, I would program the optimizer to simply sum all of the RANGE_ROWS and EQ_ROWS values for the remaining 133 steps of the histogram. Those columns add up to 700 and that is SQL Server's estimate.

Other filter values may not give exact results. For example, the following two queries both have cardinality estimates of 704 rows:

SELECT * FROM t1 WHERE b>293; -- returns 707 rows
SELECT * FROM t1 WHERE b>299; -- returns 701 rows

Both estimates are very close but not exactly right. The histogram does not contain enough information for those values to provide an exact estimate.


Does sp_indexoption cause index rebuilds? I want to switch AllowRowLocks=ON on a number of indexes. Some of those indexes are on large tables, and I don't really want to rebuild or reorganize the index.

It is not clear from the MSDN article whether or not the index will be rebuilt.

As a secondary related part of this question, the MSDN article states that the use of this is being phased out and shouldn't be used anymore:

  This feature will be removed in the next version of Microsoft SQL
  Server. Avoid using this feature in new development work, and plan to
  modify applications that currently use this feature. Instead, use
  ALTER INDEX (Transact-SQL).

Looking at the ALTER INDEX, it seems you are forced to rebuild or reorganize. Have I understood that correctly? Through ALTER INDEX can I just reset this row lock option?

Under the covers sp_indexoption just does an ALTER INDEX as well.

My recommendation would be to forget about sp_indexoption and just utilize ALTER INDEX:

alter index IX_YourIndex
on dbo.YourTable
set
(
    allow_row_locks = on
);
go

As noted in the BOL reference for ALTER INDEX, when you specify just the set options:

  Specifies index options without rebuilding or reorganizing the index


How to determine if a timestamp in one table is between two timestamps in a subquery of another table? I have two tables - one titled "index" which contains the 'datetime' timestamp with time zone, and one titled "planning" which contains the 'start_time' and 'end_time' timestamps.

Here are the schema of the two tables (edited for readability):

                 Table "public.index"
    Column     |           Type           | Modifiers
---------------+--------------------------+-----------
 datetime      | timestamp with time zone | not null
 pln           | boolean                  |

                     Table "public.planning"
      Column      |           Type           |         Modifiers
------------------+--------------------------+----------------------------
 name             | text                     |
 start_time       | timestamp with time zone |
 end_time         | timestamp with time zone |
 macro            | text                     |
 state            | text                     | default 'deselected'::text

I would like to assign TRUE to the items in the "index" table if their 'datetime' timestamp falls between a 'start_time' and 'end_time' in a subquery of the "planning" table.  Here is my attempt to do so:

UPDATE index
SET pln=TRUE
WHERE datetime BETWEEN (SELECT start_time
                        FROM planning
                        WHERE state='selected' AND macro NOT LIKE 'mae%')
                   AND (SELECT end_time
                        FROM planning
                        WHERE state='selected' AND macro NOT LIKE 'mae%');

which results in the error:

ERROR:  more than one row returned by a subquery used as an expression

You can do this with one EXISTS subquery:

UPDATE "index" AS i
SET pln = TRUE
WHERE EXISTS
      ( SELECT *
        FROM planning AS p
        WHERE p.state = 'selected'
          AND p.macro NOT LIKE 'mae%'
          AND p.start_time <= i.datetime
          AND i.datetime < p.end_time
      ) ;

I changed the BETWEEN to >= start_time and < end_time, as it is more common (and more sensible) to have inclusive-exclusive datetime ranges. You can change it back to >= and <= or to (the equivalent) BETWEEN.


Is it faster to load several small files, or one big file? We have a 26GB file that we have to load into an empty table. I was curious, is it faster to load nine 3GB files or one 26GB file? Or is there no difference?

It's easier for the storage engine to load in smaller chunks. The reason is that a big data load fills up the InnoDB rollback segment, and that takes time to purge.

For this reason, tools like pt-fifo-split exist. This helps to process a huge file in modestly-sized pieces, without copying the pieces to disk.


CHROOT install after I already installed Mysql 5.6 I installed some time ago Mysql 5.6 on Ubuntu server 12.04 and now I'd like to enhance security by putting mysql into a CHROOT environment on that same Ubuntu server.

Is this possible without re-installing the whole mysql?  mysql files are under var/lib and system files are under OPT/mysql/server-5.6

Anyone did this before?
Thanks

I didn't tested it but mysql has build in support for chroot by adding "chroot=/path/to/chroot" into my.cnf

Normally mysql should start and load all libraries and the log files it needs and then make a chroot to the path set in the parameter.
That means the database must be in the chroot and the path in the config file have to be relative to the chroot.

So if your "datadir" path is set to /var/lib/mysql/data and your chroot is /var/lib/mysql you have to change the "datadir" path to /data

According to the documentation LOAD DATA INFILE and SELECT ... INTO OUTFILE having some limits.

http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_chroot