How can you save all user-defined functions to txt files on your local hard drive? I am running SQL Server 2008 and need to back up the UDF scripts/queries that I have created. How can this be done in a timely manner? I tried right clicking on the folder icon where the UDF are saved (Table-Valued Functions) to copy the folder but am not bale to.

The only thing I can think up is using the MODIFY command on each and every single one my UDF scrips and copying and pasting to separate text files.

Even If if there is only  a simple way to save ALL the Scripts in one text file instead of separate txt files I would be more than happy to do that instead of having to copy and paste every single file one at a time.

Any suggestions?

The easiest way I can think of is the following:

Right-click on your database -> Tasks -> Generate Scripts...
Select specific database objects
Select the checkbox User-Defined Functions
Save to file and just name your text file whatever you'd like

This will generate and save the scripts for your UDFs to your desired file.  You could use PowerShell (off the top of my head) to generate scripts for each UDF and save them to a separate text file (one text file per UDF).

Edit:  Below is some PowerShell to do what you want to do.  This script will get all of the User Defined Functions in your database (set the variables to your appropriate environment) and write the definition of each of them to their own file, naming the file what the function is named.

$serverName = "YourServerName"          # example: "YourServer" or "YourServer\YourNamedInstance"
$databaseName = "YourDatabaseName"
$outputDirectory = "C:\Dir1\YourUDFs"   # the dir you want these files saved in

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

$server = New-Object Microsoft.SqlServer.Management.Smo.Server($serverName)
$database = $server.Databases.Item($databaseName)
$udfCol = $database.UserDefinedFunctions | Where-Object {$_.Schema -ne "sys"}
foreach ($udf in $udfCol) {
    $udfName = $udf.Name
    ($udf.TextHeader + $udf.TextBody) |
        Out-File -FilePath $outputDirectory\$udfName".txt"
}


Check remaining time for sqlserver I installed the evaluation version of SQL Server on Ubuntu, which is free for 180 days.

How can I check how many days I have left in the trial period?

Does that edition have any limitations, such as storage or CPU?

How can I enter a serial number to activate SQL Server before (or event after) the 180 days?

There's a couple of ways you can check how many days have past since you installed the evaluation edition:
From Pinal Dave:
SELECT create_date AS 'SQL Server Installation Date'
       ,DATEADD(dd, 180, create_date) AS 'SQL Instance will expire on'
sys.server_principals
WHERE NAME = 'NT AUTHORITY\SYSTEM'

Alternative solution - Finding Your SQL Server Evaluation Time Limit:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE
GO

EXEC sp_configure 'Agent XPs', 1;
RECONFIGURE
GO

DECLARE @RemainingTime INT
DECLARE @InstanceName SYSNAME
SELECT @InstanceName = CONVERT(SYSNAME, SERVERPROPERTY('InstanceName'))
EXEC @RemainingTime = xp_qv '2715127595', @InstanceName
SELECT @RemainingTime 'Remaining evaluation days:'

GO

The evaluation edition is a full edition without limitation (other than the timeframe you can use it for), as mentioned in the License section of the Docker hub.
I don't believe you need to enter a serial after you've purchased it (you just need to retain the purchase information Microsoft provides in case you were audited). You can follow this guide on how to upgrade to a non-evaluation edition.


Can't start mysqld service after changing datadir I have an AWS EC2 instance with RHEL 7 on which I installed mysql-5.5 on xvda. Its datadir is under /var/lib/mysql (as is the default).

I am importing dumps from a database on version 5.5.29 and the currently installed version is 5.5.60

A larger volume xvdf has been attached since I was running out of space and mounted it at /data_drive

Then followed these steps to change the data directory:

Stop mysqld and verify if it had stopped
used rsync -av /var/lib/mysql /data_drive to move folder with permissions
moved the older mysql data directory to a backup file on the xvda (original volume)
Edited /etc/my.cnf to change the [mysqld] block to reflect the new location in both datadir and socket

datadir=/data_drive/mysql
 socket=/data_drive/mysql/mysql.sock
added the client block at the end of the file

[client]
 port=3306
 socket=/data_drive/mysql/mysql.sock
saved the file and then tried to restart the mysqld service as root user and non-root user but it won't start.

Here is the /var/log/mysqld.log output from the relevant timestamps:

180713 19:27:57 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
180713 20:20:37 mysqld_safe Logging to '/var/log/mysqld.log'.
180713 20:20:37 mysqld_safe Starting mysqld daemon with databases from /data_drive/mysql
180713 20:20:37 [Warning] Can't create test file /data_drive/mysql/ip-10-0-0-180.lower-test
180713 20:20:37 [Note] /usr/sbin/mysqld (mysqld 5.5.60) starting as process 18669 ...
180713 20:20:37 [Warning] Can't create test file /data_drive/mysql/ip-10-0-0-180.lower-test
180713 20:20:37 [Warning] Can't create test file /data_drive/mysql/ip-10-0-0-180.lower-test
180713 20:20:37 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
180713 20:20:37 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
180713 20:20:37 InnoDB: The InnoDB memory heap is disabled
180713 20:20:37 InnoDB: Mutexes and rw_locks use GCC atomic builtins
180713 20:20:37 InnoDB: Compressed tables use zlib 1.2.3
180713 20:20:37 InnoDB: Using Linux native AIO
180713 20:20:37 InnoDB: Initializing buffer pool, size = 128.0M
180713 20:20:37 InnoDB: Completed initialization of buffer pool
180713 20:20:37  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.

When I ran sudo mysql_upgrade it gave the following output:

Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Error: Failed while fetching Server version! Could be due to unauthorized access.
FATAL ERROR: Upgrade failed

This is how the /data_drive/mysql directory looks at the moment:

[ec2-user@ip-10-0-0-180 mysql]$ ls -al /data_drive/mysql/
total 5921812
drwxr-xr-x. 4 mysql    mysql       4096 Jul 13 18:45 .
drwxr-xr-x. 5 ec2-user root        4096 Jul 13 18:46 ..
-rw-rw----. 1 mysql    mysql 6053429248 Jul 13 18:45 ibdata1
-rw-rw----. 1 mysql    mysql    5242880 Jul 13 18:45 ib_logfile0
-rw-rw----. 1 mysql    mysql    5242880 Jul 13 18:35 ib_logfile1
drwx------. 2 mysql    root        4096 Jul 12 19:42 mysql
drwx------. 2 mysql    mysql       4096 Jul 12 19:42 performance_schema

This is likely to be an issue with SELinux. If SELinux is running in enforcing mode, you need to to persuade it to allow mysqld to use the non-default datadir. Or alternatively, put SELinux into permissive mode, or disable it entirely. Run sestatus to see which mode it's currently running in. Run grep mysql /var/log/audit/audit.log to see if SELinux has blocked any mysql file access attempts or similar. (But note that this is fine if running in permissive mode, because then it doesn't actually block anything, it just logs what it would have blocked if you were running in enforcing mode.)

Run setenforce permissive to put SELinux into permissive mode, though note you need to edit /etc/sysconfig/selinux (or /etc/selinux/config) to make the change permanent.

If you go the route of trying to persuade SELinux to allow the new datadir, you need to make sure the SELinux context is kept for the files. I suspect this may be lost when using rsync, but not sure.

Here's a page explaining in detail how to deal with SELinux when moving the MySQL datadir: How to move MySQL data directory on CentOS and Ubuntu


SQL Server 2016 Bad Query Plan locks up DB once a week Once a week, for the past 5 weeks, around the same time of day (early morning, may be based on user activity when people are starting to use it), SQL Server 2016 (AWS RDS, mirrored) starts timing out a lot of queries.

UPDATE STATISTICS on all tables always fixes it immediately.

After the first time, I made it update all stats on all tables nightly (instead of weekly), but it still happened, (about 8 hours after the update stats runs, but not every day that it runs).

This last time, I enabled Query Store to see if I could find which specific query/query plan it was.  I think I was able to narrow it down to one:

After finding that query, I added a recommended index that was missing from this not-frequently-used query (but which does touch a lot of frequently used tables).

The bad query plan was doing an Index Scan (on a table with only 10k rows).
Other query plans that returned in milliseconds, used to do the same scan though.  The newest query plan, after creating the new index does only seeks.  But even without that index, 99% of the time, it was returning within a few milliseconds, but then, weekly, it'd take > 40 seconds.

Bad one that times out: http://brentozar.com/pastetheplan/?id=rymaWt56e
Previous plans that don't time out: http://brentozar.com/pastetheplan/?id=HyN7ftcpe
Newest plan with new index: http://brentozar.com/pastetheplan/?id=ryLuGKcag

This started happening after moving to SQL Server 2016 from 2012.

DBCC CHECKDB returns no errors.

Will the new index fix the problem, making it never choose the bad plan again?
Should I "force" the plan that works well now?
How do I make sure this doesn't happen to another query/plan?
Is this a symptom of a larger problem?

Indexes I just added:

CREATE NONCLUSTERED INDEX idx_AppointmetnAttendee_AttendeeType
ON [dbo].[AppointmentAttendee] ([UserID],[AttendeeType])

