pgadmin dashboard is gone The Dashboard option is gone from the menu. Now I can't see the nice graphs anymore.

What do I need to get it back

I'm using version 4.17?

Try File - Preferences - Dashboard within PGAdmin. See if toggling those options will bring it back.
If not, then providing you do not have any customised layout, try File - Reset Layout.


Database Design - 'Lists' storage I'm "about" to implement a feature on one of my apps which requires storing a lot of data (PlayLists).

Assume the following scenario:

A user can have multiple playlists
These playlists have up to 500k entries (maybe even more)
I must be able to get, remove, re-sort or query the whole playlist or by using indexes
Currently a playlist for each user; but
I'm about to implement a feature that will allow users to have more than 1 playlist.
About 3500 entries per playlist. Some can go as high as 590k.

Playlist entries are represented by the following class:

public class Item {
  private Long owner; // User
  private String data;
  private String name;
  private String author;
  private int index; // Index of this item on the list
}

TL;DR: List< Item > for every user (more than 1 per user) where this list can extend to 500k+ entries.

Currently, I have SQL-based storage for this, however, it's slow as heck, specially when I need to shift indexes - slow up to the point of my db driver thinking that the connection failed and leaked.

To explain "shift indexes": Let's say I'm removing an item at the 3rd index (i = 3), this means that all other items after it need to shift one index down, so I can later on request index 3 and get the item that was previously index 4.

Currently, I decrement the column value Order for that playlist for all elements after the one I deleted.

Current PlayList Items Table design

Id (Ordering/Index) | PlayList (Id) | Owner (User) | Name | Author | Data

Question

Which would be a better/more efficient way of storing this kind of "bulk" data, while still being able to query and modify it?

I'm ok with changing/implementing a new database software for this kind of data, since it's really big (I currently use Redis and MariaDB). However, I would love to avoid having to.

User has >1 playlist

The Answer by Vérace is correct except that it skipped the fact that each user may have more than one playlist.

So here is a diagram of my table design. I changed one column name to "sort_order" as the name “index” in a database and in programming will cause all kinds of confusion.

Basically I suspect you are failing to notice the 'list' in 'playlist', conflating each person's list of their favoritesfrom the full list of possible choices. Each playlist is a collection of references to the desired items (songs, books, or whatever your matter is  — not clear in the Question).

Performance

As for your performance issue when inserting a new/changed row amongst the existing rows’ sort-order  numbers, you can dramatically reduce the frequency of this chore.

Increment your sort-order value by a hundred or by a thousand or by a million rather than by one. You can insert a new row by assigning a sort-order number in between the numbers of existing rows without any shifting. You only need to do a full shift when 99 or 999 or 999,999 rows happen to have already been inserted between the incremented rows. Until then you may need to do mini-shifts when clumps appear with the in-between numbers, but on small subset of rows, <100 or <1,000 or <1,000,000.

If you need to show sequential 1, 2, 3… numbers to user, let the database create these numbers dynamically as part of your SELECT statement.


Should I use TINYINT for ID (PK) field in MySQL? Should I perform this type of optimisation if I know that a table can't have more records than 10?

Yes.  You should also say UNSIGNED:  TINYINT UNSIGNED.  Range 0..255; 1 byte.

Actually, it is such a tiny optimization that I don't bother mentioning it.

Think about using the smallest datatypes when you first CREATE the table; it is messier to make the change later.

SMALLINT UNSIGNED:  0..65535, 2 bytes
MEDIUMINT UNSIGNED:  0..16M, 3 bytes
INT UNSIGNED:  0..4B, 4 bytes

Keep in mind that certain operations "burn" AUTO_INCREMENT ids.  This could lead to an unexpected overflow.  Examples:  INSERT IGNORE and REPLACE.  They may preallocate an id before realizing that it won't be needed.


Edition upgrade on SQL Server failover clustered instance We did Edition upgrade on our active node for the SQL Server instance SQL01 and that was successful.

I have a question/doubt --> Do I need to proceed the same on other node as well since SQL Server or its binaries were installed on passive node too?

If yes, which I assumed and tried to test and went ahead with checking what it shows when I start up the set up:

It takes me all the way to same screen where instance name is selected after selecting edition upgrade under maintenance and I am confused if the upgrade was success on active node?

Please suggest how does the upgrade of edition works on 2 node cluster with SQL Server FCI?

Thanks

No, unlike Service Packs and Cumulative Updates where you're responsible to keep the bits in the same state across all nodes, the Edition upgrade applies to both nodes at once. I applied it myself to the 'passive' node and Configuration Manager on the active node showed Enterprise immediately. You still need to restart the service though, as SSMS showed Standard in Properties even after refreshing and reconnecting, but immediately reflected Enterprise after refresh once I'd restarted the service.


What is the difference between Com_xxx and handler_xxx? Initially my com_delete variable and handler_xxx variables are nil.

| Com_delete     | 0     |+
| Handler_delete | 0     |
+----------------+-------+

then I issued a delete query delete from gt where i=1;

when I checked both the variables it changed to 1.

My Question is what these variables differentiate ? I'm using MySQL 5.6

Com_delete is number of times a user sent DELETE command to MySQL.

Handler_delete is number of times MySQL server sent delete handler to a storage engine via internal API

To illustrate the difference here's an example:

mysql> delete from t1 where id < 100;
Query OK, 88 rows affected (0.00 sec)

mysql> show status like 'Com_delete';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_delete    | 1     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show status like 'Handler_delete';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Handler_delete | 88    |
+----------------+-------+
1 row in set (0.00 sec)


Using a table swap for a data load but I have triggers I'm working on re-writing a table load.  The current method is a daily job that deletes from the table, then re-loads it.  The complaint is that if the load fails the users have no data.  The users have said they are ok if the load fails and they have to use yesterdays data.

My solution is to do a table swap.

Create a staging table that has exactly the same structure, indexes
etc
Then the daily job does the following

Delete from the staging table
Load the staging table
Begin a transaction to keep everyone out of the tables until I'm done
Rename Live to Backup
Rename Staging to Live
Rename Backup to Staging
Commit the transaction

This works fine on 3 of the 5 tables I'm working with.  The other 2 have triggers.  Because the trigger has code that references the table name (of course) it can't handle the rename of the object it is attached to.

I start with this:

Table1_Live
  |
  -- Trigger for Table1_Live

Table1_Stage
  |
  -- Trigger for Table1_Stage

After I do my renames I get the following

Table1_Stage
  |
  -- Trigger for Table1_Live

Table1_Live
  |
  -- Trigger for Table1_Stage

The benefits to the method are that the down time for the users is minimal.  I have yesterdays data.  And if anything goes wrong I roll back to yesterdays data.

My question is does anyone know a better method without the trigger problem, or a way that I can swap my triggers between the two tables so that the code remains correct after the rename?

Oh, and I'm somewhat embarrassed to admit but even though the database is 2005 (we are upgrading soon) the requirement is that the load be done using a DTS package.  My plan is for the rest of the process to be done in a stored procedure that can be called from the DTS package (or SSIS if I can ever talk them into it).

This is a pretty crappy workaround, since there really should be an easy way to rename a table or transfer it to a different schema and keep its triggers (I've put in a bat-signal to an MS contact to see about this). But I think it's better than carrying around triggers on both tables that have the live name hard-coded.

Drop the trigger on the stage version of the table. This isn't the one people are touching. Then alter your process slightly:

Step 3.5:

DECLARE @sql NVARCHAR(MAX);
SELECT @sql = OBJECT_DEFINITION(OBJECT_ID('dbo.table1_trigger_name'));
DROP TRIGGER dbo.table1_trigger_name;

Step 6.5:

EXEC sp_executesql @sql;

This will re-create the trigger on the right version of the table. And actually, contrary to my comment, does not require the trigger code to be stable over time, since every time you run it, it will grab the current definition.

Now, you will not be able to have any GO / batch separation between those steps, and you should regularly keep a copy of the trigger in source control just in case anything every goes wrong.


SQL Server: Do Statistics Updates cause query plans to be flushed? Should they? I've found that a big, nasty data-extraction query that runs daily needs updated stats to avoid making horrible query plans based on incorrect rowcount estimates (let's not worry about whether or not my stats should be updating automatically).

My question, as noted in the title, is: Should I be concerned about incorrect query plans sticking around if a query plan happens to be prepared before a given set of statistics is updated, at a time when the desisions made by the optimizer turned out to be wrong?

Or to stats updates automatically cause dependent query plans to be flushed?

If the plans stick around, is there a way to figure out which plans depend on a given index's statistics? (I know, I could go digging in the DMV docs, just hoping someone already has the answer)

The answer is close to your "stats updates automatically cause dependent query plans to be flushed". They don't "stick around"

Flushing a plan from cache is determined by memory pressure.
Statistics updates cause plan recompilations if AUTO_UPDATE_STATISTICS is on.
Updated statistics only mean that individual statements in a batch plan need an optimality based recompile, it does not flush the whole thing.

The analogy might be separation of concerns: the stats thingy does stats, the plan cache widget does plan cache stuff. Statistics says "I've changed", and the consumer of this status decides what action to take: not the generator of this status.

See Execution Plan Caching and Reuse on MSDN.


SQL DB Permissions so anyone can modify the DB I'm teaching a class on SQL Server 2014, and want students to be able to restore a Db I give them and then be able to take ownership and modify tables, and also to view the database diagram. Apparently, the diagram part is problematic.

The students will have a local Windows administrative account on the lab PC. If I make the guest role a db_owner will that allow students to take ownership? If not, what strategy can I adopt to give them full permission to, among other things, the database diagram?

Add the Windows Group, Everyone, to the database db_owner role, if you're really certain you want anyone and everyone to be able to do anything they like to the database. Take a backup of the database that you will then distribute to the class. After you take the backup, remove the group from the db_owner role.

The only gotcha is the students need to have their instances configured with Windows Authentication enabled.

Windows Authentication stores the SID of the Windows principal in the database; in the case of the "Everyone" group, the SID is the same on all Windows machines.


pg_dump does not finish I am trying to backup our postgres database (8.4.17), which is about 25GB in size.

pg_dump database_name > database_db_dump_2014-05-05.sql

unfortunately, the backup stops after about 600MB in, and does not continue. It always stops when trying to dump a specific table (fb_crawler_event). I was able to successfully dump all the other tables using the --table swtich. I have already stopped any other process that could interact with the database.

server log after a restart of the DB (looks ok to me):

