Move new data records automatically to another database in oracle xe What's the best way to move all the data from a table on my local oracle database to an identical table on another pc? I will have to transfer the data to the second database as soon as it's written to the local one. The local table should not contain any data, unless it's not possible to insert it to the remote database. If the connection is lost, all new datasets must be stored in the local table.
I tried to solve this by using a trigger, but it didn't work as expected. It works just fine if the remote database connection is valid, but it performs an entire rollback (including the original insert) if the connection is lost. Because of that, the data isnt even inserted to the local database. Another huge problem is that it takes about 40 seconds every time to return ORA-12170 (Connection timeout). Is there any way to set a much shorter time interval for the timeout or to abort the query if it takes that much time?

create or replace TRIGGER DATA_TO_SERVER
AFTER INSERT ON LOCAL_TABLE
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SAVEPOINT sp;

  INSERT INTO SERVER.SERVER_TABLE@SERVER_LINK
  SELECT *
  FROM LOCAL_TABLE;

  DELETE
  FROM LOCAL_TABLE
  WHERE ID IS NOT NULL;

COMMIT;

EXCEPTION WHEN OTHERS THEN
    ROLLBACK to sp;
    RAISE;

END;

SQL> Create Directory EXPDIR as 'PATH';

The export command

expdp directory=expdir dumpfile=newExport.dmp full=y logfile=newExport.log

on the target database also create a directory

Create Directory EXPDIR as 'PATH';

The import command

IMPDP directory=ExpDir dumpfile=newExport.dmp Logfile=import_Database.LOG FULL=Y

By the way this is a full export and import, you can customize it according to your preference on what exactly to export or import

https://docs.oracle.com/cloud/latest/db112/SUTIL/dp_export.htm#SUTIL200


Get previous and next record with two order criteria I have a SQL Query with two ORDER BY criterias. How I can get the correct previous and next position for every record?

My Solution works as long the actual and previous or next record match the same first ORDER BY criteria.

Sample data

http://sqlfiddle.com/#!9/409b3/3

Target: How I can get for id 100026 the previous id 100018 and the next id 100003?

Schema

