Default user group in phpMyAdmin I am setting up a multi user environment for shared hosting. Since phpMyAdmin 4.1.0 it's possible to create user groups, add users to them and define which functions they may use.

Now this is a great feature, but it would be better if new MySQL users get a default user group when they login to phpMyAdmin for the first time. I haven't seen this setting in phpma (so far), so I wanted to ask you if you know whether this is possible in phpma or that I maybe can set a default user group in the php files of phpma?

Thanks in advance!

I came up with my own solution (but if you think this can be done better, please post your option):

In the file /libraries/Menu.class.php I changed ...

        if (isset($cfgRelation['menuswork']) && $cfgRelation['menuswork']) {

... with

        $groupTable = PMA_Util::backquote($GLOBALS['cfg']['Server']['pmadb']) . "." . PMA_Util::backquote($GLOBALS['cfg']['Server']['usergroups']);
        $userTable = PMA_Util::backquote($GLOBALS['cfg']['Server']['pmadb']) . "." . PMA_Util::backquote($GLOBALS['cfg']['Server']['users']);

        if (!empty($GLOBALS['cfg']['Server']['users']) && !empty($GLOBALS['cfg']['Server']['usergroups'])) {
            $defaultUserGroup = 'User';
            $all_mysql_users = PMA_queryAsControlUser("SELECT User FROM mysql.user GROUP BY User", false);
            $all_phpma_users = PMA_queryAsControlUser("SELECT username FROM " . $userTable . " WHERE `usergroup` != ''", false);

            if ($all_mysql_users) {
                $default_user_group = PMA_queryAsControlUser("SELECT `usergroup` FROM " . $groupTable . " WHERE `usergroup` LIKE '" . $defaultUserGroup . "' LIMIT 1", false);

                if ($default_user_group) {
                    $default_user_group = $GLOBALS['dbi']->fetchAssoc($default_user_group);
                    $allPhpmaUsers = array();

                    while ($phpma_user = $GLOBALS['dbi']->fetchAssoc($all_phpma_users))
                        $allPhpmaUsers[] = $phpma_user['username'];

                    while ($mysql_user = $GLOBALS['dbi']->fetchAssoc($all_mysql_users)) {
                        if (!in_array($mysql_user['User'], $allPhpmaUsers))
                            PMA_setUserGroup($mysql_user['User'], $default_user_group['usergroup']);
                    }
                }
            }
        }

        if (isset($cfgRelation['menuswork']) && $cfgRelation['menuswork']) {

And then I replaced ...

        if (isset($cfgRelation['menuswork']) && $cfgRelation['menuswork']) {
            $groupTable = PMA_Util::backquote($GLOBALS['cfg']['Server']['pmadb'])
                . "."
                . PMA_Util::backquote($GLOBALS['cfg']['Server']['usergroups']);
            $userTable = PMA_Util::backquote($GLOBALS['cfg']['Server']['pmadb'])
                . "." . PMA_Util::backquote($GLOBALS['cfg']['Server']['users']);

            $sql_query = "SELECT `tab` FROM " . $groupTable

... with

        if (isset($cfgRelation['menuswork']) && $cfgRelation['menuswork']) {
            $sql_query = "SELECT `tab` FROM " . $groupTable

Then I created an user group "Admin" with all rights, put the root user into this Admin group. At last I created an user group "User" with less rights, refreshed the page and all other users had this group. It is important to do this in this order, because otherwise the root user will also get the "User" rights. When this group can't edit user settings, you can't change it back from within phpMyAdmin.


If shared memory, named pipes, TCP/IP network protocols are all enabled which has priority? If you have all 3 protocols enabled in SQL Server 2016, which protocols are prioritized on the server instance?

Couldn't find any specific documentation on this and don't really understand how it works that well.

If no protocol is specified , the connection will try based on the order. This is specified in the Enabling a Protocol section in Choosing a network protocol


Should I split my tables into different tables when all of the data is dependant on the primary key? For my assignment I have to make a database for my licensing application. I have 12 columns and one column is for the license key. All other columns are data that is related to the license key. The license key is always different. I'm getting told that it's better to split the table into more tables to remove redundant data.
The table is as follows:

  license_code
  product_amount
  product_limit
  product_name
  start_date
  end_date
  first_registration_date
  most_recent_check_date
  registration_end_date
  hardware_id
  IP
  status

Currently I have it split into this:

Table: License

  license_code*
  product_id*
  start_date
  end_date
  first_registration_date
  most_recent_check_date
  registration_end_date
  product_amount
  product_limit
  hardware_id
  IP
  status

Table: Product

  product_id*
  product_name

I'm really thinking that it's unnecessary to split it because all the data is dependant on the license_code column anyway. Can anyone help me out? Thanks.

EDIT: explaination of how it's used

A license key/code is basically a unique string. To use a product (software program), a license key has to be used. A product can be activated with multiple license keys, person 1 can use code X to activate product Y, person 2 can use code Z to activate product Y. So a license key can activate multiple instances of a product but it can only be used for a single product.

I hope I'm clearer with this explaination.

I think we are short on a little bit of information such as what exactly the license is for and how it is to be used. For example, do all products have exactly one license? Can licenses contain multiple products? As the term license is used, I would imagine that each individual would have their own license to use the product (thus one to many relationship). With that scenario, I will assume that a license is only valid for one product and that several licenses can reference a single a product (a one-to-many relationship).

So to satisfy that requirement, we will need two tables. One for the products, and one for the licenses. The product table would contain all product information such as:

Products
-----------
ProductID
Name,
Description,
Price,
(etc)

Licenses
--------
LicenseID,
ProductID,
StartDate,
EndDate,
Amount, -- I believe this is to represent how many individual licenses of this one is used
Limit,  -- And this is to represent how many people can use this current license
(etc)

With this, we have a few benefits. Now, we can license out an individual product to multiple people (again, I am assuming the product is something like software) and we can now change licenses with out affecting the product and if we need to make changes to the product, all the licenses will automatically be updated. A good example of this is the changing of a product description. If products were stored with the licenses, we would have to change all the licenses that used that same product. That's not good and prone to having inconsistent data.

If I am off base, feel free to let me know. As you can see, I made quite a few assumptions.


Postgres Listen/Notify As Message Queue Is there any way to use Postgres Listen/Notify feature to deliver a message to a channel and have only one listener consume this message?

The purpose for this is that I have multiple 'worker' apps all listening to the same Postgres channel. But I only want the work done once per message received through the notification channel.

If Listen/Notify is not the correct feature in Postgres, is there a seperate feature I should be using?

Ideally I would like to do this without using any additional extensions.

According to PostgreSQL documentation about NOTIFY:

  The NOTIFY command sends a notification event together with an optional "payload" string to each client application that has previously executed LISTEN channel for the specified channel name in the current database. Notifications are visible to all users.

(emphasis mine)

This means you cannot do what you want just with LISTEN/NOTIFY. However, you can have both a table to store queued messages, LISTEN/NOTIFY to notify external applications that "there are new things in the message queue", and use some extra logic from these external applications so that only one consumes the message.

The strategy depicted in the article What is SKIP LOCKED for in PostgreSQL 9.5? is probably the safest/easiest way to implement a message queue within PostgreSQL. Pay special attention to the "How SKIP LOCKED helps" part. Read also carefully one of their caveats:

  A queue implemented in the RDBMS will never match the performance of a fast dedicated queueing system, even one that makes the same atomicity and durability guarantees as PostgreSQL. Using SKIP LOCKED is better than existing in-database approaches, but you’ll still go faster using a dedicated and highly optimised external queueing engine.

This is specially important if the queue volume is high.


one to one relationship This is my database structure:

Users {user_id, username}

Profiles {profile_id, user_id, user_views}

My question is: If I do it the way mentioned above, should I to set user_id as primary key or unique key? and what about profile_id?

The right way is like the following:

Users {user_id (PK), username (Unique)}

Profiles {profile_id(PK), user_id FK, user_views}

in the given structure, we are making user_id as PK to link between tables, and making the username as Unique to avoid duplication, some people will do the username as PK, but this will make a problem if you want to update the username in any related detailed table linked to that username, in general, you should make sure that your table has only the correct data, without duplication, easy to update, normalized and easy to select with the given PKs or FKs
...

Please read more info from the following reference:
http://dev.mysql.com/doc/refman/5.0/en/create-table-foreign-keys.html


Importing a large column (8000 chars) from Excel using SSIS I'm trying to set up a regular import of an excel spreadsheet that we get from a vendor.  I'm using SQL 2008 R2 SSIS to import it into a table.  The problem connection manager is an OLE DB connection to the spreadsheet.  The spreadsheet is Excel 2005.  The database connection manager is using Native OLE DB\SQL Native Client.

The problem I'm getting is that the OLE DB Source keeps setting a couple of the excel columns to DT_WSTR with a 255 length.  I have 2 columns however that are 4000 and 8000 characters respectively.  I've gone into the Advanced Editor/Input and Output Properties tab for the OLE DB Source and changed the DataType/Length in the External Columns list and the Output Columns list.  Unfortunately when I leave the Advanced Editor an error appears in the error list.

Validation error. Data Flow Task: Data Flow Task: The output column "ColumnName" (226)
on the error output has properties that do not match the properties of its
corresponding data source column.

When I click on the Source again I get an option to "fix" the errors automatically.  I then select "yes" and "ok".  The error is now gone but when I go back to the External Columns the datatype/length settings are back to the original DT_WSTR/255.

I could manually change the spreadsheet into a delimited file to avoid the problem but would rather not add a manual step into the process.  Does anyone know of a way to get an Excel source to allow for a long column?

We finally resolved the issue.  It turns out that SSIS calculates the length based on the first handful of rows in the excel file.  When we moved the rows with the longer data to the top the columns changed to unicode text (allowing for the extra length).


postgres backup with raid1 I've found nothing relevant on the internet about backing up postgres DB with raid1. So here's my question

My sys admin thinks that backing up the raid1 disk ,on which a Postgres database is sitting, is adequate as a Postgres backup. He would restored it when needed.

To my understanding, backup needs to have the database stopped, at least for some commercial database like Oracle.

Is it possible to backup the database without having it stopped with a raid1 scenario?

Thanks

Tell your sys admin to search for "RAID is not a backup", whether it will provide a usable backup is irrelevant, since it doesn't protect you from database corruption, human error, and many other things.


Merging two rows having different value I have a Sample table:

Region    | OpenServices | DFC
Karaci    | 14           | 4
Lahore    | 13           | 3
Islamabad | 10           | 4

This is Second Table

Region    | OpenServices | DFC
Karaci    | 14           | 4
Lahore    | 10           | 3
Islamabad | 10           | 4

I want my Final table to look like

Region    | OpenServices | DFC
Karaci    | 14           | 4
Lahore    | 13 , 10      | 3
Islamabad | 10           | 4

I'll assume LUW and a recent one. There you can use the LISTAGG aggregate function:

with Sample(region, openservices, dfc) as (
    values ('Karaci','14','4')
         , ('Lahore','13','3')
         , ('Islamabad','10','4')
), Second (region, openservices, dfc) as (
    values ('Karaci','14','4')
         , ('Lahore','10','3')
         , ('Islamabad','10','4')
)
select region, substr(listagg(openservices,','),1,10), dfc
from (
    select region, openservices, dfc
    from sample
    union
    select region, openservices, dfc
    from second
)
group by region, dfc

The union will eliminate duplicate rows and the we can use listagg to concatenate openservices for the group region, dfc. Here I assumed that dfc is functionally dependent of region (according to you sample data). Since the return type of LISTAGG is a rather long string, I trimmed it down using substr.

Result:

REGION    2          DFC
--------- ---------- ---
Islamabad 10         4
Karaci    14         4
Lahore    10,13      3


Copy from file: ERROR: invalid input syntax for integer: "\N" My table column defined intcol integer; no NOT NULL constraint. In the load  file most values are integers, but a few are null values labeled with the '\N' default as suggested by the copy manual. Can the copy command convert '\N' to null values in the table?  I also tried with null 'NA'. Got similar error message.

ERROR:  invalid input syntax for integer: "\N"
CONTEXT:  COPY table_name, line 111, column position3: "\N"

Any trick?

Use the NULL parameter. From the documentation:

  NULL

  Specifies the string that represents a null value. The default is \N (backslash-N) in text format, and an unquoted empty string in CSV format.

Example

copy my_table from '/my/csv_file.csv' (format csv, null '\N');


Help with an entity-relationship diagram re Manager entity type and its relationships In the below ER diagram:

A manager can have many Users but a User can only have 1 Manager
A Manager must be able to assign multiple users to multiple courses

Are the manager relationships correct?

One issue with this design is allowing for a user to have more than one course at a time. A way to work this is to have a join table between the User table and the Courses table, with only three columns: Join_id, Course_id, User_id. The relationship between users and managers looks good to me, with many users being assigned only one manager each.
Regarding one of the comments; adding to the Course_Assigned table a few fields that include a session_id, start_date, and end_date could be used to directly connect a user to a particular session.


MySQL Server Config, Small Timeframe, Extreme Usage-Scenario I want to configure a MySQL server for the following scenario.

A large amount of users will need to sign up for a university course selecting individual "timeslots". The signup page will open at a specific time when around 200 users will try to access the page.  In fact they are probably refreshing the page like crazy a few minutes before that.

There will be about 150 timeslots (rows) available that can be selected by the users. A web application will handle a transaction for each request trying to LOCK IN SHARE MODE a row ("timeslot") to check if it is still available and set it to a "booked" state if it is available. My database is using the InnoDB engine.

The the scenario is a lot of users doing a vast amount of SEELCTs and subsequently within a few seconds a vast amount of UPDATEs. Thinking of a basic Ubuntu MySQL server, what configuration options would you suggest to take a look at to make this "event" happen as smooth as possible. Things I want to avoid for example is the server to be overly durable writing changes to the hard drive during this period.

-- Edit

Just for information: The accepted answer below shows how this can be achieved without any LOCKING and explains very well how the MySQL will behave when trying the LOCKING approach.

However we managed to go through the scenario by changing the commit behavior of MySQL to only write to disk once every second and allowed 2GB of memory usage. We also had 8 thin instances running a rails application below a 4 worker nginx (basically default setup with SSL). We ran it on a desktop i5 system with 12GB of memory (which was much too much) and the server hardly reached a load of 1 (out of 4). So, if you are running a similar scenario: don't panic.

Hopefully, I'm missing or misinterpreting something, because I don't see an explicit SELECT ... LOCK IN SHARE MODE as a viable way to approach this.

Using SELECT ... LOCK IN SHARE MODE explicitly sets an IS lock ("intention shared") on the row(s) matched, but other threads can also set IS locks on the same row(s) at the same time, with no feedback from the server indicating whether anyone else already has such a lock on the row.

So, let's say two threads set an IS lock on the row, then both of them issue an UPDATE.

One unlucky thread sees this:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

It's a deadlock because two threads are asking for mutually-exclusive things.  Thread #1 is asking for its IS lock to be escalated to an X (exclusive) lock so it can update the row... but this can't be permitted because thread #2 already has an IS lock, which prevents #1 from getting its X lock ... and, thread #2 is also trying to escalate to an X lock, which is being blocked by thread #1's IS lock.

You might assume that the first thread to request the lock would be the one that gets to update the row, but that's not the case, either.  My testing shows that in this case the winning thread is the one whose UPDATE is noticed first, not the one that gets an IS lock first.  This is sensible, since that thread has done slightly more work than the other, and, after all, both threads had previously only expressed the intention to lock the row exclusively.

But... with all of that said, it seems to me that there's a much simpler approach to this, that would be far easier on your server.

If visitors to your site are trying to claim one specific timeslot, identified by a unique timeslot_id, then I'm not sure why this approach wouldn't work:

START TRANSACTION;
UPDATE timeslot_guest_map SET guest_id = ? WHERE timeslot_id = ? AND guest_id IS NULL;
SELECT ROW_COUNT();
COMMIT;

If ROW_COUNT() returns '1' then you got the time slot.  If ROW_COUNT() returns 0 then sorry, someone else got there first.  In this setup, InnoDB implicitly handles the row locking, and thread #2 will block until thread #1 commits, at which time guest_id will no longer be null on that row so it will not be updated.

On the other hand, if the "timeslots" are actually identical, such as might be the case when 150 "general admission" seats were available in an auditorium, the approach could be like this:

START TRANSACTION;
UPDATE timeslot SET user_id = ? WHERE user_id IS NULL LIMIT 1;
SELECT ROW_COUNT();
COMMIT;

Here, thread #1 implicitly gets an X lock on the first available row, updates it, and commits.  Thread #2 has to wait for an implicit IS lock on the first row, because of the X held by thread #1... but when thread #1 commits, thread #2 gets its IS lock so it can examine that row, and finds out that the row no longer matches... so the next available row gets matched, locked, and updated, if there is one.

The workload involved in either of these scenarios sounds to me like it should be trivial for MySQL, assuming you take care to properly commit (or rollback) your transactions.  Otherwise, you'll end up with a backlog of threads waiting on locks held by abandoned transactions.

As @Valor suggested, you're far more likely to have problems providing your web server with the resources it will need in order to handle the concurrent connections... as was likely the case here and here, to cite a couple of recent examples of MySQL being the victim of memory exhaustion on the server, not the perpetrator.


How does MySQL decide what partitions to load into memory? I have a server where the MySQL indexes won't fit into memory (2TB total index, 512GB RAM on machine), and am wondering how MySQL decides what parts to put into memory and what not.

The relevant tables are range partitioned on ID, and I'm told (but can't find anything proving this) that the last 3 months is available in memory.

I'm hoping there's a more substantial answer available somewhere.

InnoDB's "buffer_pool" contains blocks that are kept there in (roughly) a "least recently used" order.  That is, when you touch a block for either reading or writing, it is pulled into the buffer_pool and marked as "most recently used".  If there was not room for this block, then the "least recently used" block was first removed from the buffer_pool.

PARTITIONing has nothing to do with the caching scheme.  In particular, partitioning by the PRIMARY KEY is almost always useless, and almost always redundant with letting the caching (described above) happen.

If you would like to discuss performance improvements, please provide SHOW CREATE TABLE and the slow query.

Using PARTITION BY RANGE(TO_DAYS(...)) is an excellent use of partitioning, but only because it makes dropping "old" data very efficient.  Note: I did not say BY RANGE(id).

Even if the math is correct, you may or may not have "the last 3 months" cached in RAM -- if someone scanned stuff from 4 months ago, someblocks would have to be bump to allow for that scan.  Then the 4-month-old bllcks would clutter the buffer_pool until they get bumped out.

Update

Since you have a "mission critical" system that must accept writes all the time, and have no PRIMARY KEY, you need some drastic action.  Here's what I would do.

I would back up into the code.  If there is not already code between the "users" and the database, I would start by building such a layer and force the "users" to call an API that say "write ... to the database".

To start with, the API would simply do an INSERT like the original code.  This has minimal impact.

Then I would prepare new code, wherein the API has more complex code behind it.  In particular, it would be writing to the existing table and the new table (with PRIMARY KEY, new index(es), changed datatypes, PARTITIONing, etc).

Meanwhile, another script would be copying, in chunks, the existing data from the existing table to the new table.  (This is part of what pt-online-schema-change does, but we will take some liberties.)

I suggest chunk size of 1000 rows, each chunk as a its own 'transaction' (assuming you are a using InnoDB).  The chunking should follow some index, whichever one closes to being unique.  (An yes/no column would be poor because you could not get more than 2 chunks.)

When all finished, you will have two copies of the data; you can test before dropping the old table.

Reads can proceed unchanged during the process because the old table remains intact.

Some blogs with tips:

Altering a huge table
Chunking
Partitioning
Don't use OFFSET


Activity Monitor showing long duration for WAITFOR RECEIVE In Activity Monitor, the following query is causing high CPU and 281ms average duration.

WAITFOR (
RECEIVE conversation_handle
    , service_contract_name
    , message_type_name
    , message_body
FROM ExternalMailQueue
INTO @msgs
)
, TIMEOUT @rec_timeout

Any idea or steps how to resolve this?

This is a red herring. WAITFOR (RECEIVE...) is by definition supposed to ... wait for messages! Therefore 281 seconds elapsed simply means that for 281 seconds there was no message to receive. In this case the application is the Database Mail external sending process that is sitting idle waiting for messages to be enqueued (ie. waits for sp_send_db_mail to be called). There is nothing to 'resolve' here.


Query to Return Missing Data I have two tables with almost exact structure, but one table always seems to not be updated appropriately.  To show the issue, I have created two test tables that illustrate.

Declare @Test1 Table (card4 int, d1 date, amt float)
Declare @Test2 Table (card4 int, d1 date, amt float)

Insert Into @Test1 VALUES
('4356', '01/01/2015', '12.24'),
('4356', '01/01/2015', '44.12'),
('1111', '01/01/2015', '55.10'),
('2222', '01/01/2015', '23.12')
Insert Into @Test2 VALUES
('4356', '01/01/2015', '12.24'),
('4356', '01/02/2015', '11.11'),
 ('4356', '01/01/2015', '44.12')

Select * from @Test1
Select * from @Test2

From the sample data @Test1 is missing the amt 11.11 and I am not sure how to write a query that will pull all values from @Test1 and any values that are missing from @Test2

This is the data set that I want returned

  card4    d1          amt
      1111    1/1/2015    55.1
      2222    1/1/2015    23.12
      4356    1/1/2015    12.24
      4356    1/1/2015    44.12
      4356    1/2/2015    11.11

What would syntax be to achieve this?

You can use SQL's EXCEPT clause.
https://msdn.microsoft.com/en-us/library/ms188055.aspx

Values in the first table (the left query) that are not in the second (the right query) will be shown.  You can change the table order to achieve the opposite.

select * from @Test1
except
select * from @Test2

card4       d1         amt
----------- ---------- ----------------------
1111        2015-01-01 55.1
2222        2015-01-01 23.12


Influx - Help to choose in between Tag and Field I have the following columns:

'datetime', - which gives the time when the sensor values are recorded.

'machineID', - which gives the machine ID of the machine from where the sensor values are recorded.

'volt', 'rotate', 'pressure', 'vibration' - 4 different sensor values measured for a particular Machine at a time.

Please let me know if I need to name datetime and MachineID coulumns under tag or fields.

Thank you in advance.

You can take a call considering below two scenarios.

If you need to run a GROUPBY clause on a particular column/field, then you need to create it as Tag, otherwise you won't be able to use GROUPBY clause.
Indexing: Fields cannot be indexed in influxdb. Tag are automatically indexed.

Also, influx by default creates a 'time' tag, so it's better while inserting you pass the value to 'time' instead of adding an extra column called 'datetime'


Two left joins not working EDIT: Ok, so I managed to get something but it only changes to table on the left side even though I gave 2 specific left joins.

SELECT speler.Roepnaam as team1, wedstrijd.Speler2ID as team2
FROM wedstrijd
LEFT JOIN speler as player1
ON wedstrijd.Speler1ID = speler.ID
LEFT JOIN speler
ON wedstrijd.Speler2ID = speler.ID WHERE speler.ID

SELECT speler1.Roepnaam as team1,     -- unique alias for 1st value from 1st table copy
       speler2.Roepnaam as team2      -- unique alias for 2nd value from 2nd table copy
FROM wedstrijd
LEFT JOIN speler as speler1           -- unique alias for 1st table copy
ON wedstrijd.Speler1ID = speler1.ID   -- join this 1st copy by its alias
LEFT JOIN speler as speler2           -- unique alias for 2nd table copy
ON wedstrijd.Speler2ID = speler2.ID   -- join this 2nd copy by its alias


Enforce a single NULL end date per foreign key value using an index I want to implement a Check Constraint across a date range and foreign key.

I have a table created as:

Primary Key
Foreign Key
DateStart
DateEnd

The ForeignKey can appear many times in the table as long as there is a value in the EndDate. But there should only ever be one entry for the ForeignKey where the DateEnd is Null (the active version).

Apologies if this is simple, but I've been trying to use SQL Server GUI to put in an Constraint expression, but I can't get it to work.

Any help, or pointers to help would be appreciated.

You can do this with a unique filtered index.

Table setup.

create table dbo.OtherTable
(
  FK int primary key
);

create table dbo.YourTable
(
  PK int identity primary key,
  FK int references dbo.OtherTable(FK),
  DateEnd date
);

Add the unique index on FK filtered on DateEnd is null.

create unique index UX_YourTable_FK_DateEnd on dbo.YourTable(FK)
  include (DateEnd)
  where DateEnd is null;

Add one row to OtherTable.

insert into dbo.OtherTable(FK) values(1);

Add a couple of rows to YourTable.

insert into dbo.YourTable(FK, DateEnd) values(1, '2001-01-01');
insert into dbo.YourTable(FK, DateEnd) values(1, null);

Add another row with a value in DateEnd will work just fine.

insert into dbo.YourTable(FK, DateEnd) values(1, '2001-01-01');

Add a second row with null in DateEnd will fail.

insert into dbo.YourTable(FK, DateEnd) values(1, null);

  Msg 2601, Level 14, State 1, Line 33 Cannot insert duplicate key row
  in object 'dbo.YourTable' with unique index 'UX_YourTable_FK_DateEnd'.
  The duplicate key value is (1).

Create Filtered Indexes
Create Unique Indexes

When scripting indexes from SSMS:

Make sure "Script Indexes" is True in the Advanced Scripting Options. It is default True if you script the entire database but False if you specify a specific table.
Also make sure the setting under "Tools / Options / SQL Server Object Explorer / Scripting" is set to "Script for server version 2008" (at least) otherwise the filtering condition will be silently omitted (Connect bug report).


Connection timeout errors from Postgres Update Monitor (postgres 9.6) I am running PostgreSQL 9.6 on windows 10

It seems it has an update monitor that does not use any information about the proxy agent from Windows (we have a corporate proxy). It also does not look at the environment variables HTTP_PROXY or HTTPS_PROXY (which I have set to add credentials via CNTLM for our corporate firewall). This is useful for programs such as NPM and GIT which need to get past our outgoing firewall.

So I am getting the following message every 10 or some minutes as a pop up from a program called:

"D:\Program Files (x86)\postgresql\updatemonitor\bin\UpdManager.exe" --execute  "D:\Program Files\PostgreSQL\9.6\bin\stackbuilder.exe"

The message is very deceiving, but I tracked down the program, and it is the one listed above.

While this answer does not resolve the current question, I think it could be useful for someone searching about Postgres timeout issues on DBA.SE or across the Internet.

According to Postgres documentation, you can use:

  statement_timeout (integer)

  Abort any statement that takes more than the specified number of
  milliseconds, starting from the time the command arrives at the server
  from the client. If log_min_error_statement is set to ERROR or lower, the
  statement that timed out will also be logged.
  A value of zero (the default) turns this off.

  Setting statement_timeout in postgresql.conf is not recommended because it would affect all sessions.

That means that you can set it in postgres.conf or you can use it in pgAdmin.

I've found an example in Postgres community.

set statement_timeout to 1000;
select pg_sleep(2);

  ERROR:  canceling statement due to statement timeout

You can also have different timeout settings per user:

ALTER USER postgres SET statement_timeout=0

And even using a client session via libpq:
From Postgres-XL

  When starting a client session via libpq, parameter settings can be
  specified using the PGOPTIONS environment variable.
  Settings established in this way constitute defaults for the life of the
  session, but do not affect other sessions. For historical reasons, the
  format of PGOPTIONS is similar to that used when launching the postgres
  command; specifically, the -c flag must be specified. For example,

env PGOPTIONS="-c geqo=off -c statement_timeout=5min" psql


How to check parameter value existence in a procedure? The following is a procedure that uses 2 parameters: customer_code and pay_amount. The procedure works as expected, however when I enter wrong cus_code I get this error instead of my custom checking:

ORA-01403: no data found
ORA-06512: at "XXXXX.CUST_PAY", line 19
ORA-06512: at line 2

The procedure:

CREATE OR REPLACE PROCEDURE cust_pay (temp_ccode IN NUMBER, pay_amount IN NUMBER)AS
BEGIN
    DECLARE
      Value_check NUMBER;
      cbalance NUMBER;
    BEGIN
        SELECT Count(cus_code)
        INTO value_check
        FROM customer
        WHERE cus_code = temp_ccode;

        IF value_check IS NULL THEN
            Dbms_Output.put_line('the value was not FOUND');
        ELSE
            UPDATE customer
            SET cus_balance = cus_balance - pay_amount
            WHERE cus_code = temp_ccode;

            SELECT cus_balance
            INTO cbalance
            FROM customer
            WHERE cus_code = temp_ccode;

            IF cbalance < 0 THEN
                Dbms_Output.put_line('The client owes us ' || cbalance);
            ELSE
                Dbms_Output.put_line('Customer new balance is ' || cbalance);
            END IF;
        END IF;
    END;
END;

What did I do wrong? I guess I need to check the value before I SELECT, right?

If you use a count() aggregate on a resultset that has no rows, you'll get zero, not a null.

If a SELECT doesn't find anything, an exception is raised (NO_DATA_FOUND), and you need to catch that exception in an EXCEPTIONS block.

exceptions
  when no_data_found:
    dbms_output.put_line('ooups');
end;

In your case the second select is raising the exception - since zero is not null, your first if branch will not be taken. (update doesn't raise an exception if it doesn't update anything.)

Your procedure is generally too complicate already though. You can declare variables in the procedure "header" directly, remove both selects - all the info you want can be gotten from a single update.

create or replace
procedure cust_pay(temp_code number, pay_amount number)
as
  new_balance number;
begin
  update customer
  set cbalance = cbalance - pay_amount
  where cust_code = temp_code
  returning cbalance into new_balance;

  if sql%rowcount = 0 then
    dbms_output.put_line('No such customer: ' || temp_code);
  elsif new_balance < 0 then
    dbms_output.put_line('Negative: ' || new_balance);
  else
    dbms_output.put_line('Positive: ' || new_balance);
  end if;
end;

(This assumes that cust_code is unique.)

If the update didn't do anything, sql%rowcount will be zero, which tells you whether that customer exists or not (equivalent to your first select).
The update itself is unchanged from your code except that the returning ... into avoids the need for your third select and grabs the updated value directly into a local variable.


Postgres - Querying Min, Max and getting associated columns data specific to those result of those rows I have query in which returns averaged values by site and month.  What I want to know is the min and max of those monthly averages by site and (the difficult part) the month when each occurred.

Here is an example:

SQL Fiddle Stuff here

CREATE TABLE events (
    esite     integer NOT NULL,
    edate     timestamp with time zone NOT NULL,
    evalue    integer NOT NULL
);

INSERT INTO events Values
    (1, '2016-01-03', 11),
    (2, '2016-01-05', 90),
    (1, '2016-01-08', 7),
    (2, '2016-01-10', 40),
    (1, '2016-01-15', 12),
    (1, '2016-01-18', 66),
    (2, '2016-01-22', 54),
    (2, '2016-02-03', 70),
    (2, '2016-02-05', 56),
    (1, '2016-02-08', 61),
    (2, '2016-02-10', 23),
    (1, '2016-02-15', 30),
    (1, '2016-02-18', 15),
    (1, '2016-02-22', 41);

So I'm looking for a query that returns (by site) the min and max monthly average evalues and the months at which the min and max values occurred.  I can get this by using the query below:

SQL Fiddle this

select esite, date_trunc('month', edate) as emonth, round(avg(evalue),2) as evalue_avg from events
  Group by esite, emonth

-- produces the following output:
esite | emonth                      | evalue_avg
2     | January, 01 2016 00:00:00   | 61.33
1     | January, 01 2016 00:00:00   | 24
1     | February, 01 2016 00:00:00  | 36.75
2     | February, 01 2016 00:00:00  | 49.67

Now for the part I'm having difficultly with, I need produce the following result: -- basically the min, max values and the date(month) at which each occurred by site -- (Desired Output)

-- (one row per site)
esite | avg_min | eval_avg_min_date          | avg_max | eval_avg_max
1     | 24.00   | January, 01 2016 00:00:00  | 36.75   | February, 01 2016 00:00:00
2     | 49.67   |February, 01 2016 00:00:00  | 61.33   | January, 01 2016 00:00:00

I've searched around and have seen some examples using windowing and lateral joins, but I haven't been successful in getting any of them to work.  This might be a pivot but since I don't have a fixed number of sites, this tends to be difficult with PostgresSQL.

I'm guessing if anyone has an easy way to do this using postgres 9.4+.

Your help would be appreciated!

I would recommend you do something like this - and don't go down the route of having a row per month.

What happens when you have 5 (10...15... x) years' data?

This is superior IMHO. You can always use the CROSSTAB table function, but I would recommend against it.

SELECT
  esite,
  MIN(evalue),
  MAX(evalue),
  AVG(evalue)::INT,
  EXTRACT(MONTH FROM edate) AS emonth,
  CASE
    WHEN EXTRACT(MONTH FROM edate) = 1 THEN 'January'
    WHEN EXTRACT(MONTH FROM edate) = 2 THEN 'February'
    -- <... fill in rest of months here ...>
  END AS litMonth
FROM events
GROUP BY esite, emonth, litMonth
ORDER BY esite, emonth -- , min, max, avg;

This gives the result:

esite;     min;   max;   avg;    emonth;    litmonth
----------------------------------------------------
    1;       7;    66;    24;     1;         January
    1;      15;    61;    37;     2;        February
    2;      40;    90;    61;     1;         January
    2;      23;    70;    50;     2;        February

Note, that I have CAST the AVG as an INTEGER - you can of course ROUND this off to whatever you like. You can add EXTRACT(YEAR  FROM edate) AS eyear, after the EXTRACT(MONTH... for more clarity and a more elegant result.


Installing a SQL Server patch on a node within a 2-Node FCI When installing a SQL patch on a 2-Node Automatic FCI with a shared disk (in the same datacenter / same subnet), with quorum achieved by Node and FileShare Majority, is it possible to install a SQL patch on one node without impacting the other node in that 2-Node Cluster? Obviously, this is for rollback purposes. What is the common approach in this scenario?

Thanks for the help!

The way we go about SQL patching and windows updates for a 2 node cluster is.

In Failover Cluster Manager, remove the node you want to patch from the Possible owners (makes sure it cant failover to this node until you are ready)
Patch the inactive node
Re-add the node to the Possible owners
Failover to the inactive node
Leave this running for a day or two to make sure there are no issues
Remove the first node from the Possible owners (now the inactive node)
Patch the now inactive node
Re-add the node to the Possible owners
Fail-back

As long as your inactive node does not have the Quorum or Cluster Group patching or rebooing the inactive node should have zero impact.

The easiest way to make sure the node you want to patch does not own anything is using Powershell.

Open Powershell
Run the following command "import-module failoverclusters"
Then run "get-clustergroup"

The output will show you what resources you have in the cluster and what node the currently live on.

Here is an article from MS on the topcic, https://support.microsoft.com/en-au/kb/958734.


Piecemeal restore, filegroup and file extension As per my understanding (so far) any given user database contains three types of files - mdf, ndf and ldf.

MDF stands for meta data file and stores data definition as well as data (in case of small db and no other file exists for data).

NDF is basically for table data and stores data, we can dictate which table can be allocated here based on file groups.

LDF stands for log data file. This stores VLFs (Virtual Log Files)

I was under the impression that there can be only one .mdf file belonging to primary file group and n number of .ndf files belonging to primary or user defined file group.

I was creating a database and created multiple file group and extension left .mdf (by mistake) for all of them and to my surprise, now my database has three .mdf files belonging to different file group as shown below:

CREATE DATABASE [TEST_DBASE]
ON  PRIMARY
( NAME = N'TEST_DBASE', FILENAME = N'E:\DB\TEST_DBASE.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB ),
 FILEGROUP [ACCNTS_FILEGROUP]
( NAME = N'ACCNTS_FILE', FILENAME = N'E:\DB\ACCNTS_FILE.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB ),
 FILEGROUP [LOAN_FILEGROUP]
( NAME = N'LOAN_FILE', FILENAME = N'E:\DB\LOAN_FILE.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )

In this case, how shall we do piecemeal restore if we need to bring only Accounts table online?

Earlier we were following below steps:

RESTORE DATABASE [TEST_DBASE]
FILEGROUP = 'PRIMARY' FROM DISK = 'E:\BACKUPS\FullBackup.BAK' with NORECOVERY, PARTIAL

RESTORE DATABASE [TEST_DBASE]
FILEGROUP = 'ACCNTS_FILEGROUP' FROM DISK = 'E:\BACKUPS\FullBackup.BAK' with NORECOVERY

RESTORE LOG [TEST_DBASE] FROM DISK = 'E:\BACKUPS\LogBackup.trn' with RECOVERY

Above steps were making Accounts table online and accessible.

To bring Loans tables online at a later point of time (non-critical table), we used to perform below steps:

BACKUP LOG [BANK_DATABASE] TO DISK = 'E:\BACKUPS\Backup_tail.trn' with FORMAT, NORECOVERY

RESTORE DATABASE [TEST_DBASE]
FILEGROUP = 'LOAN_FILEGROUP' FROM DISK = 'E:\BACKUPS\FullBackup.BAK' with NORECOVERY

RESTORE LOG      [TEST_DBASE] FROM DISK = 'E:\BACKUPS\LogBackup.trn' with NORECOVERY
RESTORE LOG      [TEST_DBASE] FROM DISK = 'E:\BACKUPS\Backup_tail.trn' with RECOVERY

My concern in the above scenario is - will there be any change for piecemeal restore considering the fact that meta data file is now spread in all the file groups and does not belongs to primary file group only.

For any partial restoration, restoration of primary file group was mandatory since it contains all metadata however if all files are now .mdf, how will it work?

File extensions are meaningless to the application.  For example, create a text file and name it textfile.foo and open it in Notepad.  Notepad doesn't care what the filename is--it just opens it because you told it to open it.

SQL Server works the same way.  You can name the files with any extension you want and it doesn't change how SQL Server uses them.  The first data file, which is typically the one with the .mdf file, will always hold the metadata and can never be removed.  The metadata will not be spread out to other files just because you gave them an .mdf file extension.

File extensions are almost always for the humans--so that we can more easily keep track of things.  Accordingly, we use the .mdf extension on the first data file and .ndf on additional data files so that humans will be able to easily tell which file contains the metadata and can never be removed from the database.

I'm sure there are some applications out there that require file extensions to meet certain requirements, but SQL Server isn't one of them, at least not in regard to the file extension on database files.


db_datareader role along with selected table update I have given one data base user read permission only, using db_datareader role.
Now, I need to give the same user permission to update only one table (PROD.CustomerRelations).

Is there any way to give this permission ?

EDIT:
I have tried the below method, but still I cannot update. I'm accessing SQL Server DB thru Microsoft Access.

Allow update access to only 2 tables in a database schema

I guess the above method does not work because I used Deny for Delete, Update and Insert in the screen Database Properties -> Permissions -> Explicit permissions for [domain\user1]

grant update on object::PROD.CustomerRelations to db_user

https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-object-permissions-transact-sql?view=sql-server-2017


How to create a dynamic comment to an object? I want to embed variable information in a table that is created from a script. The following works:

SELECT( 'created ' || CURRENT_DATE ||' and is in CH1903 LV03');

But it seems I can't use it for creating a comment:

COMMENT ON TABLE public.mytable IS SELECT( 'created ' || CURRENT_DATE ||' and is in CH1903 LV03');
\set mycomment SELECT( 'created ' || CURRENT_DATE ||' and is in CH1903 LV03');
COMMENT ON TABLE public.mytable IS :mycomment;

Is dynamic SQL the only option?

Here is a very simple example of such a function:

create or replace function comment_on(p_type text, p_object text, p_comment text)
  returns text
as
$$
declare
  l_sql     text;
  l_comment text;
begin
  l_comment := replace(p_comment, '${created}', 'created at '||to_char(current_date, 'yyyy-mm-dd'));
  l_sql := 'comment on '||p_type||' '||p_object||' is '||quote_literal(l_comment);
  execute l_sql;
  return l_comment;
end;
$$
language plpgsql;

You can of course add more placeholders to also support e.g. the current user or similar things (or always append this to the comment regardless of the placeholder):

Note that it lacks any error handling or prevention of SQL injection (but that is probably not a problem).

Usage is like this:

create table foo (id integer);
select comment_on('table', 'stuff.foo', '${created} stores bar data');
select comment_on('column', 'stuff.foo.id', 'the id column');

The table foo will then have the following comment:

List of relations
 Schema | Name | Type  | Owner    |  Size     |              Description
--------+------+-------+----------+-----------+---------------------------------------
 public | foo  | table | postgres | 0 bytes   | created at 2015-10-27 stores bar data
(1 row)

And the column will have the following comment:

                           Table "public.foo"
 Column |  Type   | Modifiers | Storage | Stats target |  Description
--------+---------+-----------+---------+--------------+---------------
 id     | integer |           | plain   |              | the id column


Should I include as much information as possbile to help the optimiser? Reading the article "Dynamic Search Conditions in T‑SQL" I saw the following statement in the "Using sp_executesql" section.

  Double Feature

  You may wonder why there are two conditions on the order id, customer
  id and product id (lines 32-33, 48-49 and 64-65). This is because I
  once learnt that you should always specify all conditions you know of
  to help the optimizer out. That was very long ago, and the rule may no
  longer apply. But I have included it here nevertheless.

SELECT @sql =                                                      -- 19
    'SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,     -- 20
            c.CustomerID, c.CompanyName, c.Address, c.City,        -- 21
            c.Region,  c.PostalCode, c.Country, c.Phone,           -- 22
            p.ProductID, p.ProductName, p.UnitsInStock,            -- 23
            p.UnitsOnOrder                                         -- 24
     FROM   dbo.Orders o                                           -- 25
     JOIN   dbo.[Order Details] od ON o.OrderID = od.OrderID       -- 26
     JOIN   dbo.Customers c ON o.CustomerID = c.CustomerID         -- 27
     JOIN   dbo.Products p ON p.ProductID = od.ProductID           -- 28
     WHERE  1 = 1'                                                 -- 29
                                                                   -- 30
IF @orderid IS NOT NULL                                            -- 31
   SELECT @sql = @sql + ' AND o.OrderID = @xorderid' +             -- 32
                        ' AND od.OrderID = @xorderid'              -- 33
                                                                   -- 34
--(snip)
                                                                   -- 46
IF @custid IS NOT NULL                                             -- 47
   SELECT @sql = @sql + ' AND o.CustomerID = @xcustid' +           -- 48
                        ' AND c.CustomerID = @xcustid'             -- 49
                                                                   -- 50
--(snip)
                                                                   -- 62
IF @prodid IS NOT NULL                                             -- 63
   SELECT @sql = @sql + ' AND od.ProductID = @xprodid' +           -- 64
                        ' AND p.ProductID = @xprodid'              -- 65
                                                                   -- 66

Is that pattern of including the search parameter on both joined tables still correct in modern versions of SQL Server? (Most interested in SQL Server 2008 R2 SP2 and newer)

The best way to determine this is to simply look at the execution plans. Typically the optimizer will "condense" a lot of the operations in a query down. For example, if you include "WHERE 1=0" in your query it will see that, know it to be false, and do as little work as possible to return an empty set. In fact, in this case, it does 0 I/O operations as far as STATISTICS IO is concerned.

Since, by default, the optimizer is going to shuffle your JOINs around into an order that it thinks makes the most sense, I would be very surprised if it didn't also get rid of the redundant conditions in the WHERE clause.

EDIT: This is all assuming the WHERE clause is just going to be repeating what was already established in the JOIN clause. In your example you are including the columns used in the JOIN clause in the WHERE clause, so that equality has already been evaluated once.

I did a quick test on my end and the execution plans were identical, but I still encourage you to give it a try. Also, check out Benjamin Nevarez's book on the optimizer: https://www.red-gate.com/products/sql-development/sql-prompt/entrypage/sql-query-optimizer-ebook

I forgot to mention the amazing tool that is SQL Sentry Plan Explorer: http://www.sqlsentry.com/products/plan-explorer/

Great tool to look at plans, and the free version is VERY capable.


How to PIVOT data from MSSQL query and Fetch to PHP view I have a MSSQL 2005 Database with query content like this :

nopas
code
name
order

00115301
D031
ADAM
1

00130015
D031
ADAM
2

00149998
D026
JAMES
1

00149970
D028
FIKI
1

00142641
D028
FIKI
2

00127700
D028
FIKI
3

For report reason, I need Output a table like :

ADAM
JAMES
FIKI
...(dynamic content based new record)

00115301
00149998
00149970
...

00130015
-
00142641
...

-
-
00127700
...

Can anyone help me??

You can do a pivot with CASE statements. It's basically the manual way of pivoting and if you were using a version of SQL Server before 2005, you would need to go that route. SQL Server 2005 added the PIVOT function.
Because you need it to be dynamic, you will need to use dynamic SQL. It's a lot of leg work but definitely doable.
I'm going to create your table content in a global temporary table (has to be global to work in dynamic SQL).
-- CREATE GLOBAL TEMPORARY TABLE
CREATE TABLE ##tablename (
  [nopas] VARCHAR(8),
  [code] VARCHAR(4),
  [name] VARCHAR(64),
  [order] SMALLINT
);

INSERT INTO ##tablename
  ([nopas], [code], [name], [order])
VALUES
 ('00115301','D031','ADAM',  1)
,('00130015','D031','ADAM',  2)
,('00149998','D026','JAMES', 1)
,('00149970','D028','FIKI',  1)
,('00142641','D028','FIKI',  2)
,('00127700','D028','FIKI',  3);

Next, I'm going to SELECT just the unique names into another temporary table and associate them with an IDENTITY with which I can iterate through in a for loop.
SELECT IDENTITY(INT, 1,1) as [id], [name]
INTO #temp
FROM ##tablename
GROUP BY [name];

Now comes the difficult part. The PRINT @dynSQL generates what I'm trying to achieve dynamically (below). We need to SELECT the [order], [name], and [nopas] for the final result. We are pivoting on the [name] in the second part of the statement, and aggregating by MAX([nopas]); but because we contain an [order] grouping in your original table content, it will only retrieve the MAX([nopas]) based on the MAX value of a single row. So, the result is that it removes aggregation. If you did not have an [order] column in your original content, we would need to use the ROW_NUMBER() function* PARTITIONED BY the [name] to generate that [order].
EDIT: I discovered that the ROW_NUMBER() function is only available in SQL Server 2012 and newer.
-- PRINT @dynSQL result
SELECT * FROM
(SELECT [order], [name], [nopas]
  FROM ##tablename) AS sourceTable
PIVOT(
MAX([nopas])
FOR [name] IN ([ADAM],[FIKI],[JAMES])
) AS pivotTable;

-- The leg work to create the dynamic SQL.
DECLARE @count INT = 1;
DECLARE @max INT = (SELECT MAX(id) FROM #temp);
DECLARE @dynSQL VARCHAR(2048) = 'SELECT * FROM
(SELECT [order], [name], [nopas]
  FROM ##tablename) AS sourceTable
PIVOT(
MAX([nopas])
FOR [name] IN (';

SET @count = 1;
WHILE(@count <= @max)
BEGIN
  SET @dynSQL += '[' + (SELECT [name] FROM #temp WHERE id = @count) + '],';
  SET @count += 1;
END

SET @dynSQL = LEFT(@dynSQL, DATALENGTH(@dynSQL) -1) + ')
) AS pivotTable;';

PRINT @dynSQL;
EXEC (@dynSQL);

DROP TABLE ##tablename;
DROP TABLE #temp;

Oh, if you want to remove the [order] column, from the result. You could  use SELECT * INTO ##newGlobalTable in the dynamic SQL and then ALTER the new table and DROP the column.


Proper use of extract function? I am trying to select names of employees who were born in 1985.

I am doing this but it is giving me an error.

SQL> select fname, lname, count(E#) as "total"
  2  extract(year from date, dob) as "year"
  3  from employee
  4  where "year"=1985;
extract(year from date, dob) as "year"
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected

SQL> select fname, lname, count(E#) as "total"
  2  from employee
  3  where extract(year from date, dob) =1985
  4  group by fname, lname;
where extract(year from date, dob) =1985
                        *
ERROR at line 3:
ORA-00936: missing expression

You have a missing comma after the alias "Total" in your first query.
Extract will take single column as input, while you are giving two columns.

You could do it like:

SQL> SELECT COUNT(deptno)          AS "total",
  2    extract(YEAR FROM hiredate) AS "year"
  3  FROM emp
  4  GROUP BY extract(YEAR FROM hiredate);

     total       year
---------- ----------
         2       1982
         1       1983
         1       1980
        10       1981

SQL>

UPDATE

Using the WHERE clause:

SQL> SELECT COUNT(deptno)          AS "total",
  2    extract(YEAR FROM hiredate) AS "year"
  3  FROM emp
  4  WHERE EXTRACT(YEAR FROM hiredate) = 1981
  5  GROUP BY extract(YEAR FROM hiredate);

     total       year
---------- ----------
        10       1981

SQL>


Any way to make stored procedure code dynamically reference other databases, without dynamic sql? I've a situation where I have two databases:

ABCData
ABCUsers

One of the stored procedures in ABCData needs to perform an insert to a table in ABCUsers. This could just be hard coded:

INSERT ABCUsers.dbo.Table1 VALUES('a');

But the next problem we run into is that we want to backup and redeploy these database pairs to similarly named other databases for testing and dev purposes:

XYZData
XYZUsers

Which means the XYZData database will contain a sproc that doesn't work - because the insert has a hard coded ABCUsers.dbo.Table1, and we don't want to get into changing it

We could find out the current database name in the sproc, adjust it and run the sql dynamically:

DECLARE @DataDbName sysname = (DB_NAME());
DECLARE @UserDBName NVARCHAR(20) = (SELECT REPLACE(@DataDbName, 'Data', 'Users'));
EXEC CONCAT('INSERT ', @UserDbName, '.dbo.Table1 VALUES(''a'');')

But I think the prospect of doing so actually makes me feel physically ill ;) (not least because this is a massively simplified example of tens of sprocs each making tens of calls to other dbs)

Is there a way to alias a database name during its creation, so the sproc can always say something like:

INSERT UserDbAlias.dbo.Table1 VALUES('a');

And when the ABCUsers database is created, the UserDbAlias -> ABCUsers but when XYZUsers db is created UserDbAlias -> XYZUsers

Alternatively, this is a git/tfs source control - is there a way to write the source control version of the sproc so that it is modified dynamically when it's deployed, and the original source can contain some kind of placeholder for the db names, but they're replaced with actual hard coded varying db names upon deploy (automatically)

Footnotes: I looked at A way to reference other DB without hardcoding its name which does seem to describe my problem, but doesn't have an answer. That question mentioned "I've looked at synonyms but they wouldn't work out with EF6.." (paraphrase)

Would synonyms work out for me? We use EF6, but I'm not sure what the actual problem is in terms of why it wouldn't work out. Our sproc name in the db never changes, and we don't need to alias the sproc itself, it's the insert table name we need to alias. If the sproc is always called Sproc1 regardless of whether it's in ABCData or XYZData, and the synonym resolving happens inside the sproc, would EF6 even have a problem with it?

Apart from design considerations raised in the comments, ...

Consider one level of indirection prior to creation of the stored procedure:

write a template for the stored procedure, with token placeholders for the schema names

INSERT DBNAMETOKEN.dbo.Table1 VALUES('a');

write a pre-deployment script (sed?) that creates a new file  specifically for each schema, substituting the actual schema name for the token.
place resulting scripts in per-schema deployment directories, from which they may be installed as usual.

Benefits

Scales well
Both template and custom versions may be under version control
Updates/Fixes may still be deployed through a single change to the template file and propagated through a build step
Possibly easier to schedule/track deployment of updates to each schema  depending on situation
Reduces the stored procedure to a simple(r) set of SQL focussed on the task at hand, rather than the dynamics of making it work across different schema.


Forgot to run END_UPGRADE during a Timezone File Upgrade (ORA-56924) I ran the following to upgrade my timezone file from 4 to 14. The issue was after running the upgrade I forgot to run DBMS_DST.END_UPGRADE and put the DB back up and enabled incoming connections.

exec DBMS_DST.BEGIN_PREPARE(14);

TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;

BEGIN
DBMS_DST.FIND_AFFECTED_TABLES
(affected_tables => 'sys.dst$affected_tables',
log_errors => TRUE,
log_errors_table => 'sys.dst$error_table');
END;
/

SELECT * FROM sys.dst$affected_tables;
0 rows returned

EXEC DBMS_DST.END_PREPARE;

shut immediate;

startup upgrade

EXEC DBMS_DST.BEGIN_UPGRADE(14);

shut immediate;

startup

SELECT version FROM v$timezone_file;

Flip to a week later when I realise this. When I try to run DBMS_DST.END_UPGRADE I get the following:

ERROR at line 1:
ORA-56929: Ending an upgrade window failed
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DST", line 1277
ORA-06512: at line 5

I tried running it in upgrade mode as well but no luck. When I searched online I saw the following:

ORA-56929: Ending an upgrade window failed
Cause: Ending an upgrade window failed or there were tables containing TSTZ data which have not been upgraded.
Action: Check alert or trace file for error(s) or complete upgrading of tables containing TSTZ data.

How would I "complete upgrading of tables containing TSTZ data."?

Just start the database normally and run DBMS_DST.UPGRADE_DATABASE

Then you  must see the messages "An upgrade window has been successfully ended." and "Failures:0"

Double check that it succeeded  by running:

1:

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
    FROM DATABASE_PROPERTIES
    WHERE PROPERTY_NAME LIKE 'DST_%'
    ORDER BY PROPERTY_NAME;

2:

 SELECT * FROM v$timezone_file;

-- file with the newer version must appear here.

Lastly, make sure the versions match for the following queries:

CONN / as sysdba
select VERSION from v$timezone_file;
select TZ_VERSION from registry$database;

--if they differ after an upgrade then updating registry$database can be done by

CONN / as sysdba

update registry$database set TZ_VERSION = (select version FROM v$timezone_file);
commit;

Hope it helps.


Find uncompressed size of all tables in a database In Dynamics AX there is a caching mechanism where tables can be configured to be loaded into memory and cached. This cache is limited to a certain amount of KB to prevent memory issues. The setting I'm talking about is called entiretablecache and loads the whole table in memory as soon as a single record is requested.

Up to recently we relied on some scripts to verify the size of the tables that have this setting to see if the table size is above this limit.

Now however, compression comes into play and things like sp_spaceused or sys.allocation_units seem to report the space actually used by the compressed data.

Obviously, the application server is working with uncompressed data so the data size on disk in SQL Server is irrelevant. I need the actual size the uncompressed data will have.

I know of sp_estimate_data_compression_savings but as the name says, this is just an estimate.
 I would prefer to have size as correct as possible.

The only way I could think of was some convoluted dynamic SQL creating uncompressed tables with the same structure as the compressed tables, inserting the compressed data in that shadow table and then check the size of that shadow table.
Needless to say, this is a bit tedious and takes a while to run on a database of several hundreds of GB.

Powershell could be an option, but I wouldn't like to iterate over all tables to perform a select * on them to check the size in the script as that would just flood the cache and would probably take a long time too.

In short, I need a way to get the size for each table as it will be once uncompressed and with fragmentation out of the equation as presented to the application, if that's possible. I'm open to different approaches, T-SQL is preferred but I'm not opposed to Powershell or other creative approaches.

Assume the buffer in the application is the size of the data. A bigint is always the size of a bigint, and a character data type is 2 bytes per character (unicode). BLOB data takes the size of the data too, an enum is basically an int and numeric data is numeric(38,12), datetime is the size of a datetime. Also, there are no NULL values, they are either stored as an empty string, 1900-01-01 or zero.

There is no documentation on how this is implemented, but the assumptions are based on some testing and the scripts used by PFE's and the support team (which also ignore compression apparently, since the check is built in the application and the app can't tell if the underlying data is compressed) which also check the table sizes. This link for example states:

  Avoid using EntireTable caches for large tables (in AX 2009 over 128
  KB or 16 pages, in AX 2012 over ‘entire table cache size’ application
  setting [default: 32KB, or 4 pages]) – move to record caching instead.

I need the actual size the uncompressed data will have.
  ...
  I would prefer to have size as correct as possible.

While the desire for this information is certainly understandable, getting this information, especially in the context of "correct as possible" is trickier than everyone is expecting due to faulty assumptions. Whether doing the uncompressed shadow table idea mentioned in the question, or @sp_BlitzErik's suggestion in a comment about restoring the DB and uncompressing there to check, it should not be assumed that the size of the uncompressed table == the size of said data in memory on the app server:

Are all rows in the table being cached? Or just within a range? The assumption here is that it is all, and that might be correct, but I figured it should at least be mentioned that this might not be the case (unless documentation states otherwise, but this is a minor point anyway, just didn't want it to not be mentioned).

Question was updated to state: yes, all rows are being cached.
Structure overhead

On the DB side:
Page and row-overhead on the DB side: How many rows fit on a page is determined by many factors that could throw off estimates. Even with a FILLFACTOR of 100 (or 0), there is still likely to be some unused space left over on the page due to it not being enough for an entire row. And that is in addition to the page header. Also, if any Snapshot Isolation functionality is enabled, there will be, I believe, an extra 13 bytes per row taken up by the version number, and that will throw off estimates. There is other minutia related to the actual size of the row (NULL bitmap, variable length columns, etc) but the items mentioned thus far should alone make the point.
On the app server side:
What type of collection is being used to store the cached results? I assume this is a .NET app, so is it a DataTable? A generic list? A SortedDictionary? Each type of collection has a different amount of overheard. I would not expect any of the options to necessarily mirror the Page and Row overheads on the DB side, especially at scale (I'm sure a small amount of row might not have enough various to matter, but you aren't looking for differences in hundreds of bytes or just a few kB).

Datatypes

On the DB side:
CHAR / VARCHAR data is stored at 1 byte per character (ignoring double-byte characters for the moment). XML is optimized to not take up nearly as much space as the text representation would imply. This datatype creates a dictionary of element and attribute names and replaces the actual references to them in the document with their respective IDs (kinda nice, actually). Otherwise, the string values are all UTF-16 (2 or 4 bytes per "character"), just like NCHAR / NVARCHAR. DATETIME2 is between 6 and 8 bytes. DECIMAL is between 5 and 17 bytes (depending on the precision).
On the app server side:
Strings (again, assuming .NET) are always UTF-16. There is no optimization for 8-bit strings such as what VARCHAR holds. BUT, strings can also be "interned" which is a shared copy that can be referenced many times (but I don't know if this works for strings in collections, or if so, if it works for all types of collections). XML may or may not be stored the same way in memory (I will have to look that up). DateTime is always 8 bytes (like T-SQL DATETIME, but not like DATE, TIME, or DATETIME2). Decimal is always 16 bytes.

All of that to say: there is pretty much nothing you can do on the DB side to gain even fairly accurate memory footprint size on the app server side. You need to find a way to interrogate the app server itself, after being loaded with a particular table, so know how big it is. And I am not sure if a debugger would let you see the runtime size of a filled collection. If not, then the only way to get close would be to go through all rows of a table, multiplying each column by the appropriate .NET size (e.g. INT = * 4, VARCHAR = DATALENGTH() * 2, NVARCHAR = DATALENGTH(), XML = , etc), but that still leaves the question of the overhead of the collection plus each element of the collection.

Given some new definition in the question, one could probably do the following query to get rather close. And it doesn't matter whether the table is compressed or not, though it's up to each person to determine if scanning all rows is appropriate on Production (maybe do from a restore or during off-peak hours):

SELECT
   SUM( DATALENGTH([NVarcharColumn_1]) + DATALENGTH([NVarcharColumn_N]) ) +
   SUM( (DATALENGTH([VarcharColumn_1]) + DATALENGTH([VarcharColumn_N])) * 2 ) +
   SUM(4 * [number_of_INT_columns]) +
   SUM(8 * [number_of_BIGINT_and_DATETIME_columns]) +
   SUM(16 * [number_of_DECIMAL/NUMERIC_and_UNIQUEIDENTIFIER_columns]) +
   etc..
FROM [SchemaName].[TableName] WITH (NOLOCK) -- assuming no Snapshot Isolation

But remember, this doesn't account for collection or collection element overhead. And not sure if we can get that value without a debugger (or possibly something like ILSpy, but I am not recommending that as it might violate the EULA depending on local laws).


Force index using the unused index columns having effect as order by for the result set? if I have a index for (col1, col2, col3, col4), my query put col1 and col2 in the query where clause and force MySQL to use the above index. Would my query result be sorted by col3 and col4 automatically?

Table is something like this:

CREATE TABLE IF NOT EXISTS `jobs` (
    `job_id` varchar(36) NOT NULL,
    `job_status` varchar(30) NOT NULL,
    `created_at` datetime NOT NULL,
    `lease_date` datetime,
    `priority` int NOT NULL,
    PRIMARY KEY(`job_id`),
    INDEX `job_status_priority_lease_date` (`job_status`, `priority`, `lease_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

The full query I want to do is the following:

select * from jobs use index (job_status_priority_lease_date)
where status="IN_PROGRESS"
and priority=0
limit 100

Would my query result be sorted by the lease_date automatically? Because of some limitation, I cannot use the order by statement.

There is no guarantee of any order without an explicit ORDER BY clause.
This is a fundamental aspect of SQL which deals with sets. Sets, by definition, have no order. An "Ordered Set" is no longer a set, it is a cursor.
Even if you happen to see rows come back in a certain order repeatedly due to indexes or execution plan operators, you should never assume it will always be that way.
I've seen many application fail due to making such assumptions.

I'm not sure what 'some sort of limitation' prevents you from using ORDER BY, but as a general rule, and since the order in this case is only for presentation purposes only, you should consider ordering the rows at the client side instead of at the database.
The client (or presentation tier) is responsible for presentation aspects such as order, fonts, colors, indentations etc.

Also note that your LIMIT clause can pick any 100 arbitrary rows without an ORDER BY.
Don't expect the LIMIT to pick up the first 100 rows based on some index order.

HTH.


Default value for "microtime" column in MySQL My interest is to store the time with FSP of 6. As I've read, this cannot be achieved with TIMESTAMP or DATETIME data types. So, I have a double field to store the output of the microtime function.

Is there anyway I can set (or even write some code to create) a default value for such a field?

I want to use something like NOW(6) and get 1442059062.065123 for example.

You could format it with UNIX_TIMESTAMP like below:

mysql> SELECT UNIX_TIMESTAMP(NOW(6));
+------------------------+
| UNIX_TIMESTAMP(NOW(6)) |
+------------------------+
|      1442068528.543100 |
+------------------------+
1 row in set (0,00 sec)

mysql>

You can find any others date and time functions here. You could add a trigger and if you want a default value for your decimal(16, 6) microtime, use BEFORE INSERT and replace your NEW.bigintvalue=UNIX_TIMESTAMP(NOW(6));.

Example:

mysql> CREATE TABLE `test`.`test1` (
    ->   `id` INT NOT NULL AUTO_INCREMENT,
    ->   `time` DECIMAL(16, 6) NULL,
    ->   PRIMARY KEY (`id`));
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> DELIMITER //
mysql> CREATE DEFINER=`root`@`localhost` TRIGGER `test`.`test1_BEFORE_INSERT` BEFORE INSERT ON `test1` FOR EACH ROW
    -> BEGIN
    -> SET NEW.time=UNIX_TIMESTAMP(NOW(6));
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql>
mysql> # Adding 1 into id field
mysql> INSERT INTO `test`.`test1` (`id`) VALUES ('1');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test.test1;
+----+-------------------+
| id | time              |
+----+-------------------+
|  1 | 1442069359.675330 |
+----+-------------------+
1 row in set (0.00 sec)

mysql>


Create a table with the same constraints,triggers,indexes, and statistics for house-keeping table I have a current database with few hundred tables, say, tables A,B,C... and I want to create the same tables with the same constraints,triggers,indexes, and stats as the original tables.

I know I could use generate script to do this but is there a faster way?

Do I have to rename the "new" tables with different names as well as their constraints,indexes etc?

Right mouse click on your database -> Tasks -> Generate scripts -> Tables:

On the next page select if you want to generate a single file or single file per object and go to Advanced:

Here select constraints, triggers, indexes and what else you want to script


Create a copy of the database in same server I am trying to create a copy of database in mysql using mysqldbcopy like :

mysqldbcopy --source=root:xxx@13.67.59.xxx --destination=root:xxx@13.67.59.xxx old_db:new_db

I am getting error saying cannot connect to the destination server (Error code 110).
Source and destination is same server. I want to know which user and ip i need to put here, whether to put mysql username and password or server username and password. Please help me out in this.

do dump of your current database

mysqldump -p yourDatabase > file.sql

then create new database and restore file.sql

mysql -p -e "create database new_DB"

mysql -p new_DB < file.sql


Oracle DB administrator-specific security features Yesterday I was presenting an internet banking solution to a potential client management. My offer is based on using MS SQL Server 2008 RDBMS.

One of the client's officers mentioned, that Oracle's RDBMS has some "specific" features that allow a security department to ensure that no sensitive data is visible to an admin, that MS SQL doesn't have.

For me it is hard to imagine how can this be mitigated, unless preventing access to some tables in a DB.

My question - is there really a "know-how" in Oracle RDBM when it comes to security tuning, that MS SQL does not implement?

I do not know if there is a SQL Server equivalent but they are probably talking about Oracle Database Vault. It allows administrators to take care of operations on the database without having access to the data. Now if your organization doesn't make the distinction between a role where DBAs handle operations functions vs DBAs that have access to data (to run DDL scripts, DML, etc) then this isn't much help. But it can help limit exposure in those situations where only a few people need to access data but where many help keep up with database operations.


BEFORE INSERT validation trigger for GPS coordinates I have a MySQL database table that contains zipcodes and their corresponding lat/lng values.

I have another table that contains business locations (including lat/lng)

Prior to inserting new records for a particular business, or updating existing records, I need to validate that the lat/lng values being inserted or update ARE within specified tolerances for the the business location and zipcode. For example, if the coordinates are within one mile of the business location, then insert, otherwise skip over the insert or update.

Essentially the trigger or stored procedure needs to perform calculations on fields from the tables and make comparisons of the resulting calculated values against maximum allowable tolerances to DECIDE whether to allow the INSERT/UPDATE to occur.

I have the necessary calculations themselves, I just don't have the knowledge of how to structure it within a trigger or stored procedure.

Load the new data into a temporary table.
Batch compute distances (one UPDATE the temp table)
One INSERT..SELECT to copy the rows that are within the desired distance.

If you need to write an expression for distance, suggest using Pathegorus, with an adjustment to the longitude of COS(latitude).

Batching things is often a faster way to do bulk operations.


How to insert a Unicode character verbose into a varchar DB? I need to insert this character '●' into a VARCHAR column of a MSSQL database with collation set as SQL_Latin1_General_CP1_CI_AS (or at least mock what my Python + Windows MSSQL Driver might have done). I tried to insert this from my client, but it gets inserted as ?.

The hex value as checked in Python is \xe2\x97\x8f which is binary 226, 151, 143.

The collation spec says that 226 is defined but 143 and 151 is undefined. So my best would be to insert 226?

The reason I am doing this exercise is that our app was inserting this character into DB earlier. At this point, I don't know how it went in because but it went through a Python App with Windows MSSQL Driver and our DB Team wrote a script to correct it and apparently it got replaced with "?".

Now I am just trying to reproduce this scenario to ask them why it is getting replaced with "?" because we are replacing it as empty in the App. Since my App already has a fix for this issue (we remove anything greater than 127), I am trying to mock this directly from back-end to prove that it is getting replaced with '?' which is actually my claim and I think it is done because it says "COLLATE" in the update query.

UPDATE pr_response
SET nur_respon =
REPLACE (nur_respon,
     SUBSTRING(nur_respon, PATINDEX('%[^ !-~]%' COLLATE Latin1_General_BIN, nur_respon), 1),
         '')
WHERE PATINDEX('%[^ !-~]%' COLLATE Latin1_General_BIN, nur_respon) > 0

Is this a fine script to remove Non-ASCII-Data > 127? Can someone explain this query in plain English?

I need to insert this character '●' into a VARCHAR column ... with collation set as SQL_Latin1_General_CP1_CI_AS ... The reason I am doing this exercise is that our app was inserting this character into DB earlier.

No, the app was not inserting this character (Black Circle U+25CF) into a VARCHAR column using a [SQL_]Latin1_General... Collation. Latin1_General Collations use Code Page 1252 which does not have such a character, nor does there appear to be a "best fit" mapping to convert it to something similar.

That character does exist in some code pages, such as those used by the Korean and Japanese Collations, as the following queries show (both are double-byte character sets, which is why the conversion to VARBINARY shows two bytes instead of one):

SELECT CONVERT(VARCHAR(10), N'●' COLLATE Korean_100_CI_AS),
       CONVERT(VARBINARY(10), CONVERT(VARCHAR(10), N'●' COLLATE Korean_100_CI_AS));
-- ●    0xA1DC

SELECT CONVERT(VARCHAR(10), N'●' COLLATE Japanese_XJIS_100_CI_AS),
       CONVERT(VARBINARY(10), CONVERT(VARCHAR(10), N'●' COLLATE Japanese_XJIS_100_CI_AS));
-- ●    0x819C

SELECT CONVERT(VARCHAR(10), N'●' COLLATE Latin1_General_100_CI_AS),
       CONVERT(VARBINARY(10), CONVERT(VARCHAR(10), N'●' COLLATE Latin1_General_100_CI_AS));
-- ?    0x3F

It is possible, however, that the app was inserting '•' (Bullet U+2022), which looks quite similar, just a little smaller. The "Bullet" character is available in Code Page 1252 (Dec 149 or Hex 0x95).

  our DB Team wrote a script to correct it and apparently it got replaced with "?"

Well, replacing "●" with "?" doesn't sound like fixing it ;-).

  Now I am just trying to reproduce this scenario to ask them why it is getting replaced with "?" because we are replacing it as empty in the App. Since my App already has a fix for this issue (we remove anything greater than 127)

Regardless of technicalities, it seems fairly obvious both statements cannot be true at the same time: it cannot get replaced with "?" and get removed before hitting SQL Server. Clearly the app code does not have a fix for this, and is not removing everything with a value over 127. It is being converted into "?" in SQL Server because that character is being inserted yet does not exist in Code Page 1252.

  I think it is done because it says "COLLATE" in the update query.

No, the forcing of a binary Collation via the COLLATE keyword is not changing this character into "?". Both the column's Collation ( SQL_Latin1_General_CP1_CI_AS ) and the explicit Collation ( Latin1_General_BIN ) use Code Page 1252, so no characters could change due to this.

The "●" character is being replaced with "?" as it is being inserted into the VARCHAR column. And once it is inserted as a question mark, there is nothing to clean up, and no way to identify that question mark as being originally from something else as opposed to an intended question mark.

  Is this a fine script to remove Non-ASCII-Data > 127? Can someone explain this query in plain English?

That query finds any occurrences of characters having an ASCII value of greater than 127 (that is what the PATINDEX does), and then gets any such character (that is what the SUBSTRING does), and then replaces all occurrences of that character in the column with empty string (this is what the REPLACE does). The query does not do any UPDATE if no characters are found having an ASCII value over 127.

This query only works on one character at a time. So, if a column has 2 or more characters with ASCII values above 127 that are not the same ASCII value, then the script would need to be executed multiple times.

That query will not help in this particular case (i.e. of replacing "●" with "?") since that conversion is happening on the way in. This query only handles characters with ASCII values of 128 - 255, yet "●" is not one of those characters as it could never be in this column in the first place.


How should I limit a table to N rows, discarding oldest? I have a SQL Server table which I use to store events.

I only want the table to contain the 1000 most recently added rows, when row 1001 is inserted, row 1 should be deleted.

I have both a DateCreatedUTC column and an autonumber column.... I could write a query to execute a delete and trigger it on INSERT but I don't know that this is the most appropriate solution.

You can create a view to hide the rest of the rows when they do exist:

CREATE VIEW dbo.Top1000Rows
AS
  SELECT TOP (1000) ...columns...
    FROM dbo.table_name
    ORDER BY DateCreatedUTC DESC;
GO

Then periodically run this in a background job (just play with the frequency until you're happy with how often the table itself is "off", and by how much):

;WITH x AS
(
  SELECT pk_column,
    rn = ROW_NUMBER() OVER (ORDER BY DateCreatedUTC DESC)
  FROM dbo.table_name
)
DELETE x WHERE rn > 1000;

If it doesn't have to be exactly 1,000 rows all of the time, then you can potentially forget about the view altogether, knowing that sometimes there will be more than 1,000 rows.


Is there any way to find out when particular view was compiled? I have an Issue with a view, recently I made changes to the view and committed the code (in SVN). Code was moved to another repository and compiled, but my modifications are not reflected. I searched on Google to find when the view was last compiled, but I didn't find any clue.

Is there any way to find when particular view was compiled? Does Oracle store historic data (when was it compiled/modified/etc.)?

I think that you can determine the time a view was last compiled querying the data dictionary view dba_objects:

sql> select last_ddl_time from dba_objects where object_type = 'VIEW' and object_name = 'MOO';

You can also see the text the view is based on in the dba_views dictionary view:

sql> select * from dba_views where view_name = `MOO`;


How to design indexes for columns with NULL values in MySQL? I have a database with 40 million entries and want to run queries with the following WHERE clause

...
WHERE
  `POP1` IS NOT NULL
  && `VT`='ABC'
  && (`SOURCE`='HOME')
  && (`alt` RLIKE '^[AaCcGgTt]$')
  && (`ref` RLIKE '^[AaCcGgTt]$')
  && (`AA` RLIKE '^[AaCcGgTt]$')
  && (`ref` = `AA` || `alt` = `AA`)
LIMIT 10 ;

POP1 is a float column that can also be NULL. POP1 IS NOT NULL should exclude about 50% of the entries, that's why I put it at the beginning. All other terms reduce the number only marginally.

Amongst others, I designed an index pop1_vt_source, which seems to be not used, while an index with vt as first column is used. EXPLAIN-output:

| id | select_type | table | type | possible_keys                          | key                 | key_len | ref         | rows     | Extra       |
|  1 | SIMPLE      | myTab | ref  | vt_source_pop1_pop2,pop1_vt_source,... | vt_source_pop1_pop2 | 206     | const,const | 20040021 | Using where |

Why is the index with pop1 as the first column not used? Because of the NOT or because of NULL in general. How can I improve the design of my indices and WHERE clauses? Even when limiting to 10 entries, the query takes more than 30 seconds, although the first 100 entries in the table should contain the 10 matches.


Tracking object use using SQL Auditing I am looking for a solution to track object usage in my database.  Objects such as tables, stored procedures, and functions.

I discovered SQL Auditing and was under the impression that it's exactly what I needed.

So I set it up to audit SELECT, INSERT, UPDATE, DELETE on my tables, and EXECUTE on my stored procedures and functions.

I set it up to target the Windows Security event log via Event Viewer Utility.

I made sure to allow permissions and set object access in Local Security Policy and using auditpol.exe.

Then I ran some INSERTs on tables and EXECUTEs on stored procedures. I then opened Event Viewer expecting to see logs of the time each of these statements where executed and on what object, however all I see is this:

Did I skip a step, am looking in the wrong place, or did I make a mistake from the beginning about what SQL Audit actually does?

EDIT: OK, I switch the target to a file and viewed the file via SSMS (right clicking Audit -> View Audit File)... and here the INSERTs and other actions are indeed being logged....So clearly I'm looking in the wrong place in the Event Viewer or missing something.

To track object use with the SQL Server Audit feature, it’s necessary to set up the auditing. In order to do so, an audit object must be created first.

This can be done using SQL Server Management Studio or T-SQL.

The next step is to set up the auditing in the particular database on specific objects. This is where availability of the SQL Server Audit feature is required on the database level (the database level auditing is available in SQL Server Enterprise and Developer editions only).

To continue setting up the auditing, it’s required to create a database level audit specification. Such database level audit specification will belong to the audit object previously created.

Although SQL Server provides a built-in feature (the View Audit Logs context menu option of an audit object) to view captured information, this is not a convenient way for creating comprehensive reports, and it provides basic filtering only.

So, in order to provide tracked information for any deeper analysis or documenting, use the fn_get_file_audit SQL Server function to read repository .sqlaudit files used by the audit object.


How to keep records of past week visits in a web app? I am developing a newspaper-like MVC webapp with an article content type which has a visits field. The database is MariaDB.

Each time a visitor requests an article, the visits field of the article row is incremented by 1. So I can easily query articles by their overall visits.

But what I want is to sort out articles based on number of visits which each article has got in the past week.

I thought to have a separate table like weekly-stats in which I can insert and increment article info with their recent visits upon each request, but then I need to empty the table weekly as a cron job, which is not ideal, and it leaves the stats empty after the data is being purged.

I also thought of having articles' visits saved in an expiring cache like redis and set the cached records to expire after a week, but then it brings all the complexities of inserting and querying redis hash elements.

So I thought there might be some more straightforward design strategies that I miss. How can I achieve this with the least hassle?

The weekly_stats table can have one record for every week, i.e. something like:

CREATE TABLE weekly_stats (
  week_start date PRIMARY_KEY, -- e.g. '2018-03-05'
  article_id int unsigned,
  view_count int unsigned DEFAULT 0,
  CONSTRAINT week_stats_fk1 FOREIGN KEY (article_id)
    REFERENCES `article` (`article_id`) ON DELETE CASCADE ON UPDATE CASCADE
);

Your application will then have update the weekly_stats.view_count every time an article is viewed. If the record for that particular week doesn't exist, the application will have to create it.

If you want to delete old data, which may not be necessary: Instead of a cron job, you could use a MariaDB event. Set it up to only delete records older than some limit, e.g. 365 days.


postgresql replay_location in pg_stat_replication is empty I'm running a cluster of PostgreSQL 9.5.2, and export the stream log with confluentinc/bottledwater-pg: Change data capture from PostgreSQL into Kafka. The replication works fine, but I'm wondering about the output of pg_stat_replication:

db# select * from pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid              | 371125
usesysid         | 16385
usename          | db
application_name | bottledwater
client_addr      | 127.0.0.1
client_hostname  |
client_port      | 22338
backend_start    | 2016-09-30 16:58:04.074937+08
backend_xmin     |
state            | streaming
sent_location    | 12DD/D325D478
write_location   | 12DD/D2D75EF0
flush_location   | 12DD/D2B8B5F8
replay_location  |
sync_priority    | 0
sync_state       | async

See near the bottom, the replay_location is null. If anyone knows what's wrong, let me know.

It looks like you are seeing this on the master.  The documentation says this about replay_location:

  Last transaction log position replayed into the database on this standby server

If this DB was never a standby yet, that column will be empty.

Note: you should upgrade to the latest minor version (9.5.6 at the time of writing).


SQL Server 2005-2008 Compatibility Check A few databases were recently migrated from SQL Server 2005 to SQL Server 2008, and I have to check if all the stored procedures, and triggers still work well. With 'checking', I mean that I should guarantee that they are compilable and executable.

There are many stored procedures and triggers to check. To do it manually, I would need maybe 1 month. So, I want to take another approach.

What kind of checking procedure would you recommend me, so that I can say surely that the stored procedures and triggers will also work in SQL Server 2008?

Thanks

Is the 2005 instance still your production instance? If so, you could use a server side trace to capture a workload and replay it on the 2008 instance. You should try to capture your normal workloads. You have to decided what is normal in your system. This wouldn't guarantee that all stored procs will work, but you day to day activity should be fine.

As far as compatibility levels please review the BOL link:
http://msdn.microsoft.com/en-us/library/bb510680.aspx


Efficient way to store multi choice in a column with few options I am looking for advices on how to handle multiple choices in a column?

By way of example lets say selecting a fruit for lunch.  Options are Apple or Pear.  And you are 100% sure those two options will always be available, but you can only be 95% sure that other fruit won't be added to the list, but can be sure that if they are there will never be more than 3 or 4 types ever, and once added they will never be removed.

Then let's say you have another similar column for sandwich types, and another for drink types with the same requirements for their types.

Is it still best to create a table for fruits, another for sandwiches and another for drinks?

What about if there really will only ever be 2 options, like new car or second hand car but you want to use the descriptions, not a boolean value so other staff can drag and drop a quick query in MS Access or paste it into a spreadsheet?

If it matters the DB is ms SLQ Server, and there will be about 1500-2000 new rows per month in this table, but queries sometimes going back over several years of data.  Currently have about 5 years of data to insert into this table when complete.

Well, it is always difficult to give a generic answer to design questions.
Best would be to study the normal forms and see how that can be applied to exactly your situation! :)

Having said that, it sounds like you want to store reporting data (OLAP type table/database) so I base it on that.
In OLAP, you can see all different types of items which can be selected 0 or 1 time as Attributes to whatever you like to consider the 'Fact' and can be stored in the 'Fact' table. You can opt to not use separate tables for your fruit, sandwiches or drinks but only have 'check constraints' in your Fact table columns.

However, if your requirement is a little bit more complex, I'd opt for one Fact table, one for fruit (where you can store more lookup data per fruit), one for sandwich (with the same requirements as for fruit but possibly a different set of attributes/columns) and one for drinks. I.e. One extra table per 'selectable attribute'.
Now, if someone can choose More then 1 of anything, it gets more complex and you need to consider other options (link tables or such).


How do you find SQL Server version/edition without SSMS installed? How do find out which edition is installed without having the management studio installed?  I have a server that functions as a license manager for another software.  Upon investigation of a high RAM usage alert, I found that the sqlservr.exe process is taking up almost 2 GB of RAM.

I looked through the program menu and found that the configuration manager was installed, otherwise, it is pretty bare bones.  I clicked on properties of the EXE file and found 10.50.1600.1, but there is no place that I've found that states whether it is Express, Dev, STN, ENT, etc.

If I had to guess, this is an express edition, but I wanted to know if there is an obvious tell-tale sign.

Update:
@Bob - The file tells me what I know, not the edition.

@valo - I get the following error when I run that command and I did verify named pipes was enabled:

  HResult 0x35, Level 16, State 1
  Named Pipes Provider: Could not open a connection to SQL Server [53].
  Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance->specific error has occurred while establishing a connection to SQL Server. Server is not >found or not accessible. Check if instance name is correct and if SQL Server is configured >to allow remote connections. For more information see SQL Server Books Online..
  Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

@thomas - I noticed the Stock Keeping Unit Name before I asked the question, but that seemed too easy, I guess my initial suspicion was correct.

You can try the command line tool:

C:\>sqlcmd -S [SERVER] -d [DB_NAME] -E -Q "SELECT @@VERSION"


Is Memory Grant size 'cached' for subsequent executions of a cached plan? I hope the title of the question is clear.  I am a DB developer as one of my job roles.  I recently asked a question regarding index statistics and I have spent a lot of time reading about statistics, cardinality estimates, and SQL Server memory management since.

I talked with my team lead and senior team lead about my findings and showed data from tests I had done where queries ran better when a larger sample size was used on certain indices.  As our primary role is software development and no one on my team knows much about database administration it was a very enlightening discussion.  They took the findings to the CIO who asked that I schedule a meeting with the entire DBA team, himself, and all the IT senior team leads.

Within 5 minutes of scheduling the meeting, the team lead of the DBA team was at my desk and not in a good mood.  He was semi hostile and I explained to him that this was meant to only be a discussion and I didn't want to step on toes.  During the course of this conversation he made a comment that Memory Grant sizes get reused from one execution to the next for executions that followed a cached plan.  I had not read anything about that in the 20 or so different websites I read about memory grant.

I wondered if it was in fact true and I am starting to have doubts in the abilities of this TL after another two comments in the same conversation.  The first comment he made was that his predecessor had created a complex script for handling the thresholds and sample sizes and that he had never made any changes to them.  He has been the TL for 2.5 years now.  The second comment he made 3 times was that setting IX_myTable with a sample size of 5 or 10 percent would "take forever to run".  I finally replied that I had tested that and it only took 2 seconds.  He was taken back by my reply and I followed up with by saying that a much larger table's index only took 25 seconds to run a 5% sample size (this table currently has a 0.2% sample size).  I showed him my documented tests that showed improved performance and smaller memory grants and even fully disclosed that in my findings certain indices at certain sample sizes no  longer yielded improved cardinality estimates.

I have tried searching on his claims about the memory grant size but I have turned up nothing.  Can anyone elaborate?

The team lead is correct in the statement about memory grants given the version of SQL Server you're using (SQL Server 2014, based on the tags here).  They are part of the cached execution plan, and the values are re-used across executions as long as the cached plan is in place.

Improvements have been made in this area on newer versions of SQL Server:

SQL Server 2017 introduced batch mode memory grant feedback: SQL Server 2017: How does Batch Mode Memory Grant Feedback Work?
SQL Server 2019 has introduced row mode memory grant feedback: Row mode memory grant feedback

These improvements allow the cached plan to be updated based on runtime feedback (how much of the memory grant was used on the previous execution).

I can't find a specific source in the documentation that confirms memory grants are reused as part of the cached plan, but hopefully it's implied well enough from the memory grant feedback improvements that have been introduced in newer versions.