2014-05-05 14:34:46 CEST LOG:  all server processes terminated; reinitializing
2014-05-05 14:34:46 CEST LOG:  database system was interrupted; last known up at 2014-05-05 14:32:50 CEST
2014-05-05 14:34:46 CEST LOG:  database system was not properly shut down; automatic recovery in progress
2014-05-05 14:34:46 CEST LOG:  record with zero length at 1A1/AD6A78C0
2014-05-05 14:34:46 CEST LOG:  redo is not required
2014-05-05 14:34:47 CEST LOG:  database system is ready to accept connections
2014-05-05 14:34:47 CEST LOG:  autovacuum launcher started

verbose output from pg_dump (nothing suspicious, but the obvious table, which doesn't let the database continue to dump)

... lots of lines ...
pg_dump: restoring data for table "django_site"
pg_dump: dumping contents of table django_site
pg_dump: restoring data for table "fb_crawler_event"
pg_dump: dumping contents of table fb_crawler_event

And then it just halts.

output from the pg_locks table, which seems to be huge to me (294 entries all together):

  locktype  | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |      mode       | granted
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+-----------------+---------
 relation   |    16384 |     2674 |      |       |            |               |         |       |          | 1/23               | 19526 | AccessShareLock | t
 relation   |    16384 |    27367 |      |       |            |               |         |       |          | 1/23               | 19526 | AccessShareLock | t
 relation   |    16384 |   695092 |      |       |            |               |         |       |          | 1/23               | 19526 | AccessShareLock | t
 relation   |    16384 |     2675 |      |       |            |               |         |       |          | 2/7                | 18960 | AccessShareLock | t
 relation   |        0 |     2671 |      |       |            |               |         |       |          | 1/23               | 19526 | AccessShareLock | t
 virtualxid |          |          |      |       | 2/7        |               |         |       |          | 2/7                | 18960 | ExclusiveLock   | t
...

I am no expert in postgres, so I am wondering if the database is corrupt?

How can I further debug this anomaly?

I was able to figure out which file in the database storage was the culprit, by copying all the files to /dev/null.

cp -vR /usr/lib/postgresql/8.4 /dev/null

(The path to your DB files might differ)

The currupt file could not be copied, but there was nothing I could do to change that. (so it was most probably a FS error or hardware failure)

So I restarted the server with a forced fsck (e.g. touch /forcefsck), to make sure the FS would do the best to fix itself. This might not be the way you'll want to go, since it is possible to have a total data loss afterwards, but I was able to preserve the most precious data already beforehand, so I took the risk.

After reboot I could finally access the inaccessable table again, but I am not sure, if the data contained is corrupted or not. Anyway, I do have a backup now, which I can disect to find out, and my server can go back online for now...

I recommend reading the wiki of postgres about corruption and the slides of this FOSDEM presentation for some more info on DB corruption


SQL Server 2005 Analysis Services won't start We use SQL Server 2005 and sometimes our Analysis Services won't start. A simple reboot solves the problem, however, I'm trying to find a solution that does not rely on that.

If I try to start it from the "SQL Server Configuration Manager", I get a timeout error. When starting from windows' services manager, it states that the service was "started and then interrupted".

The windows application log has three entries:
1 - The flight recorder was started.
2 - The service cannot be started: The following system error occurred: . The following system error occurred: .
3 - Service stopped.

I have also tried to start "Analysis Services" with every single logon option. The same error occurs.

Are you doing a service restart or a Stop and start in SQL Server Config Manager? I had a similar issue on an old 2005 box and if I did a restart of SQL services in SQL server config manager the issue would not be resolved until I do a Stop and start or reboot the server.


Create view with group by and extra fields I had a hard time putting the question title together but essentially I want to create a view like this:

ALTER VIEW vPlaceStatistics
AS
SELECT COUNT (TypeID) as Count, TypeID
FROM PlaceStatistics
GROUP BY TypeID

...which works great, but there is a Date field (mydatefield) on the table that I want to be able to use as a filter in my view.

If include mydatefield in my view then it creates record for each date whereas I want it to create one for each TypeID.

Essentially, I want my view to have access to the date field without it being part of the group by, if that is the right way to do it.

If you want to filter rows before the aggregation, I would use an inline table-valued function instead of a view:

CREATE FUNCTION dbo.PlaceStatisticsForDate
    (@DateFilter AS date)
RETURNS TABLE
AS
RETURN
    SELECT COUNT(PS.TypeID) AS TypeCount, PS.TypeID
    FROM dbo.PlaceStatistics AS PS
    WHERE PS.mydatefield = @DateFilter
    GROUP BY PS.TypeID;

Usage:

SELECT PSD.TypeCount, PSD.TypeID
FROM dbo.PlaceStatisticsForDate ('20160506') AS PSD;


Why does DBMS_SESSION.session_trace_enable create multiple files? I have a trace on a session configured like this:

execute immediate 'ALTER SESSION SET TRACEFILE_IDENTIFIER = ''TEST1''';

DBMS_SESSION.session_trace_enable (waits => TRUE,
                                   binds => TRUE,
                                   plan_stat      => 'all_executions'
                                      );
-- do SQL stuff --
DBMS_SESSION.session_trace_disable();

When completed executing, I have some 80 .trc files created.  Why is that, and is there a way to force all tracing to one file?  Thanks.

This could be for at least 2 possible reasons:

Shared server architecture: SQL trace files belong to server processes, but when you use shared server architecture, your session does not have a single dedicated server process, your session can exist in several different server processes over time.
Parallel processing: when your statements run in parallel, each slave thread is a different process and as SQL trace files belong to processes, you have multiple processes running the same statement, hence the multiple trace files.

You can not force the creation of a single trace file, but you can consolidate these traces into a single file with trcsess, based on your SID, module name, action name, service or client identifier.


Data transformation implementation My company's database, I am working with, makes available a data to a lot of external applications. So I need to transform the same data to a lot of dynamic views. I can see that a former database developer has implemented many long chain of view-function-procedure call sequences to do transformation more common to all external applications. I think, this architecture and so long requests (stored proc calls some function, then funcs calls some view and this view based on other one and so on) are a performance problem at least query optimizer does not resolve these issues (please confirm my guesses). Is it good approch? Is it cause of degradation of performance? If yes, how can I reimplement objects of the database. At this moment I see these steps to do this:

analysis of source data structure (own data)
analysis of all external systems (what formats does database have to provide for)
separate views, functions, stored procs for every external subsystems (i have to avoid long chains, common to many subsystems db objects, if it is a cause of problem)

Thank you.

Is it a cause of degradation of performance? Nobody can really tell until you have measured it. From your description, it is certainly possible, but before I'd get going on the tasks I'd make sure if this is really the case. I'd also weigh in that reimplementing an existing setup doesn't come for free.

As for the steps to do: I'd put in place test cases that make sure that the external applications get the same data when you change something.

Lastly, I'd also get the commitment of management before you start doing that. It wouldn't be the first time that someone tries to put the fault on you if something with the database doesn't, even if you're not involved (and even if it is only marginally related to the database) just for the fact that someone heard that "this guy" is "chaning the system".


Cannot see SQL Agent Job history for more two days I have a job which runs every night around 02:00 AM.

But I cannot see the job history for more than two days.Can you kindly help me on this.

The limit size is very high and we hardly have one job which runs each day.

Still the issue. Please advice.

SQL Server Agent stores the history of job execution in system tables
  in msdb database.

The following query gives us the details of last/latest execution of the SQL Server Agent Job and also the next time when the job is going to run (if it is scheduled). This information can also be found in the Job History/Job Activity Monitor windows in SSMS.

SELECT
        [sJOB].[job_id] AS [JobID]
        , [sJOB].[name] AS [JobName]
        , CASE
            WHEN [sJOBH].[run_date] IS NULL OR [sJOBH].[run_time] IS NULL THEN NULL
            ELSE CAST(
                    CAST([sJOBH].[run_date] AS CHAR(8))
                    + ' '
                    + STUFF(
                        STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)),  6)
                            , 3, 0, ':')
                        , 6, 0, ':')
                    AS DATETIME)
          END AS [LastRunDateTime]
        , CASE [sJOBH].[run_status]
            WHEN 0 THEN 'Failed'
            WHEN 1 THEN 'Succeeded'
            WHEN 2 THEN 'Retry'
            WHEN 3 THEN 'Canceled'
            WHEN 4 THEN 'Running' -- In Progress
          END AS [LastRunStatus]
        , STUFF(
                STUFF(RIGHT('000000' + CAST([sJOBH].[run_duration] AS VARCHAR(6)),  6)
                    , 3, 0, ':')
                , 6, 0, ':')
            AS [LastRunDuration (HH:MM:SS)]
        , [sJOBH].[message] AS [LastRunStatusMessage]
        , CASE [sJOBSCH].[NextRunDate]
            WHEN 0 THEN NULL
            ELSE CAST(
                    CAST([sJOBSCH].[NextRunDate] AS CHAR(8))
                    + ' '
                    + STUFF(
                        STUFF(RIGHT('000000' + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)),  6)
                            , 3, 0, ':')
                        , 6, 0, ':')
                    AS DATETIME)
          END AS [NextRunDateTime]
    FROM
        [msdb].[dbo].[sysjobs] AS [sJOB]
        LEFT JOIN (
                    SELECT
                        [job_id]
                        , MIN([next_run_date]) AS [NextRunDate]
                        , MIN([next_run_time]) AS [NextRunTime]
                    FROM [msdb].[dbo].[sysjobschedules]
                    GROUP BY [job_id]
                ) AS [sJOBSCH]
            ON [sJOB].[job_id] = [sJOBSCH].[job_id]
        LEFT JOIN (
                    SELECT
                        [job_id]
                        , [run_date]
                        , [run_time]
                        , [run_status]
                        , [run_duration]
                        , [message]
                        , ROW_NUMBER() OVER (
                                                PARTITION BY [job_id]
                                                ORDER BY [run_date] DESC, [run_time] DESC
                          ) AS RowNumber
                    FROM [msdb].[dbo].[sysjobhistory]
                    WHERE [step_id] = 0
                ) AS [sJOBH]
            ON [sJOB].[job_id] = [sJOBH].[job_id]
            AND [sJOBH].[RowNumber] = 1
    ORDER BY [JobName];

I am also attaching the output of after execution of this script.

For your ref Here and Here


Update a record whilst creating a link to a parent record Someones account was deleted from a system we have. They created a new account for her and moved on. But it turns out she wasnt deleted from SQL - when they delete in the app, the record is marked as 'deleted' in a column in SQL.

So I am able to bring the account back, but there are certain triggers/constraints/something that is making it complicated to consolidate the data from the two accounts.

I cant bring back her 'child' records unless they have a link to the parent table, and I cannot have back both records in the parent table simultaneously because two staff numbers cannot exist at the same time (unless one is marked as deleted).