CREATE TABLE `profiles` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `rnd_order` INT(11) NOT NULL,
  `profile_status` INT(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `rnd_order` (`rnd_order`),
  KEY `profile_status` (`profile_status`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `profile_photos` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `member_id` INT(11) DEFAULT NULL,
  `photo` VARCHAR(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `photo_status` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `member_id` (`member_id`),
  KEY `photo_status` (`photo_status`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Select query

SELECT profiles.id,
IFNULL(profile_photos.photo_status, 0) photo_status,
IFNULL(profile_photos.photo, 0) photo
FROM
`profiles`
LEFT JOIN profile_photos
ON (
  profiles.id = profile_photos.member_id AND profile_photos.photo_status = 1
)
WHERE (profile_photos.photo_status = 1 OR
profile_photos.photo_status is null)
GROUP BY profiles.id
ORDER BY photo_status DESC, profiles.rnd_order DESC;

My Solution (it don't work for the case where order by criteria of previous or next record is different)

Previous

SELECT profile.id,,
IFNULL(profile_photos.photo_status, 0) photo_status,
IFNULL(profile_photos.photo, 0) photo
FROM
          `profiles`
          JOIN profile_photos
            ON (
              profiles.id = profile_photos.member_id AND profile_photos.photo_status = 1
            )
AND (profile_photos.photo_status = 1 AND profiles.rnd_order < {actual_profile_id})
ORDER BY profile_photos.photo_status DESC, profiles.rnd_order ASC
LIMIT 1

Next

SELECT profile.id,
IFNULL(profile_photos.photo_status, 0) photo_status,
IFNULL(profile_photos.photo, 0) photo
FROM
          `profiles`
          JOIN profile_photos
            ON (
              profiles.id = profile_photos.member_id AND profile_photos.photo_status = 1
            )
AND ((profile_photos.photo_status = 1 AND profiles.rnd_order > {actual_profile_id}) OR (profile_photos.photo_status IS NULL))
ORDER BY profile_photos.photo_status DESC, profiles.rnd_order DESC
LIMIT 1

select s2.*, @nv NXV, (@nv := id) NNXV from (
select s1.*,
@pv PRV, (@pv := id) NPRV, (@rn := @rn + 1) RN
from (
SELECT DISTINCT profiles.id,
IFNULL(profile_photos.photo_status, 0) photo_status,
IFNULL(profile_photos.photo, 0) photo,
profiles.rnd_order
FROM
`profiles`
LEFT JOIN profile_photos
ON (
  profiles.id = profile_photos.member_id
  AND profile_photos.photo_status = 1
), (select @rn := 0)  RNT
WHERE (profile_photos.photo_status = 1 OR
profile_photos.photo_status is null)
GROUP BY profiles.id
ORDER BY photo_status DESC, profiles.rnd_order DESC) s1
) s2
order by RN desc;

see http://sqlfiddle.com/#!9/409b3/20

you will find the info you wanted in columns NXV and PRV

This fiddle http://sqlfiddle.com/#!9/409b3/47 shows the query for a specific profile id.

select p.*, pf.*,
       pmax.id, pmax.rnd_order,
       pmin.id, pmin.rnd_order
  from profiles p left outer join
       profile_photos pf
       on  pf.member_id = p.id
       and pf.photo_status = 1,
       profiles pmax,
       profiles pmin
  where p.id = 100024
    and pmax.rnd_order = (select ifnull(max(rnd_order),p.rnd_order) from profiles pm where pm.rnd_order < p.rnd_order)
    and pmin.rnd_order = (select ifnull(min(rnd_order),p.rnd_order) from profiles pm where pm.rnd_order > p.rnd_order)
;

Can you check this for performance?  (explain plan looks promising)


Does Log File contain both committed and uncommitted transactions? My question is related to SQL Server architecture. Mainly log file. In the picture below it says that when transaction is committed, log record in LOG CACHE will be moved to Log file. Does it mean that Log file contains only committed transactions?

Some sites also state that when COMMIT happens, then dirty page is moved from BUFFER CACHE into LOG CACHE first and afterwards into log file. So I do not understand what happens when COMMIT occurs. I will appreciate any help.

SQL Server uses write-ahead logging so that changes (committed or not) are always written to the log file prior to being written to any data file.

Records written to the transaction log do not necessarily reflect the entire page being modified.  Log records typically consist of compensation data that allow transactions to be rolled forward and back.  COMMIT flushes the log buffer along with a record of the commit to guarantee durability.  See the documentation.

In addition to commit, log buffers are flushed when they become full and during checkpoint operations.  The log may contain uncommitted transactions and it doesn't matter how long transactions run.  During crash recovery, all transactions from the last completed checkpoint are rolled forward from log records and then uncommitted transactions are rolled back. The database is then consistent, containing only committed transactions.

The above was converted from comments made by Dan Guzman


Delete Top VS Delete All I have a table with records logs.
Every few minutes  I delete old logs by Service.
Each deletion can have almost 10,000 records.
The question is whether to delete all the logs with delete top, a few logs in loop or deleting them all at once will be more effective?
I have indexes and there is no cascade on the tables.

Generally:

10k records is tiny. I use loops in a script to bulk delete becasue I want to keep transactions smaller, but I do deltes of 64 million rows each loop.

Generally:

Loops of TOP X, outside a transaction, are more effective than deleting all rows if you do not care about the order of deletes. Reason simply is that every delete is then a separate transaction. Particularly in staging environments when you can for whatever reason not use truncate, this can be good to keep the tx log smaller. It also avoids longer locking because, again, smaller transactions.

But 10k rows is tiny to start with.


SQL Server service automatically stops after starting I have a problem with my SQL Server 2014 Express instance. It worked fine until now. I can't get it working because SQL Server service automatically stops immediately after I start it.

I'm trying to start it from SQL Server Configuration Manager. When I open it I see that the SQL Server service is stopped, I re-run it and the service doesn't give me any error while starting. Then after pressing F5 to refresh the screen it's stopped again.

Here are some error logs from the starting operation. Can someone explain to me what happened? Can someone help me with solving this situation?

Error log 1
Error log 2

I have gone through your Error log file. As per your Error log file your System data has corrupted. Mainly in your case 'Model' database. as per your log error shown like

  66.2015-10-14 09:31:04.68 spid12s      Error: 9003, Severity: 20, State: 1. at line number 66 in your error log.

As per Gail Shaw Blog :-
If a model file is corrupt, then we need to restore a backup of model. Let's see what Books Online has to say about restoring model:

  "Restoring model or msdb is the same as for performing a complete
  database restore of a user database."

For Ref : - https://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/

If a model file is missing, we need to find out what happened to it. Has a drive not come online (or come online after SQL started)? Has someone accidentally renamed or moved the file? If we can make the model files accessible to SQL Server, in a similar fashion as described previously for the missing or inaccessible master files, then this will solve the problem.

Let's try starting SQL Server from the command line, with traceflag 3608

Sqlservr.exe –T3608

This will prevent recovery of all databases except model. This will  'Clearing tempdb database' and 'Starting up database 'model'.

Since the problem seems to be the clearing of TempDB.
let's go back to the beginning and try to restart SQL Server, but this time with traceflag 3609.

SQLServr.exe –T3609

This time SQL Server won't even start. We specified via use of traceflag 3609 that SQL Server should, on startup, recover TempDB from the existing files but not attempt to clear it.

you can also ref   http://www.dbforums.com/showthread.php?1203681-Error-9003-Severity-20-State-1 and https://support.microsoft.com/en-us/kb/822852#/en-us/kb/822852 for clarification.

NB:- In case of production databse , Before doing any troubleshooting you must consult with system Administrator and DBA Team. Always keep all backup file in safe zone.


64bit SQL Server with total virtual address space of 4GB. How could this be? My SQL Server 2012 Standard edition, 64bit has a total virtual address space of 4GB. Why is that?

it is a 64bit installation: print @@VERSION :

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (Intel X86)
    Dec 28 2012 19:06:41
    Copyright (c) Microsoft Corporation
    Standard Edition on Windows NT 6.2 <X64> (Build 9200: ) (WOW64)

virtual space of just 4GB

select total_virtual_address_space_kb from sys.dm_os_process_memory
4194176

(entire result of sys.dm_os_process_memory
http://screencast.com/t/2PN2PDQYpt )

The reason is because it isn't a 64-bit installation.

  Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (Intel X86)

Intel X86 is 32-bit. X64 is 64-bit.


In postgresql.conf, does the order of the listed parameters matter? I'm relatively new to the DBA field and I have read the official documentation/guides on this topic, but I apologize if my question seems weird.
I have deployed PostgreSQL 12 on my AWS instance and tried to install the pgaudit extension.
In the postgresql.conf configuration file you need to add shared_preload_libraries = 'pgaudit' and restart the PostgreSQL service to apply the change.
I added shared_preload_libraries = 'pgaudit' in the very end of the config file and restarted pg_ctl, which caused the service to crash. Postgresql started operating normally once I commented out the added line.
Later I found the shared_preload_libraries parameter in that config file, it was already there, located a few blocks above, but also commented out. Once I uncommented it and set its value to 'pgaudit', my postgresql service started working normally and I managed to create an extension.
My question is: does it really matter in what order you list the parameters? Why didn't it work when I added the line in the end of the file, but worked, when the line was somewhere in the middle?

If you have several times the same parameter in postgresql.conf, the last value will override the previous ones.
I normally advise keeping the postgresql.conf file as short as possible to prevent confusion. See here.
My guess is that you misspelled something on the line you added. To be sure, you can look at Postgres logfiles which should tell you exactly what was wrong. (see here how to find your Postgres logfile.)


Where is the database SERVICE NAME defined and what is the proper method for changing it? I'm researching the connection methods for an Oracle 12c database and I understand the difference between an SID and Service Name, but I cannot figure out where the service name is defined or how to change it.

Presumably it was named the same as the SID I typed in during installation, but can it be changed post-installation?  Is there a configuration file where that is done?

Thanks in advance!

Understanding Database Services

  The service name defaults to the global database name, a name
  comprising the database name (DB_NAME initialization parameter) and
  domain name (DB_DOMAIN initialization parameter).

You can find service information in ALL_SERVICES.

You can manage services with DBMS_SERVICE.

In an Oracle Restart or clustered environment, you can manage services with srvctl, which uses DBMS_SERVICE in the background.

No, you should not use the service_names parameter. That should be left to maintained by the above.

The default services should not be used by clients, and you should always create your own service(s) with the required properties.

Default Service Connections

  Your Oracle RAC database includes an Oracle database service
  identified by DB_UNIQUE_NAME, if set, or DB_NAME or PDB_NAME, if not.
  This default service is always available on all instances in an Oracle
  RAC environment, unless an instance is in restricted mode. You cannot
  alter this service or its properties. Additionally, the database
  supports the following two internal services:

  SYS$BACKGROUND is used by the background processes only

  SYS$USERS is the default service for user sessions that are not
  associated with any application service

  All of these services are used for internal management. You cannot
  stop or disable any of these internal services to do planned outages
  or to failover to Oracle Data Guard. Do not use these services for
  client connections.

The above is about RAC from the RAC documentation, but it is true for single instance environments as well.


Casesensitive vs. Caseinsensitive Collatesequences? I just started working with the SQLite database places.sqlite.

Fact is that this database uses case sensitive collate sequences making writing queries as funny as in Oracle.

I want to know, are there really any applications where the user wants case sensitive sorting.

Or is the default use of case sensitive sorting a heritage of the fact that binary sort order is cheaper than the other forms.

EDIT:

Just in case you are curious. SQLite can handle caseinsenitive compares. see this SO question

BTW what is the state of art with Oracle. We migrated our SQL databases to ORACLE at the time of 8i and never checked, if we can do it caseinsensitive today.

In oracle it is possible since 10.2. Take a look here Case-Insensitive and Accent-Insensitive Linguistic Sorts,so you might want to take a look again, 8i is a while ago now ....


Convert MySQL database from latin1 to utf8mb4 - and take care of German umlauts I am using 5.6.28 MySQL Community Server to host WordPress + ProPhoto blog in German language at CentOS 6.7 Linux server:

mysql> show tables;
+-----------------------+
| Tables_in_blog        |
+-----------------------+
| wp_commentmeta        |
| wp_comments           |
| wp_links              |
| wp_options            |
| wp_postmeta           |
| wp_posts              |
| wp_term_relationships |
| wp_term_taxonomy      |
| wp_terms              |
| wp_usermeta           |
| wp_users              |
+-----------------------+
11 rows in set (0.00 sec)

Then recently I have started developing a Russian language app and had to change MySQL settings to utf8mb4 encoding in /etc/my.cnf:

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
character-set-client-handshake=FALSE
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci

Now I have a minor problem with the first database that it is still shown as latin1:

mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | latin1                     | HOW TO CONVERT?
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

What is the best way to convert a latin1 database to utf8mb4 please?

And how to ensure that the German umlaut characters ( ä ö ü ) are converted properly in the process?

There are two possible questions here, and they have two different answers --

How do I make all new tables utf8mb4

It can be done (for one database) while creating a database:

CREATE DATABASE dbname
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE       utf8mb4_unicode_ci;

You can ALTER a database (similar syntax), but that only provides a default for future tables.

There are bugs relating to inheriting from the 'server' variables:  http://dev.mysql.com/worklog/task/?id=3811 , so I suggest that you simply be explicit on each database.

How do I convert existing latin1 tables

If you have a table declared to be latin1 and correctly contains latin1 bytes, and you would like to change all the char/text columns to utf8...

ALTER TABLE tbl CONVERT TO CHARACTER SET utf8mb4;

This changes the definition and actively changes the necessary bytes in the columns.

ALTER TABLE tbl MODIFY col1 ... CHARACTER SET utf8mb4;

is similar to the above, but works only one column at a time, and needs
exactly the right stuff in the MODIFY clause.  Hence, it would be quite tedious.  However, it is useful if you want to change only some of the columns.


I am trying to get the string between hyphen (-) which is before (+) upto next colon (:) 03/02/2020, 7:41 pm - +91 9999999999: At 7:10;
03/02/2020, 7:41 pm - + abcd : At 7:10;

Note: The string in between - and : is not of same length in each record. Please, help me with a solution applicable in such case.

SELECT txt,
       SUBSTRING(txt, CHARINDEX('-', txt), CHARINDEX(':', txt, CHARINDEX('-', txt)) - CHARINDEX('-', txt))
FROM test

or

SELECT txt,
       SUBSTRING(txt, CHARINDEX('- +', txt), CHARINDEX(':', txt, CHARINDEX('- +', txt)) - CHARINDEX('- +', txt))
FROM test

fiddle


Add fields to view from select (transpose fields) I've got a table with following structure:

CREATE TABLE test
(
  id integer,
  field character varying,
  value character varying
);

field     value
------    -----
field1    value1
field2    value2
field3    value3
...       ...

I need to transpose the field value as the fields in a view and the value of the field with be the "value" field's value. Example of what I need:

field1    field2    field3      ...
------    -----     ------      ----
value1    value2    value3      ...

I have no idea on how to start building the create view statement.

For the simple case you present, build an array from your sorted values and unnest one by one in the SELECT list

SELECT arr[1] AS field1
     , arr[2] AS field2
     , arr[3] AS field3
FROM  (SELECT ARRAY(SELECT value FROM test ORDER BY field) AS arr) sub;

Obviously, this is not dynamic, but only works for given fields. You did not actually ask for "dynamic" - except that you added the tag dynamic-sql.

For more complex operations use crosstab() from the additional module tablefunc. Detailed instructions:

PostgreSQL Crosstab Query

Still not "dynamic". A completely dynamic function is hardly possible since SQL demands to know the return type at the time of the function call. If the return type is dynamic, you need two steps:

build your query dynamically
execute it.

But there may be something for you here:

Dynamic alternative to pivot with CASE and GROUP BY


Getting the Date of Some Day of Last Week In learning how to get the date of a day of the week from the previous week (e.g. the date of Monday from the previous week), I found the following two expressions coming up commonly, where you change the very last number (e.g. 0=Monday) depending on which day you want:

SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 0)

SELECT DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), 0)

Both seem to return the correct Date 2017-05-29 when I run it today (2017-06-17).

My question is - how does this work?  It seems to be getting a count of weeks since 6 or 7 weeks after 1900-01-01? And why does it work with both a 6 and 7?

Also, are there edge cases one would need to be aware of where it might not give the expected results?

This takes advantage of the fact that 1900-01-01 is a Monday. So adding n number of weeks will also be a Monday.

Here are the datetime values that are being represented by the integers in that expression:

dateadd(week, datediff(week, 7 /*'19000108'*/, getdate()), 0 /*'19000101'*/)

Adding the number of weeks since 1900-01-08 (the week after the first Monday) to 1900-01-01 returns one week less than the current week, i.e. Monday of last week.

These three are equivalent:

select dateadd(week, datediff(week, 7 , getdate()), 0)
select dateadd(week, datediff(week, '19000108', getdate()), '19000101')
select dateadd(week, datediff(week, 0, getdate())-1, 0)

rextester demo: http://rextester.com/BQX59903


Detect if only one column is changing in a trigger I have an after update trigger that is queuing record ID's into another table for further processing at a later time. It uses inserted to get the ids of the records to process.

I'd now like to exclude certain records being queued to that table if the only change has been to one particular column. If any other columns beyond that one column are changed I still want to queue the record.

How can I identify if only one particular column has been changed?

I've had a look at UPDATE(), but it seems like I'd need to list out all the other 47 columns on the table, which I'd like to avoid.

COLUMNS_UPDATED as used in How to use COLUMNS_UPDATED to check if any of certain columns are updated? seemed promising, but I can't get it to work.
The column I want to exclude has the ColumnID 30. By my figuring that gives me an integer bitmask of 536,870,912 (SELECT POWER(2, 30 - 1)).

The calculator in Windows tells me that is correct to have the 30th bit set.

When I did an UPDATE to just the column I want to exclude COLUMNS_UPDATED() returned 0x000000200040. That to be gives a bit pattern that suggests two completely different columns were updated. Column Id's 7 (2^(7-1)=64) and 22 (2^(22-1)=2,097,152).

I'm not sure it it is worth pursing that approach. It seems like it would be an easy comparison to pickup any changes to other fields.

UPDATE:
I've resorted to using a CHECKSUM comparison other fields between inserted and deleted. I'd prefer not to use this approach as it could cause problems if other fields are added to the table in the future. There is also the chance that a hash collision could occur.

Subject to the limitations mentioned in the other answers, COLUMNS_UPDATED can be used to check if only one (or some) column(s) were targeted by the triggering UPDATE statement.

This can be useful where update statements are written to only mention columns that may be updated. The following script demonstrates:

Table and data

-- A table with an identity key and 48 other integer nullable columns
CREATE TABLE dbo.Test
(
    ID integer IDENTITY PRIMARY KEY,
    col01 integer NULL, col02 integer NULL, col03 integer NULL, col04 integer NULL,
    col05 integer NULL, col06 integer NULL, col07 integer NULL, col08 integer NULL,
    col09 integer NULL, col10 integer NULL, col11 integer NULL, col12 integer NULL,
    col13 integer NULL, col14 integer NULL, col15 integer NULL, col16 integer NULL,
    col17 integer NULL, col18 integer NULL, col19 integer NULL, col20 integer NULL,
    col21 integer NULL, col22 integer NULL, col23 integer NULL, col24 integer NULL,
    col25 integer NULL, col26 integer NULL, col27 integer NULL, col28 integer NULL,
    col29 integer NULL, col30 integer NULL, col31 integer NULL, col32 integer NULL,
    col33 integer NULL, col34 integer NULL, col35 integer NULL, col36 integer NULL,
    col37 integer NULL, col38 integer NULL, col39 integer NULL, col40 integer NULL,
    col41 integer NULL, col42 integer NULL, col43 integer NULL, col44 integer NULL,
    col45 integer NULL, col46 integer NULL, col47 integer NULL, col48 integer NULL
);

-- A single row of sample data
INSERT dbo.Test
(
    col01, col02, col03, col04, col05, col06, col07, col08, col09, col10,
    col11, col12, col13, col14, col15, col16, col17, col18, col19, col20,
    col21, col22, col23, col24, col25, col26, col27, col28, col29, col30,
    col31, col32, col33, col34, col35, col36, col37, col38, col39, col40,
    col41, col42, col43, col44, col45, col46, col47, col48
)
VALUES
(
    01, 02, 03, 04, 05, 06, 07, 08, 09, 10,
    11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
    21, 22, 23, 24, 25, 26, 27, 28, 29, 30,
    31, 32, 33, 34, 35, 36, 37, 38, 39, 40,
    41, 42, 43, 44, 45, 46, 47, 48
);

Trigger

The logic here is to construct the expected value of  COLUMNS_UPDATED when only col30 is targeted by the UPDATE, and to compare that with the value actually encountered. The trigger uses COLUMNPROPERTY to find the id of the column to account for possible future metadata changes. Repeating the logic for col30 would enable other columns to be tested at the same time.

CREATE TRIGGER Test_AU
ON dbo.Test
AFTER UPDATE
AS
BEGIN
    IF @@ROWCOUNT = 0 RETURN;
    SET ROWCOUNT 0;
    SET NOCOUNT ON;

    -- Zero @Mask to the same length as COLUMNS_UPDATED()
    DECLARE @Mask varbinary(128) =
        SUBSTRING(CONVERT(binary(128), 0x), 1, DATALENGTH(COLUMNS_UPDATED()));

    DECLARE
        @ColumnID integer,  -- metadata ID of the target column
        @Byte integer,      -- byte of @Mask to change
        @Work binary(1);    -- working value of the byte to change

    -- Find the object ID of the trigger's parent table
    DECLARE @OID integer;
    SELECT @OID = T.parent_id FROM sys.triggers AS T WHERE T.[object_id] = @@PROCID;

    /* Set @Mask bits for column(s) of interest */

    -- col30

    -- Find byte, bit, and bit value to change
    SET @ColumnID = COLUMNPROPERTY(@OID, N'col30', 'ColumnId') - 1;
    SET @Byte = 1 + (@ColumnID / 8);

    -- Retrieve the right @Mask byte and set the target bit
    SET @Work = SUBSTRING(@Mask, @Byte, 1);
    SET @Work = @Work | POWER(2, @ColumnID % 8);
    SET @Mask = CONVERT(varbinary(128), STUFF(@Mask, @Byte, 1, @Work));

    -- Test if the resulting mask matches COLUMNS_UPDATED
    -- If not, other columns were updated
    IF @Mask != COLUMNS_UPDATED() PRINT 'Other columns updated';
END;

Tests

Neither of these updates produce a message, since only col30 is targeted by the UPDATE:

UPDATE dbo.Test
SET col30 = col30;

UPDATE dbo.Test
SET col30 = 1;

All of these statements produce the "Other columns updated" message:

UPDATE dbo.Test
SET col30 = col30,
    col31 = col31;

UPDATE dbo.Test
SET col30 = 2,
    col31 = 3;

UPDATE dbo.Test
SET col31 = 4;


MYSQL REGEXP for text not between parentheses I'm looking to search a large MYSQL table for a string and only return results that aren't between parentheses. If my table looks like this:

|  ID   |Data                      |
|   1   |Some search term here     |
|   2   |Search term               |
|   3   |Search term (search term) |
|   4   |(Search term) search term |
|   5   |Here's the (search term)  |
|   6   |Never match this line     |

And I want to select the rows where "search term" (case-insensitive) appears, but not in parentheses, how can I write the REGEXP to achieve that?

IDs 1,2,3,4 should return. IDs 5 & 6 shouldn't.

The search term may include letters, numbers and punctuation (though it shouldn't need to support parentheses as they'll never be nested).

I hate regex(or dont get them) so:

SELECT id,data FROM table
WHERE CONCAT(REPLACE(SUBSTRING_INDEX(data,'(',1),'(',''),
REPLACE(SUBSTRING_INDEX(data,')',-1),')',''))
LIKE '%search term%';

SQL Fiddle


How to return script IDs not in policies I need to identify a list of scripts not in use via our mySQL database.
Active policies have a "created_by" value of "JSS" in the "policies" table. All Scripts are listed in the "scripts" table. Scripts used in all policies are listed in the "policy_scripts" table. "scrips.script_id" and "policy_scripts.script_id" are the same.

table: policies
column: policy_id
column: created_by

table: scripts
column:script_id
column: file_name

table: policy_scripts
column: script_id
column: policy_id

My Two attempts were:

    SELECT DISTINCT
    s.file_name
FROM
    policy_scripts AS ps
        LEFT JOIN
    scripts AS s ON ps.script_id = s.script_id
WHERE
    ps.script_id != ANY (SELECT
            script_id
        FROM
            scripts)
ORDER BY s.file_name;

and

SELECT
    COUNT(DISTINCT script_id)
FROM
    policy_scripts
WHERE
    script_id != ANY (SELECT
            script_id
        FROM
            scripts);

however I don't think this is working as intended... It also does not take into account active policies. I'm not sure how to add that in.

any help would be appreciated.

select script_id
from scripts s
    left outer join policy_scripts ps on ps.script_id = s.script_id
where ps.policy_id is NULL;


XML Network Call to Application from SQL Server 2005 We don't really have a DBA here, just a semi-experienced SQL Developer and me. I've only got about a year's experience myself and we're the only programmers for our small business.

We're trying to integrate a ticketing system in our billing software with a set of stored procedures and jobs that will help us automatically determine daily whether or not we need to change the status of an account so our Tech Support department knows to look into it.

The other developer asked me to look into SSIS as a way of exporting an XML file with calls to the billing software, but I'm not convinced we're on the right track. The API for the billing software does support XML calls, but we'd need to be able to make a network call from SQL Server to the proper port for the API. Both are on the same server and have tables which exist within the same database. We're trying to go through the API because we're concerned about allowing the primary database to make changes to the extremely convoluted billing database, as it could cause unforeseen problems and would not update the billing software's internal tracking system properly.

Is there a way to make the XML API network call from SQL Server? If not, is there a workaround that might allow us to go through the API like this?

The billing software is called Platypus by TuCows, though I doubt anyone's familiar with it and this server is running SQL Server 2005 for the moment, though we'll have 2012 implemented in another month or so.

Thanks so much for your time!

So one potential way of doing this would be to write your SQL Server query to output an XML string of the data you want to send by using FOR XML.

You could then create an SSIS package and use a Data Flow Task to save the XML to an XML file, meaning you're logging all the exports made.

A script task, following the success of the Data Flow Task, can then do the API call. You'd write standard C#, grabbing the file and calling the API method. You would then use the Task result variables to return Success/Failure based upon the response from the API.

However, you've said you're more of a C# Dev, so why not just write a stand alone App that does all this? It would be much more robust than SSIS!

Update

There is actually a Webservice task in SSIS that can apparently do what you'd require. However, I believe that if you're more comforatble developing in C#, that is the approach you should take.


Opening MySQL connection is sometimes very slow I have a very weird issue with an older MySQL server (v5.0.45) and a Java application.

From time to time getting a connection from the database takes 7 seconds, 15 seconds, 30 seconds etc (most of them are 15 seconds) or sometimes times out. This happens just from time to time. I don't see a pattern when it does happen. The rest of the time connections are created in 2-3 milliseconds.

The database is running on Linux CentOS 6.4. The servers that have issues while connecting are on Linux CentOS 7. I'm using the latest MySQL connector (currently at v5.1.38), Java 8, with a connection pool (be it that of Tomcat or c3p0) or with no connection pool (just opening and closing connections), server in load or with few clients, a few simultaneous connections or up to 100 connections. It's the same result.

Multiple applications on multiple servers connect to this database. Only on two of them this occurs, the rest have no issues, the network seems fine, the other machines are in the same network.

QUESTIONS

How can I debug this issue and find the cause?
Has anyone seen this behaviour before?

EDIT: We've norrowed down the issue to the actual servers. Don't know if it's a hardware problem or an OS/kernel/drivers one. Still investigating.

Slow connections could be caused by the amount of memory consumed by each DB Connection being allocated.

Back on April 24, 2012, I answered the post How costly is opening and closing of a DB connection?. In that post, I mentioned how there are many buffers connected to a single DB session.

If your Java application strictly closes DB Connections and opens new ones with out, think about deallocating multiple data buffers and communication packets connected to that old connection.

Combine that with the DB Connection entering in infamous TIME_WAIT state (See my post Too Many Connections) and you get Connections that linger in the OS for quite a while before its resources are released (even if mysqld closed the connection already).

You may need to decrease the size of your sort_buffer_size, read_buffer_size, and join_buffer_size.

If you have any queries that run multiple joins in a single statement, that connection will have multiple join buffers (See my post Fast alternative for "NOT IN"). In that event, you have two choices

Increase the join_buffer_size to make sure an entire join level fits in memory (requires more RAM in the DB Server)
Decompose multiple join query that joins more than 2 tables into individual joins between 2 tables


How to Design Name and Address with Multiple Addresses I have a names and addresses table:

 Name
 Address
 AddressType
 ID
 CustID

Sample:

 Bob Smith, 123 Main St, Corporate, 1, 1
 Bob Smith, 123 Main St, Home, 2, 1
 null, P.O. Box 123, Warehouse, 3, 1

and I have a customer table

 ID
 TaxID
 BusinessType
 etc

 1, 94-123456798, Consultant

The customer may have many addresses.

I need to query on specific address types and do CRUD operations.   For example, what is the corporate address?   Retrieve and edit the DBA and Corporate addresses.  Where is the inventory kept?

So my first inclination is to modify the customer table to add CorpAddressID, InventoryLocationID, etc.   There might be a slew of old and new addresses but certain addresses have to be designated clearly.

So then I thought I could just reference the address type -- Customer join Address where AddressType = Corporate -- I'm worried about doing this and I have no idea why.   Like having to hard code where AddressType = Corporate and relying on the Corporate designation being stored in the database.

I checked this out and it was similar but not close enough to help:

Data Warehouse Kimball Customer and Customer Address Dimensions

So, I ask, is there a compelling reason to not reference the name and address field IDs directly from fields such as CorpAddressID, DBAAddressID, etc?

Any thoughts or feedback would be greatly appreciated.

If there is something in the software or data entry that mandates choosing the Address Type before successful submission, then I don't think you'd need the Address Type ID because you could just index and query the Address Type column.

If the Address type being blank is a concern, then I'm not sure that an Address Type ID would help because you'd still be dealing with nulls or empty fields.


join to other table multiplied by loop foreign key in mysql i have 2 table one node and relation and Second store buy each node. my code work fine but when sum multiple buy for each node result multiplied by number of purchases .

table nodes

+-------------+---------------+-------------+
| ancestor_id | descendant_id | path_length |
+-------------+---------------+-------------+
|           1 |             1 |           0 |
|           1 |             2 |           1 |
|           1 |             3 |           2 |
|           1 |             4 |           1 |
|           1 |             5 |           2 |
|           1 |             6 |           3 |
|           1 |             7 |           4 |
|           2 |             2 |           0 |
|           2 |             3 |           1 |
|           2 |             5 |           1 |
|           2 |             6 |           2 |
|           2 |             7 |           3 |
|           3 |             3 |           0 |
|           4 |             4 |           0 |
|           5 |             5 |           0 |
|           5 |             6 |           1 |
|           5 |             7 |           2 |
|           6 |             6 |           0 |
|           6 |             7 |           1 |
|           7 |             7 |           0 |
+-------------+---------------+-------------+

table buy

 +-------------+---------------+-------------+
   |      userid |        amount |
   +-------------+---------------+-------------+
   |           2 |          1500 |
   |           7 |          2000 |
   +-------------+---------------+-------------+

mysql code

SELECT

    DISTINCT users.descendant_id  ,
    SUM(CASE WHEN ances.ancestor_id = buys_ances.userid THEN 1 ELSE 0 END) level_compress

    FROM webineh_prefix_nodes_paths as users

    join webineh_user_buys as buys on (users.descendant_id = buys.userid  )

    join webineh_prefix_nodes_paths as ances on (users.descendant_id = ances.descendant_id )

    join webineh_user_buys as buys_ances on (buys_ances.userid = ances.ancestor_id )

    WHERE  users.ancestor_id = 1

     and
    (SELECT SUM(g2.amount) as amount FROM webineh_user_buys g2 where  g2.userid = ances.ancestor_id     group by  g2.userid ) >= 1000

     and
    (SELECT SUM(g1.amount) as amount FROM webineh_user_buys g1 where  g1.userid = users.descendant_id   group by  g1.userid ) >= 1000

group by buys.userid ,ances.ancestor_id

result when in curent purchases data

users.descendant_id   |  users.ancestor_id   |      level_compress

2                     |                      |         1
6                     |                      |         2

i need to show ancestor_id compressed id

The issue of the amounts that are multiplied is easy to solve. You just have to use a derived table that does the calculation (group by) first and then join that (derived table) to the other one, that stores the hierarchical structure.

The derived table to use:

( SELECT userid,
         SUM(amount) AS sele_descendant_amount
  FROM webineh_user_buys
  GROUP BY userid
  HAVING SUM(amount) >= 1000
) AS buys

Then we can join it to the other table. You also need to join it twice and that's another issue that may lead to a less efficient query. Unfortunately MySQL doesn't yet have CTEs, so the derived table code has to be duplicated. But you'll get at least a start with this code.

Tested at SQLfiddle:

SELECT
    buys_d.userid,
    buys_d.sele_descendant_amount,
    COUNT(*) AS level
FROM
    ( SELECT userid,
             SUM(amount) AS sele_descendant_amount
      FROM webineh_user_buys
      GROUP BY userid
      HAVING SUM(amount) >= 1000
    ) AS buys_d
  JOIN
    webineh_prefix_nodes_paths AS users
      ON  users.descendant_id = buys_d.userid
  JOIN
    ( SELECT userid,
             SUM(amount) AS sele_descendant_amount
      FROM webineh_user_buys
      GROUP BY userid
      HAVING SUM(amount) >= 1000
    ) AS buys_a
      ON  users.ancestor_id = buys_a.userid
GROUP BY
    buys_d.userid, buys_d.sele_descendant_amount ;


Meaning of the number of days in an average age? I would like to know the average age of my users and did the following to do that:

# SELECT avg(age(birthday)) FROM "user";
                   avg
------------------------------------------
 33 years 10 mons 32 days 08:33:10.577946

What does the number of days mean? How can it be over 31 days?

I have 3746 records and I'm on the UTC time zone.

PS: I'm using Postgres 9.5.3

age() returns intervals. In SQL-92 "an Interval is an unanchored directional duration of the time line" [1]. They have two types due to different month lengths in Gregorian calendar (year to month and day to second). Oracle gives you an error message, if you try to do what you did.
Intervals can be positive and negative. They need to be anchored to have a precise meaning, like date + interval = date2. Taking averages of these values is ill-defined in general and trying it for age-values might still give you unexpected results (as it does).

So what is the meaning of a 3 month 32 days in Postgres? Well only the code can tell (or the one who has written it) for sure. I guess it means "advance one month, then 32 days". One can't convert days to months or vice versa.

How can it happen to be there? Average is sensitive to outliers, so if some rather large value is in there for days, it will have an influence. How are null values handled? Did some users specify dates in the future or gave unreasonable values? Is there some implicit conversion going on? Have Postgres developers created a special average function for intervals?

For your problem at hand I recommend using (sorry if this doesn't work, I don't have a database at hand):

select avg(extract(epoch from now()) - extract(epoch from birthday)) from "user";

This works for users of reasonable age, but if you had users from the 1700th,the answer would also depend on location, as the new calendar was adopted later in some countries.

Read the following book to get to know other quirks with dates.

[1] "Developing time-oriented database applications in SQL", Richard T Snodgrass, Morgan-Kaufmann, 1999. See his homepage, Pages 30-32.


Automatic seek on an index on a calculated column I have a table with a varchar field than I need to be unique, but it is too long for the nonclustered index that the uniqueness constraint requires, so I have added a computed column (either using LEFT or HASHBYTES) and based the uniqueness constraint on that.

CREATE TABLE [dbo].[TableWithLongField] (
    [Customer] [char](10) NOT NULL,
    [PrimaryKey] [varchar](88) NOT NULL,
    [LongField] [varchar](4096) NOT NULL,
    [LongFieldLeft] AS CONVERT(varchar(900), LEFT([LongField], 900)),
    CONSTRAINT [UQ_LongFieldLeft] UNIQUE ([LongFieldLeft]),
    CONSTRAINT [PK_PrimaryKey] PRIMARY KEY NONCLUSTERED ([PrimaryKey]),
    INDEX [IX_Customer] CLUSTERED ([Customer])
);

When I perform a query like

DELETE
FROM dbo.TableWithLongField
WHERE
    PrimaryKey <> @PrimaryKey AND
    LongField = @LongField

I had hoped that SQL Server would be intelligent enough to realise it has an index based on the field available and automatically use it. However, that doesn't seem to be the case, when I look at the execution plan or at the user_seeks on the index. I wondered if it was just calculating that the function would cost more than the whole-table scan it is currently performing, but I've added a few tens of thousands of rows and it hasn't made any difference.

It does use the index if I manually perform the same calculation in the query

DELETE
FROM dbo.TableWithLongField
WHERE
    PrimaryKey <> @PrimaryKey AND
    LongFieldLeft = LEFT(@LongField, 900)
    LongField = @LongField

but having to define the function in two places already seems bad, and will only get worse as I write more queries.

Am I expecting too much from SQL Server and my only option is to do this manually? Or is there something I can do to hint that the index is available for querying on the long field, without adding it explicitly to every query?

You’re basically adding a helper-predicate, and that’s not something the SQL engine will do out of the box most of the time. It does for LIKE, in that if you have WHERE col LIKE 'A%', then the query plan will show that it can seek on col >= 'A' AND col < 'b', but most others don’t get picked up.

It’s not a ton of extra work, because it worked out the column value when it populated the index, and the parameter only needs to be shortened once. But I get that you might have a lot of queries to change. Sadly, it’s probably just what you have to do.


delete var/lib/mysql folders after dropping database via mysql I am aware that you don't want to remove the innodb tables from /var/lib/mysql for a linux distro of mysql (I have Ver 14.14).  So I used the command
drop database mydb1;
drop database mydb2;
-- etc

but see that the folders mydb1 and mydb2 etc. are sill in /var/lib/mysql, and the .MYD, .MYI and .frm files are there in most cases.  Are the database folders safe to manually delete now?  I know the innodb files are very sensitive.

First, MySQL latest version is 8.x.x, so it's impossible for you to have 14.x, check that.
Second, maybe your data dir is not /var/lib/mysql. To check this variable run the following script and check the actual datadir for your instance.
mysql> SHOW VARIABLES LIKE '%dir%';
> +----------------------------+--------------------------------------------+
> | Variable_name              | Value                                      |
> +----------------------------+--------------------------------------------+
> | basedir                    | /usr/local/mysql-max-4.0.13-pc-linux-i686/ |
> | datadir                    | /usr/local/mysql/data/                     |

Also check for any errors when you run the DROP DATABASE sentence.


Poor performance on a 'leaderboard' query Consider the following 2 tables:

players:  | rooms:
id        | id
name      | type
room_id   | embed_code_id
points    |

My query needs to fetch the 100 players with the most points from a given room type and embed_code_id.

here's what I have currently:

    select `players`.`id`,
       `players`.`points`
from `players`
       inner join `rooms` on `players`.`room_id` = `rooms`.`id`
where `rooms`.`embed_code_id` = 678
  and `rooms`.`type` = 'single'
order by `players`.`points` desc, `players`.`id` asc
limit 100;

This query results in poor performance (1.5 - 10 seconds, depends on the parameters passed)

Notes:
* Both tables have about 1M records
* when i remove the ORDER BY clause, the time drops below 100ms
* I tried setting an index on players.points & players.id, which didn't help
* I tried setting an index on embed_code_id & type which also didn't help

The EXPLAIN output suggests Using index; Using temporary; Using filesort which i don't know how to fix:

1   SIMPLE  rooms   NULL    ref PRIMARY,embed_code_id,type  type    6   const,const 478797  100.00  Using index; Using temporary; Using filesort
1   SIMPLE  players NULL    ref room_id room_id 4   playgo.rooms.id 1   100.00  NULL

Can this query be more efficient or i'm missing some index?

Thanks

You need a combined index on rooms(embed_code_id,type).

Only one index can be used at a time. type is a poor index.


How to make a line/string to multiple records So, I want to create a teacher-degree (1:m) table. I have this example:

One professor has 3 degrees.

This is the actual string I have(multiple of these actually):

  B.A, 1983, University of China; M.B.A, 1999, University of Taiwan;
  Ph.D, 1999, University of Southern California,

The fields are: professorID, degreeType, degreeYear, Institute

The expected table data:

  Prof-13434, B.A, 1983, University of China

  Prof-13434, M.B.A, 1999, University of Taiwan

  Prof-13434, Ph.D, 2003, University of  California

That is my goal, I have tried everything but I cant figure anything out. Trying to go from one string to multiple records is harder than I thought.

Just to be clear:

Each degree has:

Position 1) Degree Type
Position 2) Year
Position 3) Univerity / College

