PostgreSQL not SELECTing by RETURNING value of INSERT I have a query that's supposed to insert a new row, then return it. Normally a RETURNING would suffice, but there's a lot of other JOINed data must be added, so a SELECT seems to be necessary after the INSERT.
In this simplified example id is a SERIAL:
WITH new_row AS (
  INSERT INTO my_table (
    some_row,
    some_other_row
  ) VALUES (
    0,
    'whatever'
  ) RETURNING id
)

SELECT
   my_table.*,
   other_table.*
FROM my_table
LEFT JOIN other_table ON other_table.somekey = my_table.id

WHERE my_table.id = (SELECT id FROM new_row)

The result is an empty set. Apparently WHERE doesn't work.
If I just do this:
WITH new_row AS (
  INSERT INTO my_table (
    some_row,
    some_other_row
  ) VALUES (
    0,
    'whatever'
  ) RETURNING id
)

SELECT id FROM new_row;

then I get the value of id correctly.
Why doesn't it work in the WHERE condition?

The basic problem is that the insert (that happens in the CTE) in not visible (via the my_table snapshot) in the main query at that time.
So you could finish first the CTE and then select for example from a temporary table

CREATE tABLE my_table(id serIAL,some_row
 INT, some_other_row varchar(199))

WITH new_row AS (
  INSERT INTO my_table (
    some_row,
    some_other_row
  ) VALUES (
    0,
    'whatever'
  ) RETURNING id
)
SELECT new_row.id INTO films_recent  FROM new_row;
SELECT
   my_table.*
FROM my_table
WHERE my_table.id = (SELECT id FROM films_recent)

1 rows affected

id | some_row | some_other_row
-: | -------: | :-------------
 1 |        0 | whatever

db<>fiddle here

Or use new_row in the main query, which makes the insert changes visible!:

WITH new_row AS (
  INSERT INTO my_table (
    some_row,
    some_other_row
  ) VALUES (
    0,
    'whatever'
  ) RETURNING *
)
SELECT
   new_row.*
FROM new_row ;

It's like a schrödinger cat, the new row can be visible or not ;)


Restore a newer filegroup backup So this is my set up. In our data warehouse we have mostly tables that are filled with data from a ETL-process. We do not take backup of these tables since we have an option to do a full reset using ETL. We also have a couple of tables with historical data. These are valuable and need to be backed up.

What I did was to split the historical tables into a separate filegroup and do a backup of this filegroup pretty often.

Then comes the trouble. I want to restore this. Since the historical filegroup are newer than the primary, I'm unable to restore it because I get a SLN error. I have simple recovery mode on the database.

In case of a data crash my thought was to restore the primary filegroup (consisting of empty tables) than restore the newest historical filegroup with the latest data, then start the reset action of my ETL.

Any ideas or solutions to how I can manage to to this?
I'm running MSSQL Server 2014

One of the considerations implementing multiple databases is different RTP/RPO requirements.  It seems this is the case in your situation because the RPO is different.

Consider creating a separate databases for the ETL tables and historical data.  Backup the historical data database as needed to meet your RPO.  The ETL database can be backed up infrequently (e.g. only after schema changes), or not at all if you can simply recreate the database script.

Personally, I would split the database only if the size of ETL tables are disproportionally larger than the historical data.  The additional complexity might not be worth the effort, especially with backup compression.  If the ETL tables can be memory optimized, you have the option to specify SCHEMA_ONLY durability for those tables as long as you can run the ETL reset process after an instance restart.  That would avoid the additional backup overhead and allow you to keep all in a single database.


Stored procedure in master slave replication I have a master-slave replication for a schema products which is being replicated with binlog_do_db = products.

one of the the tables in the master schema is being archived by calling a stored procedure which is in schema products_archive.The data is moved to a different schema on the same database from products to products_archive.

START TRANSACTION;
        INSERT INTO products_archive.dial
        SELECT * FROM products.dial
        WHERE products.dial.dial_datestamp <= DATE(NOW() - INTERVAL 30 DAY);
        DELETE FROM products.dial
        WHERE producst.dial.dial_datestamp <= DATE(NOw() - INTERVAL 30 DAY);
COMMIT;

But the slave has only one schema products which is not being archived.
Since stored procedure is on the schema which is not being replicated the master should be archived and the slave shouldn't.

But what instead happened is the master archive was successful and the same data got deleted on the slave.

How did the data get purged on the slave as the schema products_archive is not added to list of replicated schemas.

It seems what is happening is that your stored procedure deletes rows from products.dial, and these DELETE statements are simply replicated to the slave. It doesn't matter that the stored procedure itself is in a non-replicated schema.


Getting started with AWS: Price for large databases? At the moment, I have a database that isn't accessed very often, but has 400 million rows and totals out to be 100gb disk space. I'm worried that, despite the low usage, I'll have to shell out for a near enterprise-level database instance in order to get the storage I need. How does storage and database usage affect pricing? I've been looking at pricing on the AWS site for a few weeks now and honestly still don't know how to estimate how much this database will cost. Any help is appreciated.

are you looking at RDS or EC2 ?, I would suggest you go with an EC2 as you can get a EBS backed storage around 100GB for much less on a lower instance type.have look at this calculator it will give you a good estimate


Multiple databases in production We have 3 level hierarchy in our business model. Entities at level 3 contains huge data (like dailyfacts tables).

In our current DB architecture, we have one single database & all level 1 entities separated by db schemas. All level 2 entities are being used only for logical grouping & don't contain any data as such. Then comes all level 3 entities. All daily level data is stored with respect to these entities. There is a dailyfacts table in each schema which stores data for all level 3 entities under that level 1 entity. Plus there are some other additional table are being used to store data for level 3 entities.

Currently our business is growing very rapidly & with this DB architecture we are not able to handle data properly. SQL timeouts & deadlocks have became daily headaches. Also if load on one entity increases then it starts affecting other entities as well. It very hard to scale horizontally with current DB architecture.

So after long discussion with team, we came to conclusion that we should create separate databases for each level 3 entity & one master database which will maintain all business model hierarchy. But the problem with this approach is that we will end up creating almost 100 databases(as per current number of entities & it will keep on increasing as & when new customer comes). & maintaining these many databases will become pain in the a** after sometime.

I am not an expert on DB architecture. So I want to know,

Is it OK to have these many databases in production environment? Are we moving in right direction?
If it is OK then what all things we should look at before/after migration? (all are newbies here)
Is there any other alternative to solve above problem?

We are using,

DBMS : SQL Server 2014
OS : Windows Server 2012 R2
Processor : Intel Xenon CPU E5-2630 v3 @2.40GHz, 16 Cores
RAM : 128 GB

Thanks in advance.

No, creating several databases will not help you if the underlying tables and their dependencies stay unchanged. Think of it this way: why would it?

You should get your database setup checked out by an expert and hire a database consultant. Your problems can probably be solved with changes to your indexes, partitioning or tuning DBMS parameters.


SQL Server 2005: Full-text search on a multi-lingual table I have an articles table as follows.

+-----------+--------------+---------+
| uid       | int          | PRIMARY |
| articleId | int          | UNIQUE  |
| lang      | varchar(2)   | UNIQUE  |
| title     | varchar(500) |         |
| body      | varchar(max) |         |
+-----------+--------------+---------+

I would like to implement full-text search on the article title and body.  The complication is that the title and body can be in one of six languages, indicated by the "lang" column.

I see there are noise word files for several languages, so depending on the currently set language, I would like to use the appropriate noise file when searching.

Does this require one catalog per language?  If separate catalogs are used, is it possible to, for example, not include French articles in the English catalog?

When creating the index, it is possible to specify a "LANGUAGE" argument.  Within the description of the "LANGUAGE" argument is the following:

  For non-BLOB and non-XML columns containing text data in multiple languages, or for cases when the language of the text stored in the column is unknown, use the neutral (0x0) language resource.

Then, when doing a search using FREETEXT or FREETEXTTABLE, once again use the "LANGUAGE" argument.  Microsoft defines the "LANGUAGE" argument as follows:

  Is the language whose resources will be used for wordbreaking, stemming, and thesaurus and noise-word removal as part of the FREETEXT query. This parameter is optional and can be specified as a string, integer, or hexadecimal value corresponding to the locale identifier (LCID) of a language. If language_term is specified, the language it represents will be applied to all elements of the search condition. If no value is specified, the column full-text language is used.

So, I will use one catalog and one index on the multi-lingual table using a neutral language resource.  Then I'll use the appropriate language setting when searching so everything is parsed correctly.


Problem Connecting Report Server to Report Server Database in SSRS I have configured Reporting Services on one Server and have the ReportServerDb and ReportServerTempDb on other server (Future requirement of scale out deployment).

I am connecting to these servers using a domain account. I have configured Reporting Services using ReportingServiceConfiguration Wizard and it seems to have worked fine except that the Initialization tab does not have the green tick in front (not initialized).

The error I get is:

  The report server cannot open a connection to the report server
  database. The logon failed. (rsReportServerDatabaseLogonFailed) Get
  Online Help Logon failure: the user has not been granted the requested
  logon type at this computer. (Exception from HRESULT: 0x80070569)

The domain account I have used is perfectly fine and I am able to connect using SSMS (No Password errors). I have searched through many a blogs, forums etc, but have not been able to find a solution.

Re-run through the database configuration part to select the same database and it will re-issue the grants to make sure the permissions are in place, but make sure the account you run the wizard with has sysadmin access to the instance.  If it's 2005 you can generate the grant script but it's kind of buggy.


T-SQL trigger to update columns I've been working with SQL queries for a couple of weeks and I'm trying to learn and understand from my mistakes. I have a table with two columns IsInvisible (bit) and ShortName (string) and when one is changed to update the other one also like: - if added a ~ in front of the ShortName to set IsInvisible to 1 and if ~ is removed to set it to 0 - if IsInvisible is set to 1 to add ~ in front of the ShortName and when set to 0 to remove it.

I've tried something like:

ALTER TRIGGER [dbo].[updateInvisibility]
ON [dbo].[table]
AFTER UPDATE
AS
BEGIN

UPDATE t
SET IsInvisible = (CASE WHEN i.ShortName like '~%' THEN 1 ELSE 0 END),
    ShortName = (CASE WHEN i.IsInvisible = 1 AND t.ShortName NOT LIKE '~%'
                 THEN '~' + t.ShortName
                 ELSE t.ShortName
            END)
FROM table t JOIN
     inserted i
     ON t.Id = i.Id;

end