They deleted her new account, so I was able to bring the old one back. Now I need to add the absences that were put in for her new account to the restored old one, but if i try to do so by updating the ID column of her new absences to match the ID of the old (restored) user, I get the error

  Msg 50000, Level 16, State 1, Procedure trsys_Absence, Line 83 [Batch
  Start Line 209]
  ------------------------------------------No link made with the Parent table.

So I thought perhaps I could update the record by somehow linking to the parent table via a join as such

UPDATE tbl_Absence
SET b.ID = a.ID
FROM tbl_Users a
INNER JOIN tbl_Absence b ON a.ID = b.ID
WHERE b.ID = 9999 AND b.deleted = '2019-11-14 10:00:00'
AND a.ID = 9999;

But in this case

  The multi-part identifier "b.ID" could not be bound.

Not sure if what I am doing will even work, but any suggestions of ways I could do this - and hat I am doing wrong in this particular case - would be great, thanks.

Change your statement to

UPDATE b
   SET b.ID = a.ID
  FROM tbl_Users        AS a
 INNER JOIN tbl_Absence AS b
    ON a.ID = b.ID
 WHERE b.ID      = 9999
   AND b.deleted = '2019-11-14 10:00:00'
   AND a.ID      = 9999;

(just the first line is changed - use the alias b (for tbl_absence) direct after the UPDATE

PS: the AND a.ID = 9999 is obsolete too, since is implied by the JOIN condition


What is the permission necessary to drop a service in service-broker? When I want to drop a service which is already created, this error happens:

Msg 15151, Level 16, State 1, Line 316
Cannot drop the service '//Audit/Contract', because it does not exist or you do not have permission.

My sql query to perform drop operation is:

IF EXISTS(SELECT * FROM sys.service_contracts  WHERE NAME = '//Audit/Contract')
    DROP SERVICE [//Audit/Contract]

So, the main question is: What is the permissions necessary to drop a service in service-broker?

After I post this question, I just found my answer with some tricky way. I went to the left object explorer and delete the contract with it. but instead of submitting the form I click on the script button which produces the scripts. It was my mistake to drop a contract with drop service command. so the correct query to drop a contract is:

IF EXISTS(SELECT * FROM sys.service_contracts  WHERE NAME = '//Audit/Contract')
    DROP CONTRACT [//Audit/Contract]

I post this answer because maybe somebody encounter with this error and my guide can be useful to him/her.


How to merge column in mysql depending on match I have one table which looks like this in mysql

column1  column2    name    column3 data
1          1        foo     1       1.5
1          1        foo     2       2.5
1          1        foo     3       3.5
1          1        foo     4       4.5
1          1        foo2    1       1.1
1          1        foo2    2       3.5
1          1        foo3    3       5.6
1          1        foo3    4       6.7

I want to do query like something like this, I really don't know how to write as I am new to mysql

select name from mytable where name = 'foo' or name = 'foo2'or name = 'foo3'

I expect output to be like this

column1 column2 column3    foo    foo2  foo3
1       1         1        1.5     1.1  NaN
1       1         2        2.5     3.5  NaN
1       1         3        3.5     NaN  5.6
1       1         4        4.5     NaN  6.7

Is it possible in mysql ?

SELECT column1,column2,column3
MAX(CASE WHEN name='foo' THEN data ELSE 0 END)as foo,
MAX(CASE WHEN name='foo2' THEN data ELSE 0 END)as foo2,
MAX(CASE WHEN name='foo3' THEN data ELSE 0 END)as foo3
FROM tableName
GROUP BY column3


Weird issue with the order of clauses seeming to matter It's not an issue for me I was more just looking for an answer as to WHY it does this.

I have 2 queries both the same with the exception that one has a where clause in a different place (but no so that it should affect the results).

apologies I deal with sensitive information so these images don't really do this justice

Query 1:

Query 1 Results:

Query 2:

Query 2 Results:

I guess you will just have to take my word that both queries are exactly the same (aside form the IN statement moving to the top, underneath the date query, in the first query its at the bottom) but there is no subqueries or anything so I don't understand why both queries don't yield the same results lol

EDIT : Full queries with changed names;

While Re-writing the full query I noticed a hidden OR, god this is embarrassing, the last line is;

AND NOT myTable2.field LIKE 'Other Thing' OR 'Some OtherThing'

so When I added the line to the bottom it obviously became part of the OR LOL!

OK, just to follow up my correct (yay!) comment answer - in my experience almost everything along the lines of "weird issue" involving data selection, involves a rogue, unparenthesised "OR" somewhere in the where clause. It's always the first place I'd look.

Typically you write some code along the lines of:

WHERE a
AND b OR c
AND d

and then when you look at the code your mind kind of fills in the parenthesis where you think it ought to be. Whereas order or precedence rules actually perform it thus:

WHERE (a AND b) OR (c AND d)

A good trick to use when coding, which I tend to do automatically now, is as soon as you use an "OR" anywhere in your code, then add some parenthesis to make it totally clear.

Even if your where clause is simply:

WHERE a OR b

Then explicitly change it to

WHERE ( a OR b )

since, you're future-proofing against someone adding to the code later and accidentally writing something like:

WHERE a OR b
AND c


Linked Server using ODBC too slow The scenario is that developers used a linked server object on ServerM to reach ServerST. This linked server was setup as a "SQL Server" type using a domain login that exists as a login on ServerST.

Now the developers require this linked server object (named ServerST) to be reconfigured to point at ServerAR (even though confusingly the object would still be named ServerST). This allows them to save time rewriting a lot of code, time they do not have.

Now I can create a new linked server on ServerM of the type "Other data source" and provide a connection string that works. However a query on the original linked server that took 5s took 46s using the new linked server.

So my question is how do I provide a linked server named ServerST, that points to ServerAR and operates at the same speed.

All servers are on SQL Server 2008 R2 SP2.

The code I currently use to create the slow linked server is:

sp_addlinkedserver 'ServerST', ' ', 'MSDASQL', '', '',
    'Driver={SQL Server};Server=ServerST;Database=theDB;Uid=link_ServerM;Pwd={P@ssw0rd};'

Why not just use the SQLNCLI10 provider? Are you required to use MSDASQL?

Here is a sp_addlinkedserver script adding a linked server named ServerST that points to ServerAR.

EXEC master.dbo.sp_addlinkedserver @server = N'ServerST', @srvproduct=N'ServerST',
    @provider=N'SQLNCLI10', @datasrc=N'ServerAR'

Update 19/082014

Download SQL Query Stress Tool to compare performance of both SQL Servers, ServerST and ServerAR.

Scripting out your existing Linked Server

This will also include any specially defined parameters such as collation and time out configurations.


Permissions using .NET SqlDependency SQLDependency Notifications work fine logged in as Windows Domain User, using Trusted Connection. But not as SQL-User:

I created a db login, user and role as follows:

CREATE LOGIN myLogin WITH PASSWORD = 'somePW', CHECK_POLICY = OFF;
BEGIN CREATE USER myUser FOR LOGIN myLogin;
BEGIN CREATE ROLE "myRole" AUTHORIZATION [dbo];
EXECUTE sp_addrolemember N'myRole', N'myUser';

Furthermore, I added some permissions, following this link

--Database level permissions
GRANT CREATE PROCEDURE to [myRole];
GRANT CREATE QUEUE to [myRole];
GRANT CREATE SERVICE to [myRole];
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [myRole];
GRANT VIEW DEFINITION TO [myRole];

--Service Broker permissions
GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [myRole];
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [myRole];

and also tried changing the db-owner as described in this article

This DBA-Question ( aiming in the same direction ) does not provide any help.

The permissions to execute the query and do a select on the table by the SqlDependency are also set. I verified this using SSMS and execute the query with the user/pw-combination created above.

Now, I try to use the SqlDependency via .NET ( connection to DB is established with the login/user described above. Executing SqlDependency.Start( myConnectionString ) throws an exception ( translated from german ):

  The specified user 'owner' was not found.
  Search the queue object 'SqlQueryNotificationService-9741490d-4f04-1f4e-AD70-b198e85b2812' is not possible because the object does not exist or you do not have the required permission.
  Invalid object name 'SqlQueryNotificationService-9741490d-4f04-1f4e-AD70-b198e85b2812'.

Any ideas what is missing?

After days of debugging, one little setting made all the difference.

If you get this error mentioned in OP, please check the owner of the DB-role used! It wasn't set to dbo in my case. Changing that made it work - see comments for history!


Disabling IPV6 on Always on availability group In our environment we only have one network adapters for the Replicas and IPV4 is being used. By default the IPV6 is also enabled. My question is will there be any problems if I unchecked IPV6 from the Adapter properties

My question is will there be any problems if I unchecked IPV6 from the Adapter properties

Do not disable it, specially if you are not having any issue. WSFC does use IPV6 both for internal communication and can also be used for public network communication. Plus even if you disable IPV6 it is highly unlikely that cluster would not work or start giving some issue. I am sure that even if you disable IPV6 the WSFC would work but may be other network functionality might be affected since network is beyond scope of discussion I am not sure what might be affected but WSFC will work.

I would quote from Failover Clustering and IPv6 in Windows Server 2012 R2

  Should IPv6 be disabled for Failover Clustering?

  The recommendation for Failover Clustering and Windows in general,
  starting in 2008 RTM, is to not disable IPv6 for your Failover
  Clusters. The majority of the internal testing for Failover
  Clustering is done with IPv6 enabled. Therefore, having IPv6 enabled
  will result in the safest configuration for your production
  deployment.


Multiple concurrent "REFRESH MATERIALIZED VIEW": how to manage? I have a large Postgres database with many tables, some with tens of millions of rows. Several working processes update the database concurrently. For faster searches, relevant data is compiled into a materialized view.

There are possibly multiple parallel processes writing to the database, then refreshing the materialized view afterwards. However, as the "REFRESH MATERIALIZED VIEW" query takes at least several minutes, quite often such queries pile up in a queue, and they all execute one after the other.

Unfortunately in such cases, only the latest query is of any relevance; all the previous queries consume processing time in vain to refresh stale data. Is there a way for stopping already running calls to "REFRESH MATERIALIZED VIEW" when a new call is issued?

Please note that "REFRESH MATERIALIZED VIEW CONCURRENTLY" has the same behaviour, but considerably slows the refresh (from a few minutes up to an hour), therefore aggravating the performance problem.

Of course it's possible to test for an existing lock on the view for each new query, so it's easy to cancel new queries; the problem is that I'd rather cancel the old queries and keep only the latest one...

It looks like you want to refresh the materialized views whenever the data in the tables change. If you do that with materialized views, it will take a long time, and updates will block each other and queries.

Maybe you can build your own “on commit refresh” materialized views as tables.

A simple example:

Instead of

CREATE MATERIALIZED VIEW sum_eumel AS
SELECT eumel_category,
       sum(eumel_data) AS eumel_sum
FROM eumel
GROUP BY eumel_category;

you could do this:

BEGIN;

CREATE TABLE sum_eumel (
   eumel_category text NOT NULL PRIMARY KEY,
   eumel_sum bigint NOT NULL DEFAULT 0
);

CREATE FUNCTION eumel_trigger() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF TG_OP IN ('UPDATE', 'DELETE') THEN
      /*
       * Will leave rows with value 0 after the last
       * row for a category has been deleted.
       */
      UPDATE sum_eumel
      SET eumel_sum = eumel_sum - OLD.eumel_data
      WHERE eumel_category = OLD.eumel_category;
   END IF;

   IF TG_OP IN ('INSERT', 'UPDATE') THEN
      INSERT INTO sum_eumel (eumel_category, eumel_sum)
      VALUES (NEW.eumel_category, NEW.eumel_data)
      ON CONFLICT TO UPDATE
      SET eumel_sum = sum_eumel.eumel_sum + EXCLUDED.eumel_data
   END IF;

   IF TG_OP = 'TRUNCATE' THEN
      TRUNCATE sum_eumel;
      RETURN NULL;
   END IF;

   IF TG_OP = 'DELETE' THEN
      RETURN OLD;
   ELSE
      RETURN NEW;
   END IF;
END;$$;

CREATE TRIGGER eumel_dml_trig
   AFTER INSERT OR UPDATE OR DELETE ON eumel
   FOR EACH ROW EXECUTE PROCEDURE eumel_trigger();

CREATE TRIGGER eumel_truncate_trig
   AFTER TRUNCATE ON eumel
   FOR EACH STATEMENT EXECUTE PROCEDURE eumel_trigger();

INSERT INTO sum_eumel
SELECT eumel_category,
       sum(eumel_data) AS eumel_sum
FROM eumel
GROUP BY eumel_category;

COMMIT;


Restarting Galera MySQL after proper Shutdown I already have read and followed dozens of documents describing a split brain situation and how to restart the cluster after then.

However, I am less in this situation as in the situation of shutting down the Cluster properly, that is, stopping all nodes with

systemctl stop mysql

So, the cluster is reachable until the last node is stopped. Then, I know which one was the last one and I should be able to restart the nodes without creating a new cluster, so that joining nodes perform an IST.

How do I achieve this?

I am not quite certain if I understand your question completely, but let's consider two scenarios:
After an orderly, manual shutdown of the cluster, you will know which node you shut down last. Just begin the startup sequence with that node, and bring them up manually one after the other.
But suppose your shutdown was accidental, due to a power outage or similar. You will not know which node considers itself "the last to go down". However, you can check the file grastate.dat on the individual cluster members and search for the flag safe_to_bootstrap: 1. If there is exactly one node that has this flag set to 1, boot that one first.
Otherwise, as described in https://galeracluster.com/2016/11/introducing-the-safe-to-bootstrap-feature-in-galera-cluster/, start mysqld with the --wsrep-recover switch, compare the UUIDs in the "Recovered position" line in the output and set the safe_to_bootstrap flag to 1 by hand on the node with the highest UUID. Then, proceed as above.
Those were the procedures for manually starting the nodes. I'm asking about a way to automate this in a more robust way in a different question.


corrupted Oracle 11g database At a client site with a development Oracle 11.2.0.3/linux DB

power outage and crash - no archivelog mode - no backups - Don't know how this was even running

select file#,substr(name,1,70) name, recover, status, fuzzy, checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time from v$datafile_header;

FILE# NAME                                               REC STATUS  FUZ
CHECKPOINT_CHANGE# CHECKPOINT_TIME      RESETLOGS_CHANGE# RESETLOGS_TIME
1 /oracle/oradata/orcl/system01.dbf                 NO  ONLINE             YES         4663440487 08-MAR-2018 02:57:17            945184 04-APR-2012 12:07:59
2 /oracle/oradata/orcl/sysaux01.dbf                 YES ONLINE             YES         3759618229 21-DEC-2016 22:19:41            945184 04-APR-2012 12:07:59
11 /oracle/oradata/orcl/rmdata01.dbf                 YES ONLINE             YES         3759618229 21-DEC-2016 22:19:41            945184 04-APR-2012 12:07:59

Basic plan:

alter database datafile 2 offline drop;
alter database datafile 11 offline drop;
recover database ;
export/import
rebuild DB

Don't care about sysaux, however, I hope to keep whatever is in datafile 11.

I was hoping to force the DB open even with datafile 11 in a bad state.

Oracle says any hidden parameters will not help due to very old SCNs.

Have you faced this situation and successfully recovered the data?

For anyone who wants to know how this was resovled...

Bobby Durrett was correct, Oracle does have the ability to pull data from blocks.

I contacted Oracle ACS.  They sent a top consultant and using the Data UnLoader tool (DUL), which by the way is not their product, were able to extract data from the blocks that survived the corruption.

Bascially you configure the tool with a few parameters and identify the datafiles of interest.  It helps a lot if the system data file is present.  You can tell it to create SQL*Loader or dmp files.  It will skip any block that is corrupt and move on to the next block.  The datafiles are read cold so no need to mount or create a new instance or any such thing.

Caveat:
All you get is table DDL and data.  No users, contraints, FK so your are not recovering the database, just the data.

It works beautifully - on any block not corrupted!


DB Recommendation While Minimizing Size I apologize if this question is too specific, if it is, please let me know!

I have searched around both on here and the web on selecting the best project-specific engine to fit your needs, and have come up with nothing. So, generally, what is the best way to select a host/DB Management System?

In my specific case, I will have a DB storing data from now on (forever?) from up to 150 sources, with data coming every 30s 24/7/365 (up to 300 rows added/min). Each source will be inserting into it's own table. The data will be read in bulk from each table - e.g. dumping 1k+ rows at a time.

A general explanation on how to highlight the positives and negatives of each option would be immensely helpful.

Once I've selected a platform to start on, what can I do to minimize the space I use? I plan to match data types with the data coming in, but here is an example:

{ (up to)12-digit unsigned int, date/time(epoch), unsigned long, signed int (<10k), unsigned int (<1k), unsigned int (<250), unsigned double (<500) }
{ 120308, 1428844432, 8222786, -5, 0, 0, 24.7 }

Thank you for any help you may have.

Short answer: It depends on a lot of different things.

Longer answer w/ questions: If you haven't used any database management systems before, then any of the main options (SQL Server, Oracle, PostgreSQL) will suit your needs. However, do you already have a DBMS installed on a server, or is this a personal project starting from scratch? Do you require a physical server and your own copy of the software, or are you willing to explore cloud-based options?

What are your plans for backup and restoration of critical data? What level of availability do you need (for example, 5 9s)? Apart from loading data every 30 seconds, are you querying this data often or at all, or are you simply storing it?

Without knowing anything else about your application, how it's going to be sending data to this central DB, and how you're going to use the data, it's hard to make a firm recommendation (at least for me).

As for data types and minimising space used, you've got the right idea. Good indexing strategies based on a use case come into play when trying to minimise data usage. Minimising data duplication (look at normalisation forms if you're not sure about it) using properly set up Dimension and Fact tables in the appropriate layout (Star, Snowflake etc.), assuming a Data Warehouse use (that's sort of what it sounds like) will help with query performance as well as minimising space used.

TL;DR: Just spin up an instance or two of Microsoft SQL Server on Microsoft Azure to test your application/service and see how it goes. It's scalable, can be well within a small budget and if you don't need it, you can get rid of it.

http://azure.microsoft.com/en-us/services/sql-database/


Date and time parameter In msrs report Hi I am looking to set up my report so the start date will always be say '14-09-2011 00:00:00'
And the end date will always be '14-09-2011 23:59:59' Or in other words Start date will be 00:00 and end date will be 23:59 for what ever days I select.

At the moment my parameters are

Startdate: =DateAdd(DateInterval.Day, -7, Today())

End Date: =today()

I wouldn't worry about setting the time component.  Just write your query like this instead:

WHERE SomeColumn >= @StartDate
  AND SomeColumn < DATEADD(Day, 1, @EndDate)

You have @EndDate = Today() which by default does not have a time component (or rather it's already 00:00:00).  Adding a whole day to that gets you tomorrow at midnight.  So the query above asks for any data on or after @StartDate but no later than tomorrow at midnight.

If you're querying a SQL 2K8+ instance you can further do this to be sure you're not erroneously using a time component:

WHERE SomeColumn >= CONVERT(Date, @StartDate)
  AND SomeColumn < CONVERT(Date, DATEADD(Day, 1, @EndDate))


What is the purpose of the command-line options "-ANe" for MySQL? I've recently changed how a mysqldump backup is set up, and happened across a helpful answer by RolandoMySQLDBA where his example:

  mysql ${MYSQL_CONN} -ANe"${SQL}"

Uses the -ANe command line switch to precede the query to execute.

I understand that these have the following effects:

  --no-auto-rehash, -A

  Disables auto-rehash, which is described as: Enable automatic
  rehashing. This option is on by default, which enables database,
  table, and column name completion. Use --disable-auto-rehash to
  disable rehashing. That causes mysql to start faster, but you must
  issue the rehash command or its # shortcut if you want to use name
  completion.
  --skip-column-names, -N

  Do not write column names in results.
  --execute=statement, -e statement

  Execute the statement and quit.

Obviously, I understand that since we are executing a query, the -e argument is required.

The query I am executing is changing the global variable to enable/disable the slow query log. I'm not quite sure how omitting column names in results is important in this case. I'm less clear on how disabling auto rehashing is necessary or precisely what it is doing. I understand from the description that it causes mysql to start faster, but since the mysql process is already running, isn't that ignored?

In short, what are the -A and -N arguments for in the context of changing a global variable via shell script?

MySQL 5.6.4 on Ubuntu 14.04 LTS

The biggest reason was already stated by Vérace, but I wanted to add to it

When a database instance has lots of tables and lots of columns, the information_schema has to inspected in memory. This can be a cause for alarm. Why ?

Back in April 2014, I wrote the answer to Do Inactive MySQL Databases Consume Memory? and Adding new tables -- memory usage increases.

I have seen MySQL instances with 800+ databases with each database containing 162 tables.

Imagine loading metadata for the DB you connect to. Lots of needless prep work for the beginning of a DB Connection's LifeCycle along with allocating memory for a local copy of that metadata in addition to per-session buffers (See my post How costly is opening and closing of a DB connection?)

This is the biggest reason why I use -A to connect to do little things.


Ola Hallengrens - Output File cleanup does not delete files We run Ola Hallengrens backup,dbcc & index jobs but we have not scheduled Output File Cleanup for some reason. Noticed this today and when we tried to run it on a server that had 2 or more instances it doesn't delete old logs.
We have for example set up 1 server with 2 instances and it's set up with AvailabilityGroups.
When running the script this is the output: (I've deleted a few rows so it didn't take up the whole page but it deleted a few files)

  Date      2017-07-04 10:57:55 Log     Job History (Output File Cleanup)

  Step ID       1 Server        SERVER1183\INST01 Job Name      Output File Cleanup
  Step Name     Output File Cleanup Duration        00:01:54 Sql Severity   0 Sql
  Message ID    0 Operator Emailed   Operator Net sent   Operator Paged
  Retries Attempted 0

  Message Executed as user: VT\sqladmin-internaltes.
  ...8C0E25584E08C4CB393567C2C66083E_1_20170301_132833.txt"  del
  "E:\MSSQL\INST01\MSSQL12.INST01\MSSQL\Log"\"DatabaseBackup_0x18C0E25584E08C4CB393567C2C66083E_1_20170304_200000.txt"
  del
  "E:\MSSQL\INST01\MSSQL12.INST01\MSSQL\Log"\"DatabaseBackup_0x6DFC82E95...
  Process Exit Code 0.  The step succeeded.

After checking in the Logs folder which is located: E:\MSSQL\INST01\MSSQL12.INST01\MSSQL\Log we still have 12500 files.

The jobs are set up as standard when adding Olas jobs, with output file config:

$(ESCAPE_SQUOTE(SQLLOGDIR))\IndexOptimize_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt

We have not changed the job command at all which is:

cmd /q /c "For /F "tokens=1 delims=" %v In ('ForFiles /P
"$(ESCAPE_SQUOTE(SQLLOGDIR))" /m *_*_*_*.txt /d -30 2^>^&1') do if
EXIST "$(ESCAPE_SQUOTE(SQLLOGDIR))"\%v echo del
"$(ESCAPE_SQUOTE(SQLLOGDIR))"\%v& del
"$(ESCAPE_SQUOTE(SQLLOGDIR))"\%v"

The script works fine when it's a single instance or default instance.
It's sql 2014 if that's relevant but we have this problem on 2008 & 2012 aswell.

Anyone got this figured out or know how to fix this?

I should add that this works fine with 2 or more instances that are not set up with availability groups. It's only when it's more then 1 instance and AG set up. But what I did instead was to edit the parameters and change $(ESCAPE_SQUOTE(SQLLOGDIR)) to the actual path folder and it worked great.


Count all rows for each DISTINCT How can count all rows of WHERE statement:

SELECT DISTINCT column1 FROM table COUNT WHERE date::text LIKE '%2014%';

My output:

column1
=======
info1
info2
info3

Desired OUTPUT:

column1|number_of_rows
=====================
info1  | 2000
info2  | 1500
info3  | 1000

Can you support me How can improve it?

Thanks in advance.

In this case you can use GROUP BY to get distinct column1 values, and instead of convert the date to text you can use EXTRACT function for this purpose.

select
    colum1,
    count(*) as number_of_rows
from
    your_table
where
    extract(year from date) = 2014
group by
    column1;


3NF Decomposition - I'm not sure about this example I am trying to make sense of an example of 3NF decomposition using the 4-step algorithm mentioned by Ullman here, but I'm not understanding what my lecturer is doing with the last step (or, worse, I'm not understanding the algorithm itself).

I realize this is a bit of a newbie question, but I did all the googling but couldn't find anything illuminating and I've been sitting here scratching my head for a few hours now.

Now,

the algorithm in question is:

Find a canonical cover F_c for F
For each FD X->Y in F_c create a relation with schema XY
Eliminate a relation if its schema is a subset of another
If none of the schemas created so far contains a key or R add a relation schema containing a key of R.

Now, the example my lecturer gave us starts with

 R<A,B,C,D,E,F>
 CE->A, C->D, A->B, D->BE, B->F, AD->CF

Fc is shown to be

C->A, C->D, A->B, D->B, D->E, B->F, AD->C

and the keys are C and AD.

All's well until now.

Now we apply step 2 of the algorithm.

We have:

R_i:    A,B   |   D,B,E  |   F,B    |   C,D,A   |   A,D,C
     ---------+----------+----------+-----------+-----------
F_i    A->B   |   D->B,  |   B->F   |   C->D    |   AD->C
              |   D->E   |          |   C->A    |

Now this is were it gets ugly.
The lecturer remarks that the fifth one, R_5 is a subset (a trivial subset, say I) of the fourth and proceeds to... "merge" them, leaving us with

R_i:    A,B   |   D,B,E  |   F,B    |   C,D,A
     ---------+----------+----------+-----------
F_i    A->B   |   D->B,  |   B->F   |   C->D
              |   D->E   |          |   C->A
              |          |          |   AD->C   - - - - - notice this!

Notice how AD->C popped up in F_4.

The question is simply: why?

Why didn't he remove the fifth column altogether?

Is he applying step 3 of the algorithm?
But it only instructs to eliminate the offending schemas, it doesn't say anything about adding dependencies to other schemas.

Is he applying step 4 of the algorithm?
But it says that the schemas must contain a key of R, not all of them (which would then make some sense).

Is this simply a trivial mistake?
Is the final result correct anyway?

Thanks everybody.

The theory is all fine and good, but it only starts to really make sense once you understand the practice. The Lawyer's version of the first three Normal Forms is:

The Key - there must be a Primary key for every relation being normalized.
The Whole Key - There must not be any functional dependencies of attributes on any proper subset of the Primary Key.
And Nothing but The Key - There must not be any functional dependencies of attributes on non-key attributes.

In practice relations frequently have multiple candidate keys, and these rules must apply to all of them in turn.

Also, when Normalizing one must determine the minimal key(s) for each relation, and use those rather than any Super Key.

Your lecturer didn't remove the fifth column altogether because the functional dependency in that column still exists, and must still be accounted for in the Normalization process.

Update:

The FD AD->C doesn't disappear by virtue of recognizing ADC as a subset of CDA; neither is it sensible to have two relations ADC and CDA both in the model as this is a redundancy of exactly the sort Normalization is designed to eliminate.


Huge Heap Table and table compression on SQL Server 2016 My database size is huge, and recently I noticed that a new table added few months ago is the culprit.

Here is the table script.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Entry_tracker](
    [S.Number] [int] IDENTITY(1,1) NOT NULL,
    [EntryId] [varchar](50) NOT NULL,
    [EventNumber] [varchar](18) NOT NULL,
    [Data] [varbinary](max) NOT NULL,
    [TrackDateTime] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Entry_tracker] ADD  CONSTRAINT [DF_Entry_tracker_TrackDateTime]  DEFAULT (getdate()) FOR [TrackDateTime]
GO

I collected information on this table, and learned that the table has about 16 million rows and the table size is 1.4TB.

Still, I think that the table size is huge for the no. of records.

This table is not queried by the application. It just stores different versions of same entries from another table.

I checked the fragmentation information and it shows 0 average fragmentation with 93% average space used.

Since I am using SQL Server 2016, I though I could use table compression, so  tried sp_estimate_data_compression_savings to estimate possible space savings.
 However, the results shows no savings.

size_with_current_compression_setting(KB) is equal to size_with_requested_compression_setting(KB)

Can anyone direct me in finding the issue with this table?

It is really important, as this is taking a lot of disk space.

Appreciate your help.

Still i think that the table size is huge for the no. of records.

Why? What makes you say this?

  what it does is stores different version of same entry from another table.

So it holds versions, which could be large or small, especially given:

[Data] [varbinary](max) NOT NULL,

Have you checked the datalength() on the rows to see if you have some large rows in there eating space?

Seems to me, based on the data the size it is given the function seems reasonable. Versioning is rarely small unless it's just changed bytes and you use various algorithms to understand which bytes, how, and if it builds off previously changed versions or not.


Changing the Parametization option in SQL Server 2012 from Simple to Forced Does changing the Parametization option in SQL Server 2012 from Simple to Forced clear the cache; or should DBCC FREEPROCCACHE be run after the change?

It frees the plan cache for the database that you change the option for, and nothing else.

On my server, I run these queries:

USE StackOverflow;

SELECT TOP 1000 * FROM dbo.Users AS u WHERE u.Reputation = 2;

USE StackOverflow2010;

SELECT TOP 1000 * FROM dbo.Users AS u WHERE u.Reputation = 1;

They I use sp_BlitzCache to look at my plan cache:

After that, change one database to use Forced Parameterization:

ALTER DATABASE StackOverflow SET PARAMETERIZATION FORCED

Give sp_BlitzCache another run:

Only the plan from StackOverflow2010 remains.

Full disclosure: I contribute a lot of mostly working code to the open source sp_BlitzCache stored procedure.


How to disable duplicate entry checks in MySQL workbench? I am trying to import a large database dump (~30 GB) with millions of rows. There are some duplicate rows in there for sure, and I'd like MySQL to just skip those rows and move on without giving me the following error:

ERROR 1062 (23000) at line 23: Duplicate entry '31' for key 'PRIMARY'

If you're bringing the data into an otherwise empty table, then drop the primary key. You'll be able to import the data as is, clean it up, make sure that the primary key column(s) are now unique, then put the primary key back in place.

I would not recommend bringing the data into a table with other data directly, as you know the data does need to be cleaned up. Import the "dirty" data into a staging table, clean it up, decide how you need to merge it into the existing table (as in, if there is duplication in keys between the data in the target table and the "dirty" data, which data should be kept, and which should be discarded; this may differ on a record-by-record basis).

Note: The links on merging data may work, if you already know which table's data should be kept in all cases; however, it sounds like the data coming in did not have a properly enforced primary key, and thus the incoming data may have multiple records with the same primary key value. I'm not certain if all those options work as desired in that case.


Concat a string to a variable to use as a column name in a query I have a procedure where I query table a and create variables like this
SELECT TRCODE , COLA , COLB , COLC , COLD
INTO TRANSACTION_TYPE , HOURS , RATE , FACTOR , APPROPRIATION
FROM PR . PREXEC
WHERE USRID = USER_NUMBER ;

The appropriation variable contains one character and it is a number from 1-4.
I have another table called pr.earnings. In that table I have a columns named APPROP1 through APPROP4. In this same procedure I need to query pr.earnings for by taking the string 'APPROP' and concatenating the APPROPRIATION variable from the query.
I tried
select concat('APPROP',APPROPRIATION)

FROM PR.EARNIGS;

The result I want is select APPROP1 FROM PR.EARNINGS; What happens is I get a system created column with APPROP1 inside it. I can't figure out how to concat a string to a variable to use in a query.
I tried a using dynamic sql but kept getting errors because I don't know what I'm doing.
How do I concat a string and a variable to use it as a column name in an sql query?
Based on what Lennart suggested I have this now
SET STMT = 'SELECT APPROP' || appropriation || ' pr.earnings into ' || active_appropriation ||
               ' where empnumber = ' || user_number;
    EXECUTE IMMEDIATE STMT

And am trying to insert active_appropriation like this
insert into pr.worksheet (W_EMPLOYEE_NUMBER, W_TRANSACTION_TYPE, W_HOURS, W_RATE, W_APPROPRIATION)
    values (user_number, transaction_type, hours, rate, active_appropriation);

I get the error 'Variable cannot be null' I am assuming it is the active_appropriation value.
I got it to work. here is my solution.
    SET STMT2 = 'VALUES (SELECT approp' || appropriation ||
    'FROM pr.earnings where empnumber = ' || user_number || ') INTO ?';
PREPARE S2 from stmt2;
EXECUTE S2 using active_appropriation;

The problem with your query is that you need to construct meta-data (name of a column) with data. You therefore need to first construct the query, and then execute it using dynamic SQL. Something like (untested)
CREATE PROCEDURE P
LANGUAGE SQL
BEGIN
    DECLARE STMT VARCHAR(100);
    DECLARE APPROP CHAR(1);

    SELECT APPROPRIATION INTO APPROP FROM ...;
    SET STMT = 'SELECT APPROP' || APPROP || ' FROM ... ';
    EXECUTE IMMEDIATE STMT;
END @

I used @ as a statement terminator. Note that the compiler has no way to verify that the query makes sense, so you may end up with run-time errors.
Debugging stored procedures
IBM Datastudio has a fully-fledged stored procedure debugger, i.e. you can set breakpoints, inspect variables, etc. If you for some reason does not want to install it (or like me, can not find the download page:-), you can use the DBMS_OUTPUT module:
~]$ # add some sample data
~]$ db2 "create table t (APPROPRIATION char(1) not null)"
~]$ db2 "insert into t (APPROPRIATION) values ('1')"
~]$ db2 "insert into t (APPROPRIATION) values ('2')"
~]$ db2 "insert into t (APPROPRIATION) values ('3')"
~]$ db2 "insert into t (APPROPRIATION) values ('4')"