Item 4) There can be more than 1 degree or only 1 degree.

Other Criteria:  Each entire section is separated by a ';' (semicolon) each subsection is separated by a comma.
No other commas are in the string?

Here's the code.

Declare @TestName as varchar(500)
Declare @Pos1 as int
Declare @Pos2 as int
Declare @Pos3 as int
Declare @Pos4 as int

Set @TestName = 'B.A, 1983, University of China; M.B.A, 1999, University of Taiwan; Ph.D, 1999, University of Southern California,'

---Start loop
--Get the position of the first 'comma'
Select @Pos1= PATINDEX('%,%',@TestName)

--Get the position of the second 'comma'
Select @Pos2= PATINDEX('%,%',Substring(@TestName, (@Pos1+1), (Len(@TestName)-(@Pos1+1))))

--Get the position of the end section.
Select @Pos3= PATINDEX('%;%',Substring(@TestName, (@Pos2+@Pos1+1), (Len(@TestName) )))

Select
SUBSTRING(@TestName, 1, @Pos1-1) as DegreeType,
lTRIM(SUBSTRING(@TestName, @Pos1+1, @Pos2-1)) as GradYear,
SUBSTRING(@TestName, @Pos1+@Pos2+1, @Pos3-1) as University

-- Reset the value of @TestName so you can loop start over.
Set @TestName = SUBSTRING(@TestName,@Pos1+@Pos2+@Pos3+1, Len(@TestName) - @Pos3+1)