My problem is that when I'm updating one of the columns or both, nothing happens and I get this error:

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)

What am I doing wrong?

The "Allow Triggers to Fire Others" option displays "True".

Here is the problem:

You update the table
It triggers the update trigger: updateInvisibility
In the trigger you update the table again so it triggers the updateInvisibility trigger

and then it keeps doing that so you end up triggering the trigger many times in a sort of recursive fashion without even realizing. SQL server will allow this call nesting to be 32 levels deep and then it throws this error:

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)

which is pretty clear.
T-SQL Solution
The code-only solution is to check the nesting level and leave if bigger than 1 (1 is the first time it is called by the update):
IF TRIGGER_NESTLEVEL() > 1 RETURN;

Like this:
ALTER TRIGGER [dbo].[updateInvisibility]
ON [dbo].[table]
AFTER UPDATE
AS
BEGIN
    IF TRIGGER_NESTLEVEL() > 1 RETURN;

    -- Do your work...
END

Alternative
As pointed out in a comment by RBarryYoung, you can prevent this problem in the first place by disabling recursive triggers.


Unable to enter characters in MariaDB I have just switched from MySQL to MariaDB and am running into a very silly problem: I cannot enter any extended characters in the database (for example, ö ä or å).
The system is utf8, and I tried switching the keyboard to swedish — works everywhere except for mariadb prompt, and even tried copying and pasting from gucharmap — nothing is pasted at prompt.
Either method worked under MySQL in the past. I looked in MariaDB conf.d, the character_set_system was not set so I changed it to utf8, but that did not help. default-character-set in 50-client.cnf is set to utf8mb4. Any ideas?
System: debian bulseye/sid
Mariadb: Ver 15.1 Distrib 10.5.8-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper
These are the relevant mariadb variables:
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |

| character_set_results | utf8mb4 |

| character_set_server | utf8mb4 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

| collation_connection | utf8mb4_general_ci |

| collation_database | utf8mb4_general_ci |

| collation_server | utf8mb4_general_ci |

| session_track_system_variables |
autocommit,character_set_client,character_set_connection,character_set_results,time_zone

show create table gives
ENGINE=InnoDB AUTO_INCREMENT=1625 DEFAULT CHARSET=utf8mb4

The rest are field definitions, and no collations are set.
Of course, there is a prompt: it is a standard mysql prompt.
I am not sure how to make it clearer. I am at mysql prompt. In the past, typing something like å worked fine, now it shows nothing. When I try to paste the character copied from another utility like gucharmap, nothing is pasted. When I paste "normal" characters (i.e. standard English text) everything works.
Note that the extended characters in database entries created BEFORE the switch show up just fine. Yes, it is all very mysterious.

If you are using Windows cmd, you need chcp 65001 before entering mysql/mariadb.  Since you are using a Linux variant, terminal may need SET NAMES utf8mb4; from within the mysql/mariadb.
If the columns are not utf8mb4, you will have other problems.  Check a table by using SHOW CREATE TABLE tablename;
This provides a rundown on common problems and leads to their solutions:  https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored


query on 2 fields but 1 is a unique value postgresql 9.6, windows 7

Here is an example of a database :

fld1 fld2
abc  D123
xyz  D123
abc  D789

how can I select rows with fld1='abc' if/only for fld2 value exists just one time in the database ?
In my example, I only want to select the third row.

I have extended a bit your sample data:

create table tbl (fld1 text, fld2 text);
insert into tbl values
('abc',  'D123'),
('xyz',  'D123'),
('abc',  'D125'),
('abc',  'D128'),
('def',  'D128'),
('abc',  'D789');

You can use EXIST in your WHERE clause with a dataset grouped by fld2:

select
    t1.*
from
    tbl t1
where
    fld1 = 'abc'
    and exists (select 1
                from tbl t2
                where t2.fld2 = t1.fld2
                group by fld2
                having count(*) = 1);

fld1 | fld2
:--- | :---
abc  | D125
abc  | D789

db<>fiddle here

Or you can use a CTE to get all fld2 rows with a count of 1, and join it with all rows where fld1 = 'abc':

with count1 as
(
    select   fld2
    from     tbl
    group by fld2
    having   count(*) = 1
)
select
    t1.*
from
    tbl t1
join
    count1
    on t1.fld2 = count1.fld2;

db<>fiddle here


MySQL user per database performance implications? There is a PHP application that uses only one MySQL username. And there are several databases in the MySQL server. Each database is used by a different company. Should I keep "one MySQL" for the entire system or should I switch to "user per database"? Will I have performance increase if I switch to "user per database"?

Using different users will not reduce or increase the performance. It is clearly a security issue.

Usually you don't want users from company A to be able to access company B data.

I would recommend to create separate users for each application and grant them only the necessary permissions.

If you run the application using a user with admin rights you risk a lot when the application gets compromised.


A Script to insert dummy Data in all tables of DB There is an SP posted in the below link stating that it will insert dummy data in the entire DB. But the SP works only for a single table. Can anyone please share the one where we can insert dummy data throughout the db?

A Script to insert dummy Data in all tables of Database

I needed to do this for a staging environment to test performance before release. What i ended up doing was using sql data generator by redgate.

Way easier than a custom solution.

sql red gate sql data generator


Which MySQL server parameters should be adjusted to improve INSERT performance for a large MyISAM table? I have a MyISAM table with roughly 20M rows for which inserting 1000 rows takes about 40 seconds on the production server (Ubuntu 12.04, MySQL 5.6.35). On the test machine (Windows 8.1, MySQL 5.6.22), the same insert takes less than 1 second which seems a more reasonable timing for me. On the production server inserts of ~1000 rows happens every 5 minutes. Which my.cnf configuration values should I try to change to improve performance on the production server?

Note: I've already increased key_buffer_size=256M (and even to key_buffer_size=1024M) which didn't change anything.

This is how the table definition looks like:

CREATE TABLE `mytable` (
  `id1` varchar(40) NOT NULL,
  `t` datetime NOT NULL,
  `id2` varchar(12) NOT NULL,
  `a` varchar(8) NOT NULL,
  `b` int(11) NOT NULL,
  `f1` varchar(4) DEFAULT NULL,
  `f2` varchar(5) DEFAULT NULL,
  `f3` varchar(16) DEFAULT NULL,
  `f4` varchar(16) DEFAULT NULL,
  `f5` tinyint(4) DEFAULT NULL,
  `f6` tinyint(4) DEFAULT NULL,
  `f7` tinyint(4) DEFAULT NULL,
  `f8` tinyint(4) DEFAULT NULL,
  `f9` tinyint(4) DEFAULT NULL,
  `f10` tinyint(4) DEFAULT NULL,
  `f11` tinyint(4) DEFAULT NULL,
  `f12` tinyint(4) DEFAULT NULL,
  `f13` varchar(8) DEFAULT NULL,
  `f14` int(11) DEFAULT NULL,
  UNIQUE KEY `id1` (`id1`,`t`,`id2`),
  KEY `t` (`t`),
  KEY `id2` (`id2`),
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

id1 and id2 are random values, t is a timestamp representing web user local times, so from a coarse perspective it's constantly increasing, but within a bunch of 1000 rows times may vary +/- 1 day.

Update 1

Rows will only be inserted into the table, never updated. Hence, there is no fragmentation, as expected:

mysql> select ENGINE, TABLE_SCHEMA, TABLE_NAME, DATA_LENGTH, INDEX_LENGTH, DATA_FREE from information_schema.tables;
+--------+--------------+----------------+-------------+--------------+-----------+
| ENGINE | TABLE_SCHEMA | TABLE_NAME     | DATA_LENGTH | INDEX_LENGTH | DATA_FREE |
+--------+--------------+----------------+-------------+--------------+-----------+
| ...                                                                             |
| MyISAM | products     | mytable        |  2301965084 |   2030800896 |         0 |
| ...                                                                             |
+--------+--------------+----------------+-------------+--------------+-----------+

Update 2

I've 'cloned' mytable to mytable2 for testing purposes and running test INSERTs into mytable2. In general I'm seeing better performance here: roughly 8 seconds for inserting 1000 rows (after some initial startup time). So, (1) looks like I'm encountering OS level caching issues for my production table. (2) When changing the random id1 to an increasing value, inserting 1000 rows takes roughly 400ms.

Update 3

After changing indexes to:

  UNIQUE KEY `t` (`t`,`id1`,`id2`),
  KEY `id1` (`id1`),
  KEY `id2` (`id2`)

inserting 1000 rows with random t, id1 and id2 keys, this takes roughly 500ms after some initial startup time: inserting first batch took 40000ms, second batch took 18000ms, ... 10th batch took 500ms. Hence, the initial question comes up again: is there any my.cnf setting to improve this startup delay?

A long list of possible improvements:

Turn off updating "atime" on the filesystem?

INDEX(id1) can be DROPped since there is another index starting with that.  This would decrease from 6 to 5 the number of indexes to update -- a small speedup.

Toss any other indexes you are not using.

Use NOT NULL where appropriate.  I doubt if you meant for all columns to be NULLable?

With a 1G key_buffer, I hope that you have at least 6G of RAM.  If not, decrease that setting.

Increasing the key_buffer will not instantly improve the index caching -- it takes time for blocks to get into the key_buffer and become useful.

If practical, "batch" the inserts.  That is,

INSERT INTO mytable (...) VALUES (...), (...), ...;

Optimal is to batch 100-1000 at a time.

Switch to InnoDB.

What will you do with the data?  (Usually the performance problems occur on the read side.)

Two Hardware suggestions:  SSD; RAID-striping with write buffer.


Populate a table that should have an X amount of rows with default values with a big insert query? Or can it done somehow by default? I have a table lets say calendar

Table Calendar --> idCal (int) , numberOfFields (int)

And each Calendar has asigned a number of fields

Table Field --> idField(int), textField(text), idCal (int) *fk

Point is right now, each time an user register, he is assigned a calendar, once the numberOfFields is populated, I select that value and generate an insert query similar to:

INSERT INTO Field (textField, idCal) Values ("","idOfTheGeneratedCalendar") , ("","idOfTheGeneratedCalendar") ......

Until I have a number of rows equivalent to numberOfFields from the table Calendar. Where each idField(int) begins at 0, and its an autoincrement up to whatever the numberOfFields is

I do that for each user. Point is... is there a better way to do this without building huge insert queries with aprox 3000 values each using a for iteration? Should I be concerned?

you can use generate_series() for that:

insert into field (textfield, idcal)
select '', c.idcal
from calendar c
where idcal = 42
  cross join generate_series(1, c.numberoffields) as g(f)


Create function with a parameter, which will become the part of IN (1, 2, 3) clause Following is the part of function declaration. Unfortunately it doesn't work the way I would like it to.

CREATE FUNCTION my_cool_func(some_parameter INTEGER, some_other_parameter INTEGER[])
RETURNS BOOLEAN
LANGUAGE 'plpgsql'
STABLE

AS $BODY$

DECLARE count_entries INTEGER;

BEGIN

    SELECT COUNT(some_table.id) INTO count_entries FROM some_table
    WHERE ...
    AND some_table.some_integer_field IN (some_other_parameter);

        IF (count_entries > 0) THEN...

some_other_parameter is a list of integer values (1, 2, 3).

My questions:

Is this function declaration correct?
How do I define the default value for some_other_parameter?

Use = ANY instead of IN

AND some_table.some_integer_field = ANY (some_other_parameter);

The equivalent of NOT IN would be <> ALL (...)

To declare a parameter with a default value use:

CREATE FUNCTION my_cool_func(..., p_id_list INTEGER[] default array[42])


Error Msg 823, Level 24, State 2, Line 1 We have been recieving the error below in one of our server during restoring a database. Has anyone experienced the same problem? If yes can you please share how you fixed it?

The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0x000001f3400000 in file 'H:\Data\XXXX_data.mdf:MSSQL_DBCC17'. 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

I have heard of this occurring during a restore of multiple databases, non-sequentially (i.e.: multiple restores occurring on different SPIDs at once), after a drive failure, on a hyper-V VM.

(I'm sorry to include all of those caveats, but that was the situation... and I'd hate to find out that any one factor changes the results.)

Sequential restores (only one at a time) seemed to have resolved the issue.


Counting number of visitors who did NOT log in I'm trying to calculate the number of visitors to the homepage of my site who did NOT then log in to the member's section of the site, while also filtering bots and admin users. Bots and admins are marked as such in the table by a separate script.

The way I'm currently doing it is counting all distinct sessions (accessid) without a logged in user (userid = 0), then counting all the distinct sessions with a logged in user (userid > 0), and finally excluding the logged in sessions from the non-logged in sessions and counting that result.

Here's the query that does the above:

SELECT Count(DISTINCT accessid) AS c
FROM   accesslog
WHERE  date = '2014-01-21'
   AND userid = 0
   AND is_robot = 0
   AND is_admin = 0
   AND url = 'www.example.com/'
   AND accessid NOT IN (SELECT DISTINCT( accessid )
                        FROM   accesslog
                        WHERE  DATE = '2014-01-21'
                           AND userid > 0
                           AND is_robot = 0
                           AND is_admin = 0)
ORDER  BY c DESC

This query can take from 1-7 seconds depending on the traffic on a given day. Is there any way I can speed up this query?

Here's the explain output for the above query:

id  select_type table   type    possible_keys   key key_len ref rows    filtered    Extra
1   PRIMARY accesslog   ref date,userid,dateaff,url,dateaccessid,userid+accessid,is_robot,is_admin  date    3   const   6113    100.00  Using where; Using temporary
2   DEPENDENT SUBQUERY  accesslog   index_subquery  accessid,date,userid,dateaff,dateaccessid,userid+accessid,is_robot,is_admin accessid    32  func    2   100.00  Using where

Do it with a LEFT JOIN

SELECT Count(DISTINCT accessid) AS c
FROM   accesslog a
LEFT JOIN
(SELECT accessid
 FROM   accesslog
 WHERE  DATE = '2014-01-21'
 AND userid > 0
 AND is_robot = 0
 AND is_admin = 0
 GROUP BY accessid)x
ON a.accessid=x.accessid
WHERE  a.date = '2014-01-21'
   AND a.userid = 0
   AND a.is_robot = 0
   AND a.is_admin = 0
   AND a.url = 'www.example.com/'
   AND x.accessid IS NULL


The multi-part identifier "d.dept_name" could not be bound I want to create  a table with department name as column name and no. of employess for each department as its row. Here dept_name is coming from Dept table and count  of emp.ID is coming from emp table.

I am getting this error for my code

  Msg 4104, Level 16, State 1, Line 3
  The multi-part identifier "d.dept_name" could not be bound.

This is my query:

SELECT d.dept_name
from dept d
inner join emp e
on e.dept_ID=d.ID

Pivot
(
  count(e.ID)
  for d.dept_Name
  in([pROD],[elect],[inst],[fin],[otherdepartment])

  )
  as pivotab

First of all, after applying PIVOT, all columns of the pivoted set must be referenced using the alias specified for the PIVOT clause. As per the manual:

  When a derived table, rowset or table-valued function, or operator clause (such as PIVOT or UNPIVOT) is used, the required table_alias at the end of the clause is the associated table name for all columns, including grouping columns, returned.

So, if everything else was correct, it would need to be

SELECT pivotab.dept_name
from dept d
inner join emp e
on e.dept_ID=d.ID

Pivot
(
  count(e.ID)
  for d.dept_Name
  in([pROD],[elect],[inst],[fin],[otherdepartment])

  )
  as pivotab

Alternatively, you could omit the alias altogether since in this case you are not joining the pivoted set to other datasets and so all columns available would be only those coming from pivotab.

However, one other problem is that you are referencing a column that is no longer available after the pivot: your dept_name column is used to produce columns [pROD],[elect],[inst],[fin],[otherdepartment] and itself does not exist in the result set.

You could try selecting all columns (*) to see the output. I suspect that you will find that your output is not what you expect, because you likely have too many grouping columns coming from the result of the join between dept and emp. How you should resolve that may need to be asked as a separate question, though.


Postgresql enabling extensions without super-user I have a PostgreSQL 9.5 server on which I have scripts that create roles and databases for users automatically. Within these databases it would be helpful to enable specific extensions (e.g. pgcrypto), but as I understand it one must be a superuser to run CREATE EXTENSION. Is there a way to enable such extensions without manually logging in with a superuser account?

From the docs on Extensions,

  superuser (boolean) If this parameter is true (which is the default), only superusers can create the extension or update it to a new version. If it is set to false, just the privileges required to execute the commands in the installation or update script are required.

The value isn't set in pgcrypto.control, so it's defaulting to true which requires a SuperUser.

This means you can not CREATE EXTENSION as the mere owner of the database, despite what the docs on CREATE EXTENSION lead you to believe.

I tried hard setting it to false, and no joy. C is an untrusted language and you'll get

  ERROR:  permission denied for language c

From the docs on pg_language

  Only superusers can create functions in untrusted languages.

... of course you can make c trusted with UPDATE pg_language set lanpltrusted = true where lanname = 'c'; as a superuser. Then CREATE EXTENSION pgcrypto will work fine as a non-superuser. But, that sounds like a bad idea if you have to worry about your users uploading source to your extension directory and then installing it in the database. That is to say, I wouldn't go that far. I'd find another way to skin this cat.


How can I know which stored procedure or trigger is using a table on SQL Server 2008 R2? This is the case that in the DB I'm checking, there is an archive table which keeps the user history, and there is a trigger or store procedure that after some time delete rows from this table, in order to avoid the oversize of the same, I didn't design the DB, I'm just taking the maintenance of an application that use this DB, so I don't know the name of these stored procedures or triggers, what I want to do is locate this stored procedure or trigger, check the code and modify it to leave this "user history" longer on the table.

Someone told me to check the "sysobjects" table, where I can actually see something with the same name of the table, but this is the only information I have been able to retrieve, any advise?

Thank you.

Search all code using sys.sql_modules

SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules sm
WHERE definition LIKE '%Mytable%'

Or use Red Gate SQL Search which is completely free

Do not use syscomments or INFORMATION_SCHEMA.RUBBISH


How to delete previous incarnation in MySQL (/w Group Replication) There exist almost no mentions whatsoever of MySQL "incarnations", so I'm opening this topic to make a precedent, even if I solve it myself. Feel free to help of course :)

2018-08-10T16:21:34.795206Z 0 [Note] Plugin group_replication reported: 'Old incarnation found while trying to add node fuelstat-db-1.novarumcloud.com:33061 15339180934204040.'

The Group Replication member node fails to join the GR cluster with the above error, after I have rebuilt the same server. I've checked the internal MySQL tables and I still can't locate what makes it unhappy with that "incarnation". Thanks in advance.

Solved by bringing down the entire cluster, as mentioned in a similar issue here. After the restart, bringing the GR cluster back up from scratch had no mentions of incarnations.


ORDER BY ASC is 300x faster than ORDER BY DESC The ascending sort is 300x faster than descending sort on a column which has a B-Tree index.
I have table of around 20 million rows.
I have indexes on inserted_at and x columns.

Ascending Sort

select
  * from table_a a where
  a.inserted_at >= '2018-09-01 00:00:00.0' and a.x in ('some_x_val')
  order by a.inserted_at asc
  offset 0 limit 20;

Limit  (cost=0.44..2027.69 rows=20 width=51) (actual time=418.529..464.558 rows=20 loops=1)
  ->  Index Scan using table_a_inserted_at_index on table_a a  (cost=0.44..5406390.18 rows=53337 width=51) (actual time=418.527..464.543 rows=20 loops=1)
        Index Cond: (inserted_at >= '2018-09-01 00:00:00'::timestamp without time zone)
        Filter: (event_type_id = ''some_x_val'::uuid)
        Rows Removed by Filter: 4092
Planning time: 0.177 ms
Execution time: 464.596 ms

explain (analyze, verbose, buffers) select * from table_a a where a.inserted_at >= '2018-09-01 00:00:00.0' and a.x in ('some_x_val') order by a.inserted_at asc offset 0 limit 20;
                                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit  (cost=0.44..2027.77 rows=20 width=51) (actual time=527.032..575.516 rows=20 loops=1)
  Output: x, y, z
  Buffers: shared hit=2492 read=905
  ->  Index Scan using table_a_inserted_at_index on public.table_a a  (cost=0.44..5406596.96 rows=53337 width=51) (actual time=527.029..575.496 rows=20 loops=1)
        Output: x, y, z
        Index Cond: (a.inserted_at >= '2018-09-01 00:00:00'::timestamp without time zone)
        Filter: (a.x = 'some_x_val'::uuid)
        Rows Removed by Filter: 4092
        Buffers: shared hit=2492 read=905
Planning time: 0.162 ms
Execution time: 575.550 ms

Descending sort

select
  * from table_a a where
  a.inserted_at >= '2018-09-01 00:00:00.0' and a.x in ('some_x_val')
  order by a.inserted_at desc
  offset 0 limit 20;

Limit  (cost=0.44..2027.69 rows=20 width=51) (actual time=148377.757..148385.703 rows=20 loops=1)
  ->  Index Scan Backward using table_a_inserted_at_index on table_a a  (cost=0.44..5406383.67 rows=53337 width=51) (actual time=148377.756..148385.691 rows=20 loops=1)
        Index Cond: (inserted_at >= '2018-09-01 00:00:00'::timestamp without time zone)
        Filter: (x = 'some_x_val'::uuid)
        Rows Removed by Filter: 2737952
Planning time: 0.663 ms
Execution time: 148385.756 ms

explain (analyze, verbose, buffers) select * from table_a a where a.inserted_at >= '2018-09-01 00:00:00.0' and a.x in ('some_x_val') order by a.inserted_at desc offset 0 limit 20;
                                                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit  (cost=0.44..2027.77 rows=20 width=51) (actual time=168306.377..168312.296 rows=20 loops=1)
  Output: x, y, z
  Buffers: shared hit=2439002 read=359523 dirtied=288
  ->  Index Scan Backward using table_a_inserted_at_index on public.table_a a  (cost=0.44..5406596.96 rows=53337 width=51) (actual time=168306.375..168312.282 rows=20 loops=1)
        Output: x, y, z
        Index Cond: (a.inserted_at >= '2018-09-01 00:00:00'::timestamp without time zone)
        Filter: (a.x = 'some_x_val'::uuid)
        Rows Removed by Filter: 2745418
        Buffers: shared hit=2439002 read=359523 dirtied=288
Planning time: 0.139 ms
Execution time: 168312.326 ms

What could be the problem?

I think the problem is two-fold (or 3-fold):

missing composite index on both columns filtered: (x, inserted_at)
uneven distribution
old statistics

   Filter: (a.x = 'some_x_val'::uuid)
   Rows Removed by Filter: 2745418

The unusual distribution means that the single index scan has to do a lot of work to remove the rows that don't match the 2nd filter (by the other (x) column). The index scan for ORDER BY ASC is just lucky and finds the 20 rows a bit more quickly, only by luck. It could be the other way around with other values in the parameters of inserted_at and x

So the best way to solve this is to add this composite index. You could follow the advice on the linked question and answer by Erwin, to update statistics on the table but this may help with the issue or not. It's not certain.


Does Data Migration Advisor migrate system databases? I would like to know if Data Migration Assistant copies system databases (master, model, msdb) into the new server.

No, because the Data Migration Assistant helps you to move from SQL Server to Azure SQL.

Azure SQL is a different product and thus the system databases are not the same. You will only be able to access the master database on your Azure SQL deployment. That being said, there really isn't anything you will be missing by not moving those over, since most functions of msdb aren't available on Azure SQL in the same way anyways.

From the documentation:

  For Azure SQL Database single databases and elastic pools, only master
  Database and tempdb Database apply.


SQL Server - In Recovery Was uploading a few hundred thousand rows in my SQL 2008 R1 database and have come in this morning to an "In Recovery" message next to the database name in SQL Management Studio.

I've tried a DBCC CheckDB, which returns  CHECKDB found 0 allocation errors and 0 consistency errors in database 'DBName'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.  When I check the status (state_desc) in sys.databases, it returns ONLINE.  In fact, nothing in the sys.databases table looks out of the ordinary.

I can't get rid of the 'In recovery' message though, and I don't want to just restart.  Is there any way of telling how long it will be like this for, or are there any other problems I should check for?

first you must identify the spid

look up

sp_who2 'active' ---> look for the spesific session_id

  SELECT * FROM sys.dm_exec_sessions WHERE session_id = 60 SELECT
  command,percent_complete FROM sys.dm_exec_requests WHERE session_id =
  60


Execute Stored Procedure that cannot be manually stopped I am looking for a solution to:

Allow an admin to manually run a stored procedure
Once the stored procedure runs the same admin cannot stop it using the "Cancel Executing Query"
The stored procedure must be invoked from SSMS ONLY.
No other external program or language except for MS Management Studio and TSQL can be used.

I have tried creating a stored procedure than invokes another stored procedure, but it did not work. Any ideas are welcome....

What you want is, by definition, impossible. Because the definition of admin is: the user to which no privilege can be denied. Therefore any attempt to do something that has the phrase 'admin cannot' is flawed from the get go, because the admin will, simply, always 'can'.

There are more fallacies in your reasoning, like the belief that there is any way to make privilege decision based on untrusted input. Case in point, the requirement that The stored procedure must be invoked from SSMS ONLY.. This is a security decision (privilege to run the procedure) based on an untrusted input (the APP_NAME()).

What I suggest is rather than ask us how to implement what you already concluded must be the solution, better lay down your requirements and let us suggest possible solutions to your problem.


How to have CTE query recurse parent query result one by one? I have this query used on Postgres to return place administrative levels.

WITH RECURSIVE hierarchy(name, pinyin, level) AS (
    SELECT p.name, p.pinyin, p.level, p.upper_place_object_id_fk
    from place p
    where p.pinyin = 'Jiulong'
    UNION ALL
    SELECT up.name, up.pinyin, up.level, up.upper_place_object_id_fk
    from hierarchy h
    INNER JOIN place up ON h.upper_place_object_id_fk = up.object_id
    WHERE h.upper_place_object_id_fk IS NOT NULL
)
SELECT h.name, h.pinyin, h.level from hierarchy h;

It works just fine for a single match, e.g.

"Jiulong"   4
"Yingde"    3
"Qingyuan"  2
"Guangdong" 1

But of course, there can be several places with the same name (and a different position in the admin hierarchy)

"Fenggang"  4
"Fenggang"  4
"Huaiji"    3
"Dongguan"  2
"Zhaoqing"  2
"Guangdong" 1
"Guangdong" 1

In this case the result doesn't make sense as I can't reconstruct the proper hierarchy.
And that's because the recursion occurs for every result of the parent query at the same time.

It'd work if the recursion could be done until the exit condition for each result of the parent.

It is possible to control how the recursion is done?

Place Table script

CREATE TABLE public.place
(
    object_id uuid NOT NULL DEFAULT uuid_generate_v4(),
    upper_place_object_id_fk uuid,
    name character varying(50) COLLATE pg_catalog."default" NOT NULL,
    level smallint NOT NULL,
    CONSTRAINT pk_place PRIMARY KEY (object_id),
    CONSTRAINT unique_place UNIQUE (name, upper_place_object_id_fk, level)
,
    CONSTRAINT fk_place_upper_pla_place FOREIGN KEY (upper_place_object_id_fk)
        REFERENCES public.place (object_id) MATCH SIMPLE
        ON UPDATE RESTRICT
        ON DELETE RESTRICT
        NOT VALID
)

and here are the relevant data from the table

object_id                               upper_place_object_id_fk                 name      level
"540ed8e9-c456-4a46-8049-1332f4ab52c1"  NULL                                    "Guangdong" 1   false
"4f7c7071-f917-4ab8-82c9-75aff0ba44d0"  "540ed8e9-c456-4a46-8049-1332f4ab52c1"  "Dongguan"  2   false
"afce8ec3-599f-4cea-9227-29bb750d4b01"  "540ed8e9-c456-4a46-8049-1332f4ab52c1"  "Zhaoqing"  2   false
"714b8e23-c346-4370-8697-25bb09497342"  "afce8ec3-599f-4cea-9227-29bb750d4b01"  "Huaiji "   3   false
"0461963d-e886-4149-bded-ee08216dd374"  "714b8e23-c346-4370-8697-25bb09497342"  "Fenggang"  4   false
"6955f89b-b7d3-4b1e-b21e-fb584d7f165b"  "712c1f45-24ac-4711-a47f-22513afc6fdd"  "Dongguan"  4   true
"d931f691-0ec7-4fc7-9197-b15461e984e0"  "4f7c7071-f917-4ab8-82c9-75aff0ba44d0"  "Fenggang"  4   true

It does not seem possible to get control on how the recursion happens in CTE.

One solution to disentangle results is to add info on the parent query and build a 'path' for each result.

As shown in this post:
How do I sort the results of a recursive query in an expanded tree-like fashion?

This seems to me the most acceptable way at the moment, but I'm still looking for a way to be able to directly get more concise results.


Breaking down by minutes in between two dates In MySQL, I've got a data model with a "visit" table that has start and end datetimes.

How can I get a breakdown of the number of visits that are active per minute?

e.g.

| visit_id | start | end   |
| 1        | 10:10 | 10:15 |
| 2        | 10:15 | 10:20 |
| 3        | 10:12 | 10:18 |

Would return something similar to:

| time  | number of visits |
| 10:10 | 1                |
| 10:11 | 1                |
| 10:12 | 2                |
| 10:13 | 2                |
| 10:14 | 2                |
| 10:15 | 2                |
| 10:16 | 2                |
| 10:17 | 2                |
| 10:18 | 2                |
| 10:19 | 1                |
| 10:20 | 1                |

You are going to think I am crazy but here it goes

PROPOSED QUERY

select the_time,count(1) visits from
(select the_date + interval m minute the_time from
(select '2014-10-30' the_date) AA,(select hh*60+mm m from
(select 0 hh union select  1 union select  2 union select  3 union select  4 union
select  5 union select  6 union select  7 union select  8 union select  9 union
select 10 union select 11 union select 12 union select 13 union select 14 union
select 15 union select 16 union select 17 union select 18 union select 19 union
select 20 union select 21 union select 22 union select 23) hours,(select t*10+u mm from
(select 0 t union select 1 union select 2 union select 3 union select 4 union select 5) tens,
(select 0 u union select 1 union select 2 union select 3 union select 4 union select 5 union
select 6 union select 7 union select 8 union select 9) units) minutes order by m) BB) A
inner join visit on the_time >= starttime and the_time <= endtime
group by the_time;

What the query does is generate every minute of a day. You inject the date in subquery AA

SAMPLE DATA

use test
drop table if exists visit;
create table visit
(
    visit_id int not null auto_increment primary key,
    starttime datetime not null,endtime datetime not null
);
insert into visit (starttime,endtime) values
('2014-10-30 10:10:00','2014-10-30 10:15:00'),
('2014-10-30 10:15:00','2014-10-30 10:20:00'),
('2014-10-30 10:12:00','2014-10-30 10:18:00');

SAMPLE DATA LOADED

mysql> use test
Database changed
mysql> drop table if exists visit;
Query OK, 0 rows affected (0.00 sec)

mysql> create table visit
    -> (
    ->     visit_id int not null auto_increment primary key,
    ->     starttime datetime not null,endtime datetime not null
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into visit (starttime,endtime) values
    -> ('2014-10-30 10:10:00','2014-10-30 10:15:00'),
    -> ('2014-10-30 10:15:00','2014-10-30 10:20:00'),
    -> ('2014-10-30 10:12:00','2014-10-30 10:18:00');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>

PROPOSED QUERY EXECUTED

mysql> select the_time,count(1) visits from
    -> (select the_date + interval m minute the_time from
    -> (select '2014-10-30' the_date) AA,(select hh*60+mm m from
    -> (select 0 hh union select  1 union select  2 union select  3 union select  4 union
    -> select  5 union select  6 union select  7 union select  8 union select  9 union
    -> select 10 union select 11 union select 12 union select 13 union select 14 union
    -> select 15 union select 16 union select 17 union select 18 union select 19 union
    -> select 20 union select 21 union select 22 union select 23) hours,(select t*10+u mm from
    -> (select 0 t union select 1 union select 2 union select 3 union select 4 union select 5) tens,
    -> (select 0 u union select 1 union select 2 union select 3 union select 4 union select 5 union
    -> select 6 union select 7 union select 8 union select 9) units) minutes order by m) BB) A
    -> inner join visit on the_time >= starttime and the_time <= endtime
    -> group by the_time;
+---------------------+--------+
| the_time            | visits |
+---------------------+--------+
| 2014-10-30 10:10:00 |      1 |
| 2014-10-30 10:11:00 |      1 |
| 2014-10-30 10:12:00 |      2 |
| 2014-10-30 10:13:00 |      2 |
| 2014-10-30 10:14:00 |      2 |
| 2014-10-30 10:15:00 |      3 |
| 2014-10-30 10:16:00 |      2 |
| 2014-10-30 10:17:00 |      2 |
| 2014-10-30 10:18:00 |      2 |
| 2014-10-30 10:19:00 |      1 |
| 2014-10-30 10:20:00 |      1 |
+---------------------+--------+
11 rows in set (0.00 sec)

mysql>

PROPOSED QUERY WITH DATE FORMATTED, TOTAL VISITS

select IFNULL(TIME(the_time),'Total Visits') the_time,
count(1) visits from
(select the_date + interval m minute the_time from
(select '2014-10-30' the_date) AA,(select hh*60+mm m from
(select 0 hh union select  1 union select  2 union select  3 union select  4 union
select  5 union select  6 union select  7 union select  8 union select  9 union
select 10 union select 11 union select 12 union select 13 union select 14 union
select 15 union select 16 union select 17 union select 18 union select 19 union
select 20 union select 21 union select 22 union select 23) hours,(select t*10+u mm from
(select 0 t union select 1 union select 2 union select 3 union select 4 union select 5) tens,
(select 0 u union select 1 union select 2 union select 3 union select 4 union select 5 union
select 6 union select 7 union select 8 union select 9) units) minutes order by m) BB) A
inner join visit on the_time >= starttime and the_time <= endtime
group by the_time with rollup;

EXECUTED...

mysql> select IFNULL(TIME(the_time),'Total Visits') the_time,
    -> count(1) visits from
    -> (select the_date + interval m minute the_time from
    -> (select '2014-10-30' the_date) AA,(select hh*60+mm m from
    -> (select 0 hh union select  1 union select  2 union select  3 union select  4 union
    -> select  5 union select  6 union select  7 union select  8 union select  9 union
    -> select 10 union select 11 union select 12 union select 13 union select 14 union
    -> select 15 union select 16 union select 17 union select 18 union select 19 union
    -> select 20 union select 21 union select 22 union select 23) hours,(select t*10+u mm from
    -> (select 0 t union select 1 union select 2 union select 3 union select 4 union select 5) tens,
    -> (select 0 u union select 1 union select 2 union select 3 union select 4 union select 5 union
    -> select 6 union select 7 union select 8 union select 9) units) minutes order by m) BB) A
    -> inner join visit on the_time >= starttime and the_time <= endtime
    -> group by the_time with rollup;
+--------------+--------+
| the_time     | visits |
+--------------+--------+
| 10:11:00     |      1 |
| 10:12:00     |      1 |
| 10:13:00     |      2 |
| 10:14:00     |      2 |
| 10:15:00     |      2 |
| 10:16:00     |      3 |
| 10:17:00     |      2 |
| 10:18:00     |      2 |
| 10:19:00     |      2 |
| 10:20:00     |      1 |
| 10:20:00     |      1 |
| Total Visits |     19 |
+--------------+--------+
12 rows in set, 1 warning (0.01 sec)

mysql>

GIVE IT A TRY !!!


The log.trc file is missing The log.trc file is missing on the server, but there are archived trace files like (log_15.trc, log_16.trc, log_17.trc, log_18.trc, and log_19.trc).  Is this something to be concerned about?

Is there a way to create the log.trc file?

This is nothing to be concerned about. The default trace creates up to 5 rollover files, each with a max size to 20MB, and a incremental number is automatically appended to the file name. The older files are automatically deleted once the max of 5 files is reached.

You can query the trace in T-SQL using fn_trace_gettable, specifying only the base file name log.trc along with the number of files to read or default to read all available trace files.


Log shipping copying job login failed Trying to setup log shipping between two SQL Server 2016 servers using the built-in wizard (right click database > Tasks > Ship Transaction Logs). Wizard succeeds, backup job runs fine, copy/restore jobs fail.

Wizard setup:

Configured primary db to backup at a network location. gave SQL Server Agent service full control on that folder. SQL Server service is running under a domain account which also has full control on that folder.
Added secondary server, configured copy job to copy to a local folder on that server. Gave service accounts full control on that local folder.
Configured restore job with no recovery mode

Wizard completes with no issues and creates jobs on both servers. On the secondary server, when I expand the databases, the database appears in "Restoring" mode. This mode does not go away. When I check the error logs I see the message

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

  Login failed for user 'EAGLE\my_username'. Reason: Failed to open the
  explicitly specified database 'My_Db'. [CLIENT: ]

my_username is the username I used to log into the primary database and run the wizard and this user is a sysadmin on both servers. Why and where is this account even coming in picture? Why not the SQL Job agent service account?

Anyway if I try to manually run the copy job on secondary server. It fails and gives no specific error message except that this step failed. Event viewer or DB error log have no entries for this fail.

How can I make this work?

It was a totally unexpected underlying issue. As Hilary Cotter pointed out in this post, running the copy job from command prompt on secondary server "revealed" that .NET 3.5 framework was missing. Installing it fixed the issues. Beleive it or not, I did not do anything else other than installing .NET 3.5 framework.


SQL Server database file fragmentation I have inherited a system where the previous DBA added 7 data files to the PRIMARY filegroup (8MB initial size) and left the AUTOGROW option at 8MB. What I have now is a set of eight files each about 3 - 4GB in size that have been slowly growing over a two-year period. I'd like to remove the file fragmentation in the fastest way possible.

Here are the options that I came up with:

Expand the 1st file in the PRIMARY filegroup by ~28GB (7 files x 4GB)
Move the data off each of the successive files and mark them for deletion
Delete the other 7 files
Detach the database
Copy the detached database file to a different drive on the server
Copy the detached database file back to the original drive
Reattach the database

or

Create a new database 32GB in size (8 x 4GB)
Transfer all of the objects, tables, users and permissions to the new database using SSIS
Drop the old database
Rename the new database

Is an OS level "Disk Defrag" still not an option?

I do not know which option is the best or if it will even work.

Also, this database is being mirrored and replicated, so the least amount of work in regard to having to rebuild that it optimal.

Thanks for your help.

Honestly with a 32 Gig database physical fragmentation shouldn't be much of an issue unless you don't have a couple of gigs of RAM.


Should I add my index after the table is populated or before (MySQL. Aurora) I know this has been asked in the past, but sometimes things change over time and I wanted to double check.

I have a table with about 9 Billion rows. Should I add the index before inserting the data or after. I'm using Aurora. Does it matter if I'm adding more than one index?

Everything, I'm aware of says you should do this after the insert, but one of my colleagues is insisting it's faster to do it on the insert.

Have the primary key in place, but not the secondary keys.  When finished loading, add all the secondary keys in a single ALTER statement.

Caveats:

I have not done that particular task, so I dont speak from experience.
There have been changes over the years in how ALTER works.  Doing multiple alters at once used to be optimal.  But with online/instant/etc ALTERs, it is hard to say.  Still, the current ALTER .. ADD INDEX.., even though it comes back instantly, has a lot of work to do afterwards.
If the PRIMARY KEY in not in place to start with, it will have to reorganize the data.  However, this aspect may depend on whether the source data is in PK-order.


Query to retrieve index details for a particular column in all tables There is a column created_at in most of tables.

I would like to get the details if that field exists in the table and it is not indexed.

Is there any thing can be done with the information_schema to get this checked?

I'm using MySQL 5.6

To check the column properties you can run the query

SELECT * FROM information_schema.columns WHERE table_schema = 'your_schema_name'
AND COLUMN_NAME = 'created_at';

This will list all the tables in your schema with the columns created_at. Here you can check some of the column properties including the COLUMN_KEY with values PRI, UNI, MUL.

For a detailed view of the table index run the below query against the tables returned by the first query.
SHOW CREATE TABLE table_name;


Some queries are very slow after index rebuild After finding out almost all databases on my SQL Server had fragmentation over 40%, I decided to do an index rebuild on all tables using a fill factor of 80.

After rebuilding all indexes, some queries seem to take forever for at least two queries/tables.

Here's one of those slow queries:

SELECT  a.FileID,
        a.EventID,
        MAX(b.cyNumber) AS cyNumber,
        MAX(b.skNumber) AS skNumber,
        MAX(b.cyFormat) AS cyFormat,
        MAX(b.Cost) AS Cost,
        MAX(b.PackageRef) AS PackageRef,
        MAX(CASE WHEN b.BMUpdatedON = '1900-01-01 00:00:00.000' THEN NULL ELSE b.BMUpdatedON END) AS BMUpdatedON,
        MAX(b.RunID) AS RunID
FROM    DB.dbo.[File] a
        INNER JOIN DB.dbo.bicy b ON a.InnerFileID = b.InnerFileID
WHERE   a.FileID NOT IN (SELECT FileID FROM DB.dbo.Event_bicy)
GROUP BY a.FileID,
        a.EventID
OPTION (MAXDOP 1);

I always used OPTION (MAXDOP 1) on all queries because when I didn't use it the query would run VERY slowly.  Now, after the index rebuild, the opposite seem to happen. If I remove MAXDOP 1 or if I remove the where clause the query runs fast, which I find strange.

Solution: when I create a non-clustered index on DB.dbo.bicy.[InnerFileID] the query runs fast.

Question: why do I need to create a non-clustered index for the query to run fast when before the index rebuild the query ran just fine?

Fragmentation on its own is not necessarily the evil demon it is made out to be.  If your database files are stored on a SAN, or some types of RAID array, or perhaps on SSDs, index defragmentation will make very little difference since pages will be spread all over the disks no matter if they are fragmented or not.  See this answer for a discussion of that:  Is there any benefit to defragmenting SQL indexes in a SAN environment?

When you rebuild indexes, you should not indiscriminately set FILL_FACTOR = 80 - that setting needs to be evaluated on a table-by-table basis.  Assuming the tables were at FILL_FACTOR = 100 prior to your rebuild, you have effectively increased the storage required for each table by 20%.  That may be a good part of the reason why things are slower now.

Using OPTION (MAXDOP 1) on ALL queries is a bad idea as well.  You should allow the query engine to decide whether to execute a parallel query or not.  Set the appropriate MAXDOP option at the server level using EXEC sp_configure 'max degree of parallelism', X where X is the maximum number of cores to use for parallel queries.  See this question for a discussion on MAXDOP:  What is a good, repeatable way to calculate MAXDOP on SQL Server?

Adding a non-clustered index on a field (or fields) will certainly make the query run faster if the database engine decides to use the index.

You may want to ensure the statistics on the affected tables are up to date.  See http://technet.microsoft.com/en-us/library/ms187348.aspx for the Books Online description of how to do that.  SQLSkills also has an excellent article on statistics, here: http://www.sqlskills.com/blogs/erin/understanding-when-statistics-will-automatically-update/


How to calculate a value for all rows in sql server? I have two tables. One is a linking table to handle a many-to-many relationship between users and construction projects, and the other one is a payment table which includes the users' payments. Now I want to calculate each user's payment to show it along with user's information.
To do this, I wrote this query.

SELECT MemberID,
MembershipDate,
(SELECT SUM(Amount) from Payment where MemberID = 1001) AS Payment
FROM   User_Project_LinkingTable where MemberID = 1001

Now, how can I do this for all users to show all of them in a list?

Thanks in advance

This query solved my problem.

SELECT DISTINCT
  UPL.memberid,
  UPL.membershipdate,
  PaymentSum.sumamount
FROM user_project_linkingtable AS UPL
LEFT JOIN (SELECT
            memberid,
            Sum(amount) AS SumAmount
            FROM payment
            GROUP BY memberid) AS PaymentSum
 ON UPL.memberid = PaymentSum.memberid;


Is it bad practice to add columns to an existing table? Conversely:  Is it better to get all the columns created at the time when a new table is created?

I'm working on a new system and new requirements are coming up all the time.  The latest requirement is to add a new tag field to each customer so that it will be easier to correlate data between the old and the new system.  The new system is not in production yet, but some test runs of the migration process have been done.

At the moment it is still viable to delete the table and rerun the batch load, but what about the future - when there is a lot of live data in the system and a requirement for a new column comes up:  Does it have any impact on performance for example, to export the data, recreate the table with all the columns, and import the data again, over just doing an ALTER TABLE ADD column .... ?

In case it makes any difference, the solution is based on PostgreSQL 9.5, and if it does make a difference it would be interesting to know which DBMS'es care more or less.

Does whether or not an index is created on this column affect the answer?  E.g when a Unique constraint is set.

ALTER TABLE is there for a reason.  More seriously, unless you plan to have a really big data set, you shouldn't worry about adding new columns on demand.  Dropping and recreating the table is only viable until you have no (important) data, meaning you have to use ALTER TABLE ... ADD COLUMN later in any case.
When I mention 'really big' above, it's about aligment and padding of the different data types used for the column definitions.  For example, a row of a table with columns (in this order) (smallint, integer, smallint) will be slightly (2 bytes) wider than one with (smallint, smallint, integer).  This starts to make a difference only with tables of 10s (on decent hardware maybe 100s) of million rows or when the table has many columns and a lot of rows.  More about this in Erwin Brandstetter's excellent answer.
When adding a new column in pre-11 Postgres versions, beware the trap of ALTER TABLE ... ADD COLUMN ... NOT NULL DEFAULT ....  As ALTER TABLE ... ADD COLUMN requires a heavy lock that prevents access to the table for concurrent sessions, you have to keep the transaction as short as possible.  If you manage to do this, adding a new column will be hardly noticeable performance-wise to the other processes.


Get Updated_At for any change in row on Sybase? I am working with a Sybase SQL Anywhere table that has horrible tracking of changes. I have a windows service that queries multiple tables every 5 minutes looking for changes, but one particular table doesn't have any timestamps. In addition to that, when I added a new record, it was given an ID in the middle for some reason. There are 15,000 records and the new ID was 6775, so simply asking for records that have an ID greater than the last won't work.

I know in MySQL I could alter the table with something like:

ALTER TABLE `customer` ADD `updated_at` DATETIME on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

Which would auto update that value any time anything was changed. That doesn't work with Sybase, so my next thought was to create a second small table:

CREATE TABLE customers_getting_updated(
   customer_id int,
   updated_at DATETIME,
   PRIMARY KEY(patient_id)
)

and then set a trigger to create / update records in that table. There are currently 150 columns in the table. I tried creating a trigger like this:

create TRIGGER "customer_record_updated" AFTER INSERT ON customer
for each row begin
    INSERT INTO customers_getting_updated (patient_id, updated_at)
    VALUES(:NEW.customer_id, NOW());
end;

I am running this on a dual processor 6 core with 64GB memory on a SSD and the query has taken 15 minutes and still hasn't finished.

Assuming this will finish eventually (I saw someone say one took them 70 minutes????), since I didn't get an error up front, is there a better way since most computers that need to run this will have substantially less hardware power.

FYI - I tried doing a AFTER INSERT OR UPDATE but got an error on the OR, so I assume I will have to write separate triggers.

From your question I assume that you cannot create the updated_at column in that customer table by yourself (maybe because that table "belongs" to the software vendor).

Logging the creation or update timestamp in your additional table customers_getting_updated is simple. You need two triggers on the customer table, one for (after!) the creation and one for (after!) the update.

The first trigger fires after a customer record has been inserted. It simply inserts a record into the customer_getting_updated table with the (new) customer_id from the customer table and sets the current timestamp.

CREATE TRIGGER customer_inserted AFTER INSERT
ORDER 1 ON customer
REFERENCING NEW AS new_cust
FOR EACH ROW
BEGIN
  INSERT INTO customer_getting_updated ( customer_id, updated_at )
  VALUES (new_cust.customer_id, CURRENT TIMESTAMP);
END;

The second trigger fires after an existing customer has been updated. The trigger checks whether or not there is already a "timestamp logging entry" for that customer id. If existing it updates the timestamp, if missing it creates a new logging record (identical to the after insert trigger).

CREATE TRIGGER customer_updated_at AFTER UPDATE
ORDER 1 ON customer
REFERENCING NEW AS new_cust
FOR EACH ROW
BEGIN
  IF EXISTS(SELECT 1 FROM customer_getting_updated WHERE customer_id = new_cust.customer_id) THEN
    UPDATE customer_getting_updated SET updated_at = CURRENT TIMESTAMP
    WHERE customer_id = new_cust.customer_id;
  ELSE
    INSERT INTO customer_getting_updated ( customer_id, updated_at )
    VALUES (new_cust.customer_id, CURRENT TIMESTAMP);
  END IF;
END;

A short note on before or after triggers:

If you want to manipulate the new (or updated) record itself you typically use a before trigger and change values in that record before they are written to the table.
If you want to do something else - often with values from the new or updated record - you use an after trigger.


One way to do it is to use a table of numbers and CROSS APPLY.

Sample data

DECLARE @T TABLE (RecordID int, date_from datetime2(0), date_to datetime2(0));

INSERT INTO @T (RecordID, date_from, date_to) VALUES
(1, '2017-02-03 08:00:00' , '2017-02-04 17:00:00'),
(2, '2017-02-05 08:00:00' , '2017-02-05 17:00:00'),
(3, '2017-02-06 08:00:00' , '2017-02-10 17:00:00');

Query

In this example I generated a table of 10 numbers on the fly (CTE_Numbers). In production I have a permanent table with 100K numbers.

WITH
CTE_Numbers1(n)
AS
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
,CTE_Numbers
AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY n) AS Number
    FROM CTE_Numbers1
)
SELECT
    T.RecordID
    ,CASE WHEN CA.Number0 = 0
    THEN date_from
    ELSE DATEADD(day, CA.Number0, CAST(T.date_from as date))
    END AS new_date_from
    ,CASE WHEN CA.Number0 = DATEDIFF(day, T.date_from, T.date_to)
    THEN date_to
    ELSE DATEADD(day, CA.Number0 + 1, CAST(T.date_from as date))
    END AS new_date_to
FROM
    @T AS T
    CROSS APPLY
    (
        SELECT CTE_Numbers.Number - 1 AS Number0
        FROM CTE_Numbers
        WHERE CTE_Numbers.Number <= DATEDIFF(day, T.date_from, T.date_to) + 1
    ) AS CA
ORDER BY
    RecordID
    ,new_date_from
;

Result

+----------+---------------------+---------------------+
| RecordID |    new_date_from    |     new_date_to     |
+----------+---------------------+---------------------+
|        1 | 2017-02-03 08:00:00 | 2017-02-04 00:00:00 |
|        1 | 2017-02-04 00:00:00 | 2017-02-04 17:00:00 |
|        2 | 2017-02-05 08:00:00 | 2017-02-05 17:00:00 |
|        3 | 2017-02-06 08:00:00 | 2017-02-07 00:00:00 |
|        3 | 2017-02-07 00:00:00 | 2017-02-08 00:00:00 |
|        3 | 2017-02-08 00:00:00 | 2017-02-09 00:00:00 |
|        3 | 2017-02-09 00:00:00 | 2017-02-10 00:00:00 |
|        3 | 2017-02-10 00:00:00 | 2017-02-10 17:00:00 |
+----------+---------------------+---------------------+


Alter large table without downtime I have a very large table, lets call it example, and I try to execute a little alter command on that table:

ALTER TABLE `example` ADD `example_column` TINYINT(1) NOT NULL DEFAULT 0;

Checking the progress of alter command:

mysql -e 'show engine innodb status \G' | grep 'undo \| log \| entries'

Give me nice info, about the time - more or less it will take 17 days to complete ...

Alter block the table, so blocking production table for 17 days is not a good option. ;)