~]$ cat p.sql
CREATE PROCEDURE P()
LANGUAGE SQL
BEGIN
    DECLARE STMT VARCHAR(100);
    DECLARE APPROP CHAR(1);

    SELECT APPROPRIATION INTO APPROP FROM T ORDER BY RAND() FETCH FIRST 1 ROWS ONLY;
    SET STMT = 'SELECT APPROP' || APPROP || ' FROM ...';
    call dbms_output.put_line('DEBUG: ' || STMT);
    -- EXECUTE IMMEDIATE STMT;
END @

~]$ db2 -td@ -f p.sql

~]$ db2 "SET SERVEROUTPUT ON"
DB20000I  The SET SERVEROUTPUT command completed successfully.
~]$ db2 "CALL P()"

  Return Status = 0

DEBUG: SELECT APPROP1 FROM ...
~]$ db2 "CALL P()"

  Return Status = 0

DEBUG: SELECT APPROP1 FROM ...
~]$ db2 "CALL P()"

  Return Status = 0

DEBUG: SELECT APPROP2 FROM ...
~]$ db2 "CALL P()"

  Return Status = 0

DEBUG: SELECT APPROP1 FROM ...
~]$ db2 "CALL P()"

  Return Status = 0

DEBUG: SELECT APPROP3 FROM ...

