Which backup files are needed for a successful restore?

The included backup.bat (windows) or backup.sh (all other platforms) script found under $ORACLE_HOME/config/scripts will do the following:

Check that the user running the script is in the DBA or DBOPER system groups
Check that the database is in archivelog mode and have the FAST_RECOVERY_AREA parameter configured.
Back up the database to the fast recovery area under XE/backupset (online/warm backup using RMAN)
Create a pfile as $ORACLE_HOME/XE/dbs/spfile2init.ora
Autobackup control file and SPFILE to the fast recovery area under XE/autobackup/
Switch to the next online redo log file, letting the current one be archived
Create a log of the backup operation, in the user's home directory

The archive logs are already located in the fast recovery area under XE/archivelog

Note that the PFILE is created in the oracle home, but it is not required to restore the database, not even to a new server. Everything you do need is located in the Fast Recovery Area.

That's not to say that it's not a good idea to additionally back up the oracle home.


Queries from different tables locking each other (LCK_M_X)

Uncommitted Transactions

As Dan Guzman mentioned, check for uncommitted transactions (e.g. using DBCC OPENTRAN) and look at the locks held by the blocking session. It may be a query timed out, wasn't rolled back properly, and the pooled connection with the open transaction reused. You need to remember locks are held by the session of that 'unrelated' query and it is quite likely that in that session the identifier update was done before and still locked (mentioned by eckes).

Foreign Keys

Aaron Bertrand mentioned foreign keys might be involved in your blocking issue.  Since you confirmed there are foreign keys defined on these tables, I'd like to briefly expand on that.

Take the second update in your execution plan as an example:

UPDATE Entity WITH (ROWLOCK)
SET EntityTypeCid = 2, EntityNumber = 49634989, NumberIssued = 1 WHERE Did = @p10

And this subset of the execution plan for that statement:

As expected, since you're updating the Entity table, there's a "Clustered Index Update" operator performing that update*.  Notice, though, that there's also a seek into the EntityType table, followed by an Assert to make sure that the value being set in Entity.EntityTypeCid (2 in this case) has a corresponding match in EntityType.Cid).

All that to say, the update query looks like it would only acquire locks on the Entity table, when in fact it will also briefly need locks from the EntityType table as well (to validate the foreign key constraint).  So other queries that acquire locks on the EntityType table could block this one.

I don't see any evidence of that happening on the update of Numeration.NumCounter, but if other tables reference NumCounter as a foreign key then the same thing could be happening there (in reverse).

Triggers

Another possibility is that the table has Triggers defined on it, which perform data access to other tables, causing blocking.  You already clarified that there are no triggers involved in these specific tables, so I just mention this for completeness.

Long-Running Transactions

You shared a screenshot of profiler in the comments:

I've highlighted session ID 423 because it looks like the following actions all occur inside a transaction:

The Numeration table is updated
The NumCounter column is selected (presumably from the Numeration table)
Another statement runs - based on your comments and the query plan, I imagine this is updating the Entity table
Then the transaction is committed

The locks on Numeration and Entity will be held for the duration of the transaction when using the default READ COMMITTED isolation level, which could block other sessions trying to read from or update these rows.

In this specific screenshot, all of that happened very quickly.  But if the 3rd statement takes longer than expected, then the lock on Numeration is also held longer, creating a blocking chain like the one shown in your other screenshot:

Notice that loads of sessions are waiting on access to the Numeration table, all of them blocked by session 227 (which is updating the Numeration table).  Session 227 is being blocked by session 222, which isn't shown in your screenshot.

It's hard to make suggestions on what to do about this without knowing what session 222 is doing.  If that's a read query, you might have better luck with an optimistic isolation level like READ COMMITTED SNAPSHOT isolation (RCSI), since in that case readers wouldn't block writers.

The problem might be a higher-level, architectural issue - where this Numeration table is a bottleneck that needs to be scaled out somehow.

* this likely isn't relevant to your question, but the update is also updating 3 nonclustered indexes on that table which contain one or more of the columns being updated.  This is not really obvious in SSMS, but SentryOne Plan Explorer calls it out nicely


Is the Execution Path for a Function invoked inside a Stored Procedure cached?

First of all, why do you use multi-statement table-valued functions instead of inline function?  Multi-statement function calls are expensive and introduce significant CPU overhead.

Second, the way you want to use it is very strange:

  The Stored Procedure in psuedo would be used IF @Condition Return
  SelectedTable ELSE Return fnExampleFunction(SelectedTable)

You just cannot return tables from sp using Return.

And now to your main question:

  So I was wondering if I invoke a Function from within a Stored
  Procedure is the entire Stored Procedure still cached?

Of course stored procedure's plan will be cached.

There is a difference of what is cached when you use multi-statement function  (which starts with a  BEGIN  and ends with an  END  keyword) or inline function:

Multi-statement functions (table-valued or scalar) have query plans on their own. Inline functions don't. SQL Server expands and embeds inline table-valued functions into the actual queries, similar to regular views, and it optimizes their statements as part of the queries. As a result, there are no separate calls of the function and you don’t have to deal with its associated overhead.

Here is how your multi-statement function can be re-written as inline function:

CREATE FUNCTION [dbo].[fnExampleFunction]
(
    @EmployeeDetails   EMPLOYEEDETAILS READONLY
)
RETURNS TABLE AS RETURN
(
    SELECT
    ed.*,
    jt.JobTitleName,
    l.LocationName,
    al.AppraisalLevelName
    FROM @EmployeeDetails ed
            LEFT OUTER JOIN AppraisalLevels al ON al.AppraisalLevelId = ed.AppraisalLevelId
            LEFT OUTER JOIN EmployeeDetails m ON m.EmployeeId = ed.ManagerId
            LEFT OUTER JOIN JobTitles jt ON jt.JobTitleId = ed.JobTitleId
            LEFT OUTER JOIN Locations l ON l.LocationId = ed.LocationId
)


The first solution is clearly more efficient than the second one, because you don't need to do the join (every join slows down a query, more or less depending on the access plan generated by the system).

More, I think the second solution has some problem, since from the schema it seems that you are using the column id inside mssg_grp as foreign key referencing the table mssg, while in that table you use as primary key the couple of columns created_at and id, which conflicts with the definition of the foreign key, from which one could assume that id is already a primary key for mssg.

So, if, as it seems from the diagram, the relation between mssg and mssg_grp is 1:1, you should opt for the first solution which is still normalized and more efficient.


Sum values at a group level rather than detail

Well, after a lot of experimenting, I couldn't find a good way to do it in Reporting Services. I tried using a custom function to accumulate total hours (called in the cell displaying the hours in the NoteGroup header), then another custom function in the table footer which would retrieve the value, but the accumulated values would reset on page breaks.

In the end, I've mangled the query into assorted subqueries. It sucks, but it works. Would be sweet if you could do something like SUM(hours) OVER(DISTINCT note_id PARTITION BY client_id)...


Absolutely no! Do not try this. The normal way of doing this is a proper dump and restore and copy the dumped file from one VM to another VM.

pg_dump -d db -f db.sql

You might want to compress the file with xz or some other compressor before transferring it.

transfer db.sql to new VM after creating the database with createdb db (adding any other option you require)

then

psql -d db -f db.sql

or use pg_restore


How do I specify that a column should be auto-incremented in pgAdmin?

two options: Use the "datatype" SERIAL or create a sequence and use this sequence as a default value for your integer:

CREATE SEQUENCE your_seq;
CREATE TABLE foo(
  id int default nextval('your_seq'::regclass),
  other_column TEXT
);
INSERT INTO foo(other_column) VALUES ('bar') RETURNING *;


Converting my comment to answer :

restore database msdb with replace, recovery

Note : if the file location is same, then you dont need to use with move in the restore clause.


Using Join to get data from multiple tables in SQL Server

If you only need to JOIN table A and B, but the configuration is on table C:

SELECT
    A.*,
    B.b1
FROM db.A
JOIN db.C ON (C.a_id=A.a_id)
JOIN db.B ON (B.b_id=C.b_id)
WHERE `table`.`field` = `parameter` # <-- Aditional


If I've understood you correctly, you want something like this

SELECT * FROM products WHERE JSON_EXTRACT(feature_products, "$.frequency") = '3.99GHz';

I created a table "products" - note that the last record corresponds to the one we want.

SELECT * FROM products;

id_products name_products   feature_products
1   blah    {"cache": {"l1": "128k", "l2": "6Mb"}, "socket": "am3+", "frequency": "3.97GHz"}
1   blah2   {"cache": {"l1": "128k", "l2": "6Mb"}, "socket": "am3+", "frequency": "4.7GHz"}
1   result  {"cache": {"l1": "128k", "l2": "6Mb"}, "socket": "am3+", "frequency": "3.99GHz}

Running the query above

SELECT * FROM products WHERE JSON_EXTRACT(feature_products, "$.frequency") = '3.99GHz';

gives:

id_products name_products   feature_products
1       result      {"cache": {"l1": "128k", "l2": "6Mb"}, "socket": "am3+", "frequency": "3.99GHz"}"

Which, as we can see, is what was required -  result "frequency" and "3.9GHz" and query for everything

I found this reference helpful. And, if all else fails, one can always consult the documentation here :-). Also, the MySQL server team blog was useful.


Can I reorganise index on a sql server 2005 database without performance hit on production server

Correct: online index rebuilds are only supported in the Enterprise edition engine.

ALTER INDEX ... REORGANIZE is always an online operation, regardless of your edition of SQL Server, even on 2005.

Doing this while the system is in operation will certainly affect performance as far as physical disk workload, but it should not affect read or modification operations in terms of locking. See the link above for more details.

Since doing a REORGANIZE is online, you can choose to schedule it more frequently, perhaps even taking advantage of periods of slower activity. The key idea would be to do it more frequently, but do less work each time, as compared to, say, doing it once/day or once/week.


What your script is doing is explicitly attempting to insert data into an IDENTITY column. You need to do one of two things:  Either set IDENTITY_INSERT to ON for that table prior to the insert, or rework the INSERT statement so that it doesn't have the IDENTITY column specified with a value.


When did sqlmag.com cease to exist, please?

Penton Media filed for bankruptcy in 2010, tried reinventing themselves as SQL Server Pro in 2011, and then gradually phased out that SQL Server Pro brand into a general IT Pro Today site.


You must always check the XACT_STATE() inside a catch block. If the value is -1 then you must issue a ROLLBACK.


Adding and populating column in Postgres with data from existing column

To achieve this, you need to do the following:

(It's implicitly understood that your ALTER TABLE succeeded).

ty=# CREATE TABLE a(b text, c inet);
CREATE TABLE

Insert a text record with an embedded IP address.

ty=# INSERT INTO a VALUES('adfasfsaa 232.123.221.10', NULL);
INSERT 0 1

And then update the second column with the first by extracting the IP address (as a SUBSTRING) using a regular expression and then (CASTing the resultant substring as an inet type).

ty=# UPDATE a SET c = SUBSTRING(b, '(?:[0-9]{1,3}\.){3}[0-9]{1,3}')::inet;
UPDATE 1

Et voilà!

ty=# SELECT * FROM a;
            b             |       c
--------------------------+----------------
 adfasfsaa 232.123.221.10 | 232.123.221.10
(1 row)

A more accurate regex to extract IP addresses from text might be - depending on your use case:

'\b(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\b'

If there's more than one IP address in the string, you will get the first occurrence of an IP address in that string.

I also tested (the simple regex) on a string like 'https://123.23.21.12/sadf.net' and it works - looks like the simple one will be sufficient for most use cases.


Audit OSQL command shutting down a SQL Server

As mentioned by Dan Guzman if you are using the T-SQL SHUTDOWN command to shut down SQL Server, you see see the details with username in two places:

SQL Server Error Log

  Date      3/15/2020 7:44:16 PM Log        SQL Server (Archive #1 - 3/15/2020
  7:44:00 PM)

  Source        spid67

  Message Server shut down by NOWAIT request from login
  domaninName\Username.

Windows Event Log

  Event Id: 6006

  Server shut down by NOWAIT request from login domaninName\Username.

If you are stopping SQL Server from configuration manager, services in the control panel or command prompt, you will only get the stop time but not the user who initiated the stop command.


SQL Server grant permissions to STANDBY database

Below steps should work :

Create windows login on ServerA
Create user in database mapped to login
Drop login on ServerA - (OPTIONAL -if you want to have that login intact then leave it, else drop it).
Grant any required permissions to user in database
Create login on log shipping ServerB

a. If using SQL authentication then create login while specifying SID from ServerA
b. If using Windows authentication, create login without specifying SID

 ---To query for the SID on the ServerA:

  Select SID
  From sys.database_principals
  Where name = 'Test'

 ---To create the login on the ServerB :

  Create Login [Test]
  With SID = '<put SID from query here>',
  Password = '<add password here>'

Make sure log shipping jobs have completed one full run since completion of step 4


Data base structure of a subscriber list

No RDBMS system I know of supports mixing a data-type within a single column. So I think you've got two solutions:

Separate tables - each user's list gets their own Database table. This would allow you the flexibility to customize the data type at the column level.
Flexible data type in the "name" column. You could use a varchar or even a binary blob in the name column and allow the user to store whatever they want in that column. Then, at the application level, you could interpret that data in whatever format you like by, for instance, casting it to an integer.

It sounds to me, since you're hoping to store different types of data with different structures to it, that you should probably just be using separate tables.


Optimization: Delete with Top and minimize the where clause date range

You trade off

speed
smaller transaction log
concurrency

So running this in batches with a good index satisfies the last 2, but is slower. However, you don't need absolute speed if it is less intrusive to other operations

Otherwise, I'm not sure what you want...


Can I force mysql server to resolve the external ip to localhost?

Great, here is the solution I found. I'm chosing to post it here instead of just removing the question since I believe it may be relevant for further reference.

Basically the idea is to leave mysql alone but to tell the server to resolve the ip. In the case of my centos6 configuration it means editing the /etc/hosts file and appending a my.servers.ip.address localhost line into it.

Hope it helped!
S.


Postgres - should I keep the log file on the fastest drive

With the logging_collector enabled, writes are sequential so you can keep log_directory on almost any media.
Assuming average size of a log message is 5 KiB, with a HDD that can do sequential writes at 100 MiB/s you can log 20k messages/second.
So in theory - if you need to you emit tons of logs - you could put them on a separate, cheaper storage.
At the same time, remember that when the log volume fails or is overloaded, it can effectively stop the server (as noted in documentation linked above).


Join two tables in MYSQL and avoid duplicate values

Do this "deduplication" on the application side. It will be much easier to clean up repetitive values with PHP or JS. It is possible to make what you want by UDVs but SQL wasn't intended for it.


HOW does ROWNUM work with this "optimization"

The rownum pseudocolumn is assigned as rows are processed.  The predicate rownum = <<x>> will evaluate to false if <<x>> is anything other than 1.

Conceptually (and I emphasize that this isn't how Oracle actually works, it's just a useful abstraction), the second query does something like

Read a row from table1
Evaluate the col2 = 'val' expression
If col2 = 'val', evaluate the extract
Assign the rownum to the row that has been returned.  Since no other rows have satisfied all the criteria yet, the rownum will be 1
Evaluate the rownum = <<x>> predicate. If <<x>> is anything other than 1, the row is
rejected
Go back to step 1 and read the next row

In this loop, every row will be read from table1.  And every row will be rejected (assuming <<x>> is something other than 1) because of the rownum = <<x>> predicate.  So you'll incur the cost of reading every row from the table, evaluating the extract for every row, and you'll never return any data.

Your first query appears to be working because it is filtering out <<x>> + 1 rows in the subquery.  Of course, since there is no ORDER BY, your first query always returns an arbitrary row.  It would be entirely legal for Oracle to return the same row for every value of <<x>> in your first query.  It probably won't, of course, but it is entirely possible that you'd get different rows over time, that some rows would never be returned, that other rows would be returned multiple times, etc.  If you want a deterministic result (prior to 12.1 which has some simpler syntax options), you'd need to do two levels of nesting

SELECT *
  FROM (SELECT b.*,
               rownum rn
          FROM (SELECT a.*
                  FROM some_table a
                 ORDER BY some_column) b
         WHERE rownum <= <<upper_limit>>) c
 WHERE rn >= <<lower_limit>>

For a pretty thorough discussion of the various issues, take a look through this askTom thread.


Moving data from staging table to production table uses up to 200GB of Log space

You might like to look at partition switching - the staging table could be switched in to the production table, and the production table switched out to a history table. https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver15#c-switching-partitions-between-tables gives an example of a switch out. There are quite a few blogs about the technique, look up a few and see if they match what you are trying to do.
A system I used to work on had a monthly data load. The data was staged to a table, which was then switched in to the live table. The live table had a partition per month. It was very quick as I think it's a metadata operation. HTH


List Jobs, Schedules, and Next Scheduled Run Datetimes

I have this script in my toolbox:

    select
       S.name AS JobName,
       SS.name AS ScheduleName,
       CASE(SS.freq_type)
            WHEN 1  THEN 'Once'
            WHEN 4  THEN 'Daily'
            WHEN 8  THEN (case when (SS.freq_recurrence_factor > 1) then  'Every ' + convert(varchar(3),SS.freq_recurrence_factor) + ' Weeks'  else 'Weekly'  end)
            WHEN 16 THEN (case when (SS.freq_recurrence_factor > 1) then  'Every ' + convert(varchar(3),SS.freq_recurrence_factor) + ' Months' else 'Monthly' end)
            WHEN 32 THEN 'Every ' + convert(varchar(3),SS.freq_recurrence_factor) + ' Months' -- RELATIVE
            WHEN 64 THEN 'SQL Startup'
            WHEN 128 THEN 'SQL Idle'
            ELSE '??'
        END AS Frequency,
       CASE
            WHEN (freq_type = 1)                       then 'One time only'
            WHEN (freq_type = 4 and freq_interval = 1) then 'Every Day'
            WHEN (freq_type = 4 and freq_interval > 1) then 'Every ' + convert(varchar(10),freq_interval) + ' Days'
            WHEN (freq_type = 8) then (select 'Weekly Schedule' = MIN(D1+ D2+D3+D4+D5+D6+D7 )
                                        from (select SS.schedule_id,
                                                        freq_interval,
                                                        'D1' = CASE WHEN (freq_interval & 1  <> 0) then 'Sun ' ELSE '' END,
                                                        'D2' = CASE WHEN (freq_interval & 2  <> 0) then 'Mon '  ELSE '' END,
                                                        'D3' = CASE WHEN (freq_interval & 4  <> 0) then 'Tue '  ELSE '' END,
                                                        'D4' = CASE WHEN (freq_interval & 8  <> 0) then 'Wed '  ELSE '' END,
                                                    'D5' = CASE WHEN (freq_interval & 16 <> 0) then 'Thu '  ELSE '' END,
                                                        'D6' = CASE WHEN (freq_interval & 32 <> 0) then 'Fri '  ELSE '' END,
                                                        'D7' = CASE WHEN (freq_interval & 64 <> 0) then 'Sat '  ELSE '' END
                                                    from msdb..sysschedules ss
                                                where freq_type = 8
                                            ) as F
                                        where schedule_id = SJ.schedule_id
                                    )
            WHEN (freq_type = 16) then 'Day ' + convert(varchar(2),freq_interval)
            WHEN (freq_type = 32) then (select  freq_rel + WDAY
                                        from (select SS.schedule_id,
                                                        'freq_rel' = CASE(freq_relative_interval)
                                                                    WHEN 1 then 'First'
                                                                    WHEN 2 then 'Second'
                                                                    WHEN 4 then 'Third'
                                                                    WHEN 8 then 'Fourth'
                                                                    WHEN 16 then 'Last'
                                                                    ELSE '??'
                                                                    END,
                                                    'WDAY'     = CASE (freq_interval)
                                                                    WHEN 1 then ' Sun'
                                                                    WHEN 2 then ' Mon'
                                                                    WHEN 3 then ' Tue'
                                                                    WHEN 4 then ' Wed'
                                                                    WHEN 5 then ' Thu'
                                                                    WHEN 6 then ' Fri'
                                                                    WHEN 7 then ' Sat'
                                                                    WHEN 8 then ' Day'
                                                                    WHEN 9 then ' Weekday'
                                                                    WHEN 10 then ' Weekend'
                                                                    ELSE '??'
                                                                    END
                                                from msdb..sysschedules SS
                                                where SS.freq_type = 32
                                                ) as WS
                                        where WS.schedule_id = SS.schedule_id
                                        )
        END AS Interval,
        CASE (freq_subday_type)
            WHEN 1 then   left(stuff((stuff((replicate('0', 6 - len(active_start_time)))+ convert(varchar(6),active_start_time),3,0,':')),6,0,':'),8)
            WHEN 2 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' seconds'
            WHEN 4 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' minutes'
            WHEN 8 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' hours'
            ELSE '??'
        END AS [Time],
        CASE SJ.next_run_date
            WHEN 0 THEN cast('n/a' as char(10))
            ELSE convert(char(10), convert(datetime, convert(char(8),SJ.next_run_date)),120)  + ' ' + left(stuff((stuff((replicate('0', 6 - len(next_run_time)))+ convert(varchar(6),next_run_time),3,0,':')),6,0,':'),8)
        END AS NextRunTime
from msdb.dbo.sysjobs S
left join msdb.dbo.sysjobschedules SJ on S.job_id = SJ.job_id
left join msdb.dbo.sysschedules SS on SS.schedule_id = SJ.schedule_id
order by S.name


PostgreSQL query and shape in json array?

You can use jsonb_path_query_first function. Here is a sample usage.

SELECT
    jdata->>'ad_name' as ad_name,
    jsonb_path_query_first(
      jdata ,
    '$.actions[*] ? (@.action_type == "video_view").value'
    ) AS "actions->video_view",
    jsonb_path_query_first(
      jdata ,
    '$.actions[*] ? (@.action_type == "mobile_app_install").value'
    ) AS "actions->mobile_app_install"
  FROM test

ad_name      | actions->video_view | actions->mobile_app_install
:----------- | :------------------ | :--------------------------
ad name here | "11"                | "33"

db<>fiddle here
You can read The SQL/JSON Path Language for reference.


How do you create a Geographic Point with MariaDB and MySQL?

MySQL

In MySQL 8+, the point is constructed the same way and whether or not it's geographic (geodetic) or geometric (carestian) is a function of the SRID and the POINT has the same type. So you'll want to use,

SELECT ST_SRID(Point(1,2), 4326);

You can set the SRID with ST_SRID

MariaDB

With MariaDB you're SOL. MariaDB still lacks the form that takes a second argument to set the SRID.

See also

How do I create a point with an SRID in MySQL?


Oracle: how to set only some roles as not default for a user (no GUI)

You have to use the GRANT command to grant roles (with admin option if required), then use ALTER USER to set roles to default (or not). So, the order of commands is,

grant roles (with admin option as needed)
alter user to set all roles to non-default
alter user to set only required roles to default.

A way to generate sql using sql is as follows, you can run this sql in instanceA, and actually execute the generated script in instanceB by replacing username if required. Make sure to test though,

with x as (select 'USER1' as usr from dual)
select cmd from (
select 1 as ord, 'grant ' || granted_role || ' to ' || grantee || case when admin_option = 'YES' then ' with admin option' end  || ';' as cmd
from dba_role_privs, x where grantee = x.usr
union all
select distinct 2 as ord, 'alter user ' || grantee || ' default role none;' as cmd from dba_role_privs, x where grantee  = x.usr
union all
select 3 as ord, 'alter user ' || grantee || ' default role ' || granted_role || ';' as cmd from dba_role_privs, x where grantee  = x.usr and default_role = 'YES')
order by ord


Solving supertype-subtype relationship without sacrificing data consistency in a relational database

One common way is to add a classifier that is "inherited" like:
CREATE TABLE products
( product_id ... NOT NULL PRIMARY KEY
, ...
, product_type ... NOT NULL
,     UNIQUE (product_id, products_type)
,     CHECK (product_type IN ('food', 'furniture'))
);

product_type would typically be a code of some kind. Might be a foreign key to a "lookup" table instead of a check constraint. For the sub-types:
CREATE TABLE food
( product_id ... NOT NULL PRIMARY KEY
, ...
, product_type ... DEFAULT 'food' NOT NULL
,    FOREIGN KEY (product_id, product_type)
     REFERENCES products (product_id, product_type)
,    CHECK (product_type = 'food')
);

and a similar one for furniture. The constraints guarantee that product_type is consistent between super- an sub- tables.
There are products (I've heard) that allow sub-selects in CHECK constraints, but the majority do not. For such product something like:
CREATE TABLE food
( product_id ... NOT NULL PRIMARY KEY
, ...
,    FOREIGN KEY (product_id)
     REFERENCES products (product_id)
,    CHECK (
         (SELECT product_type
          FROM products p
          WHERE p.product_id = product_id) = 'food'
     )
);

could be used.
An alternative to the latter is to use before triggers for insert/update, and signal an exception if the wrong product_type is used. Personally I don't fancy using procedural code for integrity constraints, but I guess it is a matter of taste.


But the central inventory location should be /u01/app/oracle/oraInventory, not what you have. It is much better to follow the recommandations of Oracle for directories naming (see OFA section in installation guide).

check what is the id and the group of the user running the runInstaller, usually it is oracle:oinstall

then as user root

mkdir -p /u01/app/oracle
chown -R oracle:oinstall /u01/app/oracle (assuming oracle:oinstall)

before starting runInstaller make sure you don't have the env variable ORACLE_HOME set. You don't need to set ORACLE_BASE, but if you do then it should be pointing to /u01/app/oracle. So

export ORACLE_BASE=/u01/app/oracle

If the env variable ORACLE_BASE is not set, Oracle is clever enough to recognize /u01/app/oracle as the standard ORACLE_BASE location

Note: as a side note, standardize the id of user oracle and the gid of groups oinstall and dba accross all your system, i.e use the same values everywhere.


MS SQL Server: DBCC ShrinkDatabase - Does it really not shrink the database below the minimum size?

You shouldn't use DBCC SHRINKDATABASE. Shrinking files is a very extraordinary event and should be used with extreme caution and, even then, only with carefully planned commands that target individual files (DBCC SHRINKFILE is SHRINKDATABASE's friendlier, less crazy cousin.)

See this post for lots of advice around this. Usually shrinking a file is only a very temporary measure and a totally wasted effort because the file(s) will just grow again.
Minimum size and initial size are different things, and are treated differently by SHRINKFILE and SHRINKDATABASE. SHRINKDATABASE definitely will not let you go below the initial file sizes, whether you explicitly set them or inherited from model - if you are seeing this happen, please show a full repro, because I couldn't make that happen. I created the same 4GB file as below, tried a SHRINKDATABASE with a target of 99%, and it didn't budge. My guess is you got it below the current size but that was not the initial size.

SHRINKFILE will let you go below initial size. Here we create a 4GB data file, then try to shrink it down to 1MB.

    CREATE DATABASE floo ON
    (
      name = floo_data, SIZE = 4096MB, filename = 'C:\...\floo.mdf'
    )
    LOG ON
    (
      name = floo_log, SIZE = 1024MB, filename = 'C:\...\floo.ldf'
    );
    GO

    USE floo;
    GO
    SELECT name,[size_on_disk] = size*8. FROM sys.database_files;
    GO

    DBCC SHRINKFILE(floo_data, 1);
    GO
    SELECT name,[size_on_disk] = size*8. FROM sys.database_files;
    GO

Results show we don't quite get to 1MB, but we get far below both our initial size and the size set up in model (8MB in most modern systems):

One single operation made the file smaller than model (which is 8MB on my system), which is what one might expect either the minimum or initial size to be, depending on how the database is created (with or without explicitly specifying a file size).

Inheriting file size from model also allowed me to shrink the file below the initial size (which should double as the minimum in this case):

CREATE DATABASE blar; -- inherit file specs from model
GO
USE blar;
GO
SELECT name,[size_on_disk] = size*8. FROM sys.database_files;
GO

DBCC SHRINKFILE(blar, 1);
GO
SELECT name,[size_on_disk] = size*8. FROM sys.database_files;
GO

Results again show we can get below the "minimum" size and the initial size:

Again I strongly recommend you really look into whether you should ever really be shrinking these files anyway. This really should be a quite exceptional event.

Further info on analyzing reasons for tempdb data/log growth:

how to find out what process(es) caused the autogrowth of a database or a database file?
Issues with TempDB mdf file ever increasing
TempDB will not shrink. No Open Transactions
How to identify which query is filling up the tempdb transaction log?
what is the best practice for tempdb autogrowth?
Find which session is holding which temporary table
Dude, who owns that #temp table?


How to automate the changes of parameters in ssis package run by a job?

The way I have been doing this is first I get the job_id and the command of the job by using the following query:

select @@servername -- this should be on the same server as the job
exec  msdb.dbo.sp_help_job @job_name = 'Daily DW CA'

that is to get the job_id and the step_id that has the command you want to update

you can also get that info like this:

note that this could be automated as well, but on this situation it was not necessary.

select * from
msdb.dbo.sysjobs
where name ='Daily DW CA'

then you use the @job_id and step_id in the script below.
the script below will change the dates from 2019-02-27 to 1900-01-01

set nocount on

declare @job_id uniqueidentifier = '90D3F4A2-1AC7-4741-BCCA-9A7B64A69B20'
declare @step_id int = 1
declare @command NVARCHAR(MAX)
declare @sql NVARCHAR(MAX)

--a quick select just to check
    SELECT step_id,
           step_name,
           subsystem,
           command,
           output_file_name,
           proxy_id
    FROM msdb.dbo.sysjobsteps
    WHERE (job_id = @job_id)
      AND ((@step_id IS NULL) OR (step_id = @step_id))
    ORDER BY job_id, step_id

--REPLACE(string, old_string, new_string)

    SELECT @COMMAND = replace(command,'2019-02-27','1900-01-01')
    FROM msdb.dbo.sysjobsteps
    WHERE (job_id = @job_id)
      AND ((@step_id IS NULL) OR (step_id = @step_id))
    ORDER BY job_id, step_id

print @command

SET @SQL =
'
EXEC msdb.dbo.sp_update_jobstep
@job_id=N''' +  '90d3f4a2-1ac7-4741-bcca-9a7b64a69b20' + ''',' + '
@step_id=1 , ' +
'@command=N''' + @command + '' + char(39)

print @sql

exec sp_executesql @sql

You can check if all is ok by having a look at the following command:

exec  msdb.dbo.sp_help_job @job_name = 'Daily DW CA'

you can see the desired values in the command shown below:


Mysql Update Query is not Giving Desired Result

As far as I understand according to your comments you should add a parentheses to your WHERE clause:

SELECT COUNT(id) AS todayleads
FROM   `lead_data`
WHERE  `date` = '2018-01-12'
AND    lead_status = 1
AND    (uid = '3' OR assign_to = '3')


Prevent Subscription to report from running on a holiday

have built my SSIS packages to check and make sure that the day isn't a holiday by comparing the date to a table that contains holidays.

You can have your 1st job step as

IF EXISTS (SELECT 1 FROM HolidayTable WHERE Date = GETDATE())
  begin
   RAISERROR ('There is nothing to run as today is holiday !', 16, 1)
  end
 else
   print 'The job can run .. as its a working day !'

The second step is to run the SSIS package only if the above condition is FALSE. If the above step is true meaning if it is a holiday as per your HOLIDAY Table, then it should silently fail.


SQL Server 2008 R2 Standard Edition not using more than 32GB for BufferPool

In case someone reads this article in the future, SQL Server 2008 R2 on Windows Server 2008 R2 Enterprise (upgraded in-place) can and does use 64 GB RAM. Windows was patched last week and rebooted.

After reboot:

select counter_name, cntr_value

from master..sysperfinfo

where [object_name]='SQLServer:Buffer Manager'

and counter_name in ('Total pages','Target pages')

Result:

counter_name      cntr_value

Total pages          7077888    <--54 GB
Target pages         7077888    <--54 GB


Foreign keys to prevent "orphan" file rows in a database for a scenario involving projects, files and downloads

Schematically (out of syntax).

Structures:

CREATE TABLE project ( project_id identity,
                       primary key (project_id),
                       name varchar,
                       unique (name) );

CREATE TABLE file ( file_id identity,
                    primary key (file_id),
                    filelink varchar,
                    content varchar,
                    unique (bf(file_link || content)) );

CREATE TABLE download ( download_id identity,
                        primary key (download_id),
                        project_id,
                        foreign key (project_id) references project (project_id),
                        file_id,
                        foreign key (file_id) references file (file_id),
                        download_timestamp default current_timestamp );

Insertion:

INSERT INTO project (name)
VALUES (@name)
ON CONFLICT (name) DO NOTHING;

INSERT INTO file (filelink, content)
VALUES (@filelink, @content)
ON CONFLICT (filelink, content) DO NOTHING;

INSERT INTO downloads (project_id, file_id)
SELECT project.project_id, file.file_id
FROM project, file
WHERE project.name = @name
  AND file.filelink = @filelink
  AND file.content = @content;


To get the information for a single instance you would use DaysUntilExpiration value for the LOGINPROPERTY function. This will give you the days until the password expires. You can then cast that as an integer and use the DATEADD function to get the actual date.

SELECT name AS LoginName,
   DATEADD(DAY, CAST(LOGINPROPERTY(name, 'DaysUntilExpiration') AS int), GETDATE()) AS ExpirationDate,
   create_date
   FROM sys.server_principals
   WHERE type = 'S'

Now you can use a few different methods to get this from multiple servers:

Multiple instance connection via SSMS and Registered Servers
PowerShell using the Invoke-SqlCmd cmdlet within the SQLPS snapin. AS long as you are on a machine with SQL Server 2008 (or higher) management tools, this is available.

Examle:

$q = @"
SELECT name AS LoginName,
DATEADD(DAY, CAST(LOGINPROPERTY(name, 'DaysUntilExpiration') AS int), GETDATE()) AS ExpirationDate,
create_date
FROM sys.server_principals
WHERE type = 'S'
"@

Add-PsSnapin *SQL*

$serverList = 'Server1','Server2','Server3\InstanceName'

foreach ($s in $serverList)
{
 Invoke-SqlCmd -ServerInstance $s -Database master -query $q
}


You are making this needlessly complicated. You are trying to build a dynamic query where a static one would do just fine. Assuming the logic in the dynamic SQL is correct, the entire procedure could look like this:

CREATE PROCEDURE test.ZTEST1
  @Messages nvarchar(4000)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT
    PARSENAME(REPLACE(SUBSTRING(@Messages, CHARINDEX(':', @Messages) + 1, LEN(@Messages)), ':', '.'), 4);
  /* or you could split the complex expression into multiple lines,
     for potentially easier understanding of the logic:
    PARSENAME(
      REPLACE(
        SUBSTRING(
          @Messages,
          CHARINDEX(':', @Messages) + 1,
          LEN(@Messages)
        ),
        ':',
        '.'
      ),
      4
    );
  */
END
GO

I have replaced @ConcatenatedErrorMessages with @Messages as that seemed an obvious mistake (and may have been the cause of an error message very similar to the one you have posted).

On a different note, the way you are calling the stored procedure:

test.ZTEST2 'test1:test2:test3:test4:test5';

is acceptable only if that statement is first in the batch. The more universal way of calling stored procedures that would work regardless of where the statement was located would be using the EXEC/EXECUTE keyword:

EXECUTE test.ZTEST2 'test1:test2:test3:test4:test5';

Finally, the name of the procedure you are trying to execute, test.ZTEST2, does not exactly match the name of the one being created above (test.ZTEST1) – so, you need to fix that too.


Access: How to Combine Data from Two Rows in a Query

You could use a CASE expression or the IIf() function to first order the two values and then group by them:

with CASE expression:

SELECT CASE WHEN Sex1 < Sex2 THEN Sex1 ELSE Sex2 END
       & CASE WHEN Sex1 < Sex2 THEN Sex2 ELSE Sex1 END AS Sex,
       Sum(Number) AS [Total Number]
FROM TableName
GROUP BY CASE WHEN Sex1 < Sex2 THEN Sex1 ELSE Sex2 END,
         CASE WHEN Sex1 < Sex2 THEN Sex2 ELSE Sex1 END ;

with IIf() function:

SELECT IIf(Sex1 < Sex2, Sex1, Sex2) & IIf(Sex1 < Sex2, Sex2, Sex1) AS Sex,
       Sum(Number) AS [Total Number]
FROM TableName
GROUP BY IIf(Sex1 < Sex2, Sex1, Sex2),
         IIf(Sex1 < Sex2, Sex2, Sex1) ;


SQL Server 2014 compile Stored Procedure response

It looks like a DDL trigger exists in some, but not all, of the databases. The trigger apparently uses a PRINT or RAISERROR statement to generate a message containing the DDL action and object name.


Applying normalization to a relational Database (Access & SQL Server)

Sorry, this may not be An Answer, but I couldn't add a comment.

This is surely a 'solved problem' in that hotel and reservation systems are available commercially 'off the shelf'? Why develop your own? Even if you do decide for good reason that you need to, it might well be worth investigating the data models underlying a few commercial products, if you can find a way of doing that.

However, if you ARE developing your own, I think you need many-to-many relationships between BIKE and GUEST (as I think you have recognised in your additional note), and between RENTAL DRIVER and either BOOKING or GUEST. These would be modelled on the same basis as you have already done between BOOKING and ROOM, with an intermediate table - which I would suggest naming ROOM BOOKING rather than BOOKABLE for ROOMs, BIKE_HIRE (as you have suggested adding) or BIKE_BOOKING for BIKEs, and CAR_BOOKING for car and driver).