I try to investigate some great tools on the net, like:

Pt-online-schema-change - Percona
gh-ost - GitHub's Online Schema Migrations for MySQL

I have also read the documentation, and the limitation section of the above tools:

gh-ost limitations

does not support triggers at all
does not support foreign keys at all

pt-online-schema-change limitations

The use of triggers means that the tool will not work if any triggers are already defined on the table.
Does not provide any good way of altering tables which contain foreign keys

My example table have triggers and foreign keys...

Could you give me some advice, how to deal with this alter?

I have MySQL 5.6.
I use GTID (row-based) replication.

I will be very grateful for advice!

You are much better off running the ALTER TABLE like this
ALTER TABLE `example` ADD `example_column` TINYINT(1) DEFAULT NULL,
ALGORITHM=INPLACE, LOCK=NONE;

What could be the benefit ? Defining a column whose default value is NULL will not attempt to increase the size of the row. This is true for VARCHAR. Honestly, I'm not sure this is true for numeric. (INT, TINYINT, etc). You need to test this (I mentioned testing this 2 years ago (Does InnoDB allow adding columns to a table with concurrent READ/WRITE of rows?)). Consequently, there should be no downtime.
The downside to this would be changing your software to deal with a NULL numeric column.
If you concerned about foreign keys, turn them off in your session beforehand
SET foreign_key_checks = 0;
ALTER TABLE `example` ADD `example_column` TINYINT(1) DEFAULT NULL,
ALGORITHM=INPLACE, LOCK=NONE;