~]$ db2 "SET SERVEROUTPUT OFF"

Another option is to use an out variable:
~]$ cat p2.sql
CREATE PROCEDURE P2(OUT outstmt VARCHAR(100))
LANGUAGE SQL
BEGIN
    DECLARE STMT VARCHAR(100);
    DECLARE APPROP CHAR(1);

    SELECT APPROPRIATION INTO APPROP FROM T ORDER BY RAND() FETCH FIRST 1 ROWS ONLY;
    SET STMT = 'SELECT APPROP' || APPROP || ' FROM ...';
    SET outstmt = stmt;
    call dbms_output.put_line('DEBUG: ' || STMT);
    -- EXECUTE IMMEDIATE STMT;
END @

~]$ db2 -td@ -f p2.sql

~]$ db2 "CALL P2(?)"

  Value of output parameters
  --------------------------
  Parameter Name  : OUTSTMT
  Parameter Value : SELECT APPROP1 FROM ...

  Return Status = 0

One major drawback with both these methods is that they will not produce any output when you need it the most, i.e. when your procedure fails.
For future posts, always include a reproducible setup. Either include DDL (create table statements, etc) and SQL (insert table statements) or use a service like DB<>Fiddle.
Most people (including myself) usually just skip to the next question unless there an easy way to reproduce the problem. Not only will you get more answers, but they will also be ready to use since you defined the setup. As of now, if my answer is at all usable for you, you will have to adapt it to your actual setup since I invented some imaginary tables etc.