--Shows you what is left.
Select @TestName
--Loop End

You'll still need to build loop and the above code is in SQL Server but it should translate across if you are using another database system.


How to restore MySQL database files without having a MySQL instance? After a crash of my PC I'm left with several databases (27Gb in total) that are left on my data drive, while my OS drive (Windows 7) is gone.
I've reinstalled the machine by now (Windows 10) and set up a new MySQL server (via installation of XAMPP). I would like to recover the old databases and add them to the new MySQL server.
What would be the best way to copy/move/restore these databases to the new installation?
The old MySQL install was version 5, but I have no idea what specific version. I could set up a new MySQL install version 5 if needed, but I'd prefer a solution that works regardless of version (within reasonable limits, of course).
I've browsed through two questions that are similar: How to restore MySQL database from Files and Can I restore a MySQL table from a file (non-sql) backup? but they don't address the possible difference in version.
Thanks!

Danblack already mentioned being restricted to one major difference, but after trying to copy data files back and forth I was not able to get my database up and running.
In the end I set up a Virtual Machine with Windows 7 and started installing MySQL versions starting from version 5 until I got to a working database.
Once my database was up and running in the VM, I could then export all schemas from there to be imported later on in my Windows 10 database.
Not the most elegant of solutions, but it worked in the end, and could help other people who are faced with the same situation.