If you have triggers and can live without it for the duration of the load, drop the trigger, do the ALTER TABLE, and recreate the trigger.
CAVEAT: Make sure you are using the latest version of MySQL 5.6 (at least 5.6.23) to avoid foreign key issues with ALTER TABLE.
Please note that I did not mention using third party tools. This is just straight up MySQL.
GIVE IT A TRY !!!


Clustered columnstore indexes - Do I need to create a non clustered index on a column that's used in a join? I have two very large tables that need to be joined to produce a data extract. Both tables have clustered column store indexes and are partitioned.

DW Sentry advises me in the plan view that it thinks a NC index should be added, but without compression, it's likely to be a very large index. Does anyone know about the join characteristics for a columnstore?

You should add a nonclustered columnstore index on the recommended columns. This will be compressed and should also provide a performance improvement.


MySQL - huge Dictionary memory allocated Our MySQL 5.5.38 servers have 1000-3000 InnoDB databases each (with about 100 tables each). All data is in single file. Most of DBs are active. While it's expected that information schema should be huge - on those servers Dictionary memory allocated from 3 to 15 GB, competing with buffer pool.

Is this an acceptable scenario? What risks are here, what settings can be used to manage this dictionary memory?

BUFFER POOL AND MEMORY

Total memory allocated 519405568; in additional pool allocated 0
Dictionary memory allocated 3839216241
Buffer pool size   30976