A room that is NOT available for booking could be 'booked' to something like deep clean, re-decoration, renovation, re-plumbing or whatever reason makes it unavailable for guests to book it - I assume your Status field would cover things like that.

One BOOKING can include multiple GUESTS (as you have noted). And surely one GUEST can have multiple BOOKINGs over time? And might rent a car and driver or a bike more than once in one visit of several days or week(s)? Indeed, one BOOKING for several GUESTs might need you to add the concept of (and a table for) GROUPs, about which you might or might not know individual names, or just the number of people in the GROUP - for example, if you were to cater for a wedding party.

I would seriously consider generalising the concept of BOOKING, so it can cover ANY type of BOOKING - use a field Booking Type with a lookup from a BOOKING TYPEs table. Most of the fields needed for a booking will be similar, and the others can either be included but not used on some types of booking, or you could use the BOOKING TYPE table to label fields in BOOKINGs appropriately for that type of BOOKING.

If you have business guests, they might need to book meeting rooms, projectors, flipchart stands, etc, and these can just be added as different BOOKING TYPEs without needing any more tables to be defined.

You only need to store two of the three elements of the duration of a BOOKING - StartDateTime, EndDateTime, and Duration, and calculate the third.

Some bookings (like Room bookings) might have a Duration measured in whole nights (unless of course you are running another type of hotel?), others might be in hours or half days (Bike bookings for example) depending on your booking policy.