CREATE NONCLUSTERED INDEX [idx_appointment_start] ON [dbo].[Appointment]
(
    [ProjectID] ASC,
    [Start] ASC
)
INCLUDE (   [ID],
    [AllDay],
    [End],
    [Location],
    [Notes],
    [Title],
    [CreatedByID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Full query text:

https://pastebin.com/Z5szPBfu (LINQ-generated, I can/should be able to optimize columns selected, but it should be irrelevant to this problem)

I'm going to answer your questions in a different order than you asked them.

4. Is this a symptom of a larger problem?

The new cardinality estimator in SQL Server 2016 could be contributing to the problem. SQL Server 2012 uses the legacy CE and you didn't experience your problem on that version. The new cardinality estimator makes different assumptions about your data and can generate different query plans for the same SQL. You may experience better performance for some queries with the legacy CE depending on your query and your data. So, some parts of your data model may not be the best match for the new CE. That's okay, but you may need to work around the new CE for now.
I would also be concerned with inconsistent query performance even with daily stats updates. One important thing to note is that gathering statistics on all tables will effectively wipe out all query plans from the cache, so you could have an issue with statistics or it could have to do with parameter sniffing. It's hard to make a determination without a lot of information about your data model, data change rate, statistics updating policies, how you're calling your code, etc. SQL Server 2016 does offer some database level settings for parameter sniffing which could be helpful, but that could affect your whole application instead of just the one problematic query.
I'll throw out an example scenario which could lead to this behavior. You said:

Some users could have 1 permission record, some, up to 20k.

Suppose you gather stats on all tables which wipes out all query plans. Depending on the factors mentioned above, if the first query of the day is against a user with just 1 permission record then SQL Server may cache a plan which works well for users with 1 record but works terribly with users with 20k records. If the first query of the day is against a user with 20k records then you might get a good plan for 20k records. When the code is run against a user with 1 record it may not be the most optimal query but it may still finish in ms. It really does sound like parameter sniffing. It explains why you don't always see the issue or why sometimes it takes hours to show up.

1. Will the new index fix the problem, making it never choose the bad plan again?

I think that one of the indexes that you added will prevent the problem because accessing the required data through the index will be cheaper than doing a clustered index scan against the table, especially when the scan cannot terminate early. Let's zoom in on the bad part of the query plan:

SQL Server estimates that only one row will be returned from the join on [Permission] and [Project]. For each row in the outer input it will do a clustered index scan on [Appointment]. All rows will be scanned from this table, but only those matching the filtering on [Start] will be returned to the join operator. Within the join operator the results are further reduced.
The query plan described above can be okay if there really is just one row sent to the outer input of the join. However, if the cardinality estimate from the join is wrong and we get, say, 1000 rows, then SQL Server will do 1000 clustered index scans on [Appointment]. The performance of the query plan is very sensitive to estimation issues.
The most direct way to never get that query plan again would be to create a covering index against the [Appointment] table. Something like an index on [ProjectId] and [Start] should do it. It looks like this is exactly the [idx_appointment_start] index that you created to address the issue. Another way to discourage SQL server from picking the query plan is to fix the cardinality estimate from the join on [Permission] and [Project]. Typical ways to do that include changing the code, updating statistics, using the legacy CE, creating multi-column statistics, giving SQL Server more information about local variables such as with a RECOMPILE hint, or materializing those rows into a temp table. Many of those techniques aren't a good approach when you need ms level response time or have to write code through an ORM.
The index that you created on [AppointmentAttendee] is not a direct way to address the problem. However, you will get multi-column statistics on the index and those statistics could discourage the bad query plan. The index may provide a more efficient way to access the data which may also discourage the bad query plan, but I don't think there's any kind of guarantee that it won't happen again just with the index on [AppointmentAttendee].

3. How do I make sure this doesn't happen to another query/plan?

I understand why you are asking this question but it's an extremely broad one. My only advice is to try to better understand the root cause of the query plan instability, to validate that you have the right indexes created for your workload, and to carefully test and monitor your workload. Microsoft has some general advice on how to deal with query plan regressions caused by the new CE in SQL Server 2016:

The recommended workflow for upgrading the query processor to the latest version of the code is:

Upgrade a database to SQL Server 2016 without changing the database compatibility level (keep it at prior level)

Enable the query store on the database. For more information about enabling and using the query store, see Monitoring Performance By Using the Query Store.

Wait sufficient time to collect representative data of the workload.

Change the compatibility level of the database to 130

Using SQL Server Management Studio, evaluate if there are performance regressions on specific queries after the compatibility level change

For cases where there are regressions, force the prior plan in the query store.

If there are query plans that fail to force or if performance is still insufficient, consider reverting the compatibility level to the prior setting and then engaging Microsoft Customer Support.

I'm not saying that you need to downgrade to SQL Server 2012 and to start over, but the general technique described may be useful for you.

2. Should I "force" the plan that works well now?

It's entirely up to you. If you believe that you have a query plan that works well for all possible input parameters, are comfortable with the query store's functionality, and want the peace of mind that comes with forcing a query plan then go for it. Forcing query plans that had regressions is part of Microsoft's recommending upgrade policy to SQL Server 2016 after all.


Is pg_trigger_depth() bad to use for preventing trigger cascading (recursion)? Why is pg_trigger_depth() = 0 bad to use (for anything other than debugging)  when preventing trigger cascading (recursion)?

Can someone provide code to demonstrate why it is bad?

I am guessing because if multiple triggers are working on the same data at the same time a condition that stops a trigger using pg_trigger_depth() = 0 will stop any trigger that is second in line to do work.

I thought it would be a good solution to this (my) question here, but I am told otherwise:

Within a trigger is there a way to tell if an update or insert came from a trigger or not?

Thought it would make a good question.

It is offered here as a solution:

Prevent recursive trigger in PostgreSQL

Postgres 9.3 documentation:

https://www.postgresql.org/docs/9.3/static/functions-info.html

It is not bad to use per se (IMHO). You just need to be aware of implications.

I'd rather make it pg_trigger_depth() < 1 instead of pg_trigger_depth() = 0, on principal, but that's not important here. We are talking about triggers like:

CREATE TRIGGER my_trigger
AFTER INSERT ON my_tbl
FOR EACH ROW
WHEN (pg_trigger_depth() < 1)
EXECUTE PROCEDURE my_trigger_func();

If you later add triggers that would in turn fire your trigger, nothing will happen instead. Your trigger only reacts to non-trigger events. Depending on your setup this may be exactly what you want - or kind of a trap, if you forget about this and later add more triggers that should in turn (also) fire this trigger. You are changing default behavior. Be sure to document this clearly.

Be aware that pg_trigger_depth() counts any trigger, not only the same trigger to prevent recursion, so the condition prevents the trigger from firing if called from any other trigger, too.
The manual:

  current nesting level of PostgreSQL triggers (0 if not called, directly or indirectly, from inside a trigger)

Related:

How to prevent a PostgreSQL trigger from being fired by another trigger?


What exactly is overlapping candidate key? Can some one please explain to me in simple terms what is overlapping candidate key ? What is overlapping as the name suggests?

Consider the below relation

R(L,M,N,O,P)
{
    M -> O
    NO -> P
    P -> L
    L -> MN
}

Which of the above functional dependency is bringing in overlapping candidate key in the above relation ?

Lets restrict the discussion to dependencies, I'm not interested in BCNF at the moment

You're right on the money with the possible candidate keys, vikkyhacks. Overlapping candidate keys are composite (consist of more than one attribute) candidate keys with at least one attribute in common. So your overlapping candidate keys are NM and NO (they share N).
Additional explanation of the above, originally left in comments:
All overlapping candidate keys are groups of (e.g. two or more) candidate keys. That means the first criterion is that your relation R must have more than one candidate key (minimal super keys). For any of the candidate keys to be overlapping, each of them (again two or more) must meet a few additional conditions. 1) They both must be composite candidate keys. They must consist of more than one attribute, so a key like A will never be overlapping, but AB might overlap with another key. 2) The composite keys must share an attribute. AB overlaps with AC and BD but not CD or EF.
To summarize: two or more sets of attributes where 1) each set is a candidate key (minimal superkey) for the relation, 2) each set is a a composite key (consists of more than one attribute), and 3) one or more of the attributes of the composite keys overlaps with an attribute of another key in the set. So you can rule out MNOP and NOPL on the basis that they are not minimal superkeys. You can rule out P and L on the basis that they are not composite keys (they consist of one attribute). You're left with two keys, NO and NM, which share the attribute N, so you are finished.
Example
It may also help to have an example you can really wrap your head around. The only time I've ever seen where you will have overlapping candidate keys is when you have 1) two attributes that functionally determine one another (e.g. a one-to-one relationship between A and B where A has one B and B has one A) and 2) these attributes are part of composite candidate keys.
For example, in some system, a Customer has one CreditCard, and a CreditCard belongs to one Customer. In the Rentals table, you uniquely identify a Rental by the EquipmentId, Date and CustomerId. For convenience, you've stored CreditCard on this table as well.
This means that the following FDs hold:
{CustomerId, EquipmentId, Date} -> {CreditCard}
{CustomerId} -> {CreditCard}

But since the association is one-to-one, the following FDs also hold:
{CreditCard} -> {CustomerId}
{CreditCard, EquipmentId, Date} -> {CustomerId}

Since CustomerId and CreditCard can be used interchangeably to uniquely identify your customer.
In the scenario above, you have overlapping candidate keys:
{CreditCard, EquipmentId, Date}
{CustomerId, EquipmentId, Date}