Should credits be stored as negative numbers in a double-entry bookkeeping database? Assuming you use a table structure like:

Journal Entries

JournalEntryID
AccountingPeriodID
TimeStamp

Journal Entry Lines

JournalEntryLineID (UID)
JournalEntryID (Foreign to Journal Entries)
AccountID
Amount

Does it make sense to use negative numbers in the amount column to represent credits? If you used negative numbers for credits and positive numbers for debits, it would be easy to check the integrity of all the records just by getting the SUM() of Amount for all records in the Journal Entry Lines table (it should be zero at all times).

Paper journals don't use signed numbers though since the sign is implicit. And other systems I've looked at have used positive amounts for both. Would it be good practice to use signed amounts or are there pitfalls to this approach?

I've worked for a long time with a range of different types of accounting systems (albeit predominantly with European accounting standards) and the norm is that you use positive numbers for debit entries and negative numbers for credit entries. This means that your assets are positive and your liabilities negative, but your income is negative and costs are positive. So when you're building income statement-related reports you'll need to invert the numbers.

I've seen instances of systems that use separate columns for debit and credit, but to me that is just anti-normalization. Typically, most of those systems also provide a "total" column, which is positive in debit and negative in credit.

As you say, this way every transaction has to balance out to zero (you can probably write some type of constraint to make sure it does).


Why is searching by one varchar column not working correctly on replica? PostgreSQL: 9.6, master, replica.
Async replication, wal archiving.

We detected that if we execute a select query on a varchar column against a replica, then the results return no rows, but if we execute against a master then the results will be correct.

SELECT id FROM platform WHERE name = 'test'

Master: 10
Replica: now rows

SELECT name FROM platform WHERE id = 10

Master: test
Replica: test

SELECT id FROM platform WHERE name in ('test', 'test1')

Master: 10, 11
Replica: 11

SELECT name FROM platform WHERE id in (10, 11)

Master: test, test1
Replica: test, test1

I didn't find any information about such issues and I don't know how to solve it.

As you say the column is indexed, try dropping and creating the index again. It might have gotten corrupted during replication.


Is there any way to prevent a PostgreSQL user to change his/her own password? I'm administering a PotsgreSQL server and recently I realized that my "nonsuperuser" users have the possibility to change the secure passwords that I assign them for insecure ones.

I searched in the REVOKE docs, but I couldn't find a way to prevent that. It is possible?

Thanks in advance!

AFAIK, there is no way to prevent a user from changing his/her password. It's been discussed (quite long ago) on ocassion on PostgreSQL mailing list, and all the answers I remember were basically "no, you can't".
However, there's a contrib module, called passwordcheck, whose objective is to guarantee the strength of passwords, if that's what you wish (as already commented by @RDFozz).
From the docs:

The passwordcheck module checks users' passwords whenever they are set with CREATE ROLE or ALTER ROLE. If a password is considered too weak, it will be rejected and the command will terminate with an error.
To enable this module, add '$libdir/passwordcheck' to shared_preload_libraries in postgresql.conf, then restart the server.

I've never used the module myself.


Can we shrink datafile of a database which is configured with log shipping I am using MS SQL Server 2012 version RTM, below is our environment,

I have a primary database Server A and Secondary Database Server B Log shipping configured on A to B of a database,

1) Database size 500 GB, We would like to delete old data and make it to 250 GB, Can we do this without breaking of log shipping?

2) After this we need to shrink the data file not log file, Can we shrink data file without breaking of log shipping?

If we do shrinking do i need to stop log shipping jobs or will it break log shipping configuration?

No, shrink won't break Your log shipping configuration. But You must be aware that both shrink (and rebuild/reorganize You will have to do afterwards) will make Your transaction log files grow a lot. All of those operations cause a lot of I/O load that is logged to transaction logs.

This, in practice, might mean that while Your log shipping won't break, it will make restore last a lot longer, depending on Your backup/copy/restore jobs frequency of course. That might lead to secondary falling behind a bit till shrink's (and defrag's) end.

Remember that shrink has serious issues, read Paul Randal's post about it, if You haven't already:

http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-930-data-file-shrink-does-not-affect-performance/

https://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/


Dilemma of Generating tables dynamically to improve performance Background: Think of an application that lets people make surveys with custom questions, In a particular case, interview families, An
  interviewer goes to House 1 and interviews two members Member 1 and
  Member 2. He asks questions like. What is this house address?, What is
  your name? and What is your age?. The questions are created by
  supervisors or survey experts

The way the data is stored for this application is as given below

The questions are saved in a table like below
+------------+--------------+----------+
| QUESTIONID | QUESTIONTEXT | SURVEYID |
+------------+--------------+----------+
|            |              |          |
| 1          | Name         | 311      |
|            |              |          |
| 2          | Gender       | 311      |
|            |              |          |
| 3          | Age          | 311      |
+------------+--------------+----------+
Results table (It has all the submissions)
+----------+----------+----------------+
| RESULTID | SURVEYID | SUBMITTED DATE |
+----------+----------+----------------+
|          |          |                |
| 90       | 311      | 11/12/2017     |
|          |          |                |
| 91       | 311      | 11/13/2017     |
+----------+----------+----------------+
Chunks table (where all the answers are stored)
+---------+----------+------------+----------+
| CHUNKID | RESULTID | QUESTIONID |  ANSWER  |
+---------+----------+------------+----------+
|         |          |            |          |
| 1001    | 90       | 1          | John Doe |
|         |          |            |          |
| 1002    | 90       | 2          | M        |
|         |          |            |          |
| 1003    | 90       | 3          | 18       |
|         |          |            |          |
| 1004    | 91       | 1          | Jane Doe |
|         |          |            |          |
| 1005    | 91       | 2          | F        |
|         |          |            |          |
| 1006    | 91       | 3          | 26       |
+---------+----------+------------+----------+

And the result after some Joins and Pivots will be like

| RESULTID | SUBMITTED DATE |   NAME   | GENDER | AGE |
+----------+----------------+----------+--------+-----+
|          |                |          |        |     |
| 90       | 11/12/2017     | John Doe | M      | 18  |
|          |                |          |        |     |
| 91       | 11/13/2017     | Jane Doe | F      | 26  |
+----------+----------------+----------+--------+-----+

Now the problem arises when we have really big surveys ( Some of them may go as far as 500 questions ). Normally the submissions exceed 20k. With this huge data, it takes forever to reach the final result.

So the solution I have in my mind is to generate a table dynamically when a survey is created.
 like

Survey_311

+----------+----------------+----------+-----+-----+
| RESULTID | SUBMITTED DATE |   Q_1    | Q_2 | Q_3 |
+----------+----------------+----------+-----+-----+
|          |                |          |     |     |
| 90       | 11/12/2017     | John Doe | M   | 18  |
|          |                |          |     |     |
| 91       | 11/13/2017     | Jane Doe | F   | 26  |
+----------+----------------+----------+-----+-----+

And to insert rows each time an answer is submitted. But as I am not really a DBA and do not have much experience with Databases. I would like to know if this approach is a bad practice in this scenario, Any solutions other than paging that will improve the performance is also welcomed

Hope you have store information about  House 1 and member detail somewhere.

Moreover purpose of Result Table is not clear to me.

Normally when you have slow running query  then we take following steps :

i) Denormalize database : So that it reduce table join.At the same time don't disturb normalize table.So I will put SubmitDate in Chunks table