Can EMPLOYEEs sometimes be GUESTs? Or Rental Drivers be Employees? If so, you might consider introducing a PERSON table, with a PERSON_TYPE of Guest or Employee or Rental Driver for example. Or the Employee type might be extended to the role the Employee plays - from Owner, or Chief Exec, through Manager to Porter, Cleaner and Maid (to give a few possible examples).

It might also be worthwhile to separate out ADDRESS (postal address and probably land line phone) and CONTACT DETAILs (other phone, cell/mobile, email address(es), etc) from EMPLOYEE or GUEST entities. That way, one address may cover related Employees living in the same place, or a Guest GROUP or Family, whereas CONTACT DETAILs may apply to individual EMPLOYEEs or GUESTs. Again, you could have one field for contact detail type (lookup), and another which can hold (in multiple rows) phone or cell/mobile number, fax number, email address, Skype contact, IM address or any other type of contact detail that might in future be useful.

Will you be catering for meals? In which case, you might want to include Dining Room or Meeting Room (if you have more than one of either) and TableNumber as Bookable resources.

And I think you might need a lot more fields and tables to cover PAYROLL. Days or shifts or hours worked? Hourly or weekly or monthly payment type, and corresponding rates? Tax band? Tax due, Tax paid? Or maybe payroll is handled quite separately?

Anyway, there are a few thoughts to consider. I've developed several types of database in my career, but am not a professional database developer nor administrator, nor do I have any specific knowledge of hotel reservation systems. But I HAVE helped to implement Theatre booking systems, and some of the concepts I suggest come from that experience. I have always found it worthwhile to try to generalise beyond one table for one specific type of entity, if similar ones can be added merely by extension with an extra field or two, and a SOMETHING_TYPE of lookup table to tell you what kind of SOMETHING you are dealing with.

Best wishes for your development plans.

A later realisation. I think you already have much of what I suggested. BUT I am rather thrown by your use of Arrival Date and Departure Date in your BOOKINGS table. That makes it look conceptually more like a Guest Stay than the kind of Booking I was suggesting. Maybe you need a new concept for stay, and a table to match?

And I think you also need a concept of Bill and Line Item on the Bill as places to keep financial data, and the basis for charging and payment processing.

Going back to another commentator's mention of Functional Definition, consider how you might handle cases such as one person making a reservation for two rooms for three guests, two sharing, with different arrival and departure  dates for each guest. And one of them wants a bike on days 1 and 3 of their stay, a car and driver for day 2 for all three of them, and three bikes on day 4.

And by the way, they are splitting the Bill!


Remove all the LEFTs.

Make sure there is an index on term_id in wp_terms.  It is probably the PRIMARY KEY, which is good.


Mariadb started giving erroneous query results for 1 view

You get one wrong row and when that row is removed, you get the next wrong row in your result - means there are mismatches between table data and index data. You had checked through two clients and getting the same wrong result which means...

Option 1

If at all something happened to your databases around your mentioned 2 a.m. try running (for all databases)

mysqlcheck -o --all-databases

in commandline and

analyze table tablename;

Finally

optimize [no_write_to_binlog | local] table tablename;

The analyze / optimize are used again to ensure if everything is fine for your specific tables.

Option 2

Check your log files past previous night 11 p.m. If you can not find anything odd, post it here, we shall help.


SQL Server 2012 Support on Windows Server 2008 R2

Yes, you can install SQL Server 2012 on Windows Server 2008 R2 (the matrix here - which is exactly where the link in your screenshot goes, if you had clicked it - shows the supported edition/OS combinations).