how to set default value of column 0 when NULL is inseerted I have a table with a column which has default value to 0. However when null is inserted to this table, I want it to set it to default.

As the insert query is generic and used by other databases too, I cannot make any changes to the insert statement.

Can I have constraints or case statement on create table, so that default 0 value is inserted whenever null is passed.

If the NULL value is explicitly specified, you'll need to use a DML trigger to update NULL values to 0 after insertion.  This can't be done with a default constraint.


Inside stored procedure, set column names of returned query according to variable I have a query which I'd like to reuse, instead of having many different copies of it just for the sake of changing the column name.

The report (for example) uses the same query, but there are either dogs or cats in
the table, and if there are cats then it returns 'Nr of Cats', and if there are dogs then 'Nr of Dogs' as heading for the returned table.

declare @Animals table (
  AnimalType varchar(20), Name varchar(20)
  )

insert into @Animals(Type, Name) values('Cat', 'Garfield')

declare @AnimalType varchar(20)

set @AnimalType = (Select Top 1  AnimalType from @Animals )

select
    Case when @AnimalType = 'Cat'
           then 'Nr of Cats'
           else 'Nr of Dogs' = count(*)  from @Animals

You'll need to use dynamic SQL for this, if you truly want SQL Server to pass back results with variable column names.

By way of an example, consider the following:

Use tempdb to avoid any nasty surprises to existing objects:

USE tempdb;

Create a table to hold the sample data.  Since we are using dynamic SQL, we cannot use a table variable for this.

IF OBJECT_ID('dbo.Animals') IS NOT NULL
DROP TABLE dbo.Animals;

CREATE TABLE dbo.Animals
(
    AnimalType varchar(20) NOT NULL
    , AnimalName varchar(20) NOT NULL
);

INSERT INTO dbo.Animals (AnimalType, AnimalName)
VALUES ('Cat', 'Garfield')
    , ('Dog', 'Lucy');

GO

Create a stored procedure using dynamic T-SQL:

IF OBJECT_ID('dbo.DynamicResults') IS NOT NULL
DROP PROCEDURE dbo.DynamicResults;
GO
CREATE PROCEDURE dbo.DynamicResults
(
    @DogsOrCats varchar(3)
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @msg nvarchar(1000);
    IF (@DogsOrCats = 'Dog' OR @DogsOrCats = 'Cat')
    BEGIN
        DECLARE @cmd nvarchar(max);
        DECLARE @vars nvarchar(max);
        SET @vars = '@Type varchar(20)';
        SET @cmd = 'SELECT a.AnimalType
            , [NumberOf' + @DogsOrCats + 's] = COUNT(1)
        FROM dbo.Animals a
        WHERE a.AnimalType = @Type
        GROUP BY a.AnimalType;';
        EXEC sys.sp_executesql @cmd, @vars, @Type = @DogsOrCats;
    END
    ELSE
    BEGIN
        SET @msg = '@DogsOrCats must be either ''Dog'' or ''Cat''';
        RAISERROR (@msg, 14, 1) WITH NOWAIT;
    END
END
GO

Running the stored proc returns varying result sets, based on the value passed in as @DogsOrCats

EXEC dbo.DynamicResults 'Cat';

Returns:

+------------+--------------+
| AnimalType | NumberOfCats |
+------------+--------------+
| Cat        |            1 |
+------------+--------------+

EXEC dbo.DynamicResults 'Dog';

Returns:

+------------+--------------+
| AnimalType | NumberOfDogs |
+------------+--------------+
| Dog        |            1 |
+------------+--------------+


Windows Login can't access SQL Server even with SYSADMIN role I'm working on AD permissions and have removed my user account from the Domain Admins security group. Now I'm having difficult accessing my SQL Server database with my Windows login. The login is in the sysadmin server role group and has mapped permissions to databases on the server. Any ideas why I can't access the server itself?

I will say that if I add a security group that my Windows login is a part of and give that group read permissions...I can then access the server.

Any advice would be appreciated.

Thank you!

Create a regular account that goes in a group that has sysadmin on the database. Then restart your SQL Server management studio and log on using windows authentication. You can start SSMS using the "Run-as" to log on with a different account than you used to log onto your desktop

This gives you Separation of responsibilities between domain admins and database administrators.


Grouping over multiple data dimensions (pivot) I am logging an Events table, and want an output (as seen at the bottom of this answer), the issue is that I am getting the data in rows, but want them to be displayed column-wise.

The two dimensions are following:

Datetime values (per Day)
ServerId (per Server)

I think I have to group Datetime values by a time part (e.g. by minutes, or per hour) and retrieve a list of occourences of events per server, but I dont know how to write the query? Do I need a having by clause?

I've already written the create statements, the structure is as following:

CREATE TABLE [dbo].[events](
    [guidEventId] [uniqueidentifier] PRIMARY KEY,
    [dtStart] [datetime2](7) NOT NULL,
    [nDirection] [int] NOT NULL,
    [nServerId] [int] NOT NULL
)

INSERT INTO events
    ([guidEventId],[dtStart],[nDirection],[nServerId])
VALUES
    (newId(),'2013-08-01 00:01:00','0','1'),
    (newId(),'2013-08-01 00:02:00','1','1'),
    (newId(),'2013-08-01 00:03:00','0','1'),
    (newId(),'2013-08-01 00:04:00','1','2'),
    (newId(),'2013-08-02 00:01:00','0','1'),
    (newId(),'2013-08-02 00:02:00','1','2'),
    (newId(),'2013-08-02 00:03:00','0','2'),
    (newId(),'2013-08-02 00:04:00','1','2');

http://sqlfiddle.com/#!6/25e74

The end table should look something like that (daywise):

DateTime     | Server1 | Server2 |     Sum |
--------------------------------------------
2013-08-01   |       3 |       1 |       4 |
2013-08-02   |       1 |       3 |       4 |

The PIVOT operator may be of help. Take a look at Books Online

For example:

SELECT
    DtStart AS Date,
    ISNULL([1], 0) AS [Server 1],
    ISNULL([2], 0) AS [Server 2],
    ISNULL([3], 0) AS [Server 3]
FROM
(
    SELECT
        DtStart,
        nServerID,
        NDirection
    FROM dbo.events
) AS SourceTable
PIVOT
(
    SUM(NDirection)
    FOR nServerID IN ([1], [2], [3])
) AS PivotTable
ORDER BY
    DtStart ASC


SQL Express bottlenecks We have a client running SQL Express.

The line of business application appears slow at times and I am trying to diagnose the cause. I suspect it may be because of the Express version.

If the client was to upgrade it would involve significant cost so I need to be sure and have some metrics I can present as part of a business case.

How can I monitor SQL Server to identify if the limitation so the Express version are causing bottlenecks?

MajorVersion    ProductLevel    Edition ProductVersion
SQL2008 R2  SP2 Express Edition (64-bit)    10.50.4042.0

Ray Barley created a post on MS SQL Tips regarding the physical limitations of SQL 2008 R2.

Looking at the specs, it looks like Memory or CPU will be the indicators you need to use.

So what you need to do from this point is measure the CPU and Memory to see if you have issues with either or neither.

Pinal Dave gives great tips on his blog SQL Authority on figuring out if you have CPU pressure.

Tibor Nagy made a good post on finding memory bottlenecks too on MS SQL Tips.

Paul Randal on SQL Skills also talks about how to find the pain points and measure who your trouble children are.

Things to keep in mind is that it may not be the specs of SQL Express causing you issues exactly. It could still be indexes, statistics, disk speed, network, etc. That's why the DMV's are important, you can measure and find the pain points that you measure. One key note is that historical information is important in this regard and you are running SQL Express. You may need another instance of SQL Express to monitor your current one so you can store enough data to create a historical view to analyze. You can potentially export the data daily and then import to another environment to analyze so you keep your database small.

Another side option would be to install a free trial of Database Performance Analyzer / SQL Sentry / Redgate to monitor your instance for a short period of time as those typically watch the DMV's and collect similar data. I would expect within about a week or less of collecting those metrics you could identify where your pressure points are in that environment.


SQL Server sometimes uses a lot more cpu for query I have a stored procedure with a quite heavy SELECT statement that includes five joins and uses FieldName LIKE '%searchterm%' on multiple columns. This query usually finishes in about 2-5 seconds. But sometimes (at seemingly random points in time) the server goes into a state where the query takes ~120 seconds to finish. This state can last from a couple of minutes to several hours. I have indications that you can end the slow state by calling DBCC FREEPROCCACHE, but I'm not completely sure. I'm using SQL Server 11.0.3153.

I've used the profiler tool to trace the server. Most of the time the query requires 900 CPU and 500 000 reads, but in the slow state it's 100 000 CPU and 7.5*10^6 reads. Even when the server is in the slow state it only seems to affect connections made by my .NET application, I can still execute the stored procedure quickly via SQL Server Management Studio.

The query looks something like this:

SET @search = REPLACE(@search, ' ', '%');
SET @search = '%' + @search + '%';

SELECT
    ...
FROM
    Table1 t1,
    Table1 t2
    /* The real query uses 3 more joined tables left out here */
WHERE
    t1.id = t2.t1_id
    AND (
        (t1.col1 LIKE @search)
        OR (t2.col1 LIKE @search)
        OR (t1.id IN (SELECT t1_id FROM Table3 t3 WHERE t3.col1 LIKE @search)
        )
ORDER BY
    SomeCol DESC,
    SomeOtherCol DESC
OFFSET @row_offset ROWS FETCH NEXT @row_count ROWS ONLY

Can anyone tell me what's going on here? What can be the cause of the inconsistent behavior?

That sounds like it is using an inappropriate query plan. There are two obvious paths this query could take and it is presumably deciding between them depending on the values of @row_offset and @row_count: for a small offset it is probably better to scan SomeCol,SomeOtherCol in order and apply the complex filtering clauses (in the WHERE and JOIN ON clauses) to each row until enough have been found to satisfy @row_count. For many other circumstances this would be a terribly bad approach.

To be more specific we'd need details on those other tables, the indexes/keys, and the sort of size/distribution of each table's contents.

What I suspect is happening is a cached query plan is being used when that plan is inappropriate for the current input values. When you run DBCC FREEPROCCACHE the cache is purged so it is forced to create a new plan and use that.

You can try the RECOMPILE hint by appending OPTION(RECOMPILE) to direct SQL and WITH RECOMPILE on the stored procedure definition if it is a procedure rather than an ad-hoc query - this tells SQL Server not to bother checking the procedure cache and always generate a fresh plan. This of course means that you lose the benefit on occasions where using a cached plan instead of having to reassess stats and replan would have been faster - this may be an acceptable tradeoff but obviously you need to benchmark and make sure you are not degrading overall performance more than is acceptable (if so you'll need a different strategy)).


Confusion about CPU time and Elapsed Time Calculation I have already searched on different online forums already, but I haven't got any clear answer on this.

The problem is I have a query which has CPU time greater 4 times greater than Elapsed time. I also have a query in which Elapsed time is greater than CPU time. I am not able to understand why is it so. Can you please shed some light, what actually happens in both cases?

An example is my query has CPU Time 4000ms while Elapsed time is just 1000ms and one query has elapsed time 2ms while CPU time is 0ms.

According to following post:

https://stackoverflow.com/questions/6419312/sql-server-query-tuning-why-cpu-time-is-higher-than-elapsed-time-are-they-rel

higher CPU time and lower elapsed time is due to hyper-threading or multi cores utilization by SQL server but I am not able to find any authentic information regarding that. Another confusion is the reverse case. How is it possible that elapsed time is for example 4ms while CPU time is 0ms?

Any detailed answer regarding this would be highly appreciated.

If a query has more CPU time than elapsed time that means that it contained a zone that executed in parallel. The ratio of CPU Time to elapsed time depends on many factors including the number of CPUs available to the query, what the query waited on and how long those waits were (partially depends on server workload), and how efficiently the query is able to take advantage of parallelism. For an example of the last point reference Amdahl's Law:

For example, if a program needs 20 hours using a single processor core, and a particular part of the program which takes one hour to execute cannot be parallelized, while the remaining 19 hours (p = 0.95) of execution time can be parallelized, then regardless of how many processors are devoted to a parallelized execution of this program, the minimum execution time cannot be less than that critical one hour.

Many queries will have zones that run in parallel and zones that run in serial. If most of the work is done in the parallel zones and the work is evenly distributed among schedulers then you might see a high CPU time to elapsed time ratio.
Below is a bit of a contrived example of a query that can parallelize extremely efficiently in SQL Server. First note that the machine I'm testing against has 4 cores and all of them are available to the query. From Task Manager:

From SQL Server:
SELECT cpu_count -- result of 4
FROM sys.dm_os_sys_info;

Now I'll put 100 rows into one temp table and 100k rows into the other:
CREATE TABLE #OUTER_TABLE (ID INT NOT NULL);

-- insert 100 integers from 1 - 100
INSERT INTO #OUTER_TABLE
SELECT N
FROM dbo.getNums(100);

CREATE TABLE #INNER_TABLE (COL VARCHAR(8000));

-- insert 100000 rows
INSERT INTO #INNER_TABLE
SELECT REPLICATE('Z', 800)
FROM dbo.getNums(100000);

SET STATISTICS TIME ON;

Here is the query that I'm going to test with:
SELECT ot.ID, it.COL
FROM
(
    SELECT TOP 100 ID
    FROM #OUTER_TABLE
) ot
CROSS APPLY (
    SELECT MAX(COL) COL
    FROM #INNER_TABLE
    WHERE COL <> CAST(ot.ID AS VARCHAR(8000))
) it
 -- TF 8649 is only used for demonstration purposes and should not be used in production
OPTION (MAXDOP 4, QUERYTRACEON 8649);

It's a bit of a mess so let's walk through it. Based on the data in the tables that query will return all 100 rows from #OUTER_TABLE along with any value from #INNER_TABLE. However, I have not made this information available to the query optimizer so it will do a lot of work to get the result that we're expecting. Overall the query is silly but what's important is how the query executes. Here's a screenshot:

I'm going to skip over why I wrote the query that way because it isn't relevant to the question. However, one thing to note is that the 100 rows from #OUTER_TABLE are split into four threads with 25 rows per thread. Each thread does a full scan of #INNER_TABLE per row, so 4 threads do 25 scans each. The part of the query on the inner part of the loop does almost all of the work as you might expect (the outer part scans 100 rows but the inner part scans 100 * 100000 = 10000000 rows).
When I run the query with MAXDOP 4 (using all four cores) I get the following results from SET STATISTICS TIME ON:

SQL Server Execution Times:
CPU time = 24500 ms,  elapsed time = 6357 ms.

Running with MAXDOP 2 (using just two cores) gives the following results:

SQL Server Execution Times:
CPU time = 23422 ms,  elapsed time = 11864 ms.

Running with MAXDOP 1 (not in parallel) gives the following results:

SQL Server Execution Times:
CPU time = 23313 ms,  elapsed time = 23361 ms.

As you can see, the query can very efficiently take advantage of parallelism. Most queries in the wild will not have such a relatively high CPU time to elapsed time ratio (after factoring in DOP).
For an example of when the elapsed time can be greater than CPU time consider the time that it takes to send the results to a client, such as to a SQL Server Management Studio results grid. The time to send those results will be included in the elapsed time but won't factor that heavily into CPU time. The work that the server does to read the results from memory or disk will contribute to both CPU time and elapsed time. The time spent sending results to the client is one type of wait event (usually ASYNC_NETWORK_IO). There are many different reasons why a SQL Server query could be waiting and not all of them affect CPU utilization in the same way.
Suppose I run this query:
SELECT * FROM INNER_TABLE;

Getting the results to the client results in a CPU time of 94 ms and an elapsed time of 749 ms. I can use sys.dm_exec_session_wait_stats to see the wait events for the session:
╔═══════════════════════╦═════════════════════╦══════════════╦══════════════════╦═════════════════════╗
║       wait_type       ║ waiting_tasks_count ║ wait_time_ms ║ max_wait_time_ms ║ signal_wait_time_ms ║
╠═══════════════════════╬═════════════════════╬══════════════╬══════════════════╬═════════════════════╣
║ ASYNC_NETWORK_IO      ║               19444 ║          662 ║               12 ║                  67 ║
║ MEMORY_ALLOCATION_EXT ║                  37 ║            0 ║                0 ║                   0 ║
╚═══════════════════════╩═════════════════════╩══════════════╩══════════════════╩═════════════════════╝

I can also run the query in a different session without returning the result set to the client. In one test this took 47 ms of CPU time and 60 ms of elapsed time. Here are the wait sessions from that query which are quite different from the other one:
╔═══════════════════════╦═════════════════════╦══════════════╦══════════════════╦═════════════════════╗
║       wait_type       ║ waiting_tasks_count ║ wait_time_ms ║ max_wait_time_ms ║ signal_wait_time_ms ║
╠═══════════════════════╬═════════════════════╬══════════════╬══════════════════╬═════════════════════╣
║ ASYNC_NETWORK_IO      ║                 185 ║            2 ║                0 ║                   1 ║
║ SOS_SCHEDULER_YIELD   ║                   7 ║            0 ║                0 ║                   0 ║
║ MEMORY_ALLOCATION_EXT ║                  40 ║            0 ║                0 ║                   0 ║
╚═══════════════════════╩═════════════════════╩══════════════╩══════════════════╩═════════════════════╝


Does Partition Creation Order Matter? I'm in a situation where I have taken over a very large db that is primarily a single table that is partitioned.  Since it hasn't been partitioned for a while, I need to re-start the partitioning, but it is a 24/7 business and it can't be out for a few hours while SPLITing the older partitions.

If I was to create the new partition for the end of this month, then go back and SPLIT the previous partitions - is there a problem with doing them out of order, other than the partition id being in the wrong order?

Is there any performance or maintenance differences if the order of the partition id is not in sequence with the partition key order?

...if the order of the partition id is not in sequence with the partition key order

SQL Server always maintains partition ids in partition boundary order. No matter how you split or merge partitions, the order of boundary values always matches the order of partition ids. SQL Server renumbers as necessary.

Concerning performance of the split operation itself, you will want to organize things so that the minimum (ideally, none at all) physical data movement occurs. This typically involves careful thought and testing in advance, using SWITCH and CREATE INDEX…WITH DROP_EXISTING = ON.

Review the following all by Dan Guzman:

Table Partitioning Best Practices
Remediating a Table Partitioning Mess
Move a Partition to a Different File Group Efficiently

If you need help designing the process, ask a new question with DDL for the table, indexes, partitioning scheme, function, and file groups. Also include a clear representation of the start and end states i.e. which data should go where.


Creating database/tables in SequelPro (or MySQL) from External FTP Server I just started getting a backup file (*.7z) from the folks that run our database once a week. They're sending it to me through an FTP server (I use FileZilla) -- but the file is pretty huge: 5.8 billion bytes. I could download the file to my computer, but it would probably take about seven hours to do so.

Can somebody help walk me through if it's possible to grab their .7z file through MySQL (or specifically SequelPro would be amazing, since that's what I use) from their server, so that I don't have to download it every time? I've never logged on to use MySQL before from anything but my 'root' login or local server.

If somebody could tell me the steps I need to take, it'd be very helpful! Would be happy to provide more information if any more is needed.

Thanks, everyone!

What you do depends heavily on what you're wanting the end result to be, but there is almost certainly not a way to do what you are thinking of... and if you examine the content of the files, the reason for this should become apparent.

The .7z file format is a compression+container format similar to zip or tar+gzip (tgz) that supports one of the highest compression ratio lossless compression formats in common use, LZMA2, among others.

Almost certainly, the file contains a heavily-compressed copy of the output from mysqldump of your database, either with a single file, a file for each schema, or a file for each table, depending on how they're making the backup.  The potential compression ratio can easily exceed 10:1, so you could be dealing with 52GiB or more of raw data in each of those weeklyh files.

MySQL is a database server (RDBMS) and SequelPro is a MySQL client application, so neither of those things would be able to directly do anything natively with the files sitting in the compressed format, and neither of them is going to fetch the file from the ftp server on its own and do anything useful with it.

If you were to download it and then uncompress it, you should be able to restore the backup onto your own local MySQL server and have a queryable copy of your data as it stood when the last backup was made -- though it would likely take many, many hours... then you could examine that data with SequelPro connected to your local MySQL server... but you're going to have to download that file somewhere, then uncompress and extract the contents, and then either review them with your eyeballs (mysqldump backups are typically human-readable, they consist of the DDL necessary to recreate the empty tables, followed by the DML to insert the existing rows int he tables) or load the onto a local MySQL server if you wanted to actually do something useful with what's in the file.

Ultimately, you'll need to download these files... but to where?

You could automate the download of the file from FTP with a cron job on the same machine as your local MySQL development/test server (or on a different machine) and you could even automate the decompression and restoration of that backup onto your local MySQL server, but that wouldn't be "MySQL" itself doing the downloading or decompressing... that would be other processes that might (or not, depending on what hardware and other resources are available) be running on the same machine -- but this is done at arms-length from the MySQL process itself... not "through MySQL."

If that's what you're asking how to do, that's more of a system administration question than a DBA question.  The last step in that process would be piping the extracted data into the mysql command line client for restoration of its content onto a local server... but before you can consider that, you'll need to examine how the files are structured and consider what you're wanting to accomplish with these files.


Exclusion constraint generates: ERROR: operator &&(anyrange,anyrange) is not a member of operator family "range_ops" How come when I try to create an exclusion constraint with GIST,

CREATE TABLE foo (
  a tsrange,
  EXCLUDE (a WITH &&)
);
ERROR:  operator &&(anyrange,anyrange) is not a member of operator family "range_ops"
DETAIL:  The exclusion operator must be related to the index operator class for the constraint.

An EXCLUSION constraint with && (Overlaps) needs to be GIST or SP-GIST because the default index is btree and its operator class does not support && (Overlaps). From the docs on Interfacing Extensions To Indexes

  GiST indexes are more flexible: they do not have a fixed set of strategies at all. Instead, the “consistency” support routine of each particular GiST operator class interprets the strategy numbers however it likes. As an example, several of the built-in GiST index operator classes index two-dimensional geometric objects, providing the “R-tree” strategies shown in Table 37.4. Four of these are true two-dimensional tests (overlaps, same, contains, contained by); four of them consider only the X direction; and the other four provide the same tests in the Y direction.

You can use the GIST index on an exclusion constraint with,

CREATE TABLE foo (
  a tsrange,
  EXCLUDE USING GIST (a WITH &&)
);

The operator class

btree supports only <, <=, =, >, >=
gist and sp-gist support far more. And, gin may also one day support being used in an EXCLUSION constraint.

So long as the default GIST operator class is properly defaulted for the type you're using it should work: it's not for inet and cidr

See also,

Why does a CIDR or INET index require me to specify the index's Operator Class?


Why am I getting different results from a query vs. a view The base problem I'm facing is that I need the latest record for each item.

A little setup... MySQL 5.6.14.

I need to create two views (because MySQL won't let me have a subquery in a view).  My first query sets up the data like this.

select
    `inventoryrecords`.`inventoryrecordid` AS `inventoryrecordid`,
    `inventoryrecords`.`logicaldeviceid` AS `logicaldeviceid`,
    `inventoryrecords`.`passrfid` AS `passrfid`,
    `inventoryrecords`.`tagepc` AS `tagepc`,
    `inventoryrecords`.`currentstate` AS `currentstate`,
    `inventoryrecords`.`statedateutc` AS `statedateutc`,
    `inventoryrecords`.`ownerobjectid` AS `ownerobjectid`,
    `inventoryrecords`.`ownerobjecttype` AS `ownerobjecttype`
from
    `inventoryrecords`
where
    1
order by `inventoryrecords`.`statedateutc` desc

Then I can restrict everything to just the last record for each TagEPC with my "real" query like this.

select
    `lastinventoryrecords_step1`.`inventoryrecordid` AS `inventoryrecordid`,
    `lastinventoryrecords_step1`.`logicaldeviceid` AS `logicaldeviceid`,
    `lastinventoryrecords_step1`.`passrfid` AS `passrfid`,
    `lastinventoryrecords_step1`.`tagepc` AS `tagepc`,
    `lastinventoryrecords_step1`.`currentstate` AS `currentstate`,
    `lastinventoryrecords_step1`.`statedateutc` AS `statedateutc`,
    `lastinventoryrecords_step1`.`ownerobjectid` AS `ownerobjectid`,
    `lastinventoryrecords_step1`.`ownerobjecttype` AS `ownerobjecttype`
from
    `lastinventoryrecords_step1`
group by `lastinventoryrecords_step1`.`tagepc`
order by `lastinventoryrecords_step1`.`statedateutc` desc

When I try to select * from the "real" view, I do not get the data I expect.  However when I run the query with the sub-query in a window I do.

select * from (
lastinventoryrecords_step1 )
group by tagepc
order by statedateutc desc

yields incorrect data whereas

select * from (
    select
        inventoryrecordid,
        logicaldeviceid,
        passrfid,
        tagepc,
        currentstate,
        statedateutc,
        ownerobjectid,
        ownerobjecttype
    from
        inventoryrecords
    where
        1
    order by statedateutc desc ) as t1
group by tagepc
order by statedateutc desc

yields the right data.

What in the heck is going on?  What am I missing?

You're using GROUP BY in a non-deterministic way. You GROUP BY tagepc, but what value for the other columns do you expect?

MySQL permits this kind of query, but MySQL chooses a row from the group arbitrarily. In practice, it tends to be the first row in the group, as they are stored physically.

But in the case when you query through the view, the view stores its results in a temporary table, probably in a different physical order than the data in the original table.

Note that http://dev.mysql.com/doc/refman/5.6/en/create-view.html says:

  ORDER BY is permitted in a view definition, but it is ignored if you select from a view using a statement that has its own ORDER BY.

So the query run by the view to populate the temp table ignores your view's ORDER BY, and since you used GROUP BY it probably takes values from arbitrary rows in the underlying table.

How to fix this? Stop running ambiguous GROUP BY queries. Any column referenced in your select-list should either by (a) listed in the GROUP BY clause, or (b) inside a grouping function like MAX() or something.

I don't know what you expect this query to do, or why you're using GROUP BY in the first place. So I can't offer any more specific advice.

Re your comment:

Thanks for the clarification. This is a pretty common type of SQL question. I created the tag greatest-n-per-group on StackOverflow and now it has close to a thousand questions. The tag exists on dba.stackexchange.com, but with fewer questions so far.

Here's a solution:

SELECT i.*
FROM inventoryrecords AS i
JOIN (SELECT TagEPC, MAX(StateDateUTC) AS StateDateUTC
      FROM inventoryrecords
      GROUP BY TagEPC ORDER BY NULL) AS maxi USING (TagEPC, StateDateUTC);


Does this mean the 'public' has full select, insert, update, and delete access? I'm trying to create a new DB user in SQL Server 2008 R2 that can only select from one SQL View.  But no matter what I try when I login as this new user I can select from any table.  I ran this query:

select  princ.name
,       princ.type_desc
,       perm.permission_name
,       perm.state_desc
,       perm.class_desc
,       object_name(perm.major_id)
from    sys.database_principals princ
left join
        sys.database_permissions perm
on      perm.grantee_principal_id = princ.principal_id
WHERE princ.name = 'public'
AND object_name(perm.major_id) = 'User_Table'

And get these results:

name    type_desc       permission_name     state_desc  class_desc          (No column name)
public  DATABASE_ROLE   DELETE              GRANT       OBJECT_OR_COLUMN    User_Table
public  DATABASE_ROLE   INSERT              GRANT       OBJECT_OR_COLUMN    User_Table
public  DATABASE_ROLE   REFERENCES          GRANT       OBJECT_OR_COLUMN    User_Table
public  DATABASE_ROLE   SELECT              GRANT       OBJECT_OR_COLUMN    User_Table
public  DATABASE_ROLE   UPDATE              GRANT       OBJECT_OR_COLUMN    User_Table

Does this mean no matter what anyone that logs in into the database will have full access based on the 'public' permissions?

This means that if the user has not had permissions explicitly set for the securable (or inherited from permissions explicitly set) that public has permissions for, then those will be applied.  In other words, if you have User1 with DELETE permissions denied on User_Table, then User1 will not be able to delete data from that table.

Please see BOL's reference on Database-Level Roles:

  public Database Role
  Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object.

Example

use TestDB;
go

create login Login1
with
    password = 'password',
    check_policy = off;
go

create user User1
for login Login1;
go

-- this will return 1 (meaning, yes it is a role member)
select is_rolemember('public', 'User1');

-- let's do a test to show the above theory
create table SomeTable
(
    id int identity(1, 1) not null,
    SomeText varchar(30) not null
        default replicate('a', 30)
);
go

insert into SomeTable
values(default);
go 10

-- give the public role permissions to SELECT on SomeTable
grant select
on SomeTable
to public;
go

-- this will be successful, because User1 is part of public
execute as user = 'User1';
go

select *
from SomeTable;

revert;
go

-- create a new role to deny SELECT on SomeTable
exec sp_addrole 'Role1';
go

deny select
on SomeTable
to Role1;
go

-- add User1 to this new role
exec sp_addrolemember 'Role1', 'User1';
go

-- this will not be successful because User1 now has been denied SELECT on SomeTable
execute as user = 'User1';
go

select *
from SomeTable;

revert;
go


Query to back up a PostgreSQL database I made a CRUD web application that uses a small PostgreSQL database. It is a donors database with the columns name, telephone, blood type, etc.

I have some SELECT and INSERT INTO queries, but now I need a query to back up the database. Nobody has asked about it on this site before, so I think it can't be done, or else I have to make a script using pg_dump or a maintenance plan).

Is it possible to write a SQL query to perform a PostgreSQL database backup?

PostgreSQL does not generate a database backup using a query as SQL Server does (ex: backup database [db_name] to disk...)! It is a different RDBMS; you can't simply do something that other one does, just because you want!

There are 2 methods to take backups from Postgres as commented before. Using pg_dump, you can produce a full sql file including all DDL and DML statements to recreate your database in another place (or restore):

pg_dump -Fp -v -h YOUR_HOST -U YOUR_LOGIN -d DATABASE_NAME -f your_file_name.sql

PostgreSQL pg_dump documentation

Or you can take physical backups by copying the pg_data folder.

It's up to you decide!


Dropped tables are not going to Recycle Bin To start, I am a student and just getting my feet wet with Oracle.

Quick history

Virtual Box XP running Oracle express with SQL Developer.
Created a user and tablespace for myself. (Key here is I'm not on System)
I have checked to make sure my Recycle Bin is active with

  SHOW Parameter recyclebin;

command.
I have also tried alter session to on.

Long story short Create table, add some data, drop table with standard drop command with no purge at the end and table does not go to recycle bin and hence can't use the flashback command to retreive it. Any idea as to why my recycle bin would not be functioning as it should, or am I missing something obvious? I am very green so assuming the latter :)
Any feedback would be appreciated!

Well with some digging on the Doc for Oracle 11g Express, it appears the flashback functionality is not supported. So this would be a good indication of why I'm having problems!!


Database transaction consistency on PostgreSQL In a table with three columns (serial id, varchar name, varchar email) and having two different transactions executing concurrently as the ones bellow:

Transaction 1:

BEGIN;
INSERT INTO public.users (name, email) VALUES ('tart', 'tart@warm.com');
INSERT INTO public.users (name, email) VALUES ('tart123', 'tart123@warm123.com');
COMMIT;

Transaction2:

BEGIN;
INSERT INTO public.users (name, email) VALUES ('raspy', 'raspy@sun.com');
INSERT INTO public.users (name, email) VALUES ('raspy123', 'raspy123@sun123.com');
COMMIT;

Is it possible that the outcome becomes the following?

1 | tart     | tart@warm.com
2 | raspy    | raspy@sun.com
3 | tart123  | tart123@warm123.com
4 | raspy123 | raspy123@sun123.com

serial is defined as such in PostgreSQL documentation:

  The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases).

They also provide this warning:

  Because smallserial, serial and bigserial are implemented using sequences, there may be "holes" or gaps in the sequence of values which appears in the column, even if no rows are ever deleted. A value allocated from the sequence is still "used up" even if a row containing that value is never successfully inserted into the table column. This may happen, for example, if the inserting transaction rolls back. See nextval() in Section 9.16 for details.

And if you go over to nextval documentation you get:

  Advance the sequence object to its next value and return that value. This is done atomically: even if multiple sessions execute nextval concurrently, each will safely receive a distinct sequence value.

The sequence is a shared object, among all your content. Each call to INSERT will by default call the underlying sequence nextval and will provide one value, irrespective to what happened in the transaction just before. So the value will just depend exactly on the order of operations, and not which transaction started first or things like that.

You could as well get this result (depending on what else is happening elsewhere in your DB at the same time):

 1 | tart     | tart@warm.com
 7 | raspy    | raspy@sun.com
13 | tart123  | tart123@warm123.com
42 | raspy123 | raspy123@sun123.com

Or

 1 | tart     | tart@warm.com
 3 | raspy    | raspy@sun.com
 2 | tart123  | tart123@warm123.com
 4 | raspy123 | raspy123@sun123.com

(which is probably what you expected, but has no specific guarantee to happen).

A sequence just guarantees you to "never" get the same value in two separate places, it does not guarantee lack of holes or monotony in increments, especially accross multiple transactions.


Setup an aerospike high-availbilty cluster? Are there any guide to set up an aerospike HA? I had search google but there was no possible result in return.

Look like Aerospike is not really popular in common.

P/s: Lab purpose so there only need 2-3 node/host

Aerospike's HA setup is one of the easiest. No need to setup sharding or failover policies. Everything is automatically taken care of. You just need to configure the nodes to see each other and boot them. I suggest to go with 'mesh' mode configuration. Ref: https://www.aerospike.com/docs/operations/configure/network/heartbeat/index.html


I need help to migrate denormalized table content to normalized form- MS Access I have a staff database where we are storing information in Normalized form. There are 3 entity tables and 2 mapping tables connecting these with m-n relationships

However, for ease of collecting this information, we are gathering this in a flatfile in excel.(Employeeid, Fname, LName, ITskills(in multiple rows), projectName)

Our plan is to import this flatfile into MS Access into a Staging Table and then write a query to read the staging table and insert the equivalent records in the 5 tables- and creating the identifier keys systemically. (Employee, Skills, Project, employeeSkillsMapping, skillsProjectMapping)

We tried converting from flat-file to 5 tables in excel manually and importing- getting very challenging to implement the relationships. Any suggestions/ links on how to get this done- how will the keys get generated systemically in all the tables.

I don't really have any links, but I can give a general how-to of this:

After importing into Access:
- A series of update scripts (or a single procedure, really) that copy the data into the appropriate tables
- A purge statement clearing out the temp table for the next upload.

It sounds really simple, and it (actually) shouldn't be too difficult, but without knowing your table structure, it's hard to give true specifics.

Are the skills and projects using defined language? if so, this greatly simplifies the job.


module_end extended events duration in microseconds? There is no description for the duration event field for the module_end event and I'm struggling to find any additional documentation.

I interpreted this as milliseconds but looking at some of the results this would be incorrect as I have an execution lasting 121 days. Is this duration represented in microseconds?

It is in microseconds.  See the picture attached here.


Combine 2 SQLite result sets to calculate percentage I have a single sqlite table that I'm trying to pull two resultsets from and then calculate the percentage.
Table columns:

email
Location
Clicked?
Test#

Test# 123 has a list of users (identified by email address) who clicked a certain link, Test# 456 includes the same people who clicked in test#123, but with only a subset of the users from Test# 123 clicking a second time. The goal is to pull the percentage of those who clicked a second time in Test#456.

Query 1: (dividend) to calculate those who clicked a second time is:

SELECT Location, count(*) as "rClicks" from table
WHERE Test# = "456" and "Clicked?" = "Yes" and email IN (
    SELECT email from table
    WHERE test# = "123" and "Clicked?" = "Yes")
GROUP BY Location

Query 2: (divisor) to calculate the total who clicked on the first test is:

SELECT Location, count(*) as "Clicks" from table
WHERE test# = "123" and "Clicked?" = "Yes"
GROUP BY Location

I've tried all sorts of combinations, the most promising being an INNER JOIN on the two result sets, but I keep getting 0.0 for the percentage.

SELECT query1.Location, query1."rClicks" / query2."Clicks" * 100.0 as "Percentage"
FROM (query 1) repeats
INNER JOIN
(query 2) previous
ON repeats.Location = previous.Location

Any suggestions for getting this right?

A fellow redditor provided me with the assistance to answer my question.  The following is the end result that did what I needed:

select t.location, printf("%.3f", sum(st.456Clicks))/printf("%.3f", sum(st.123Clicks)) as percentage
from (
    select email, count(*) as 123Clicks, 0 as 456Clicks
    from table
    where test = "123"
    group by email

    union all

    select email, 0 as 123Clicks, count(*) as 456Clicks
    from table
    where test = "456" and "Clicked?" = "Yes"
    group by email
) st
join table t
on t.email = st.email
where t."Clicked?" = "Yes" and t.test = "123"
group by t.location
order by percentage DESC


how to ignore DST in MySQL We have a database with flights, and in there, departureTime DATETIME field.

Extra info:

global.time_zone: Europe/Dublin
session.time_zone: Europe/Dublin
MySQL version: 5.7

Problem is, I need to save a flight from China at "2019-03-31 01:00", and server is saving "2019-03-31 02:00" because of the day saving time. In Dublin would be fine, but that flight is from China and should be saved properly.

So what would be the best here?,

Is there any way to ignore DST validations?.

Should I save all datetimes in UTC format and then show them in each timezone?

Should I use timestamp instead of datetime?

If I change server timezone to UTC, what would it happen with old records?

MySQL does not have a datatype that will receive a local time and timezone.  You will need to do one of these:

Store both fields and worry about interpreting it later.
Use application code to convert the time to UTC based on the given TZ.  Then store it in a TIMESTAMP while having the system time set to UTC.

When you store 2019-03-31 01:00 into a DATETIME, 2019-03-31 01:00 is the only thing you will ever get from that column.

When you store something in a TIMESTAMP column, it is converted according to MySQL's tz setting, then stored as UTC.  When retrieving, it will be reconverted.  If nothing has changed, you will get the same value back.

Between 2am and 3am is tricky because that hour repeats once a year.  Beware.

If the flight leaves China at 2019-03-31 01:00 China time, but your system is set for Dublin time, neither DATETIME, nor TIMESTAMP will be useful.

Best to convert to/from UTC in your app, then store UTC in the database.


SQL Server Installation - TempDB not created in the directory specified in ConfigurationFile.ini I'm attempting to install SQL Server 2016 SP2 on a newly created EC2 instance. I'm using a configuration file I previously generated. In the configuration file I've specified where I'd like the TempDB files to be created along with the Database Root data and log directories. That looks like this:

; The number of Database Engine TempDB files.
SQLTEMPDBFILECOUNT="8"

; Specifies the initial size of a Database Engine TempDB data file in MB.
SQLTEMPDBFILESIZE="88500"
; Specifies the automatic growth increment of each Database Engine TempDB data file in MB.
SQLTEMPDBFILEGROWTH="0"

; Specifies the initial size of the Database Engine TempDB log file in MB.
SQLTEMPDBLOGFILESIZE="88500"
; Specifies the automatic growth increment of the Database Engine TempDB log file in MB.
SQLTEMPDBLOGFILEGROWTH="0"

; Directories for Database Engine TempDB files.
SQLTEMPDBDIR="T:\Data"
; Directory for the Database Engine TempDB log files.
SQLTEMPDBLOGDIR="T:\Log"

; The Database Engine root data directory.
INSTALLSQLDATADIR="O:\Data"
; Default directory for the Database Engine user database logs.
SQLUSERDBLOGDIR="O:\Log"

After the installation I get this error message:

MODIFY FILE encountered operating system error 112(There is not enough space on the disk.) while attempting to expand the physical file 'O:\Data\MSSQL13.BI\MSSQL\DATA\templog.ldf'.

The log output from the installation has the same directory paths specified in the ConfigurationFile for TempDB and for the the user data/log directories.

For the life of me I cannot understand why the TempDB files are being created in the filepath specified in the error message.

I can't say specifically why but when reducing the initial size of the TempDB files, they were created in the directory specified in the ConfigurationTile.ini.

I updated the ConfigurationFile to the following:

; The number of Database Engine TempDB files.
SQLTEMPDBFILECOUNT="8"

; Specifies the initial size of a Database Engine TempDB data file in MB.
SQLTEMPDBFILESIZE="256"
; Specifies the automatic growth increment of each Database Engine TempDB data file in MB.
SQLTEMPDBFILEGROWTH="0"

; Specifies the initial size of the Database Engine TempDB log file in MB.
SQLTEMPDBLOGFILESIZE="256"
; Specifies the automatic growth increment of the Database Engine TempDB log file in MB.
SQLTEMPDBLOGFILEGROWTH="0"

; Directories for Database Engine TempDB files.
SQLTEMPDBDIR="T:\Data"
; Directory for the Database Engine TempDB log files.
SQLTEMPDBLOGDIR="T:\Log"

; The Database Engine root data directory.
INSTALLSQLDATADIR="O:\Data"
; Default directory for the Database Engine user database logs.
SQLUSERDBLOGDIR="O:\Log"

As an additional setup step, I'll run a script to re-size the tempdb files.