Sorry for the late answer, but I've been running into a similar situation--3.22 GB allocated for an Innodb data dictionary and eventually my server runs out of RAM and restarts MySQL.

From what I have read, there is no way to manage the size of the Innodb data dictionary in MySQL 5.5, but apparently there is a way in MySQL 5.6 and in Percona Server, so if you need to manage limits for the data dictionary, upgrade to MySQL 5.6 or newer.

According to the Percona guys and authors of High Performance MySQL, every time an innodb table is opened, an object for that table is loaded into the data dictionary. That object doesn't get removed, so the size of the dictionary increases over time. They also say that an overlarge Innodb data dictionary causes problems with database servers that have thousands of large tables, which it seems like you certainly have.

High Performance MySQL pg 356

Percona Server InnoDB Data dictionary

MySQL 5.6 InnoDB Data Dictionary


Reusing index in constraints I'm trying to understand how Oracle determine if the index can be used for constraint.
For example, the following works :

create table temp11
(id int not null,
val varchar2(10),
CONSTRAINT PK_temp11 primary key (id) using index
(create index IDX_temp11 ON temp11(id,val))
);
--Also, I can re-use the same index for another unique constraint :
ALTER TABLE temp11 ADD CONSTRAINT UQ_temp11 UNIQUE(id,val) using index IDX_temp11;