The error message you posted tells you exactly what you need to do without even going to that page: Apply Service Pack 1 to the operating system.

(Though - especially if setting up a new system - you should consider a more modern operating system, and a newer SQL Server version for that matter.)


What are reasons **NOT** to use the MEMORY storage engine in MySQL?

Looking at the feature availability list at http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html two possible problems jump out:

No transaction or FK support, meaning you will have to manage transactional integrity and referential integrity in your own code were needed (which could end up being a lot less efficient than letting the DB do this for you, though that very much depends on your app's expected behaviour patterns).
Table level locking only: this could be a significant barrier to scalability if your app needs multiple concurrent writers to the same set of tables or in cases where your read operations use locks to ensure consistent data is read - in such cases a disk based table that supports much finer lock granularity will perform far better if enough of its content is currently cached in RAM.

Other than that, assuming you have enough RAM, a memory based table should be faster than a disk based one. Obviously you need to factor in taking snapshots to disk to address the issue of what happens when the server instance is reset, which is likely to completely negate the performance benefit overall if the data needs capturing often (if you can live with losing a day of data in such an instance you could just take a backup once per day, but in most cases that would not be acceptable).

An alternative might be to:

Use disk based tables, but ensure that you have more than enough RAM to hold them all in RAM at any given time (and "enough RAM" might be more than you think as you need to account for any other processes on the machine, OS IO buffers/cache and so forth)
Scan the entire contents (all data and index pages) of the table on each startup to preload the content into memory with SELECT * FROM <table> ORDER BY <pkey fields> for each table followed by SELECT <indexed fields> FROM <table> ORDER BY <index fields> for each index

This way all your data is in RAM, you only have to worry about I/O performance for write operations. If your app's common working set is much smaller than the whole DB (which it usually the case - in most applications most users will only be looking at the most recent data most if the time) you might be better of being more selective about how much you scan to preload into memory, allowing the rest to be loaded from disk on demand.


Remove sql server service from windows 7

Well, the crude way is to run regedit as admin, go down to

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services

and remove the service entry from registry and reboot...

Or perhaps use the sysinternals process explorer tool to do the same thing....


Out-of-order results in the READ UNCOMMITTED isolation level?

That entry is poorly-worded, so I don't blame you for getting confused by it.
The author(s) appear to be trying to make the point that even when dirty reads are allowed, it is impossible to encounter changes made by a later update before seeing changes made by an earlier update.
In other words, a 'dirty read' will always include all changes (committed or not) up that point in time.
It seems to be a way to stress a perceived difference compared with non-repeatable reads, as the preceding sentence mentions:

Dirty reads work similarly to non-repeatable reads; however, the second transaction would not need to be committed for the first query to return a different result.

No such distinction appears in the original definitions as I understand them, so it would be better if the part you refer to were removed.
Note that an implementation of read uncommitted isolation is not required to experience dirty reads, just that it may. Several popular products provide a read uncommitted isolation level that delivers a much higher degree of isolation and protection from so-called read phenomena. This is explicitly allowed by the SQL Standard.


You just need to do:

/etc/init.d/oracleasm start

.... to start off with.

Look at your screenshot - you completely ignored the fact that the oracleasm command gave you the command-line usage back, rather than actually doing anything.


What does this deadlock report from MySQL mean?

You didn't specify what language your code was in, but it appears that the PESSIMISTIC_WRITE is causing a FOR UPDATE type of select.

As such, the following is happening:

Transaction A

SELECT * FROM pings where id = '52c7e28eebb26a86fb1505b5' FOR UPDATE;

Transaction B

SELECT * FROM pings where id = '52c7e28eebb26a86fb1505b5' FOR UPDATE;

At this point, both transactions are holding a lock on the space you want to insert into.

Transaction A (continuing)

insert into pings (lastProcessTime, id, type) values (1450845, '52c7e28eebb26a86fb1505b5', 'INCIDENT')

Transaction A at this point will wait, as it wants B to release its lock before it continues.

Transaction B (continuing)

insert into pings (lastProcessTime, id, type) values (1450848, '52c7e28eebb26a86fb1505b5', 'INCIDENT')

However, B not only didn't release its lock, but it wants to insert the same record. The database says no way, and forces one (B, in this case) to deadlock and be undone.

This is actually extremely easy to replicate yourself. Just start 2 transactions (I used two Terminal windows to connect to my MySQL instance), one for each transaction, A and B. Start each with START TRANSACTION, and then just enter the commands in the same order as above, in each respective window. Any table with any primary key will work, just adjust the statements to each try to insert the same row.

Once you submit the second insert, you'll see the deadlock (generally applied to the transaction that tried to insert second.)

So, your issue is fully expected, as the FOR UPDATE on the SELECT statement is doing exactly what it is intended to do (which is to keep other transactions from changing the result of the SELECT until the transaction ends.)

Edit: Well, fully expected except for the fact that one would expect the SELECT ... FOR UPDATE to wait, regardless of whether there are any rows returned for the SELECT. However, if there are no rows, the behavior is as described above, but if there are rows returned, Transaction B will wait before returning any results from its SELECT. See this the bug report on this issue.

I would suggest changing your code to check for a duplicate key after inserting (either catching the exception or using ON DUPLICATE KEY ...), rather than trying to do a SELECT ... FOR UPDATE and then an insert.


How to use NOTIFY with postgres to send previous record and new record

You can call it from a trigger and pass both OLD and NEW in the notify payload. But by default the payload accepts only a maximum of 8000 bytes total, so if your JSON BLOBs are bigger than 4000 bytes each you will need to insert at least the old value in a table and pass the key in the payload.
If you are attempting to build logical replication, you might be better off using those facilities in newer versions of Postgres. You should be planning to upgrade soon as you are now 4 major versions behind current.


How to do WHERE x IN (val1, val2,...) in plpgsql

IN (...) expects a literal list of values.

If you write x IN (some_array) then PostgreSQL expects x to also be an array, since you're testing to see whether the array x appears in the one-element list of arrays (some_array).

If you instead want to see if the value x appears as an element of the array some_array you must write:

x = ANY (some_array)

(Note that you can use ANY with pretty much any operator, not just =, and that there's an ALL as well as an ANY.)


How can I convert a key in a SQL Server deadlock report to the value?

You have the hobt_id so the following query will identify the table:-

SELECT o.name
FROM sys.partitions p
INNER JOIN sys.objects o ON p.object_id = o.object_id
WHERE p.hobt_id = 72057632651542528

From that you can then run the following statement to identify the row in the table (if it still exists):-

SELECT %%LOCKRES%%,  *
FROM [TABLE NAME] WITH(INDEX(MyPrimaryKeyIndex))
WHERE %%LOCKRES%% = '(543066506c7c)'

Be careful with the above statement however, it will scan the target table so run in READ UNCOMMITTED and monitor your server.

Here's an article by Grant Fritchey about %%LOCKRES%% - http://www.scarydba.com/2010/03/18/undocumented-virtual-column-lockres/

And here's an article from my own blog about using %%LOCKRES%% to identify rows from an extended event:-  https://dbafromthecold.wordpress.com/2015/02/24/identifying-blocking-via-extended-events/


XtraBackup provides such a functionality


How to understand what Columns Have Changed in Merge Replication

You can use sp_showlineage and sp_showcolv to inspect the lineage and columns that have been changed.  The sp_showcolv results contain a column, colidx, which is the index of the column(s) that were changed for a given version of the row.

If you need to take a more proactive approach, you can setup an auditing scheme.  I've provided an example of how to setup an auditing scheme on my blog:  http://www.sqlrepl.com/sql-server/auditing-changes-in-merge-replication/


Show all results for past N "insert batches"

if i understand you correctly, here will do:

select user, sum(RT)
from test2 a
inner join ( select distinct(time) from test2 order by time desc limit 2) t
on a.time=t.time
group by user

The 'N' for number of latest batch is determined by the limit 2 in the subquery t.

For example, if i run the above query, showing data for the last 2 batches, the output is:

Dailymotion 8.11
Facebook    1.44
Google+     0.98
Instagram   0.15
Metacafe    0.80
Twitter     0.75
Youtube     0.48

But the query above won't show any users who don't have data in the last 'N' batch. If you need to show those users as well (as '0' RT), you will have to join the above query with your users table.

P/S: The test2 table in the query above contains the exact data as in your first table in your post.


to_number, ORA-01722: invalid number error

Your format picture, 'S99,999,999.99', requires that the number begin with a sign -- either "+" or "-" -- which your actual data does not contain.

Remove the S from the format picture.


Repartition PostgreSQL tables without losing data

Essentially you create a new partitioned table and fill it with the data from the old one, for example with INSERT INTO ... SELECT. Then you can drop the old table and rename the new one. That requires downtime.

If you mind downtime, create a new database from a schema dump of the old one, except that your partitioned table is now partitioned differently. Then set up logical replication between the two databases and switch over as soon as the replica has caught up.


How to grant permission for creating,reading and executing symmetric key in sql server 2008

If you create keys without a certificate like:

CREATE SYMMETRIC KEY smTestKey
 WITH ALGORITHM=AES_256
    , IDENTITY_VALUE = 'Key to protect bla'
    , Key_SOURCE = N'Secret pass phrase'
  ENCRYPTION BY PASSWORD = 'secret password';

then the following is enough:

GRANT ALTER ANY SYMMETRIC KEY TO dbuser

If you create symmetric keys that's encrypted by a certificate (that is created by another db user) for example:

CREATE SYMMETRIC KEY smTestKeyCert
 WITH ALGORITHM = AES_256
  ENCRYPTION BY CERTIFICATE testCert;

then you also need VIEW DEFINITION permission on the certificate:

GRANT VIEW DEFINITION ON CERTIFICATE::testcert TO dbuser

However, if you want to open symmetric key by decrypting with the certificate the dbuser opening the key would need CONTROL permission on the certificate:

GRANT CONTROL ON CERTIFICATE::testcert TO dbuser

UPDATE
To summerize:

The user that is creating asymmetric keys needs ALTER ANY ASYMMETRIC KEY permission
The user that is creating symmetric keys needs ALTER ANY SYMMETRIC KEY permission
The user that is creating keyA and encrypting it with keyB needs VIEW DEFINITION permissions on keyB
The user that is OPENING keyA and decryptiong it with keyB needs CONTROL permission on KeyB

your scenario:

--UserA needs to create Asymmetric keys so needs ALTER ANY ASYMMETRIC KEY PERMISSION
GRANT ALTER ANY ASYMMETRIC KEY TO userA

--UserB needs to create Symmetric keys so needs ALTER ANY SYMMETRIC KEY PERMISSION
GRANT ALTER ANY SYMMETRIC KEY TO userB

--UserA creates Asymmetric keys that are used by userB to create Symmetric keys and later open them
--So userA must create the Asymmetric key and Also give CONTROL permission on the Asymmetric key to UserB

--Create Asymmetric key
CREATE ASYMMETRIC KEY asym_CommonKey
WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD = 'admin@123';

--Give control permission to UserB
GRANT CONTROL ON ASYMMETRIC KEY::asym_CommonKey to UserB

--UserB creates a symmetric key using the Asymmetric key from userA
--Note, at this stage VIEW DEFINITION permission on the Asymmetric key would have been sufficient
CREATE SYMMETRIC KEY sym_CommonKey
WITH ALGORITHM = AES_256
ENCRYPTION BY ASYMMETRIC KEY asym_CommonKey

--UserB opens the Symmetric key, decrypting it with the Asymmetric Key
--Note, at this stage User B needs the CONTROL permission on the Asymmetric key.
OPEN SYMMETRIC KEY sym_CommonKey DECRYPTION BY ASYMMETRIC KEY asym_CommonKey with password ='admin@123'

Source:

MSDN

  Requires ALTER ANY SYMMETRIC KEY permission on the database. If
  AUTHORIZATION is specified, requires IMPERSONATE permission on the
  database user or ALTER permission on the application role. If
  encryption is by certificate or asymmetric key, requires VIEW
  DEFINITION permission on the certificate or asymmetric key. Only
  Windows logins, SQL Server logins, and application roles can own
  symmetric keys. Groups and roles cannot own symmetric keys.

MSDN 2

  The caller must have some permission on the key and must not have been
  denied VIEW DEFINITION permission on the key. Additional requirements
  vary, depending on the decryption mechanism:

DECRYPTION BY CERTIFICATE: CONTROL permission on the certificate and knowledge of the password that encrypts its private key.

DECRYPTION BY ASYMMETRIC KEY: CONTROL permission on the asymmetric key and knowledge of the password that encrypts its private key.

DECRYPTION BY PASSWORD: knowledge of one of the passwords that is used to encrypt the symmetric key.


SQL Error: OA-12991 - Unable to Drop a Column after removing all referenced Multi-Column constraints

Setup:

create table t3 (c1 number, c2 number);
alter table t3 add  supplemental log group sl1 (c1, c2) always;
alter table t3 add constraint t3_uk unique (c1, c2);
insert into t3 values (1, 2);
commit;

Try to drop the column:

SQL> alter table t3 drop column c2;
alter table t3 drop column c2
                           *
ERROR at line 1:
ORA-12991: column is referenced in a multi-column constraint

So far so good, goes as expected. Drop the constraint:

SQL> alter table t3 drop constraint t3_uk;

Table altered.

SQL> alter table t3 drop column c2;
alter table t3 drop column c2
                           *
ERROR at line 1:
ORA-12991: column is referenced in a multi-column constraint

Still doesn't work, same error. Check the supplemental log group:

select * from user_log_groups where table_name = 'T3'

OWNER LOG_GROUP_NAME TABLE_NAME LOG_GROUP_TYPE      ALWAYS      GENERATED
----- -------------- ---------- ------------------- ----------- --------------
BP    SL1            T3         USER LOG GROUP      ALWAYS      USER NAME

Drop the supplemental log group, then try dropping the column again:

SQL> alter table t3 drop supplemental log group sl1;

Table altered.

SQL> alter table t3 drop column c2;

Table altered.


show variables command constantly running on user

This specific command, "SHOW VARIABLES" is usually run a lot by "monitoring tools". Check if your monitoring tools are using this account.

Some other symptoms to tell if it is a monitoring system:

Using INFORMATION_SCHEMA related queries
SHOW STATUS command

HTH


Does PostgreSQL have a variable character storage layer that optimizes storage space by automatic dedupe?

PostgreSQL has functions for regular expressions.
PostgreSQL string functions
So you can write triggers (function + dml event) to be done what you need. I don't believe it has some optimizer that compress strings across the records. Or maybe I didn't understand what you mean in the question.


Is it possible to compress CLOB columns in DB2 10.5

Historically LOB data are stored in data structures separate from the normal row data, these data structures are not compressed. With Db2 for LUW 9.7 and later you have an option to store part of LOB columns, subject to the row length limitation, inline with data rows. Inline LOB data will be compressed along with the rows they belong to.

CREATE TABLE foo (
  lob_col CLOB (1M) INLINE LENGTH 2000
)

This is only helpful if the majority of your LOB values fall within the inline length limit. Note also that storing LOBs inline can have impact on query performance (fewer rows fit on a page, thus potentially increasing I/O demand) and logging overhead (inlined LOBs are always logged).


You can't use the album table because it isn't actually included in the query. (in the FROM clause). You need to do this instead:

DELETE songs.*
FROM songs
  JOIN album
    ON album.id = songs.id_album
 WHERE album.id_band = 16

See the section titled Multi-Table Deletes in the documentation for DELETE.

Alternatively, you could use WHERE EXISTS:

DELETE FROM songs
 WHERE EXISTS (SELECT *
                 FROM album
                WHERE id_band = 16
                  AND id = songs.id_album)


pgAdmin IV and PostgreSQL 12.0: ERROR: schema "public" already exists

Well, I was able to solve my problem by another path.

I suspected the problem was related to pgAdmin III and not PostgreSQL, then I opened my remote server through pgAdmin IV and I backed my remote database up.

After that, on pgAdmin IV, I restored the database and it worked fine, with no error.

Then, I believe the problem is related to creating a backup on pgAdmin III and restore it on pgAdmin IV.

If someone gets the same error in the future, please, use just pgAdmin IV to avoid problems.


How do I make a nullable "parent" hierarchy level?

You actually have 2 questions in one question. If you create a new question for the attributes it would be neater and I'll cut/paste half of this as an answer there :)

Nullable Parent Level

You probably don't want NULLs in your OLAP dimensions, and Kimball seems to agree.

  Nulls should also be avoided when we can’t provide a value for a
  dimension attribute in a valid dimension row. There are a several
  reasons why the value of a dimension attribute may not be available:

Missing Value – The attribute was missing from the source data.
Not Happened Yet – The attribute is not yet available due to source system timing issues.
Domain Violation – Either we have a data quality issue, or we don’t understand all the business rules surrounding the attribute. The data provided by the source system is invalid for the column type or outside the list of valid domain values.
Not Applicable – The attribute is not valid for the dimension row in question.

It kind of depends if you have an ETL process and a Data Warehouse or not how you should be handling them, but there are different types of 'not found'.

Think about the difference in a foreign key, one has an empty field, another has a field that's filled but the related record can't (or no longer) be found. I like to diffentiate between BLANK and DATA ERROR in my dimension.

In your example you could differentiate between 'no price code' and 'a price code I can no longer find'

If you have an ETL process with a Data Warehouse you can handle that easily in your ETL process, if you don't you would need some case statements in your DSV queries.

This question seems to reveal issues with the underlying Data Warehouse. There are arguments for and against both star and snowflake schema's but personally I tend towards a star schema, with some snowflake mixed in when necessary.
In any case the data cleansing and missing links needs to be solved in your Data Warehouse long before you reach the dsv.

Slowly Changing Dimension attributes

With regards to your Slowly Changing Dimension I don't see how the data type of hierarchies or keys in your dimension would change because the dimension somehow is SCD, that doesn't matter at all. You just need a validity rule somewhere in your ETL that gets picked up by your SSAS dimension definition (See here). But for any dimension key you create I suggest you use a surrogate key mostly because your surrogate key can be an int or bigint instead of a varchar and that could massively improve performance even for attribute keys.

  Using a numeric key column instead of a string key column or a
  composite key will improve the performance of attributes that contain
  many members.  This best practice is based on the same concept as
  using surrogate keys in relational tables for more efficient indexing.
  You can specify the numeric surrogate column as the key column and
  still use a string column as the name column so that the attribute
  members appear the same to end-users. As a guideline, if the attribute
  has more than one million members, you should consider using a numeric
  key.

Off course that numeric key would be a representation of 'the attribute' and not necessarily include the validity fields. The validity of the record is specified at the record in your dimension table, but as you state isn't necessary for your attribute keys.

For example this could be your dimension data

+---------------+-------+-----------+----------+
| DIMENSION_KEY |  NAME |  NAME_KEY |  CURRENT |
+---------------+-------+-----------+----------+
| 1             | tom   | 1         | y        |
| 2             | mat   | 2         | n        |
| 3             | mat   | 2         | y        |
+---------------+-------+-----------+----------+

Where you can pick dimension_key for the key of your key attribute and you could pick either name or name_key as the key of your name attribute.

Determining if it's worth the hassle for name depends on how many members your attribute will have (and your key attribute typically has most members).

In the end there isn't really any relation between the fact that you have a SCD and your decision what key is a good pick for your attribute. End user requirements make that decision for you. In the example dimension you would want all sales by mat reported under mat, and not have 2 mat's in your members when users report on that.


Tips on using SQL access advisor in Oracle

something like:

declare
task varchar2(30);

begin
task:= dbms_sqltune.CREATE_TUNING_TASK ( sql_id => '&sql_id',
plan_hash_value => '&hv' );
dbms_output.put_line (task);
dbms_sqltune.execute_tuning_task(task);
end;
/

to get the job running.
List the tasks:

SELECT task_id, task_name, created, advisor_name, status
FROM dba_advisor_tasks
where TRUNC(created) = TRUNC(sysdate)
order by CREATEd desc;

when it is ready, read the report

SELECT DBMS_SQLTUNE.report_tuning_task('&tuning_task') AS recommendations FROM dual;

and if you want, implement recommendations:

DECLARE
-- accept plan profile
  l_sql_tune_task_id  VARCHAR2(20);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.accept_sql_profile (
                          task_name => '&tuning_task',
                          name      => '&profile');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

Reading the packages can help a lot.

For the SQL access advisor take a look at SQL Access Advisor in Oracle Database


Removing secondary replica from SQL Availability Group causes large log file

I think the question has a wrong assumption.

  So is there a way to keep a single (primary) replica server active in the AG for the databases without removing them but keeping the log file from growing or being stuck in 'AVAILABILITY_REPLICA'?

...

  I'd rather not remove the AG as the listener is actively used and would require reconfiguration of numerous web.configs and other configuration entries. But, I also am slightly worried about the log file growth.

If you have an AG running on a single node, you can remove the database from the AG completely AND still connect to the database via the listener (as long as the clustering part is online either forced or using Windows 2012+ with dynamic quorum).

For a normal connection without any application intent the listener just functions as a dumb alias for the server. It doesn't check something like whether the database you're connecting to is part of its AG or not.


Mongodb how to cancel a --fork without mongod service 'option'

You just kill the process. killall mongod

To check that you don't have any mongod process running anymore, you can use ps -ef|grep mongod|grep -v grep  or just pgrep mongod. With that latter, if you get pid number (as answer), mongod is still running


Is there a recommended order to create a DV model and star schema?

Data Vault is an architecture and methodology to store data historically, sometimes holding hundreds of entities which can, but don't need to be interconnected.

Star Schema is an architecture to provide fast querying performance for specific questions, usually holding a few entities which are interconnected.

Without getting any more specific in both methods, I think you might have misunderstood Data Vault. It might be wise to inform yourself more about Data Vault, especially where it might lie in a typical Data Warehouse. Since this is not concerning the original question, I will not go into detail.

I have seen it like this:

Data Vault is being used as a core storage system. All the data at all the time. No transformation is being made.
Star Schema is build upon Data Vault Raw / Business Vault. Can be views, can be persistent tables. All calculations / transformations will be done here.


how to temporarily change the sql server settings in order to do a task and when finished revert back?

I'm not sure if what you are building is a good idea or not but the code below should get it working.

You basically need to include the GO statement to separate the script into individual batches. Using a temporary table to store the original settings values allows access to the values by each of the batches.

You can't include the GO statement in a stored procedure so if you are looking to automate this process you will need to separate the script out another way (Agent job steps, SSIS, PowerShell, etc)

IF OBJECT_ID('tempdb.dbo.#Settings') IS NOT NULL
    DROP TABLE #Settings;

CREATE TABLE #Settings
(
    Setting VARCHAR(100),
    Val INT
)
INSERT #Settings (Setting, Val)
SELECT 'show advanced options', cast(value_in_use as int) from sys.configurations where name = 'show advanced options'
UNION
SELECT 'xp_cmdshell', cast(value_in_use as int) from sys.configurations where name = 'xp_cmdshell'
UNION
SELECT 'Ad Hoc Distributed Queries', cast(value_in_use as int) from sys.configurations where name = 'Ad Hoc Distributed Queries'

SELECT * FROM #Settings;

if ((SELECT val FROM #Settings where Setting = 'show advanced options') = 0)
begin
    exec sp_configure 'show advanced options', 1
    reconfigure
end

if ((SELECT val FROM #Settings where Setting = 'xp_cmdshell') = 0)
begin
    exec sp_configure 'xp_cmdshell', 1
    reconfigure
end

if ((SELECT val FROM #Settings where Setting = 'Ad Hoc Distributed Queries')  = 0)
begin
    exec sp_configure 'Ad Hoc Distributed Queries', 1
    reconfigure
end
go
/* -----------------------------------------------------------------  do work - begin */

    SELECT  *
          FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes'
          , 'set fmtonly off exec msdb.dbo.sp_help_job')
          --where name in
          --        ( 'WebFeed UKProductOffer Offers'
                --   ,'WebFeed USProductOffer Offers'
                --   ,'WebFeed DEProductOffer Offers'
                --   ,'WebFeed ATProductOffer Offers'
                --   ,'WebFeed FRProductOffer Offers'
                --   ,'WebFeed EUProductOffer Offers'
                --   ,'WebFeed AUProductOffer Offers')

/* -----------------------------------------------------------------  do work - end */

---------------------------------------------------
-- restore the settings as they were previously
---------------------------------------------------
go
if ((SELECT val FROM #Settings where Setting = 'xp_cmdshell') = 0)
begin
    exec sp_configure 'xp_cmdshell', 0
    reconfigure
end

if ((SELECT val FROM #Settings where Setting = 'Ad Hoc Distributed Queries') = 0)
begin
    exec sp_configure 'Ad Hoc Distributed Queries', 0
    reconfigure
end

if ((SELECT val FROM #Settings where Setting = 'show advanced options') = 0)
begin
    exec sp_configure 'show advanced options', 0
    reconfigure
end

IF OBJECT_ID('tempdb.dbo.#Settings') IS NOT NULL
    DROP TABLE #Settings;


How to configure multiple hot standbys in Postgres?

Yes, it is possible.
I assume (based on you term "hot standby") that you are using streaming replication right now with your actual standby server.

For this you setup the master server with a pg_hba.conf allowing the slave to connect to the special database "replication". For the additional slave you need an additional entry (don't forget the reload)

# The standby server must connect with a user that has replication privileges.
# TYPE  DATABASE        USER            ADDRESS                 METHOD
  host  replication     replication     192.168.0.20/32         md5

The user "replication" is created by using psql:

$ CREATE ROLE replication WITH REPLICATION PASSWORD 'password' LOGIN

The configuration on the master should be there (wal_level = hot_standby and probably an activated archiving).

On the reporting-slave you just need a full backup and a valid recovery.conf. If you use pg_basebackup it can create a recovery.conf for you (see the blog entry https://www.depesz.com/2012/02/03/waiting-for-9-2-pg_basebackup-from-slave/).
Don't forget hot_standby = on to use the slave for read-only-queries.

You also can use the "old" slave as master for the reporting-slave (cascading replication).


Update and Insert data from a updated table

Requirements are not clear on insert.
If the ID are not correct then how would you know a non-matching usercode is not already present?

Update:
What this does is if t2 is null then it will not replace a value in t1

update t1
set t1.col1 = insull(t2.col1, t1.col2) ....
from table1 t1
join table2 t2
on t2.usercode = t1.usercode


Small startup: Cassandra + Hadoop or ElasticSearch

If you foresee that moving to Cassandra is definitely in your future, it will be easier to do while your dataset is still small and manageable.  Also, as you learn and get a feel for Cassandra, a small dataset is a better one to make mistakes on (and thus, easier to correct them).  That way your data model is solid by the time your dataset gets big, and that's when it really matters.  And IMHO, there is no such thing as "too small" for Cassandra.

One of our applications uses Cassandra and ElasticSearch in prod.  Based on those experiences, I would offer some caution about using ElasticSearch as a persistent datastore.  We've seen it lose writes fairly often.  Have a read through this discussion on Quora, appropriately titled: Why should I NOT use ElasticSearch as my primary datastore?  That being said, it works great as a search engine.


How can dm_db_index_usage_stats show 0 for user_seeks and user scans but a large number for user_lookups?

The user_lookups are lookups in to the index. The original scan or seek will have been on another non covering index.

For example

CREATE TABLE T
(
X INT IDENTITY PRIMARY KEY,
Y INT CONSTRAINT UQ UNIQUE NONCLUSTERED,
Z CHAR(8000) NULL
)

INSERT INTO T(Y)
SELECT DISTINCT number
FROM master..spt_values

GO

DECLARE @Z INT
SELECT @Z = Z
FROM T
WHERE Y = 1

GO 1000

Gives the following plan

SELECT index_id, user_seeks,user_scans, user_lookups
FROM sys.dm_db_index_usage_stats
WHERE object_id = object_id('T')
ORDER BY index_id

Shows 1,000 seeks on the NCI and 1,000 lookups on the CI.

index_id    user_seeks           user_scans           user_lookups
----------- -------------------- -------------------- --------------------
1           0                    0                    1000
2           1000                 0                    0

BTW: Just in case you were not aware the user_lookups shown here refers to the number of times a plan was executed containing the lookup operator not the number of lookups that actually occurred. e.g. Both of the following increment the counter by 1 despite performing 0 and 2,161 lookups in reality

SELECT  Z
FROM T
WHERE Y = -999

SELECT Z
FROM T WITH (FORCESEEK)
WHERE Y >= 0


SQL Server 2016 Standard or Enterprise for SharePoint 2016

Are there any disadvantages of going for SQL server standard edition
  instead of enterprise

The disadvantage is you can't use enterprise features, like AlwaysOn AG among others (see below). If you aren't going to use these features, then you don't need Enterprise generally speaking. The advantage is you'd save some money. Both are licensed per core, and enterprise costs more per core.... like 4X more than standard.

Here are features that come in Enterprise that don't come in Standard:

Unlimited memory (max of OS)
Online Page restore
Online index rebuilds
Mirrored backups
Automatic tuning
Transparent database encryption
Parallel index operations
etc...

See the data sheets, and the full comparisons for the differences. Generally speaking, unless you really need those enterprise features, I'd would opt for standard for the HUGE cost savings.

Microsoft also has a guide to help you decide which one you need.


After further digging, it appears someone had left a transaction open, which was the underlying cause of this issue.

I managed to find there was a transaction open by running:

SELECT * FROM sys.sysprocesses WHERE open_tran = 1

I then used this article https://blog.sqlauthority.com/2010/10/01/sql-server-get-query-running-in-session/ which provided the below SQL to find what was being run.

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = (YourSessionID)
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO

It appeared to be an empty transaction that didn't have any active SQL running so I was able to restart the SQL process to resolve this.


SQL Developer suggesting I add aggregate to group by clause

Looks like a bug in (the parser of) the specific version of SQL Developer (4.1.5.21) you use.

Update to the most recent version and if it shows the same behaviour, send a bug report to Oracle.

You could try to find a smaller example (something like the below, if it does exhibit the same buggy suggestions):

CREATE TABLE t
  ( a INT NOT NULL,
    b DATE NOT NULL
  ) ;

-- no suggestions
SELECT /*+ materialize */ a, MIN(b) AS min_b
FROM t
GROUP BY a ;

-- buggy suggestions
WITH ct AS
  ( SELECT /*+ materialize */ a, MIN(b) AS min_b
    FROM t
    GROUP BY a
  )
SELECT *
FROM ct ;


You dont need to have them use COPY_ONLY. Only An intermediate LOG BACKUPS will break the LSN. What you can do is explicitly DENY BACKUP LOG to [user|group] privilege to developers or developer group. Alternatively, just create a ROLE and deny backup log to that role. So all the users in that role will inherit the permissions.

e.g.

USE test_kin
GO
CREATE ROLE [deny_log_backups]
GO
USE [test_kin]
GO
CREATE USER [Kin] FOR LOGIN [Kin]
GO
ALTER USER [Kin] WITH DEFAULT_SCHEMA=[dbo]
GO
use test_kin
GO
DENY BACKUP LOG TO [deny_log_backups]
GO
USE test_kin
GO
EXEC sp_addrolemember N'deny_log_backups', N'kin'
GO

Now test it :

backup database [test_kin]
to disk = 'C:\crap_test\kin_test_full.bak'
with compression, stats =10, init

---- ### success for FULL BACKUP

backup log [test_kin]
to disk = 'C:\crap_test\kin_test_log.log'

 --- $$$ ERROR MESSAGE

Msg 262, Level 14, State 1, Line 3
BACKUP LOG permission denied in database 'test_kin'.
Msg 3013, Level 16, State 1, Line 3
BACKUP LOG is terminating abnormally.


Can anyone recommend an encryption method for ALL user dbs using SQL Server 2016 STANDARD edition?

There are 3rd party tools which replicate TDE functionality.

I have tested the following during an exercise to evaluate whether we can move from Enterprise to Standard Edition:

https://www.database-encryption.com/
https://netlibsecurity.com/

In both cases, they seem to work by placing a driver between the SQL binaries and the storage layer, and after configuration are transparent to the connecting application. Queries work in the exact same way as for TDE-enabled databases. Once the data leaves the storage, it is unencrypted.  It would appear as an unencrypted database to all authenticated connections.

They do cost, but I believe there are trial versions available.

In performance tests (10,000 small insert queries into a clustered index) I found that DBDefence closely mirrored the performance of a TDE-enabled database. Query times for Netlib increased by approx 8%.  Obviously your specific scenario may differ.

All my tests were performed on SQL 2016 Standard.


Force spool to avoid creating temp tables?

A very similar question was asked and answered on StackOverflow: How can I force a subquery to perform as well as a #temp table?  To summarize,

The most reliable method is simply to use a #temp table and materialize it yourself.
Failing that, see Provide a hint to force intermediate materialization of CTEs or derived tables. The use of TOP(large_number) ... ORDER BY can often encourage the result to be spooled rather than repeatedly re evaluated.  Even if that works however there are no statistics on the spool.
Another option is to use a user-defined function.  Multi-statement functions (as described in How to Share Data between Stored Procedures) appear to force the SQL Server to materialize the results of your subquery.  In addition, they allow you to specify primary keys and indexes on the resulting table to help the query optimizer.  This function can then be used in a select statement as part of your view.  For example:

CREATE FUNCTION SalesByStore (@storeid varchar(30))
   RETURNS @t TABLE (title varchar(80) NOT NULL PRIMARY KEY,
                     qty   smallint    NOT NULL)  AS
BEGIN
   INSERT @t (title, qty)
      SELECT t.title, s.qty
      FROM   sales s
      JOIN   titles t ON t.title_id = s.title_id
      WHERE  s.stor_id = @storeid
   RETURN
END

CREATE VIEW SalesData As
SELECT * FROM SalesByStore('6380')


Data from more than 1 query - possible union needed?

You need to use a LEFT JOIN to get all of QueryA  and whatever records exist for QueryB

SELECT * FROM QueryA as A LEFT JOIN QueryB as B on A.columnid=B.columnid


Index not being used for logical operation

Seems like you are using an integer column flag where a series of BIT columns or - even better - an intersection table should be used.

The index on (flag) is effectively useless for the condition where (flag & 0x40) != 0. Even if it was used, the whole index would have to be scanned first, then all the values computed (with & 0x40) and compared to 0. After that, the matching rows would have to be fetched from the table, resulting in multiple, even millions of seeks. The optimizer is clever enough to do a simple - and fast but still rather slow - whole table scan.

You could improve the efficiency somewhat with a "covering" index on (flag, email). That index would be used but only with a full index scan - you probably can't avoid that with the current design.

Other suggestions, some involving altering the table:

If you can split the flags to multiple BIT columns, the query could be simplified and the possibility of using indexes would arise. Still, you might end up needing multiple (composite) indexes, one for each BIT flag.
If the possible combinations of flags is low (as the cardinality = 18 suggests), you could use that info in your favour by changing the condition to:

where flag in (64, 65, 66, 68, 72, 80, ..., 192, 193, ...)
-- the list of all possible flag values that have the proper bit set

This might use the (flag, email) index slightly better than before, especially if this list is small, still scanning a small or large part of the index but not the whole index.
A much better design would be to use an intersection (many-to-many) table to store which users have some attribute, instead of using flags.


SQL3550W: ... target column has been defined as GENERATED ALWAYS

Using the same IXF files created by db2move export, you can manually load the data using db2 import with the MODIFIED BY IDENTITYOVERRIDE parm for those tables that require it.


It depends on the GUI but they all should have a way of selecting the SYSDBA privilege. Toad has it appear as a drop-down box called 'Connect as:' as does Oracle Enterprise Manager. For SQL Developer you use the 'Role' box when creating a new connection.

As far as / as sysdba is concerned it just means that operating system authentication is being used so no password is needed. You can only use it if you are logged in as the oracle user or a member of the dba group (or for Windows, I believe as an administrator).

When you grant a user the SYSDBA privilege it only means they have the option to log in with the SYSDBA privilege and not that they will have dba privileges when they log in normally. The same is true for the SYS user: you can log in as SYS without the SYSDBA privilege and so lack the ability to shutdown the database, for example.


Application would be much better place to deal with such cases as Aaron Bertrand said but you can do it with SQL as well .

select
    case when TypeId = 1 then 1
         when TypeId = 0 then 2
         end
    from Type
    where mail = 'name@test.com'
UNION ALL
SELECT 3
FROM DUAL
 WHERE NOT EXISTS (SELECT NULL FROM Type WHERE mail = 'name@test.com')

**Depends on RDMS you're using you may or may not need specify "FROM DUAL".


Unable to search data fast in postgres table

You get that error because data->'application_form_attributes' is too large to fit in an index entry.

You'd have to index the whole left side of the expression:

CREATE INDEX ON mytable (data->'application_form_attributes'->>'5705');

Of course, if '5705' is not a constant, that won't get you far.

With your index, you could change the query to:

... WHERE data->'application_form_attributes' ? '5705'

but since you cannot create the index, that won't help you.

I don't think that there is an easy way out. Perhaps it would be best to "normalize" the schema, pull those values you want to search for out of the JSON and model it as several tables with foreign key relationship. Then your query will be simpler and perform better, and indexing won't be a problem.


WITH cte1 AS ( SELECT cr.course_id, sr.student_id, COUNT(*) cnt
               FROM course_requirements cr
               JOIN student_result sr ON cr.qualification_id = sr.qualification_id
               GROUP BY cr.course_id, sr.student_id
             ),
     cte2 AS ( SELECT course_id, COUNT(qualification_id) cnt
               FROM course_requirements
               GROUP BY course_id
             )
SELECT DISTINCT cte1.course_id, cte1.student_id
FROM cte1, cte2
WHERE cte1.cnt = cte2.cnt
ORDER BY 1,2

fiddle

Add proper conditions by result_id fields values if needed.


Backup Certificate SQL Server and return bytes with a Select Statement

New in SQL Server 2012 and also in 2014 are two new encryption functions that will return a binary stream as you are describing. CREATE CERTIFICATE was also updated to take BINARY input for public and private keys.

CERTENCODED will return the public key portion of the certificate in BINARY form.
CERTPRIVATEKEY will return the private key portion of the certificate in BINARY form. this is encrypted so the password to decrypt it would also be needed.

The above could be saved in a binary form and written to a disk, or stored in textual representation of the binary form and written to disk.

Should you need to create it again, use the BINARY options of the CREATE CERTIFICATE function.

Edit: There is nothing in 2008R2 that can do the same that I know of through system functions.


i cannot install mysql plugin keyring_file.dll

The reason for that error is that mysql can't locate the keyring_file.dll file.

To fix that do the ff:

Edit the my.ini file located in C:\ProgramData\MySQL\MySQL Server 5.7\ and add the ff in the [mysqld] block:

early-plugin-load=keyring_file.dll
keyring_file_data=C:/Program Files/MySQL/MySQL Server 5.7/lib/plugin/keyring_file

Restart the MySQL service:

net stop MySQL57
net start MySQL57

Validate that the keyring plugin is now active:

SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'keyring%';


Alternative of replication in internet disconnection period of time

Sync Framework is designed explicitly to synchronize occasionally connected data sets.


How to restore a database from the SQL 2008R2 log?

There is no free tool to do this. Basically, your only other option is to restore a backup. For that you need to have made a full backup of the database. If you have one, you can recover your table at the time of your full backup by restoring it into another database, and copy the table back to your original database.

Plus, if your database is in full recovery model, you can backup your transaction log and try to restore it after the full backup restore and other transaction log backups that you might have taken in between if you have a backup plan.
So, do you have backups of your database, and when were they taken?


Is it safe and reliable to backup MySQL databases by backing up the data folder?

Take a look here.

In a word, a resounding "yes" is the answer to your question provided the server is stopped!

I'm not sure that there are any hot backups available for Windows - Percona's XtraBackup only works on Unix (AFAIK). It's possible that MySQL have a Windows solution for hot backups, but there's nothing Open Source - (again AFAIK, and it's been a while since I've looked at this issue).

But, you are perfectly safe with a cold backup!

I'm just wondering how you're planning to make use of the cold backup in the absence of a password? Does the app that uses it have it's own table dumping facility? My advice on this is to figure out how to get your raw data back ASAP!


Controlling the order in which merge articles are applied by snapshot at initialization?

Yes, utilizing sp_changemergearticle to change the processing order will alleviate this issue.


Couldn't install SSMS: A pending restart is blocking setup from completing

Go to the Visual Studio Isolated Shell download page, fill out the form, and ask for permission to get VS Shell.  Download Visual Studio 2015 Shell (isolated), run it, repair, reboot, run it again, uninstall, reboot.

Now, SSMS 17.x should install without errors.

Alternatively, (and less safely): Search the registry for "visual studio 2015" and delete all the keys that are related to the shell or its prerequisites. Repair SQL Server 2016 and then install SSMS.


Getting stuck with creating better index (Optimize Join)

First, let's clean up the data.

Is Monthly a true/false value?  Then use 0/1 and put it in TINYINT NOT NULL.  Or is it tri-state:  Yes/No/unspecified?

If Off_comments is an optional string, then cleanse the data so that the empty string and '' are turned into NULL when it is stored.  (Meanwhile, one or two UPDATEs can clean up the data.)

I recommend those because OR is hard to optimize, and this will probably eliminate such.

LEFT says that the 'right' table is optional.  Yet AND t3... says that you must find a row.  So change both LEFT JOINs to JOINs.

Once that is done, it may be efficient to check for T3.frnd_id= '4' first instead of last.  Could it be that there aren't many rows with 4?

Indexes...

Assuming the above works, T3 needs INDEX(frnd_id, id), then T2 needs INDEX(On_site_id, id).

If the optimizer prefers to start with T1, first look for any = things in the WHERE.  But, the only possibility is monthly, depending on the discussion above.  Off_comments is not a candidate for indexing since it is TEXT.

I'll digress a moment.  What is T3 about?  It has only 50 rows?  Perhaps it is "over-normalization"?  If so, couldn't you simply put frnd_id into T2?

Grrr... Have you "simplified" the question?  This seems invalid:  social enum( 'Facebook','twitter') DEFAULT 'LI', since LI is not an option in the enum!

Grrr-squared...  This is probably not what you wanted:

`T1.createDate`

Instead:

`T1`.`createDate`

Please fix those and any other mis-simplifications!

T1 may be able to use INDEX(createDate).

One more thing:  1:1 relationships (T1.id = T2.id) are usually a questionable design.  Is id the PRIMARY KEY on both tables?