They are overlapping because they are composite keys (they consist of more than one attribute) and because at least one of their attributes is shared (in this case, they share both EquipmentId and Date.
You said you don't care about BCNF at the moment, but to fully bring this home, the scenario above is the reason why you'll occasionally see a table that is in 3NF but not BCNF. The table above is in 3NF, but not BCNF.
3NF allows FDs where 1) the FD is trivial 2) the left side of the FD is a candidate key or 3) the right side of the FD is a key attribute (an attribute used to make any key). Since CreditCard and CustomerId are both key attributes, all FDs either meet 2 or 3.
BCNF is very similar, but it only allows conditions 1 and 2 allowed by 3NF. Since the 3rd condition is not allowed by BCNF, and both CID -> CC and CC -> CID use condition 3, this table is not BCNF, but it is 3NF.
For practical purposes, the case is fairly rare and this information is pedantic. The giveaway that your table has a problem will be the fact that CreditCard/CustomerId pairs are repeated across your table. You also may recognize that the table would not even be in 2NF without this rare condition where the right side of an FD may be a key attribute because CreditCard is a partial dependency on the primary key (it depends on CustomerId but not EquipmentId or Date.


Connect to Oracle using SQLDeveloper using OS Authentication on a remote machine I reach my database server from my MacBook via SSH. You can login into this database as by using OS authentication.

MacBook:~ mangeles$ ssh oracle@10.10.1.64
oracle@10.10.1.64's password:
Last login: Thu May 21 11:45:14 2015 from 172.31.12.51
[oracle@bd ~]$ . oraenv
ORACLE_SID = [MF] ? PROD
[oracle@bd ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 21 11:46:17 2015

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

I'm able to connect to the database using SQLDeveloper but I have to provide a username and a password. I can connect to all the databases as a SYS (through SSH) but I don't have the SYS password of all the databases and changing the password is not an option.

I want to know how I can use the OS Authentication in my SQLDev to login as I do in my terminal, without entering a password.

These are the default values related to this:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string      ops$
remote_os_authent                    boolean     FALSE

Set remote_os_authent to true:

alter system set remote_os_authent=true scope=spfile sid='*';
shutdown immediate
startup

Create a database user with your OS username, prefixed with the above value:

create user ops$mangeles identified externally;
grant create session to ops$mangeles;

This way you can log in without a password because of the remote OS authentication. The problem is, anyone can create an OS user named mangeles on any machine, and they will be able to log in without providing a password.

SYSDBA will not work with this type of authentication, if you try grant sysdba to ops$mangeles, you will get ORA-01997: GRANT failed: user 'OPS$MANGELES' is identified externally.


UDF in MS SQL Server returning a query starting with "with" cause This is a perfectly valid SQL Query and works nicely (for e.g.) in Microsoft SQL Server 2014:
with t as (select 'a' as attr) select * from t

it returns a, surely one can simplify it and drop the with part, but that's not the point, the point is to find out a smallest example that illustrates the need/problem. I can pack part of it as a UDF:
CREATE PROCEDURE x(@__employee_id uniqueidentifier)
AS
RETURN (select 'a' as attr);

How can I pack a query that has with block? My aim is to achieve something like:
CREATE PROCEDURE x (@__employee_id uniqueidentifier)
AS
RETURN (
    with t as (select 'a' as attr) select * from t
);

however sql server does not like it, firing an error:
Msg 156, Level 15, State 1, Procedure x, Line 4 [Batch Start Line 0]
Incorrect syntax near the keyword 'with'.

you are mixing procedure and function

for UDF, your syntax should be
CREATE or alter function x (@__employee_id uniqueidentifier)
returns table
AS
RETURN (
with t as (select 'a' as attr)
select * from t
);


MySQL Loop Through Each Row I have two tables, division & template, in a MySQL database.

division
    - DivisionID
    - DivisionName

template
    - TemplateID
    - TemplateName

Here is an example of my current data set.

division
    - 1, Marketing
    - 2, Commerce
    - 3, Infrastructure

template
    - 1, Awareness
    - 2, AAR Summary
    - 3, Release Acceptance

I'm looking for a result set of:

Marketing Awareness
Marketing AAR Summary
Marketing Release Acceptance
Commerce Awareness
Commerce AAR Summary
Commerce Release Acceptance
Infrastructure Awareness
Infrastructure AAR Summary
Infrastructure Release Acceptance

Everything else I've seen that is similar to this is using a PHP or other script to accomplish. How would I achieve something like this using pure MySQL?

For different columns

select DivisionName, TemplateName from division join tmplate;

If you want each row as a single column string

select concat(DivisionName,' ', TemplateName) from division join tmplate;


phpmyadmin/ mysql how to copy a table with all its constraints I tried to copy a table which is connected to another table through a foreign key using

$query = "CREATE TABLE table1 AS SELECT * FROM table 2";

but when I run this query it copies every thing but the constraints are not copied.

Is there any way I can run a script through phpmyadmin or write a function that preserve the constraints and the references ?

Within phpMyAdmin, if you go to the table you wish to copy and click the "Operations" tab, there should be a section "Copy table to (database.table)"

Here you will be able to copy the table with the same constraints as the table you are trying to copy.


Syncing Uncontained Objects with AlwaysOn Availability Groups In setting up our HA environment (AlwaysOn AG), I came across the add-in ssms_2012_ag_addin_setup.exe that I got from SQLSkills. http://www.sqlskills.com/blogs/jonathan/synchronize-availability-group-logins-and-jobs/

This is very helpful in tackling the challenge of syncing the uncontained objects from the primary to secondary replicas. This add-in does not include the sync of operators, alerts or credentials. It also does not account for the need to allow any SQL job to execute on failover ONLY. I can add the following to the first job-step to accomplish this.

DECLARE @ServerName NVARCHAR(256) = @@SERVERNAME
 DECLARE @RoleDesc NVARCHAR(60)

SELECT @RoleDesc = a.role_desc
 FROM sys.dm_hadr_availability_replica_states AS a
 JOIN sys.availability_replicas AS b
 ON b.replica_id = a.replica_id
 WHERE b.replica_server_name = @ServerName

IF @RoleDesc = ‘PRIMARY’

BEGIN

— Job Step Logic

END

ELSE
 EXEC msdb..sp_stop_job –Job Name

Do you have any better solutions to automating these needs when using AwaysOn Availability Groups as a HA solution.

Native functionality, no.

AlwaysOn Availability Groups have the containment of database.  Server objects (Agent jobs, logins, alerts, etc.) just simply don't play a HADR role with availability groups.

Whenever you run into the need to ensure that server-level objects exist on all replicas, or that they only execute on [particular_replica] then you will have to have this custom logic.


SQL server memory pressure First observed few months ago at end-user's laptop (rather slow i3 CPU, 4GB RAM, SSD) with SQL 2014 Express. The problem persists with SQL 2019, even with clean Windows 10 1903 installation.
The database has 250MB so pretty small even for Express Edition. Application mostly works smooth, but there are time to time very looong pauses, while simple query which is usually finished within 2 secs timeouts after 5 mins! It's client machine, there are no other queries nor databases.
Recently the same problem start occurring on my dev machine too. I can confirm the memory pressure in Windows event log:
A significant part of sql server process memory has been paged out. This may result in a performance degradation.
Duration: 7204 seconds. Working set (KB): 203376, committed (KB): 577992, memory utilization: 35%%.

Based on this MSDN article about paging I should turn on Locked pages. There is a caveat that the hungry SQL Server can eat all free memory, so user can't work at all. Are my worries relevant?
What struggles me most is that it start happening quite recently (approx. 2 months ago) while there is no significant change at my workload as well as 16GB RAM. Second, why freeing few hundreds MBs is taking several minutes with NVMe drive (and in my case also quite performant CPU).
Are there some recent changes how Windows 10 / SQL server handles out of memory states?

To me this sounds like a clear case of using Lock Pages in Memory and setting a reasonable max and min server memory.


Find the Foreign Keys Associated with a Given Primary Key I want a way to establish which columns in a given database are joined via PK/FK relationships. I can return the PK/FK information for a given table via

SELECT *
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS cu
WHERE EXISTS (
    SELECT tc.*
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
    WHERE tc.CONSTRAINT_CATALOG = 'MyDatabase'
        AND tc.TABLE_NAME = 'MyTable'
        /*AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'*/
        AND tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME);
GO

but for a PK returned from such a query how do I establish the associated FK (assuming there is one)?

I know you can also get the referenced tables via:

SELECT CONSTRAINT_NAME = name,
       FOREIGN_SCHEMA = OBJECT_SCHEMA_NAME(parent_object_id),
       FOREIGN_TABLE = OBJECT_NAME(parent_object_id),
       REFERENCED_SCHEMA = OBJECT_SCHEMA_NAME(referenced_object_id),
       REFERENCED_TABLE = OBJECT_NAME(referenced_object_id)
FROM sys.foreign_keys
WHERE OBJECT_NAME(referenced_object_id) = 'MyTable';
GO

but I am struggling now to get the explicit column references.

I am creating a script generator for QlikView. To generate the script I need the constraints and the associated links. I need all of the constraint information for any given column (if any).

I want to construct a database class that holds all the information for a given database. This class structure database.table.column.constraints will then be used to get the matches between different columns on PK/FKs.

Clearly some columns will have FKs only and in this case I also want to retrieve the PK information of the corresponding key; some will have only PKs and then I want the reverse. Some of course can have both.

Here's a simple query to match up foreign keys to their referenced tables/columns:

SELECT
    o1.name AS FK_table,
    c1.name AS FK_column,
    fk.name AS FK_name,
    o2.name AS PK_table,
    c2.name AS PK_column,
    pk.name AS PK_name,
    fk.delete_referential_action_desc AS Delete_Action,
    fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
    INNER JOIN sys.foreign_keys fk
        ON o1.object_id = fk.parent_object_id
    INNER JOIN sys.foreign_key_columns fkc
        ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.columns c1
        ON fkc.parent_object_id = c1.object_id
        AND fkc.parent_column_id = c1.column_id
    INNER JOIN sys.columns c2
        ON fkc.referenced_object_id = c2.object_id
        AND fkc.referenced_column_id = c2.column_id
    INNER JOIN sys.objects o2
        ON fk.referenced_object_id = o2.object_id
    INNER JOIN sys.key_constraints pk
        ON fk.referenced_object_id = pk.parent_object_id
        AND fk.key_index_id = pk.unique_index_id
ORDER BY o1.name, o2.name, fkc.constraint_column_id

The output has eight columns: the table and column names for the foreign keys (FK_table, FK_column), the names of the foreign-key constraints (FK_name), the referenced PK or unique index table and column names (PK_table, PK_column), the name of the referenced PK or unique index (PK_name), and the update/delete cascade actions (Delete_Action, Update_Action).

(Edited to add some more output columns.)

EDIT: I'm back 6 years later with an improved version of this. I realized that the original query doesn't really handle multi-column foreign keys well, and I also wanted to be able to quickly identify disabled, untrusted, or unindexed foreign keys. So here's the new version that corrects all of that.

Multi-column keys are shown as comma-separated lists in FK_columns and PK_columns, using the traditional FOR XML/STUFF abuse. The FK_indexes column shows the names of any indexes on the foreign-key table that could potentially be used to satisfy seeks using the foreign-key columns (mainly for optimizing deletes or updates to the primary key table). If it's NULL, then you've got an unindexed foreign key. You can tweak the ORDER BY, or add a WHERE clause (commented out below) if you want to sort by the PK table name, filter for specific PK/FK tables, etc.

SELECT
    fk.is_disabled,
    fk.is_not_trusted,
    OBJECT_SCHEMA_NAME(o1.object_id) AS FK_schema,
    o1.name AS FK_table,
    --Generate list of columns in referring side of foreign key
    STUFF(
        (
            SELECT ', ' + c1.name AS [text()]
            FROM sys.columns c1 INNER
                JOIN sys.foreign_key_columns fkc
                    ON c1.object_id = fkc.parent_object_id
                    AND c1.column_id = fkc.parent_column_id
            WHERE fkc.constraint_object_id = fk.object_id
            FOR XML PATH('')
        ), 1, 2, '') AS FK_columns,
    --Look for any indexes that will fully satisfy the foreign key columns
    STUFF(
        (
            SELECT ', ' + i.name AS [text()]
            FROM sys.indexes i
            WHERE i.object_id = o1.object_id
                AND NOT EXISTS ( --Find foreign key columns that don't match the index key columns
                    SELECT fkc.constraint_column_id, fkc.parent_column_id
                    FROM sys.foreign_key_columns fkc
                    WHERE fkc.constraint_object_id = fk.object_id
                    EXCEPT
                    SELECT ic.key_ordinal, ic.column_id
                    FROM sys.index_columns ic
                    WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
                )
            FOR XML PATH('')
        ), 1, 2, '') AS FK_indexes,
    fk.name AS FK_name,
    OBJECT_SCHEMA_NAME(o2.object_id) AS PK_schema,
    o2.name AS PK_table,
    --Generate list of columns in referenced (i.e. PK) side of foreign key
    STUFF(
        (
            SELECT ', ' + c2.name AS [text()]
            FROM sys.columns c2
                INNER JOIN sys.foreign_key_columns fkc
                    ON c2.object_id = fkc.referenced_object_id
                    AND c2.column_id = fkc.referenced_column_id
            WHERE fkc.constraint_object_id = fk.object_id
            FOR XML PATH('')
        ), 1, 2, '') AS PK_columns,
    pk.name AS PK_name,
    fk.delete_referential_action_desc AS Delete_Action,
    fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
    INNER JOIN sys.foreign_keys fk
        ON o1.object_id = fk.parent_object_id
    INNER JOIN sys.objects o2
        ON fk.referenced_object_id = o2.object_id
    INNER JOIN sys.key_constraints pk
        ON fk.referenced_object_id = pk.parent_object_id
        AND fk.key_index_id = pk.unique_index_id
--WHERE o2.name = 'Company_Address'
ORDER BY o1.name, o2.name


Mongo db and sharding / replica sets concept questions I have few questions about sharding and replica sets:

I am planning to write important logs to a sharded environment. about 100 million rows in a day, If I will connect to a single MONGOS process , wouldn't it be over loaded? can I define more entry points to the db?
Can a config server be in the same servers as the sharded servers?
if you look at this image : http://i.stack.imgur.com/lcp4X.png , in the bottom, 2 shards nodes that is a replica set. How come a shard be a replica set? isn't replica set is a full data node and a shard is a subset of the data? how can they be together?

here is what I don't get ):

"... A replica set is a group consisting of 1 primary and N secondaries. If you are sharding, each group will be a shard. That means that the single primary and all secondaries of the same group will have the same data (replicated)..."

From my understanding:
replica set is one primary that accepts all data.
Its data getting synced to the secondaries.

Sharding is a set of instances that accept subset of the data.

So, if replica set has all the data, and a shard has some data (on each server), how can they live together?

1 - You can have more than 1 mongos instance and connect to whichever you want (the client driver should have that option). Nevertheless, a mongos is just a router, meaning it will only route the requests to the correct shard(s).

2 - Yes, a config server can be in the same machine as a primary/secondary, just don't put config instances together (you are required to have 3 because of redundancy).

3 - A replica set is a group consisting of 1 primary and N secondaries. If you are sharding,  each group will be a shard. That means that the single primary and all secondaries of the same group will have the same data (replicated). Also, having more secondaries won't help you increase the write performance as only the primary is able to do perform inserts/updates/deletes. Considering this, the only way to increase the write performance is by having more shards (horizontal scaling) and, of course, choosing a good sharding key that will balance data across all shards (you don't want all your data to be in 1 shard and all others remain empty). For further info check this official doc explaining the concept of replica set and its members http://docs.mongodb.org/manual/core/replica-set-members/

Also, instances of a replica set shard (the primary and N secondaries) are not required to be in the same machine. You might want to put them in separated machines to increase redundancy and perhaps have a better distribution of the load.


Normalization, field types and relationship The following table is part of my project. I'm really strict about field types and store engines. I've read a lot of questions from this site, but I think there are a lot to do in the first step. Is there anything wrong with below fields and/or their relationship?
Thanks in advance.

http://i.stack.imgur.com/AT1Ie.jpg

Based on the comments to your question, I would strongly recommend using InnoDB since it is ACID complaint and supports your foreign key layout.

When it comes to using InnoDB:

Every working table needs to be InnoDB.
You cannot use InnoDB in its Default setttings. You must tune its disk and memory settings.
Every query doing JOINs cannot have a mixture of InnoDB and MyISAM or every SELECT query deteriorates with full tables locks : https://dba.stackexchange.com/a/9222/877
You must give InnoDB a big enough Buffer Pool so data and index pages are cached : https://dba.stackexchange.com/a/2194/877
You need to have InnoDB tables dwell in their own tablespace files or ibdata1 (default tablespace file) will grow out-of-control : https://dba.stackexchange.com/a/8983/877

As for the log table you have that uses the ARCHIVE storage engine, keep in mind

ARCHIVE storage engine has no indexes
Only INSERTs and SELECTs allowed
All SELECTs are full table scans every time

As for SELECTs, you must tune the SELECTs to take proper advantage of table indexes. Otherwise, add the appropriate indexes to the tables.


Clustered and columnstore indexes I just started a new job where we have many tables from ETL processes. I was told not to put any columnstore or clustered indexes on any tables since the server does not have the resources (CPU). So basically everything is heaps. There are noclustered indexes on many tabels. We get data from many source systems and after data has landed we transform the data and combine it...
I just want to hear if it sounds right in some settings to not use any columnstore or clustered indexes. Does it require more CPU to use clustered indexes when tables used for analytics?

Pretty much every implementation decision is a trade off between competing factors. Building a columnstore index is CPU intensive but afterwards queries touching many rows are fast, and updates are slow. Which is most important for your workload, on average? Is there a time window in which that amount of CPU can be consumed without breaking other parts of the system? Is the additional cost repaid in future benefit? What is the problem to which a columnstore is the solution, and has this been addressed in other aspects of the system?
You mention ETL. Often such tables are only processed as scans where every row is touched by every operation. In such cases indexes will slow processing because they must be written in addition to the table.
Likely there are reasons for the current restrictions. Understand why they were put in place. If those circumstances no longer hold (maybe a server version upgrade or better hardware now) try an experiment in a test environment.


How to fetch Package DDL in Oracle 8i I want to fetch a Package DDL in Oracle 8i.

8i doesn't support DBMS_METADATA.get_ddl.

Can it be done using export?

DBMS_METADATA.GET_DDL is not mandatory to view the source of a package.

You can use DBA_SOURCE/ALL_SOURCE: https://docs.oracle.com/cd/A87860_01/doc/server.817/a76961/ch277.htm#101236

select text from dba_source
where owner = 'OWNER_OF_PACKAGE'
and name = 'NAME_OF_PACKAGE' order by type, line;


how to write pl/sql named programme for this I have a table M_PARAMETER which has a column MMRUN.

When this MMRUN='Y' then the User can not modify any transaction in the app's front end. When MMRUN='N' then the User can do the modification.

emp,dept,salgrade table do not modify users when MMRUN='Y'

You could use a global system context to do this.  See here Oracle context

The user logs on, your stored procedure determines the value of MMRun for that user and the context is set for this session.
Then your triggers or insert/update/delete triggers or procedures refer to the context for permission to edit.

Really though this sounds like something that will require a more fine grained permission structure in the future. I've never seen something simple like can modify/cannot modify stay simple. Usually you are asked to provide variations on this in the future. There are  open source PL/SQL  packages available which can be adapted to do this.


Resolving TNS Permision Denied for XE on Windows 7 I am working with Oracle 11g XE on a Win7 laptop.  I have had this up and running without issue for more nearly two years.  Went about a month without connecting, and the next time I tried to I got “ORA-12546: TNS:permission denied”.

Since I first ran into this about a week ago I have:

Tried connecting with multiple clients (SQLPlus, PLSQL Developer, DB Forge Studio) all of which report the same error.
Tried connecting to other instances with the above clients, all seem to be working fine.
Tried connecting using the full connection descriptor instead of the XE alias.
Completely removed all oracle clients and installations from my laptop, and reinstalled them.  Including cleaning out residual registry entries, as noted here https://superuser.com/questions/256762/how-do-i-completely-remove-oracle-11g.

As far as I can tell, everything seems to have reinstalled correctly in terms of files being where they belong and services running, but I am still getting the same TNS errors.

What should I be looking for at this point?

So I still don't know exactly what caused my issues.  But after spending way to much time trying to research it and tweak things, I got it working again.  The two items that I believe were most critical to the process were:

Updating/syncing my .ora files.  In particular I had some with references to localhost that had to be changed to my full machine name.

Granting my individual user full control on my oracle directory.  I found a reference elsewhere that noted that the permissions inherited from a group were not being honored correctly.


InfoBright LOAD DATA INFILE Wrong data or column definition I'm using BRIGHTHOUSE engine.

I have a column defined as varchar(100). When loading data into the table, if the column data exceeds 100 characters, the engine will throw 'Wrong data or column definition' error, and fail to complete the loading process.

I'm wondering wether I can change the behaviour, simply discard the extra characters, just like what MySQL will do.

I've checked out the reject file feature, but it's not what I want.

The only way I can think of is to truncate the column data when generating the data file. It's workable, but not as easy as in MySQL.

Thanks in advanced.

You can use SET clause of LOAD DATA INFILE to truncate values that are being read from file on the fly

LOAD DATA INFILE '/path/o/your/file.txt'
INTO TABLE table_name
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
(column1, @column2, column3, ...) -- first you read a value into a variable
SET column2 = LEFT(@column2, 100) -- then you assign truncated value to the column


Can an AlwaysOn Availability Group be used to mitigate downtime when there's replication syncing issues on the primary server? Once in a while I'll run into syncing issues with my subscriber server in a transactional replication setup.
Taking a new snapshot of the publisher causes significant downtime on my subscriber server.

If my subscriber server was part of an AlwaysOn AG as the primary, could I failover to the secondary server while the primary is being synced up to a new snapshot on the publisher server, and mitigate the downtime? (Then I can switch back to the primary once it's finished syncing with the publisher server?)

E.g. Server Z is my publisher in the transactional replication, Server A is my subscriber. Then I create an AlwaysOn AG on Server A and a new server called Server B. If Server A falls out of sync with Server Z and I need to take a new snapshot on Server Z to re-sync Server A to it, can I failover to Server B in the meantime?

In general, no, this is not going to work because the databases in the availability groups are read-only on the secondary.  If SQL Server somehow allowed other processes (the replication) to change data on the secondary, the database on the secondary would no longer be in sync with the primary, right?  And the way SQL Server AG synchronization (mirroring) works, it would have to be about completely rewritten to even think of supporting something like this.

However, if the subscriber is read-only, you could use a process similar to the following to accomplish what you want, but I do not recommend doing so as it is getting away from What It Is Designed To Do, and the longer you're in IT, the more you'll know why that is a problem.

Once the situation occurs where you need to resync:

Fail over to Server B
Remove the database(s) from the availability group that are part of the Server Z replication scheme
Bring the databases on Server A out of recovery and let your replication resync from Server Z
Once the data on Server A is in sync with Server Z, fail back to Server A
Drop the databases that are not in the availability group from Server B
Add the databases back into the availability group

Again, this would work only if Server B is used for read-only during this time.  Any changes made to the databases that were removed from the availability group while Server B is primary will be lost.


Using a type in place of a column definition list? Running the below, I get "a column definition list is required for functions returning "record"".

SELECT *
FROM json_to_record('{"a":1,"b":2,"c":3,"d":4}');
ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM json_to_record('{"a":1,"b":2,"c":3,"d":4}');

That's fine. I know what it wants.

SELECT *
FROM json_to_record('{"a":1,"b":2,"c":3,"d":4}')
  AS (a int, b int, c int, d int);

Also, In PostgreSQL, all tables already have a type created by the same name.

CREATE TABLE foo(a,b,c,d)
  AS VALUES
    (1,2,3,4);

That creates an internal type foo linked to the newly created table foo. I can easily create a similar type, bar explicitly though.

CREATE TYPE bar AS (a int, b int, c int, d int);

It would be great to be able to cast the record returned by json_to_record() to bar.

SELECT *
FROM json_to_record('{"a":1,"b":2,"c":3,"d":4}')
  AS foo; -- bar? anything?

Is there anyway to satisfy a column definition list with a type?

Use json_populate_record:

SELECT *
FROM json_populate_record(null::foo, '{"a":1,"b":2,"c":3,"d":4}')

The column matching is done by name, non-existing columns are silently ignored:

create type other_foo as (a int, b int, x int, y int):
SELECT *
FROM json_populate_record(null::other_foo, '{"a":1,"b":2,"c":3,"d":4}');

returns:

a | b
--+--
1 | 2


Very large Primary Key index with few rows I have a table with a GUID as the primary key.

The default is newsequentialid() to keep the GUID's in order.

With only 65,000 rows, the primary key is 1.5 GB! There's a similar table which was 2GB with only 3,000 rows.

I've truncated the second table in a test environment and it has reduced the table size and DB size by that amount.

How do I troubleshoot this? What causes the indexes to be so large with so little data?

It's a SQL Server 2016 standard database on a Windows 2012 R2 server.

The data is from running the script below.

;with SpaceInfo(ObjectId, IndexId, TableName, IndexName
    ,Rows, TotalSpaceMB, UsedSpaceMB)
as
(
    select
        t.object_id as [ObjectId]
        ,i.index_id as [IndexId]
        ,s.name + '.' + t.Name as [TableName]
        ,i.name as [Index Name]
        ,sum(p.[Rows]) as [Rows]
        ,sum(au.total_pages) * 8 / 1024 as [Total Space MB]
        ,sum(au.used_pages) * 8 / 1024 as [Used Space MB]
    from
        sys.tables t with (nolock) join
            sys.schemas s with (nolock) on
                s.schema_id = t.schema_id
            join sys.indexes i with (nolock) on
                t.object_id = i.object_id
            join sys.partitions p with (nolock) on
                i.object_id = p.object_id and
                i.index_id = p.index_id
            cross apply
            (
                select
                    sum(a.total_pages) as total_pages
                    ,sum(a.used_pages) as used_pages
                from sys.allocation_units a with (nolock)
                where p.partition_id = a.container_id
            ) au
    where
        i.object_id > 255
    group by
        t.object_id, i.index_id, s.name, t.name, i.name
)
select
    ObjectId, IndexId, TableName, IndexName
    ,Rows, TotalSpaceMB, UsedSpaceMB
    ,TotalSpaceMB - UsedSpaceMB as [ReservedSpaceMB]
from
    SpaceInfo
order by
    TotalSpaceMB desc
option (recompile)

A clustered index in MSSQL causes sequential data rows to be stored together in the data file, including when data in a row is updated.  You don't have to have one, but, unless you make it different, a primary key will be created with a clustered index.  As we've already said, really you're changing your entire table into the clustered index.  Basically a tree whose leaves are the data rows.  You can also have a clustered index which isn't the primary key.

A nonclustered index is a separate copy of specified table column data, organised in a different key order.  It includes the clustered index key, if there is a clustered index, to link back to the table row.  This makes it economical to use a small size clustered index key.  GUID isn't ideal, but there's worse.  Data can be fetched from a nonclustered index without touching the stored table at all.  Of course, when table data is updated, the server has the job of automatically updating the data in a nonclustered index as well.


View Existing Table-valued Parameters I have seen many tutorials on how to create a table-valued parameter - but how do you view a table-valued parameter after it has been created?

I would prefer a way of using the GUI in SSMS but T-SQL would suffice as well.

Please use this code to create a table value Parameter.

--create a new database
USE [master]
GO
CREATE DATABASE DemoTVP
GO

--Change context to the new databse
USE [DemoTVP]
GO

--creating table-Valued Parameters
CREATE TYPE LocationTableType AS TABLE
( LocationName VARCHAR(50)
, CostRate INT );
GO

Then you see the above created table value parameter in SSMS.

Using TSQL you can see the same.

USE [DemoTVP]
GO
SELECT * FROM [sys].[objects]
WHERE [type] ='TT'
ORDER BY create_date DESC


Can I retrieve all database objects owned by a particular user? We have a user who is leaving and I need to know every database object that he owns. Is there a query that will provide this information?

This should get you what you're looking for:

;with objects_cte as
(
    select
        o.name,
        o.type_desc,
        case
            when o.principal_id is null then s.principal_id
            else o.principal_id
        end as principal_id
    from sys.objects o
    inner join sys.schemas s
    on o.schema_id = s.schema_id
    where o.is_ms_shipped = 0
    and o.type in ('U', 'FN', 'FS', 'FT', 'IF', 'P', 'PC', 'TA', 'TF', 'TR', 'V')
)
select
    cte.name,
    cte.type_desc,
    dp.name
from objects_cte cte
inner join sys.database_principals dp
on cte.principal_id = dp.principal_id
where dp.name = 'YourUser';

This will get objects that are owned by your particular user (substitute 'YourUser' of course).  The types of objects this query pulls are:

FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
P = SQL Stored Procedure
PC = Assembly (CLR) stored-procedure
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function
TR = SQL DML trigger
U = Table (user-defined)
V = View


Better to create a table for each user account, or one huge table for all user data in MYSQL I'm creating a web directory that will allow individual users to signup for an account and store essentially text documents in a mysql database entry.

Initially there will likely be only a few hundred users, but we hope to have 10,000 to 100,000 at some point.  And each user would be able to upload 100-200 "documents".

Would it be more efficient to create one massive table, indexed by a user number?  Which could theoretically grow to be 20,000,000 entries.  Or to go ahead and create a table for each user with their individual documents?

I'm assuming it's not healthy to have thousands of tables in a database, but I really can't find any concrete data on this.

MySQL can easily cope with 20M rows if it's properly indexed. We have tables with more than a billion rows in it.

Having one table is cleaner. Don't need to do magic in application based the user(name). Easier to make any statistics on the documents table too.

I would definitely go with the one big table approach. If you are concerned about the table (physical) size you should consider partitioning the documents table. http://dev.mysql.com/doc/refman/5.5/en/partitioning-types.html


Join a table with itself I have a big table (which i created from 3 small tables) which looks like this

Acquisition | device | user_id | user_name
------------|--------|---------|----------
null        |   1    |    1    |   John
3           |   2    |    1    |   John
4           |   4    |    3    |   Mary
null        |   5    |    4    |   Snow

I want to know wich user have not made any  acquisition yet, for this example (4, Snow) would be the answer, as John has made the Acquisition 3.

I made this query

select user_id, user_name
from adquisicion_dispositivo
where acquisition is null and
user_id not in
(select user_id from adquisicion_dispositivo where
acquisition is not null);

But it takes too long (half hour and going ~1 million rows).
This table does not have any id's.

this is the schema of the table

  Columna      |          Tipo          | Modificadores
-------------------+------------------------+---------------
id_adquisicion    | integer                |
id_articulo       | integer                |
id_dispositivo    | integer                |
fecha_adquisicion | date                   |
id_usuario        | integer                |
version_android   | character varying(100) |
nombre_usuario    | character varying(100) |
fecha_nacimiento  | date                   |
sexo              | character varying(20)  |

On large tables you may be able to speed it up.

SELECT user_id, user_name
FROM adquisicion_dispositivo AS a1
WHERE acquisition IS NULL
AND NOT EXISTS (
  SELECT 1
  FROM adquisicion_dispositivo AS a2
  WHERE acquisition IS NOT NULL
  AND a1.user_id = a2.user_id
);

You need to show your table with \d adquisicion_dispositivo. You may need an index, @joanolo's suggestion is good switch the order though I would switch the order,

CREATE INDEX ON adquisicion_dispositivo (user_id, acquisition);


Limit connection rights on SQL Server I have an app to deploy in production that uses 'honor system' security. That is, all users connect to the DB using a SQL user/passwd credential and the app manages permissions itself. The latter part doesn't bother me as much as the fact that the connection object contains embedded credentials and can be copied around freely. I'm try to find some way to limit connections to a more limited set of clients. I can create firewall rules to limit by IP, of course. Is there any way to 'prequalify' SQL logins either by Machine account or domain membership?

As Thomas mentioned that it can be done using LOGON Trigger. Below is the script that will help you out

/*

http://www.sqlservercentral.com/scripts/Security/69558/
Credit: Gregory A. Ferdinandsen
--greg@ferdinandsen.com
--Revision 1.0, 8 Feb 10
--Requires SQL 2005 SP2 or higher
*/
if not exists (select 1 from master..sysdatabases where name = 'SQL_Audit')
 begin
 create database SQL_Audit
  end
USE [SQL_Audit]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[BlackList](
[SRV_Rule] [int] IDENTITY(1,1) NOT NULL,
[HostName] [varchar](64) NULL,
[IP_Address] [varchar](15) NULL,
[LoginName] [varchar](128) NULL,
[AppName] [varchar](256) NULL,
[RestrictionEnabled] [bit] NULL,
[Description] [varchar](2048) NULL,
CONSTRAINT [PK_BlackList] PRIMARY KEY CLUSTERED
(
[SRV_Rule] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[BlackList] ADD CONSTRAINT [DF_BlackList_RestrictionEnabled] DEFAULT ((0)) FOR [RestrictionEnabled]
GO

---------------------------------------------------------------------
---------------------------------------------------------------------
USE [SQL_Audit]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Violations](
[ViolationNum] [int] IDENTITY(1,1) NOT NULL,
[PostDate] [datetime] NOT NULL,
[LoginName] [varchar](128) NULL,
[IPAddress] [varchar](15) NULL,
[HostName] [nvarchar](64) NULL,
[ServerName] [varchar](96) NULL,
[AppName] [nvarchar](256) NULL,
[ViolationType] [varchar](512) NULL,
CONSTRAINT [PK_Violations] PRIMARY KEY CLUSTERED
(
[ViolationNum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Violations] ADD CONSTRAINT [DF_Violations_PostDate] DEFAULT (getdate()) FOR [PostDate]
GO
---------------------------------------------------------------------
---------------------------------------------------------------------
--(c) Gregory A. Ferdinandsen
--greg@ferdinandsen.com
--Revision 1.0, 8 Feb 10
--Requires SQL 2005 SP2 or higher

--
--Change with <<Execute as 'Domain\SQL'>> for a valid service account that has sa rights
--
--Information on Logon Triggers: http://msdn.microsoft.com/en-us/library/bb326598.aspx
--
USE Master
go

CREATE Trigger [trg_LoginBlackList]
 on all Server

 as
begin

 declare @data XML
declare @User as varchar(128)
declare @HostName as varchar(64)
declare @IPAddress as varchar(15)
declare @AppName as nvarchar(256)
declare @SPID as int
declare @SrvName as nvarchar(96)
declare @PostTime as datetime
declare @LogMsg as varchar(1024)

set @data = EVENTDATA()
set @User = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)')
set @IPAddress = @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(15)')
set @SPID = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int')
set @SrvName = @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(96)')
set @PostTime = @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
set @HostName = Cast(Host_Name() as nvarchar(64))
set @AppName = Cast(App_Name() as nvarchar(256))

--Check to see if the blacklist table exists, if the table does not exist, exit the Trigger, as otherwise all user would be locked out.

if Not Exists (select * from SQL_Audit.INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'BlackList')
begin
return;
end

--#1
--If a user connects from a given work station and with a given UserName, they will be dissconected
--This user need to be set up in SQL_Audit..Blacklist with a user name and a host name, no IP Address is necesary
--This is the prefered method of blacklisting, as DHCP could reak havoc on any IP restrictions
If(Exists(Select * from SQL_Audit.dbo.BlackList where LoginName = @User and HostName = @HostName and RestrictionEnabled = 1))
begin
--Any data modifications made up to the point of ROLLBACK TRANSACTION are rolled back
--The current trigger continues to execute any remaining statements that appear after the ROLLBACK statement.
--If any of these statements modify data, the modifications are not rolled back.
--http://technet.microsoft.com/en-us/library/bb153915.aspx
rollback

insert into SQL_Audit..Violations
(PostDate, LoginName, IPAddress, HostName, ServerName, AppName, ViolationType)
values (@PostTime, @User, @IPAddress, @HostName, @SrvName, @AppName, 'LoginName, HostName')

--Exit trigger without evaluating any further conditions
return;
end

--#2
--If a user connects from a given IP Address and with a given UserName, they will be dissconected
--This user need to be set up in SQL_Audit..Blacklist with a user name and a IP Address, no HostName is necesary
If(Exists(Select * from SQL_Audit.dbo.BlackList where LoginName = @User and IP_Address = @IPAddress and RestrictionEnabled = 1))
begin
--Any data modifications made up to the point of ROLLBACK TRANSACTION are rolled back
--The current trigger continues to execute any remaining statements that appear after the ROLLBACK statement.
--If any of these statements modify data, the modifications are not rolled back.
--http://technet.microsoft.com/en-us/library/bb153915.aspx
rollback

insert into SQL_Audit..Violations
(PostDate, LoginName, IPAddress, HostName, ServerName, AppName, ViolationType)
values (@PostTime, @User, @IPAddress, @HostName, @SrvName, @AppName, 'LoginName, IP Address')

--Exit trigger without evaluating any further conditions
return;
end

--#3
--If a user connects from a given Blacklisted IP Address, regardless of the host name or SQL Server User
--This IPAddress need to be set up in SQL_Audit..Blacklist with only an IP Address, no other information is needed
--This will block all connections from the designated IP Address
If(Exists(Select * from SQL_Audit.dbo.BlackList where IP_Address = @IPAddress and LoginName is NULL and HostName is NULL and RestrictionEnabled = 1))
begin
--Any data modifications made up to the point of ROLLBACK TRANSACTION are rolled back
--The current trigger continues to execute any remaining statements that appear after the ROLLBACK statement.
--If any of these statements modify data, the modifications are not rolled back.
--http://technet.microsoft.com/en-us/library/bb153915.aspx
rollback

insert into SQL_Audit..Violations
(PostDate, LoginName, IPAddress, HostName, ServerName, AppName, ViolationType)
values (@PostTime, @User, @IPAddress, @HostName, @SrvName, @AppName, 'IP Address')

--Exit trigger without evaluating any further conditions
return;
end

--#4
--If a user connects from a given Blacklisted Workstation, regardless of the IP Address or SQL Server User
--This Client need to be set up in SQL_Audit..Blacklist with only a value for HostName, no other information is needed
--This will block all connections from the designated Host
If(Exists(Select * from SQL_Audit.dbo.BlackList where HostName = @HostName and LoginName is NULL and IP_Address is NULL and RestrictionEnabled = 1))
begin
--Any data modifications made up to the point of ROLLBACK TRANSACTION are rolled back
--The current trigger continues to execute any remaining statements that appear after the ROLLBACK statement.
--If any of these statements modify data, the modifications are not rolled back.
--http://technet.microsoft.com/en-us/library/bb153915.aspx
rollback

insert into SQL_Audit..Violations
(PostDate, LoginName, IPAddress, HostName, ServerName, AppName, ViolationType)
values (@PostTime, @User, @IPAddress, @HostName, @SrvName, @AppName, 'HostName')

--Exit trigger without evaluating any further conditions
return;
end

--#5
--If a particular application connects to SQL Server, regardless of IP Address, UserName, or HostName, the session is terminated
If(Exists(Select * from SQL_Audit.dbo.BlackList where AppName = @AppName and HostName is NULL and LoginName is NULL and IP_Address is NULL and RestrictionEnabled = 1))
begin
--Any data modifications made up to the point of ROLLBACK TRANSACTION are rolled back
--The current trigger continues to execute any remaining statements that appear after the ROLLBACK statement.
--If any of these statements modify data, the modifications are not rolled back.
--http://technet.microsoft.com/en-us/library/bb153915.aspx
rollback

insert into SQL_Audit..Violations
(PostDate, LoginName, IPAddress, HostName, ServerName, AppName, ViolationType)
values (@PostTime, @User, @IPAddress, @HostName, @SrvName, @AppName, 'ApplicationName')

--Exit trigger without evaluating any further conditions
return;
end

--#6
--If a particular application connects to SQL Server, with a given UserName (i.e. service account cannot connect with SSMS)
If(Exists(Select * from SQL_Audit.dbo.BlackList where AppName = @AppName and LoginName = @User and RestrictionEnabled = 1))
begin
--Any data modifications made up to the point of ROLLBACK TRANSACTION are rolled back
--The current trigger continues to execute any remaining statements that appear after the ROLLBACK statement.
--If any of these statements modify data, the modifications are not rolled back.
--http://technet.microsoft.com/en-us/library/bb153915.aspx
rollback

insert into SQL_Audit..Violations
(PostDate, LoginName, IPAddress, HostName, ServerName, AppName, ViolationType)
values (@PostTime, @User, @IPAddress, @HostName, @SrvName, @AppName, 'ApplicationName, UserName')

--Exit trigger without evaluating any further conditions
return;
end
end;

GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [trg_LoginBlackList] ON ALL SERVER
GO


How to Disable and Drop all Temporal Tables from a database I'm working on a task where I need to backup and restore a database in another instance.

But, when I restore that database, I need to Drop all the History Tables associated with Temporal Tables.

Is there a quick way to do this? Any help is appreciated.

I believe that's what you need.

SET NOCOUNT ON ;
DECLARE @cmd NVARCHAR (MAX);

DECLARE
    @SchemaName VARCHAR(100),
    @TableName VARCHAR(100),
    @HistorySchemaName VARCHAR(100),
    @HistoryTableName VARCHAR(100);

SET @cmd = '' ;

SELECT TOP ( 1 )
       @SchemaName = SCHEMA_NAME(T1.schema_id ),
       @TableName = T1 .name,
       @HistorySchemaName = SCHEMA_NAME(T2.schema_id ),
       @HistoryTableName = T2 .name
FROM sys .tables T1
LEFT JOIN sys .tables T2
    ON T1.history_table_id = T2 .object_id
WHERE T1. temporal_type = 2
ORDER BY T1. name;

WHILE @@ROWCOUNT = 1
    BEGIN
        SET @cmd = 'ALTER TABLE ' + QUOTENAME (@SchemaName ) + '. ' + QUOTENAME(@TableName) + ' SET ( SYSTEM_VERSIONING = OFF );
DROP TABLE '       + QUOTENAME (@HistorySchemaName ) + '.' + QUOTENAME(@HistoryTableName );
        EXEC sp_executesql @cmd

        SELECT TOP ( 1 )
               @SchemaName = SCHEMA_NAME(T1.schema_id ),
               @TableName = T1 .name,
               @HistorySchemaName = SCHEMA_NAME(T2.schema_id ),
               @HistoryTableName = T2 .name
        FROM sys.tables T1
        LEFT JOIN sys.tables T2
            ON T1 .history_table_id = T2 .object_id
        WHERE
            T1 .temporal_type = 2
            AND T1 .name > @TableName
        ORDER BY T1 .name;
    END;


Restore Database using GUI - Wrong file to restore I'm just messing around with the SSMS graphic interface and studying the options of the "restore" task.

One thing that I noticed is when I click on "generate script", the first line of the query is:

 RESTORE DATABASE [MyDatabase] FROM  DISK = N'Server_Patch\Database_name_LOGSHIPPING.BKP' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5 ( and a lot of log backups for point in time )

Ok, no problem, but, i'm doing daily backups of this database. this Database_name_LOGSHIPPING.BKP was the name of the file that I made for log shipping a month ago.

Why when I try to use the SSMS graphic interface to restore a backup, it points to this backup file? I don't even have this file anymore.

With this query from MSSQLTIPS I can see all backups from this database:

SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)   and  msdb..backupset.type ='D'
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date

What can be wrong here? I'm not using COPY ONLY.

EDIT2:

i'M making daily manual backups to test, and even this way, SQL Server selects that old backup that doesn't exists anymore. When running RESTORE HEADERONLY... it says ( obviously ) the file does not exists.

EDIT 3:

This the GUI print:

Wow Wow Wow WAIT A MINUTE !

This database was a restore from another server ( same server, different instances ). Huuum... I think the problem is here.

Can you guys see on "server" in the second picture? it has 2 servers. I'm using the one with the names instance GDLIC2014.

the Script:

The backup script:

DECLARE @Patch varchar(1000)

SELECT @Patch = (SELECT 'PATCH\FULL\DATABASE_ ' + convert(varchar(500),GetDate(),112) + '.bkp')

BACKUP DATABASE DATABASE TO DISK=@Patch with compression

With the same query from MSSQLTIPS , I could find these results, using it with no date range:

The red square is the wrong backup from the older instance, the the Blue square is the last backup taken ( the GUI should be using it )

EDIT 4:

Well, With this query to list Backup History, I see that every log and full are correctly listed:

SELECT TOP 100
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() -- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date
GO

EDIT5:

Is there something to restart the header of the database

( I'm out of ideas )

Chances are something in the backup history tables got out of sync and the UI is going to the last "consistent" full backup.  If you're really interested in why it's doing what it's doing, start a profiler trace limited to your account, walk through the restore steps in the GUI, and review the commands captured in the trace that show what the UI is doing behind the scenes.  This will get you the definitive answer you're looking for.

If you'd rather just move past this, you can clear your backup history via EXEC [msdb].[dbo].[sp_delete_backuphistory] @oldest_date=getdate() (depending on the last time you ran this, you may want to clear it out a month at a time) and then take a new full, etc.  I would suspect this will reset the GUI to use the proper backups going forward.

Finally, another option is to run this script authored by Wayne Sheffield.  It may provide some more information regarding any issues with the backup chain.  I didn't come across this until after I posted this answer originally, but hopefully it helps someone else out in the future.


Order of DROP_EXISTING and ONLINE for CREATE INDEX If I call a CREATE NONCLUSTERED INDEX using DROP_EXISTING = ON and ONLINE = ON what order are things done it?

What I want is:

New index is made in the TempDB
Existing Index is dropped
New index is put in its place.

But I worry that it is like this:

Existing Index is dropped
New index is made in the TempDB
New index is put in its place.

Basically I am wondering if the old index is gone while the new index is being made?  (I want it to be there to limit the time when I am running without any index.)

If it is a long time frame then I will make a new index with a different name then drop the existing one.

The old index stays in place, is maintained and being used by queries. Only at the end of the index build does SQL Server alter the metadata in such a way that the old index disappears and the new one is set in.

There is not a single millisecond without an index. (But there is a short-lived  Sch-M lock twice during the online build.)


Import all database links using dbms_datapump Source DB version:      11.2.0.3
  Destination DB version: 12.1.0.2

I'm attempting to import all database links from one database to another using dbms_datapump.  The code is running on the above destination DB, and pulling the DB Links from the above Source DB via a Database Link.

I've used dbms_datapump.metadata_filter in the past to only import certain objects (like functions or procedures).  However, the following returns ORA-39001: invalid argument value:

dbms_datapump.metadata_filter(h1,'INCLUDE_PATH_EXPR','IN (''DATABASE LINK'')');

Therefore, I tried the plural version which also returned ORA-39001:

dbms_datapump.metadata_filter(h1,'INCLUDE_PATH_EXPR','IN (''DATABASE LINKS'')');

I know I must be missing something basic here, but searching on this issue has been annoying since search results with "dbms_datapump" and "database link" in the same search has only returned results with how to import over a Database link, and not how to import the Database Links directly.

I used to be able to import the Database Links directly using:

select DBMS_LOB.SUBSTR@dblinkimp(DBMS_METADATA.GET_DDL@dblinkimp
('DB_LINK', '[DBlinkName]', '[SchemaName]')) from dual@dblinkimp;

Then I would run the output from the above statement using execute immediate, but this is no longer a valid option starting in 11.2.0.4 (See Metalink Doc ID 1905221.1).

It is not DATABASE LINK or DATABASE LINKS, it is DB_LINK. You can find the valid values in DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS and TABLE_EXPORT_OBJECTS.


Order specific columns in json_agg I want to sort the rows inside the json_agg function.

I found this:
PostgreSQL : select columns inside json_agg

Which gave me a solution to selecting specific columns for json_agg, but now I want to give the aggregated data a specific order. The "distinct" does work the "order by" does not.

json_agg(DISTINCT (colA, colB))

This does not:

json_agg(DISTINCT (colA, colB) ORDER BY colA ASC, colB ASC)

Is there a way to sort the aggregated data?

Without knowing exactly what you meant by 'The "distinct" does work the "order by" does not.', I guess you get an error:

  ERROR:  in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list

It is more or less self-explanatory.  Try

json_agg(DISTINCT (colA, colB) ORDER BY (colA, colB))

instead - you don't aggregate individual columns in json_agg() but a record, and should order by that one.


Use PostgreSQL builtin operator <@ after including extension intarray I added the intarray extension to my PostgreSQL database in order to make use of the - operator, but I still want to use the builtin <@ operator, not intarray's <@ operator. Is there a way to do this?

The reason to prefer the builtin operator is that the builtin operator allows NULL values in the array in question, whereas the intarray operator raises an error in the event of a NULL value.

If you installed the intarray extension to the public schema (or some schema other than pg_catalog), you can schema-qualify the operator to choose the built-in one.

Instead of

a <@ b

you can write

a OPERATOR(pg_catalog.<@) b

to schema-qualify it.

e.g.

test=# SELECT 1 OPERATOR(pg_catalog.+) 2;
 ?column?
----------
        3
(1 row)


cross database way to force join strategies [how to enforce the query to use certain join algorithm]

Knowing that there are several joining algorithms [Nested Loop; Sort Merge; Hash Join], when implementing a query , is there any technique to enforce the database to adapt certain Join algorithm ? Perhaps using some hints ? creating some indexes ?

Related Topic: Enforce Hash Join -- not that useful for me.

Adding more details:
  I am talking about DB2 V9.7, but I originally thought this would apply for other DB as well as they should share some common characteristic

First of all, note that not all databases support all three (Loop, Hash, Merge) types of join. For example, you cannot hash join in all version MySQL (thanks for the comment  pointing out that this now works in MariaDb).

Second, databases will often have vendor specific syntax to force certain join strategies and orders. For example, SQL Server uses the keyword LOOP JOIN to force a loop, Oracle uses USE_NL, MySQL uses the syntax STRAIGHT_JOIN to force join order.

All that being said, there are often ways to write a query in such a way that it forces certain behaviours across database platforms. Here are the strategies I have found useful:

Forcing Loop Joins:
Expressing a JOIN as a nested, correlated subquery instead of a regular join will typically force the loop strategy.

For example, instead of:

SELECT ...
FROM t1 JOIN t2 ON t1.key = t2.key

Write:

SELECT ... (SELECT ... FROM t2 WHERE t2.key = t1.key)
FROM t1

Forcing Hash Join:
Either remove the indexes on the small table or force a table scan of the table. As a generic method for disabling indexes, you can purposefully confuse the optimiser. For example, instead of:

SELECT ...
FROM t1
JOIN t2 ON t1.key = t2.key

Write (Assuming key is INT):

SELECT ...
FROM t1
JOIN t2 ON t1.key / 1 = t2.key / 1

This trick can be expressed more elegantly using functions like COALESCE And NVL (in Oracle)

Forcing Merge: I have not found a generic way to do this. But having the right indexes in place and then forcing the indexes does the trick in the cases I have seen.

Forcing Join Order: Join order is a little easier to force. The trick is to create artificial dependencies between the join conditions. For example, let us say you wanted to force this to be joined in the order t1, t2, t3:

SELECT ...
FROM t1
JOIN t2 ON t2.k = t1.k
JOIN t3 ON t3.k = t2.key

You would write:

SELECT ...
FROM t1
JOIN t2 ON t2.k = t1.k / 1       /* For t2 to be looked up first */
JOIN t3 ON t3.k = t2+0*t1.k     /* Force t1 to be evaluated first */

For a general treatment of query optimisation across database platform, I would recommend picking up a copy of this: http://www.amazon.co.uk/SQL-Tuning-Dan-Tow-ebook/dp/B0026OR32Q/


Log Backup in AlwaysOn In our current process, log backup is done by log shipping backup job.

Is there any need to configure backup job for transaction log in next AlwaysOn environment?

If we must configure backup, we want to set “checkpoint” with log backup job.

If you use Availability Groups you must do log backups. If you use Hallengren's backup solution then you'll need to set it up on both the primary and secondary node(s). Then direct the jobs it creates to point to the same network share.

What will happen next is the script will create one directory for each server to hold system databases and any databases not within an availability group. Then another directory gets created with the availability group name and all the full and log backups go into there.

That's important because your full backups and log backups might be split up between servers (so full backups happen on the primary and log backups happen on the secondary; there are settings so you can tweak this). So you want them all stored together in case you ever need to use them.

AGs have a lot of things you have to know about. Monitoring so you know when they get out of sync, adding job steps for applications to make sure they only run on each server when it's primary (Hallengren's jobs have this built in), and patch order (don't failover with cluster manager ever). Don't put SSISDB into an AG without reading about it first. Don't use it wherever MSDTC is in use.

You'll need to do some solid research but they're not that difficult. You'll learn edge cases as you go along.


GRANT PRIVILEGES TO users stored in Database Users table I have set up a general user account using phpMyAdmin with no privileges. I can now use GRANT to give SELECT privileges:

GRANT SELECT ON DB.Table  TO general@localhost

Is it possible to apply this general user account to users within the table Users and then give them privileges to view only their own data?

For example:

GRANT SELECT ON DB.Booking TO general@localhost IDENTIFIED BY DB.Users.UserId

Database engines do not understand the contents of your tables. You can instruct them to set up a foreign key relationship between the Users table and various other tables in the database, and they can enforce that - but the engine cannot automatically prevent a query from returning data that's tied to a different UserId than the one that's stored in a variable or cookie or whatever, indicating who the person connected to your application is.

That's something that generally must be done specifically by the application. In most cases, I'd expect this to be done by giving the users of the application forms to fill out; from the input on the form, the application would build a query, including whatever business logic applied (like UserId must match the ID value for the user running the query).

If you want to set up every user of your database as an actual database-level login, then some engines (can't recall if MySQL has this capability or not) allow for row-level security. However, this works with users and role set up in the database itself, not user accounts created in and managed by an application.


Red Symbol on Server Roles I was RDP to one of my servers (SQL Server 2012 (SP4) Std Ed) and saw that the Server Roles had a red symbol (not a red x), but when I connect to that server through a VPN using MSSQL that red symbol doesn't show.  I have been through MS knowledgebases and no explanation what so ever. Anybody knows what it means and how to remove this?

Zooming in on the icon it looks like a red push pin.

Searching specifically for red pin and management studio I found this thread which explains

If a principal is disabled, then it has a red arrow pointing down in
the icon ... Here you have a red pin icon and
this means you can't modify it. Since SQL Server 2014 we can create
user defined server role ... which
wasn't possible in SQL Server 2012 and lower version ... user defined
roles don't have a red pin and can be modified, while predefined
server roles do have a pin

One exception is that the permissions of the public role can be altered (but membership can't). For all other pre-defined server roles the reverse is true.
In more recent versions of management studio it appears a lock icon is used for that instead.


How to find object name given in a problematic query I want to know the name of database object which is running the part of my query which is causing problem

From the awr report, I found one query which is making some performance issue, but I am not able to find that query anywhere in the database. Is there any way to find where that is being used in the DB in Oracle 10g? Like in which view or package or procedure.

The AWR report will contain the SQL_ID.  You can query V$SQL with this to locate additional metadata such as the MODULE, ACTION, PARSING_USER_ID.  Also, if the SQL came from a stored procedure, the PROGRAM_ID, PROGAM_LINE# columns point to the (stored procedure) object concerned.

I've got an example of the latter on my blog

https://connor-mcdonald.com/2016/01/20/problematic-sql-plsql-is-your-friend/


Insert with 0 Writes? I've been looking at some SQL Profiler traces recently, and I see something I don't understand.  I'm running simple INSERT commands:

INSERT INTO Foo (ForeignID, FileExtension, MimeType) VALUES (1, 'FOO', 'Application/Foo')

SQL Profiler consistently shows 0 Writes and 2 Reads.  How can there not be any writes on an INSERT?  Or am I misunderstanding what profiler is saying?

For completeness, here's the table definition I'm using:

CREATE TABLE [Foo](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ForeignID] [int] NOT NULL,
[FileExtension] [nvarchar](32) NOT NULL,
[MimeType] [nvarchar](96) NOT NULL,
CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

The Profiler writes column is documented as

  Number of physical disk writes performed by the server on behalf of
  the event.

Before the transaction is committed the log must be physically written to disc up to that point but those writes don't get included in the write stats reported Profiler.

For many inserts no physical writes may occur before the statement completes as the page is modified in the buffer cache and not written out to disc until later (e.g. by the checkpoint or lazy writer process).

For bulk inserts there is an eager writer so the pages may be written out to disc before . See Writing Pages in BOL for more.

However the above doesn't seem relevant as even when no pages are written to disc the physical writes can be reported as non zero.

In the test below Profiler reports 5 writes for the insert but monitoring the file writes with process monitor and sys.dm_io_virtual_file_stats shows that none actually occurred. From which I conclude that the physical writes column actually shows the number of pages made dirty (on the grounds that these will need to be written out to disc later)

  So based on that assumption SQL Profiler shows 0 Writes in your test
  because you are inserting to a page that is already dirty.

TRUNCATE TABLE Foo

CHECKPOINT;

DBCC DROPCLEANBUFFERS;

/*Returns 0 rows*/
SELECT *
FROM   sys.dm_os_buffer_descriptors
WHERE  database_id = DB_ID()
       AND is_modified = 1

SELECT 'Before Insert', num_of_writes, num_of_bytes_written
FROM   sys.dm_io_virtual_file_stats(db_id(), 1)

INSERT INTO Foo
            (ForeignID,
             FileExtension,
             MimeType)
VALUES      ( 1,
              'FOO',
              'Application/Foo')

/*Nothing Written. Figures same as previous query*/
SELECT 'After Insert', num_of_writes, num_of_bytes_written
FROM   sys.dm_io_virtual_file_stats(db_id(), 1)

/*Returns 5 rows*/
SELECT *
FROM   sys.dm_os_buffer_descriptors
WHERE  database_id = DB_ID()
       AND is_modified = 1

CHECKPOINT;

/*Shows physical writes*/
SELECT 'After Checkpoint', num_of_writes, num_of_bytes_written
FROM   sys.dm_io_virtual_file_stats(db_id(), 1)

Profiler Trace

Process Monitor Writes after CHECKPOINT

So the CheckPoint actually physically wrote 8 pages (6 single pages and 1 double page) but shows up in Profiler as being responsible for only 4.

From the offsets in the Process Monitor screenshot it is possible to see exactly what pages were written to

+-----+---------------+-----------+
|  57 | syscolpars    | DATA_PAGE |
|  86 | sysrscols     | DATA_PAGE |
|  93 | sysrowsets    | DATA_PAGE |
| 143 | sysallocunits | DATA_PAGE |
| 168 | Foo           | DATA_PAGE |
| 169 | Foo           | IAM_PAGE  |
|   1 |               | PFS_PAGE  |
|   9 |               | BOOT_PAGE |
+-----+---------------+-----------+

The three additional pages not shown in the output from dm_os_buffer_descriptors are the database boot page and sysrscols, sysrowsets.

The write of the database boot page is correlated with the CHECKPOINT itself as the dbi_checkptLSN field gets updated.

It would be expected that the insert would cause the other two pages to be updated (The rcmodified and rcrows columns respectively) but this does not appear to happen immediately and I see no  LOP_COUNT_DELTA entries in the log file either so I assume this must happen periodically rather than after every modification.

I'm not sure how to account for 9 writes being reported but only 8 occurring. Perhaps one page was double counted.


Need some new solution to shrink my SQL Server database log file I am new bee for SQL Server dba. I need some suggestion to shrink the database log file.

I have tried and found some solution to shrink the file. But i think this is not opted solution for this.

Solution 1:

ALTER DATABASE nologtest2 SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (nologtest2_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE nologtest2 SET RECOVERY FULL;

I am not sure I can do this in production.

Solution 2:

  Backup log nologtest2 to disk='C:\MAS\nologtest32_log.trn'
  DBCC SHRINKFILE (nologtest2_log, 1);

We can took some transaction log backup and then we can shrink the database. But i believe  it will break the lsn chain. Then we may face some issue in logshipping environment.

I need some very opted solution for reduce the database log file issues.

Note:

I am not very familiar with being a SQL Server dba. If i am ask anything wrong or understandingly please correct me.

Why exactly do you need to shrink the log file?  It appears that you are running log shipping, so transaction log backups are being taken and your log file has grown to this size, so the likelihood is that it will just grow to this size again, which will impact performance for users when the file growth is occurring.

Both the options you have specified in your question will break your log shipping configuration, the first one will break your backup chain completely and the second one will break log shipping as you will have transactions in the backup you have just taken that will not be copied across to your secondary.

If you really must shrink the log file, you can simply try running DBCC SHRINKFILE after a log shipping backup has run.  Bear in mind that if there are open transactions in the DB, it either won't shrink by much or at all.  You also won't be able to shrink the file below the initial size specified for the log file either and like I said earlier, if you do shrink it, it will likely grow again which will impact performance.


Querying the most recent records with two fields: datestamp and timestamp I have a table called airquality that has records for air quality measurements taken on different days and times within those days. These date/timestamps are stored in two different columns: validdate and validtime. I can query either one of those fields to return me the maximum value with the following query:

select *
from airquality
where validdate = (select max(validdate) from airquality)

This returns me the correct rows for the most recent day (format: 05/02/20). But how can I modify this query to also check for the latest time in the validtime column (format: 16:00)?

I tried the following:

select *
from airquality
where validdate = (select max(validdate) from airquality)
and validtime = (select max(validtime) from airquality)

But this doesn't work because I can have timestamps for 05/01/2020 that have a timestamp that 05/02/2020 doesn't have yet. (Example: 05/02/20 16:00 vs 05/01/20 23:00)

I am on Postgres 12.2

Any suggestions or ideas?
Thanks in advance.

Create a proper timestamp from the two columns:

select *
from airquality
where validdate + validtime  = (select max(validdate + validtime ) from airquality)

This assumes that validdate is corrected defined with the data type date and validtime is correct defined as a time column.


Advice needed for Querying DateTimeOffset data by end users We are building an Operational Data Store (ODS). The data is coming from our production databases which are located in the United States and Asia. The data in our production databases store date/time values as local time (no UTC or offsets). When we bring the data into our ODS, we're storing all date/time values as DateTimeOffset values.

When our users query the ODS data, we don't want them to have to think about including the appropriate offset value in their queries involving date/time values. We want them to be able to query the data as if it were simple local time values.

We're masking the offset values on date/time values by having users query views. The views return local time values and strip off the offset values. While this works, any indexes on the underlying datetimeoffset columns are not used because the act of converting the datetimeoffset to datetime in the views causes indexes to not be used.

So, I'm looking for advice on how others, who have dealt with this issue, have dealed with it. We need to be able to utilize indexes while, at the same time, not require users to think about time zones and daylight savings time when querying the data.

We're using SQL Server 2012.

We solved this in Oracle using function based indexes. Although, it's not quite as flexible, you can accomplish similar in SQL Server using computed columns.

If all of the information you need is in the table, and you meet all of the ownership requirements (outlined in the second article) then I would recommend making the date/time field a computed column on the table, and then add an index to the computed column.

Computed Columns:

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

Indexing Computed Columns:

http://msdn.microsoft.com/en-us/library/ms189292