ii) Optimize query :It depend upon your query.what need to be optimize ?

iii)Introducing proper indexing

iv) Paging : As you said.

v) Partition Table

These are few step  from the  the best practices list and follow database design pattern.

  Disadvantage of dynamic table.

i) I am not in favor of creating dynamic table like "Survey_311" because it sound like any design pattern.

ii) There will be several other kind of reports where you will at disadvantage like consolidating them will be problem.

iii) I see lot of dynamic Sql because of dynamic table.

iv) Database will be heavier very soonAlso there will be lot of indexes.

v) Maintainability : From both DBA and developer point of view.


Partitioning a Table Referenced by a Foreign Key I have one table:

CREATE TABLE [dbo].[entry]
(
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [EntryDate] [datetime] NOT NULL,
    [createddate] [datetime] NOT NULL,

    CONSTRAINT [PK_Entry_ID] PRIMARY KEY CLUSTERED
    (
        [ID] ASC
    )
    WITH
    (
        PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80
    ) ON [PRIMARY]
) ON [PRIMARY];

...where the primary key is referenced by another table.
To partition this table I have already done following steps :

 CREATE PARTITION FUNCTION EntryFunc (DATE)
   AS RANGE LEFT
   FOR VALUES ('2011-01-01')

  CREATE PARTITION SCHEME EntryScheme
    AS PARTITION EntryFunc
    TO ([FileGroup2], [PRIMARY])

The above 2 steps successfully completed,but when I am partitioning table I am not able to drop primary key clustered index because it is referenced by other table. My motive is to partition the table Entry according to created date.

From BOL (http://technet.microsoft.com/en-us/library/ms191160(v=sql.105).aspx)

Source and target tables must have the same FOREIGN KEY constraints. If the target table has any FOREIGN KEY constraints, the source table must have the same foreign keys defined on the corresponding columns, and these foreign keys must reference the same primary key as those of the target table. The foreign keys of the source table cannot be marked is_not_trusted (viewable in the sys.foreign_keys catalog view), unless the corresponding foreign key of the target table is also marked is_not_trusted.

So if you want to partition a table referenced by foreign key, then both tables need to have same FK.
Also for fast partitioning, below rules apply:
you cannot use fk's

No primary key/foreign keys defined if source table has the primary key. There can be no active primary key/foreign key relationship between the source table and the target table in which the source table holds the primary key.
No primary key/foreign keys defined if target table has the foreign key. There can be no active primary key/foreign key relationship between the source table and the target table in which the target table holds the foreign key.
No foreign key from another table can reference the source table. The source table cannot be referenced by a foreign key in another table.

So, you have to drop the constraint, partition the table and the recreate the costraint. Better to do as a transaction, so on success it can be committed or on failure it can br rolled back.
Step by step tutorial can be found at : http://www.mssqltips.com/sqlservertip/2888/how-to-partition-an-existing-sql-server-table/
And
http://www.confio.com/logicalread/sql-server-primary-key-vs-clustered-index,-part-3/#.Ui1lNGIpDFo


Can a MySQL trigger include a where clause I need to create a trigger that, any time a goat's sireRegNo column is updated, its sireName column is automatically updated to match.

I have a database that stores animal genealogy. Each row stores both the names and the registration #s of both its parents. The sireName field is readonly, but registrars may change sireRegno.

Create statement:

CREATE TABLE `table_name` (
    `id_goat` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(80) NOT NULL,
    `regNo` varchar(50) DEFAULT '',
    `sireName` varchar(80) DEFAULT NULL,
    `sireRegNo` varchar(50) DEFAULT '',
    PRIMARY KEY (`id_goat`,`name`),
    UNIQUE KEY `id_goat_UNIQUE` (`id_goat`),
    UNIQUE KEY `name_UNIQUE` (`name`),
    UNIQUE KEY `regNo_UNIQUE` (`regNo`)
) ENGINE=InnoDB AUTO_INCREMENT=188784 DEFAULT CHARSET=utf8;

example data:

table_name

name    |   regNo   |   sireName    | sireRegNo
----------------------------------------------
Old Man |   2048    |   Methusela   |   9999
Daddy   |   1234    |   Grandpa     |   4321
Kid     |   9191    |   Daddy       |   1234

DISCLAIMER: Yes, I know this is not normalized. I've tried JOINing on the regno, and found it took forever. Reason being, I think, we have a search page that, using DataTables, loads every row in the DB (only the public data from each, though). It doesn't display it all at once, but it loads it, so it can use search input to filter through them client side. So we were basically trying to JOIN the entire table to itself, and it took ~30 seconds per page load.

I know there's probably something I did wrong somehow, and there's a way to do this "properly" but that's not what I'm asking here. Even so, answers to that end are welcome.

Kid is now a teenager, and insists that someone put him in the database wrong, and that his real dad is Old Man, regno 2048.

My question then - can I make a trigger so that, when the registrar changes Kid's sireRegNo to 2048, Kid's sireName column gets changed to "Old Man"?

TRIGGER VERSION (least comfortable with this, least certain of what this looks like to anybody else)

Here's my attempt at an "actual" trigger, just in case there's some significant difference between triggers and stored procedures. Modified from mid way down this page

mysql> delimiter //
mysql> CREATE TRIGGER update_sire_name BEFORE UPDATE ON table_name
    -> FOR EACH ROW #I trust this means "each row that is modified"...
    -> BEGIN
    ->     IF NEW.sireRegNo = table_name.regNo THEN #searches regNo field of every row in DB for the new value stored in sireRegNo
    ->         SET NEW.sireName = table_name.name
    ->     END IF;
    -> END;//
mysql> delimiter ;

STORED PROCEDURE VERSION (more comfortable, possibly farther from what I need?)

modified from this page

DELIMITER //
CREATE PROCEDURE sp3
(IN reg CHAR(50))
BEGIN
    SELECT name FROM goatdata
    WHERE regno = NEW.reg;
    SET NEW.sireName = 'result of above SELECT statement'
END //
DELIMITER ;

I've not dealt with triggers or stored procedures before, so that looks weird to me and is probably flat wrong in multiple ways. Also, I realize this is for a stored procedure, but as I've been searching, I saw somewhere say triggers ARE stored procedures, so I'm hoping this is clear, and maybe even that I could just replace "procedure" with "trigger"? Maybe? It certainly feels a lot closer to what I'm trying to do, in any case.

REGULAR MYSQL (clearest to me, but obviously not remotely what I need)

SELECT name, regno FROM table_name WHERE regno = 'correct sire regno';
UPDATE table_name SET sireName = 'correct sire name' WHERE regno = 'kid regno'

You're almost there. Your trigger should probably look like this:

CREATE TRIGGER update_sire_name before UPDATE ON table_name
for each row

    set new.sireName =
    (select name from table_name where regNo = new.sireRegNo)

dbfiddle

1 Yes, there can be a where clause in a trigger.

2 Consider using numeric data types (e.g. INTEGER) where values are clearly numeric (e.g. regNo)


Adding Security to a legacy DB with Schema I am working on a DB where almost every technical user has full read / write permissions on all tables. We are working towards removing permissions, gradually, from certain users, and I had hoped that implementation of some new code on a new schema would be able to be strictly implemented from day 1.

However, I have realised today that most of our users have been granted db_datareader and db_datawriter role membership, which means that they all have access to this new schema by default. Therefore, I want to find a way forward that gets this schema secure without creating a load of additional work.

My idea is:

Create a new 'generalReader' role which has SELECT privilege on our huge dbo schema, and a couple of other schema we have (but are not being changed right now);
Create a new 'generalWriter' role with UPDATE, INSERT, DELETE privileges on the dbo and existing schema;
Drop the db_* roles from users, and replace them with the new generalReader and generalWriter roles;
Add new roles to control access to the new schema - I think most people could be allowed to SELECT from it, where only a few should be granted INSERT privileges. (e.g. I would create an LoanAccountReader and LoanAccountInserter roles)

Does this sound like a broadly appropriate plan to move forward - or am I missing some tricks or some security issues?

Before coming up with the above, I did some experimentation with roles and could create roles DENYing write access (that overrides the database writer role), but could not then re-enable that permission with a 'writer' role.

That sounds reasonable (from the information provided).

Also, one note - an explicit DENY at any level of the security heirarchy will always override any GRANT. This is different to a REVOKE, which nullifies a GRANT but doesn't do the "Haha, no, you're not getting that. Ever." effect of a DENY. (So you could have a GRANT SELECT at the database level, a REVOKE SELECT at the schema level, and a GRANT SELECT again at the table level - that'd give you select on that one table, and everything else in the database not in that schema (everything else in the schema aside from that table comes under the REVOKE). If you replaced that REVOKE SELECT with a DENY SELECT then you'd lose select access to the entire schema, no exceptions*.)

gbn's answer here is a wonderfully concise intro to the various permissions you can apply to a single database object (note that some of them don't necessarily apply to all objects, the most obvious one being REFERENCES). For an overview of permissions from the domain level down to the individual objects within a schema, see here.