But if I change index definition and make it unique, I get error

  ORA-14196: Specified index cannot be used to enforce the constraint.

create table temp11
(id int not null,
val varchar2(10),
CONSTRAINT PK_temp11 primary key (id) using index
(create UNIQUE index IDXU_temp11 ON temp11(id,val))
); -- ORA-14196

** Making val not null doesn't make a difference.
Could anyone explain this behaviour?
I don't see why such an unique index cannot used for enforcing constraint whereas non-unique is accepted.
I use Oracle 10 if it matters.

I understand your frustration with this behavior.  There seem to be internal obstacles to implementing this and could lead to larger indexes than are necessary.

With a unique index supporting a primary key, constraint validation is done before the unique index is modified and one look-up can verify whether the entry can be inserted or not.1
With a non-unique index supporting a primary key, constraint validation is done after changes are made to the non-unique index and multiple look-ups are required to verify whether the entry can be inserted or not.1

A primary key using the leading column of a unique index would require non-unique index constraint logic on a unique index.  This would require changes and/or additions to the logic.

If id is unique/primary then (id,val) is inherently unique, and normally you would not need/want a unique constraint/index on (id,val).

You might want a unique index on (id,val) if there were a query referencing (id,val) and you wanted to prevent a table look-up.  If this is your situation, you may have to decided which is the lesser tradeoff between allowing the table look-up, increasing the primary key to (id,val) or having two unique indexes.  For most situations I suspect the table look-up would be preferable.


Unexpected Table Scan with Parameterized LIKE I'm experiencing an unexpected table scan on SQL Server 2005 against a heap table when parameterizing a LIKE statement... but when the same value as the variable is hard-coded, the expected Index Seek occurs instead.

The issue only happens given this specific scenario... so I'm not confused as to how to resolve the issue, I'm confused as to why this is happening.

The following T-SQL should recreate the issue on SQL Server 2005:

IF (OBJECT_ID('tempdb.dbo.#tblTest') IS NOT NULL)
    DROP TABLE dbo.#tblTest
GO

CREATE TABLE dbo.#tblTest (
    ID INT IDENTITY(1, 1),
    SerialNumber VARCHAR(50)
)
GO

-- Populate the table with 10,000 rows
SET NOCOUNT ON
DECLARE @i INT
SET @i = 0

WHILE @i < 10000
BEGIN
    INSERT INTO dbo.#tblTest VALUES(CAST(@i AS VARCHAR(10)))
    SET @i = @i + 1
END
GO

-- To recreate the issue, the table must be a heap.
ALTER TABLE dbo.#tblTest ADD CONSTRAINT PK_tblTest PRIMARY KEY NONCLUSTERED (ID)
GO

-- Create a (non-covering) index on serial number.
CREATE NONCLUSTERED INDEX IX_tblTest_SerialNumber ON dbo.#tblTest (SerialNumber)
GO

DECLARE @Criteria VARCHAR(50)
SET @Criteria = '1234%'

-- This produces a Table Scan.
SELECT *
FROM dbo.#tblTest
WHERE SerialNumber LIKE @Criteria

-- This produces an Index Seek
SELECT *
FROM dbo.#tblTest
WHERE SerialNumber LIKE '1234%'

I was directed towards this article by Paul White which seems very closely related, but the conclusions / explanations don't match my specific issue.

Any insight is appreciated.

The claim that it occurs only for non-clustered indexes is due to the fact that you only had two columns - one being the indexed column, the other being

the clustering key (i.e. the other column) if there were a clustered index
the RID if there were no clustered index

In the second case, to satisfy the SELECT * (all columns) portion of your query, it would have to perform an expensive lookup, so the generic (robust) plan to perform a 10,000* record table scan is chosen instead.  In the first case, the index is all that is required to satisfy the SELECT clause.

*It should be noted that the number of records and index cardinality play a role in determining the plan as well.

With more columns, the plan predictably switches to an CLUSTERED INDEX SCAN for the parameterized LIKE statement even with a clustered index, per the revised test below.

IF (OBJECT_ID('tempdb.dbo.#tblTest') IS NOT NULL)
    DROP TABLE dbo.#tblTest
GO

CREATE TABLE dbo.#tblTest (
    ID INT IDENTITY(1, 1),
    SerialNumber VARCHAR(50),
    Othercolumn1 uniqueidentifier default (newid()),
    RowVer timestamp
)
GO

-- Populate the table with 10,000 rows
SET NOCOUNT ON
DECLARE @i INT
SET @i = 0

WHILE @i < 10000
BEGIN
    INSERT INTO dbo.#tblTest (serialnumber) VALUES(CAST(@i AS VARCHAR(10)))
    SET @i = @i + 1
END
GO

-- To recreate the issue, the table must be a heap.
ALTER TABLE dbo.#tblTest ADD CONSTRAINT PK_tblTest PRIMARY KEY CLUSTERED (ID)
GO

-- Create a (non-covering) index on serial number.
CREATE NONCLUSTERED INDEX IX_tblTest_SerialNumber ON dbo.#tblTest (SerialNumber)
GO

DECLARE @Criteria VARCHAR(50)
SET @Criteria = '1234%'

-- This produces a Table Scan.
SELECT *
FROM dbo.#tblTest
WHERE SerialNumber LIKE @Criteria

-- This produces an Index Seek
SELECT *
FROM dbo.#tblTest
WHERE SerialNumber LIKE '1234%'

Here are the plans generated based on my revised table structure. For the schema in the question, the top part becomes a Table Scan and the bottom part becomes an RID Lookup instead of Key Lookup - all else being equal.

One of the more expensive operations in executing a query is first constructing the execution plan. To help this along, SQL Server has a Plan Cache that stores the text of statements and the associated SET settings. The same text using different SET settings can result in different behaviour, so that is planned anew and is stored as a separate entry.

The non-parameterized query is simple to plan - it contains the exact text '1234%'. The VARCHAR index on SerialNumber is easily searched for the portion containing the prefix "1234".  SQL Server also estimates the cardinality of the query and invariably for your data will choose the INDEX SEEK plan.  Any further presentation of the exact query statement (text) to SQL Server will contain the static value '1234%' and is safe to re-execute the same plan efficiently.

On the other hand the parameterized query is stored into the Plan Cache (dictionary) keyed by the statement text ... WHERE SerialNumber LIKE @Criteria.  Even though @Criteria in the current batch contains the value '1234%' and can use an INDEX SEEK, it is quite possible for another user to submit the exact same query with @Criteria set to '%9' instead which would not perform as well using the INDEX SEEK + RID Lookup. This would SELECT 10% of the data which is often over the tipping point at which index seeking is no longer favourable.  For robustness and reusability, the plan cached for this query (and then executed) is the Table Scan version that will cater for the widest range of @Criteria values at average efficiency across the possible values.


RSAT on Windows 10 fails to add node to server 2016 cluster I have been testing server 2016 failover clustering. I have 3 hyper-v VMs running server 2016 core. I installed the RSAT tools on Windows 10 Pro to use the failover clustering wizard. I cannot add a node to the 2016 cluster using the Win10 machine, getting an error about the operating systems being different. When I go to a server 2016 machine with desktop experience, I can use the same wizard to successfully add the node. I assume it is because the OSs are the same.

My question is...is it true that a Windows 10 Pro machine will not be able to administer a server 2016 cluster?

The RSAT tool was updated with the general availability of Windows Server 2016. By going to the same link as the preview RSAT, https://www.microsoft.com/en-us/download/details.aspx?id=45520, you can update your workstation with the new version.


Update statement is giving an invalid column name error, even though the column exists I have a temporary table created at the beginning of this stored procedure. It is created successfully and can be selected from and inserted to. Here is the create statement

CREATE TABLE #tmpImportData
( GuideFirstName VARCHAR(MAX),
  GuideLastName VARCHAR(MAX),
  email VARCHAR(MAX),
  group_id_text VARCHAR(MAX),
  CandidateName VARCHAR(MAX),
  grade_text VARCHAR(5),
  dateofbirth DATE
)

My problem is trying to update a column after I alter the temporary table. I get the error:

  Msg 207, Level 16, State 1
  invalid column name

Code:

declare @SQl1 nvarchar(max)
set @SQL1 ='
ALTER TABLE #tmpImportData
ADD group_id INT
ALTER TABLE #tmpImportData
ADD guide_id INT
ALTER TABLE #tmpImportData
ADD password_plain_text VARCHAR(500)
ALTER TABLE #tmpImportData
ADD guide_email VARCHAR(500)
ALTER TABLE #tmpImportData
ADD class_id INT'

exec sp_executesql @Sql1

UPDATE #tmpImportData
SET group_id = CAST(group_id_text AS INT)

UPDATE #tmpImportData
SET group_id = 0 WHERE group_id IS NULL

Solution: NOT THE SOLUTION ANYMORE, IT DOESN'T WORK ANYMORE

declare @SQl1 nvarchar(max)
set @SQL1 ='
ALTER TABLE #tmpImportData ADD group_id INT
ALTER TABLE #tmpImportData ADD guide_id INT
ALTER TABLE #tmpImportData ADD password_plain_text VARCHAR(500)
ALTER TABLE #tmpImportData ADD guide_email VARCHAR(500)
ALTER TABLE #tmpImportData ADD class_id INT'

exec sp_executesql @Sql1
WAITFOR DELAY '000:00:05'

UPDATE #tmpImportData SET group_id = CAST(group_id_text AS INT)
UPDATE #tmpImportData SET group_id = 0 WHERE group_id IS NULL

What I thought would be better but still throws the invalid column name error Msg 207, Level 16, State 1, Line 2
Invalid column name 'group_id'.:

  declare @SQl1 nvarchar(max)
set @SQL1 ='
ALTER TABLE #tmpImportData ADD group_id INT, guide_id INT, password_plain_text VARCHAR(500), guide_email VARCHAR(500), class_id INT; UPDATE #tmpImportData SET group_id = CAST(group_id_text AS INT);
UPDATE #tmpImportData SET group_id = 0 WHERE group_id IS NULL; '

exec sp_executesql @Sql1
WAITFOR DELAY '000:00:05'

--UPDATE #tmpImportData SET group_id = CAST(group_id_text AS INT)
--UPDATE #tmpImportData SET group_id = 0 WHERE group_id IS NULL

SELECT * FROM #tmpImportData

You need to perform the update in dynamic SQL too (or just create the table with all columns in the first place). The error is happening because the update with the new column is being parsed before the dynamic SQL has run.

As an aside, you are probably only getting this error if you try to execute the stored procedure and choose 'Display estimated execution plan' in the toolbar. This is a parsing/binding error that will happen in many scenarios when generating an estimated plan involving #temp tables, but shouldn't happen during normal execution.

So to avoid the error during retrieval of an estimated plan, put any "new" column references in separate dynamic SQL blocks. But to really avoid the problem: stop retrieving estimated execution plans. They're pretty worthless anyway.


How do I know which binary log file to open for continous backup? This really has me confused, I want to have point in time recovery so I plan to take occasional full database dumps, and then between those dumps I plan to use stop-never with mysqlbinlog. But I'm really confused on how I use each binary log file?

How do I know which one I'm meant to open when I start reading the binary log?

With stop-never do I have to keep switching binary log files when one is no longer used?

How do I find which log file contains a specific timestamp? Am I really supposed to just search each one? That sounds very inefficient.

You need them all.  Or at least any that contain the "point in time" that you need, plus all that come after that file.

When one binlog gets "full" (according to a setting), it stops being written to, and another one starts.

FLUSH LOGS also stops the writing to one binlog and starts another.  Presumably, your full backlog does such.  That becomes the point at which you can toss all older binlogs.


Recipe Health risk Database ERD structure I have these tables:

First table(Recipe table),

  Recipe

  recipeid(PK,AI)

  recipename

  ingredients

Second table(Ingredients table),

  Ingredients

  ingredientid(PK,AI)

  ingredientname

  quantity

Then a 3rd one for display,

  Recipe_ingredients

  recipeid(FK)

  ingredientid(FK)

  quantity

How do i construct another table and its relation to the ingredient table to determine its nutritional value.

Should i add another column for nutrition on my ingredient table? Or create another table for nutrition which holds the ingredient id as FK.

  Nutrition

  Ingredientid(FK)

Say i am diabetic so i should avoid recipes with high amounts of sugar. So from ingredients... im confused where to move from there.

You probably want to have some risk information in your NUTRITION table.  Perhaps a text column to describe the risk and a threshold quantity column, which might be optional, to describe what is a safe and an unsafe amount.

The thing you have to decide is whether you want to try to normalize out some other information from your nutrition risk descriptions.  For example, do you want to have a MEDICAL_CONDITION table that allows risks to be categorized in terms of what type of person is at risk (e.g. diabetes, nut allergy, lactose intolerance,...)

From a design perspective, we can't answer your question about what is needed.  That depends on your business rules and what you want to achieve.  We can help with technical design questions based on specific statements of your business rules.


Oracle 9.2 - Bring standby up-to-date using RMAN I have a situation whereby an Oracle Standby (managed by data guard) is really out of date.  The redo logs are now being properly sync'd however there is a big gap.

I have the current SCNs for primary and standby but cannot find out how to get RMAN 9.2 to create an incremental backup from the specific SCN.

Does anyone have any experience with this?

TIA

I do not have access to a 9.2 database currently, but this is the method for doing this:

Using RMAN Incremental Backups to Roll Forward a Physical Standby Database

Basically, you can issue a BACKUP INCREMENTAL FROM SCN ... to create the desired backup. However I suspect this feature is not available in versions before 10.2.0.1, as there is no reference of it in the 9.2 Data Guard documentation nor the 9.2 BACKUP reference, and the related MOS notes also specify the lowest version as 10.2.0.1.

Try the above syntax, but if it does not work, you will need to rebuild the standby.


Reducing deadlocks with update query hint or other method I'm getting 20-30 deadlocks per day on the same table. (2 mil rows)
Profiler DeadlockGraphs show KEY LOCK and PAGE LOCK (See attched jpg)
Total datatype size per row = 114 bytes

We have multiple departments that run a process that takes orders from our PreOrders table,
checks the rows and inserts them into the actual orders table.
This very import process is VB6. So to illistrate I simplified it in TSQL (Below).

The first SELECT statement is the deadlock victim and the end UPDATE statement is the winner. (Different SPIDs)
There are 4 different departments, each running this process every 30 secs.

After reading several articles, I was thinking my easiest option would be to add WITH (ROWLOCK) to the update statement.
I am able to recreate this entire process in TSQL, except for the automatic printing of invalid rows.

What other options do I have?
This'll be the first time I've had to use a query hint and am a bit reluctant as my knownledge on hints is (still) limited.

--Loop through headers
-- THIS SELECT STATEMENT IS ALWAYS THE DEADLOCK VICTIM
SELECT  PreOrderID, <Other columns....>
FROM    PreOrders PO -- 2 mil rows
        INNER JOIN Customers C -- 50 K rows
            ON PO.CustomerNr = C.CustomerNr
WHERE   Processed = 0
        AND Department = 4 -- This is also the account number related to the Deadlock process

BEGIN
    -- < 100 orders

    -- Loop through rows
    SELECT  ProductNr, Amount, Price, <Other columns....>
    FROM    PreOrders
    WHERE   PreOrderID = @PreOrderID

    BEGIN
            -- AVG 9 rows per order

            -- Get data to validate the order row with
            SELECT  Availibilty, Price, isActive <Other columns....>
            FROM    Products P -- 100 K rows
                    LEFT JOIN ProductLocations PL -- 35 K rows
                        ON P.ProductNr = PL.ProductNr
            WHERE   P.ProductNr = @ProductNr
            -- Validate the order rows against the actual product data
            -- Bad rows get emailed and insert with a counter row (Cancels the row order)
            -- Good rows get inserted into actual Orders table
    END

    -- Generate the new order number
    -- Save the head and rows into the acutal order tables
    -- Update the preOrder as having been processed

    -- THIS UPDATE STATEMENT IS ALWAYS THE DEADLOCK WINNER
    UPDATE  PreOrders -- !!*** WITH (ROWLOCK)
    SET     Ordernumbers= @OrderNumber, -- Newly generated number)
            Processed   = 1
    WHERE   PreOrderID  = @PreOrderID

BEGIN

EDIT: Added screenshots

I resolved the deadlocks by using a NOLOCK hint in the first select statement.
No dirty reads as this is the only process that manipulates the rows and only does so once.