(* I lied, there is one exception: Ownership chaining. Permissions are not checked in the case of an ownership chain. In the simplest case, if Bob grants Jane select access on a view that he owns, and that view only references tables that Bob owns, then Jane's permissions on those tables are not checked, because the permissions are chained from the view to the underlying objects (nb: ownership chaining can get a lot more complex than that). Ownership chaining is incredibly powerful and incredibly useful, but it also has an incredible power to bite you in the ass if you're not careful. If you're running an environment where precisely defined permissions are critical, then always test your permissions before you deploy them.)

(* I lied again, there's one more exception - db_owner [and users aliased to dbo] and sa_role (which is effectively db_owner on every database, plus server-level stuff). Permissions are never checked for these, it's assumed they have full rights to everything.)

Also, as gbn noted, if you're going to use schemas, then you may as well go the whole hog and permission the schemas rather than individual objects within a schema (the primary use case for schemas is for grouping objects with the same permissions together so you can manage permissions on one thing - the schema - rather than a whole bunch of objects).


One database or multiple referencing one? Database design: one database or multiple databases, which is best?

We have a database which has about a 100 or so tables, accessed by about five different applications. Five different applications have their own set of tables but also need to access about 20 master tables (used by all our systems: users, accounts, contacts, shops, etc). Now we are going to have another 15 or so applications with their own set of tables but also again need access to get information from the master tables. So before we get set up what do you think is the best schema and database set up. i.e. one database with all applications including the master ones. Each application has its own database with the master records staying in master?

Anyone's thoughts here would be much appreciated. I think I am leaning towards separate databases so they can be managed better, and performance should be better (maybe not?).

If I go with separate are their any implications: setting up references wont be possible, performance joining databases for selects, updates, asp.net needs 2 connections strings (is that even possible with say entity framework database first or LINQ DBML).

I would lean toward multiple databases. Primarily because you can set them up for different recovery, SLA, maintenance, deployment, changes, etc. And also this allows you to move certain databases to faster or larger I/O on different drives without disrupting everyone.

For the master tables that are used by all the applications, no I don't think you should have multiple copies of these. Your applications can query different databases and/or have different connection strings for different tasks (best separated by a middle tier of some sort in most cases). If the app-specific code within a single database needs to have some kind of reference to them, you can use synonyms or three-part names. This is easy to extend if you later need to split out not just across databases but across multiple instances / servers (just add a linked server and make it a four-part name).

Only if the link between these servers is notoriously slow or otherwise unreliable would I consider making replicas of the master tables. I talked about how to do that here and here, even though I don't explicitly cover moving data across servers.


AlwaysOn High Availability - not always kicking in We currently have a set up of two nodes A and B with AlwaysOn High Availability.
When restarting node A, we can connect with the listener name 'xyz' to our SQL Server instance. In this scenario, B will shift to primary and A to secondary. Everything is transparent, and connecting to 'xyz' works fine.

Now I've tried to test the scenario by just switching off the SQL Server service on the primary node, rather than restarting the whole node. (Side note: as a consequence, the SQL Server Agent stops as well)

When doing so, I am NOT able to connect to 'xyz' (listener name) via the management studio. I can only connect to 'B' directly. When automatic failover tries to take place, I am left with the following:

Is this normal? Does failing over occur by simply checking the node's status rather than the SQL service of that node?

A deeper dive into the MS's documentation helped us discover the Flexible Automatic Failover Policy (https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/flexible-automatic-failover-policy-availability-group)

A setting is available to adjust the scenario when the failover should kick in. The default setting is 3.

ALTER AVAILABILITY GROUP AG1 SET (FAILURE_CONDITION_LEVEL = 5);

enabled us to cater for more scenarious, including turning off manually the service. Level 5 encapsulates scenarios/conditions of lesser level values.


SSIS v/s Replication v/s logshipping I have 2 SQL server instances, on different servers.
One is Production instance and gets lots of reads and writes daily.
Another one is for external applications to use a copy of few of the tables from production sql instance.
At the moment we have a SSIS job which does compare and update once in a day from production sql to the external sql server.
We want to increase the frequency of the data being replicated, so that it is more or less synchronous on both servers.
What would be a better approach? increasing the frequency of the SSIS package or creating replication or using log shipping?
Any answer is really appreciated.
Thanks.

As always, it depends on a lot of factors, but I'll answer like this:

If the SSIS package takes only the deltas from the last run and performs well, I'd keep using the SSIS, since its something that is already working and I assume you are familiar with.

Log shipping and replication will work, but they have drawbacks. For instance, with log shipping you will have to disconnect all of your users when restoring the log backups on the target server.


How can I categorize entites by their age in an entity relational diagram? I am designing an entity relational diagram and I need to categorize entities in groups by their age.
What I have is an entity of Person and some personal attributes about it. But I want to have information about people based on their age. For example I want to have have groups for people of age 1-10, 11-20, 21-30 and so on.
Is it possible to design something like this in the entity relation diagram, and if yes how, or is it a bad technique and I should implement it in later stages of the development?

This would be a case for either filtering the data during your query (WHERE clause) or creating a view that already filters it out. In normal practice, a person table is a person table, and unless the fundamental fields are different (like the difference between customers and employees), then all person records go into the same table, and the diagrams reflect that.
When drawing things up, start with just how the data is related across the database, then start worrying about sorting, grouping, and filtering based on the data for when you are planning out stored procedures and views.


ORA-01555: snapshot too old: rollback segment number with name "" too small This error appears when I am trying to move LOB segment from one tablespace to another one.

Also during exporting the same error appears.

What can be a problem?

Per the Oracle Docs and MetaLink support note 66431.1, the standard undo mechanism is not used by LOB segments, which have their own page versioning (controlled via PCTVERSION and the LOB_retention_clause).

"Snapshot too old" generally means that a transactionally-consistent version of your data could not be hold long enough because of lack of space. It might be that your PCTVERSION setting is too strict and effectively causes any new update to invalidate the consistent version(s).


MySQL upgrade via mysqldump We want to upgrade to MySQL 8.0 from MySQL 5.6 but upgrade manual seems to cumbersome and risky to follow doing an in-place upgrade so we have decided to do it via MySQL Workbench export (mysqldump) and import our exported database tables into a fresh new MySQL 8.0 installation
Should it be successful or should we do something else in the middle for it to work?

Step 1 :

It's a best practise that first to upgrade MySQL 5.6 to MySQL 5.7, then from MySQL 5.7 to MySQL 8.0.

Step 2 :

Please go through MySQL 8.0 changelog to take changes in your database for deprecated & removed features otherwise upgrade end up in so many errors
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/

Step 3 :

Before, upgrading to MySQL 8.0, run upgrade checker trail using mysqlshell.
mysqlsh mysql_user@192.168.X.X:3306 -e "util.checkForServerUpgrade();" > upgrade_alert.txt

Step 4 :

Final step for upgrade should be done logical only to avoid errors in inplace upgrade.


Normalising Customers and Employers with a people table At what normalisation stage is it recommend to split say Customers and Employees into a People table and then link to the unique attributes of Customers and Employees? I can't seem to find the Functional Dependency to justify the change. This makes sense in OOP where you use inheritance but I don’t understand why it is done in database schemas.

This is an example of what I mean, possibly with more unique attributes:

Customers: Cid, FName, LName, AddId, CustType
Employees: Eid, FName, LName, AddId, EmpType

vs

People   : FName, LName, AddId
Customers: PeopleId, CustType
Employers: PeopleId, EmpType

The current answers explain why it is recommended to move data to a People table; however, at what normalisation stage does it become a FD to justify creating the extra table?

The design of your tables is not, as you noted, an OOP inheritance.  The underlying model is simpler:

Do not duplicate your data!

The more data you have, the more important this is because:

Nobody will type the duplicate data exactly the same in all cases.  (Names, Addresses, etc.)
If you have entered similar data in two different tables, you will regularly need to do some Sherlocking to figure out who is whom.

Having said that, people identification (particularly customers) is difficult to get correct and consistent no matter how diligent you try to be.  Likely you will unwittingly enter some people a second or third time.  But a People table gives you one place to examine, which can be a bit of a help.

Creating a Persons table would be 2nd Normal Form.

I do not really understand what issue you are struggling with when you ask: when it "becomes an FD to justify creating the extra table". You are basically making a choice between:

Choosing to just cope with variant and duplicated data.
Set up your data to avoid duplicated data as much as possible.

I avoid duplicating data as much as possible, because it gives me fewer headaches over time.


Sql Server 2014 - BPE I was wondering if there is any way to determine whether is convenient or not to enable Buffer Pool Extension at one of the servers I am administering.

I was thinking about some way to measure average memory usage, memory usage peaks and so on, in order to determine if available RAM is enough of not.

Thanks in advance.

Before concluding whether you should or should not enable BPE, its good to analyse the current usage of memory on the SQL server.

You can do this with help of system health session, various DMV's and using perfmon counters:

Using system Health session you can refer here
Various DMV's you can consider for monitoring memory usage:

select * from sys.dm_os_memory_clerks

select * from sys.dm_os_memory_objects
select * from sys.dm_os_memory_pools
select * from sys.dm_os_memory_nodes
select * from sys.dm_os_memory_cache_entries
select * from sys.dm_os_memory_cache_hash_tables

Refer here on how to use above dmv's and analyzing for memory usage.

For perfmon you can start analyzing with below counters:

Memory – Available MBytes

Paging File – % Usage

Physical Disk – Avg. Disk sec/Read

Physical Disk – Avg. Disk sec/Write

Physical Disk – Disk Reads/sec

Physical Disk – Disk Writes/sec

Processor – % Processor Time

SQLServer: General Statistics – User Connections

SQLServer: Memory Manager – Memory Grants Pending

SQLServer: SQL Statistics – Batch Requests/sec

SQLServer: SQL Statistics – Compilations/sec

SQLServer: SQL Statistics – Recompilations/sec

On how to use them , please refer here.

Now, once you have analyse the proper memory usage along with how much stays out there on Buffer pool as well and how much you think it lacks, can help you in deciding should you go for enabling BPE or not.

Note: In addition to above , make sure the MAX server memory settings are properly configured. Adding below info in case you want to go with BPE

  Buffer Pool Extensions are available for the SQL Server 2014
  Enterprise, Business Intelligence and Standard x64 editions. The size
  of the buffer pool extension can be up to 32 times the value of
  max_server_memory for the SQL Server Enterprise edition and up to 4
  times the max_server_memory value for Business Intelligence and
  Standard editions. Microsoft recommends a ratio between the size of
  the physical memory (max_server_memory) and the size of the buffer
  pool extension of 1:16 or less. A good starting point is 1:4.

A suggested read on using BPE along with its Best practices  Buffer Pool Extension