Single Database Upgrade (From App side) which is part of Always On We have a task to plan out an Always On database upgrade (Schema upgrade) as per the Application. The connected application will do few schema changes as part of the application upgrade.

Database Size : 450 GB

Always ON Mode : Synchronous (With Automatic Failover)

  Can this be done without removing it from the AG.? (This is a request from App side)
  Will this cause any issues as it in in Synchronous state with the Secondary replica?

We have to give a proper downtime depending on this and do not want to go one way and do it another.

Right now our plan :

Remove the Db from AG.
Do the db upgrade (Schema change).
Add back the
db into AG with fresh backup.

Not sure I’m following why you need downtime at all. Aside from isolation and schema locks, changing a table is no different from changing data - it gets logged, sent to secondaries, etc. This is never instantaneous, but it can often be so close that there’s no way your app would notice.

  Unless both the replicas are in sync we are not allowed to keep the app running. Also we are a bit worried on the database size. If it was for a small database we would have gone straight as you mentioned. So, it will be fine to go ahead without breaking the AG?

It is 100% impossible to update both the primary and the secondary at the exact same moment in time, so let's throw that requirement out the window straight away. The app may require downtime in that case, which you can accomplish in other ways, but I meant database downtime. I don't think a schema change warrants pulling a database out of an AG. Synchronous should be very fast unless you have other configuration / latency issues already, and can you explain exactly why the rule about the replicas in sync and the app running? Surely your schema changes are backward compatible in any case?

  So you mean the Steps I mentioned is fine? Yes I meant the same in terms of downtime. Also This is set for Auto Failover which is why we want to stop the AG before doing any changes.

No, I still don't know why your steps include removing the database from the AG and putting it back in with a fresh backup. Failover seems unrelated, unless your schema changes are potentially so bad that they will cause a failover while you are deploying the changes. Also, once you add the database back into the AG, then the secondary will be out of sync until it catches up again, so this still seems to break your unnecessary rule about keeping the app running.

  Got your Point Aaron. And I totally agree. This is a critical vendor managed app and we are not sure on what kind of schema changes will happen during the upgrade. We are in discussion to finalize a full proof plan to go without any hiccups. In case they want to roll back, the secondary can save us. This is another reason we want to go that way.

Secondary can't help in your current plan, of course. If you pull the database out of the AG, there is no secondary.


SlaveThreadCount at 0 for non-root user, should be 2 In MySQL 5.5 running on Ubuntu 14 in a master-master-slave configuration

From one of the masters, when executing the query below as a user with (ALL PRIVILEGES) on the database but no other privileges elsewhere, this query returns 0

SELECT COUNT(1) SlaveThreadCount
FROM information_schema.processlist
WHERE user='system user'

When executing the same user as root, I.E. (ALL PRIVILEGES) on everything, I get back the real slave count.

Any particular reason I'm getting back 0 and not the real SlaveThreadCount which is 2 in this case?

Is this is privileges issue?

If I run the query without there WHERE in the non-root user, I only see my processes.

SELECT * FROM information_schema.processlist

If I run it in a root user, then I see all processes.

So definitely a permissions issue, so looks like I need PROCESS, just answered my own question.

If you have all privileges on a specific database, you cannot have the PROCESS privilege. Why ? The PROCESS privilege is not a database-level privilege. It's a global privilege. How ?

About 2.5 years ago, I answered How to grant super privilege to the user? Someone wanted the SUPER privilege but only had all privileges on a database. I explained by mentioning how global grants and database-level grants are stored. In short, there is a column called Super_priv in the table mysql.user (where global grants are stored). That column does not exist in mysql.db (where database-level grants are stored).

The same exact thing applies to the PROCESS privilege. There is a column called Process_priv in the table mysql.user. That column does not exist in mysql.db.


SQL Agent Job - Error Creating Step I get this error when trying to create a Job Step:

"Creating an instance of the COM component with CLSID from the IClassFactory failed due to the following error..(Microsoft.SqlServer.ManagedDTS)"

I do not get this error on other machines that have SQL Server.
I checked versions on each machine and all are the same...

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4033.0 (X64)   Jul  9 2014 16:04:25   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.2  (Build 9200: ) (Hypervisor)

I checked this link from Microsoft but does not seem to be a fix for SP2... https://connect.microsoft.com/SQLServer/feedback/details/557402/ssms-can-no-longer-create-or-edit-job-steps

This fixed it...

run this from cmd line/powershell...

regsvr32.exe "c:\program files (x86)\microsoft sql server\100\dts\binn\dts.dll"

https://connect.microsoft.com/SQLServer/feedback/details/569732/creating-an-instance-of-the-com-component-with-clsid?tduid=(b1c7cdcb47d3ef96443cdf19a57073b3)(256380)(2459594)(TnL5HPStwNw-lo_RDTVjauVtPImia9IDiQ)()


Trying to setup Postgresql 9.5 with different data directory Ultimately I want Postgresql 9.5 running in a docker container using a mounted volume for the data so it can be persistent. As part of that I am experimenting with setting up the Postgresql server to use an alternative data directory.

I read that it isn't possible to use a FAT32 or VFAT directory, so I have, for the time being, abandoned using the Samba mount from our backed up NAS and am trying something simpler.

I am working on Ubuntu 16.04.3 LTS. The commands I ran (as root (sudo su -)) were:

service postgresql stop
mkdir -p /blah/data
chown postgres /blah/data
sudo -u postgres /usr/lib/postgresql/9.5/bin/initdb -D /blah/data
sed -i 's#.*data_directory = .*#data_directory = "/blah/data"#' /etc/postgresql/9.5/main/postgresql.conf
service postgresql start

The initdb command completed perfectly, unlike when I ran it on my samba mount where I got fsync errors.

If I now type sudo -u postgres psql I get told:

Error: Invalid line 41 in /etc/postgresql/9.5/main/postgresql.conf: »data_directory = "/blah/data"«

I have followed everything I can find on the internet about this, especially the official documentation. It should just work. It doesn't. Apparently the documentation needs some improvement.

Can anyone help me get this to work, please?

If anyone can help me to then extend it to work in a docker container using a volume mounted from a directory on the host which is in turn a samba mount, I would be very grateful.

The command I typed:

sed -i 's#.*data_directory = .*#data_directory = "/blah/data"#' /etc/postgresql/9.5/main/postgresql.conf

should be:

sed -i "s#.*data_directory = .*#data_directory = '/blah/data'#" /etc/postgresql/9.5/main/postgresql.conf

The quotes around the directory path were the wrong sort. Can you believe it!


Binding errors with dependent stored procedures We are getting strange errors on one of our stored procedures. This stored procedure calls other stored procedures & we are getting errors relating to columns that cannot be found in the sub procedures.  For example:

Exec StoredProc1, this stored proc calls StoredProc2, StoredProc3 & StoredProc4

When I execute StoredProc1 the first time I get errors relating to columns on StoredProc2.
I then execute StoredProc2 directly and it works, then I call StoredProc1 again and it complains about column Errors on StoredProc3, and so on.

I am guessing this has to do with how SQL Server has cached its execution plans for StoredProc1, & executing each stored proc in turn forces the Stored Plans / Dependencies to be updated.

How can I correct this across the board?

If I execute each dependent procedure one at a time then execute the parent it works. If I then run these commands:

CHECKPOINT;
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;

and execute the parent procedure I get the errors again.

The stored procedures all use a temporary table with the same name but if I understand it correctly because they are in different procedures they would create separate tables.

Thanks SQL Kiwi, you answered my question. The temp tables are all #temp tables. After reading your comments I thought why not try rename all the temp tables and make each a unique name.

I went thought each procedure and gave each #temp table a unique name per stored procedure, then I ran the command

DBCC FREEPROCCACHE;

Then then tried running my main procedure again and all seems to be working again. SQL must be getting confused with the temp table names being the same in the all the stored procedures. What I cannot explain is why from the SSRS server the report executes and from SSMS I get errors (On our Live Environment Only)

I unfortunately have taken this system over from an outsourced development house and I would not have done things they way they did it. I have been slowly replacing some of the temp tables with CTE's & re factoring out some of the un necessary joins, in most cases this gives us better performance.


Difference between alert log and archive log in oracle databases I don't know anything about oracle databases. Actually I'm asking this question for my friend (He currently doesn't have access to internet!).

He wanted to know that what's the difference between alert logs and archive logs in oracle databases? Actually here is the scenario: There was a database for which auditing by alert logs is disabled. Someone came and truncated some data from this database. My friend didn't notice that because auditing by alert logs was disabled. But an oracle expert managed to find information about those transactions by looking at archive logs.

Now what I want to know is that: What type of information is stored in each of alert logs and archive logs? and under what conditions?

I searched documentations of oracle but unfortunately couldn't find anything. The only thing I found was mandatory auditing. But in mandatory auditing information about transactions and changes to the database isn't stored.

Thanks.

Alert log is a database log file where you can find various messages about your database, like time of startup, some db parameters, log file switch, possible errors that can occur etc.

Archive logs are archived redo(online) log files. In redo log files are all changes that happened to your data. If your database is in archivelog mode, than redo logs can't be overwritten, they are archived in some other location when they are full. Redo and archive logs are used in various database recovery scenarios.

So your Oracle expert probably used LogMiner(or some similar tool) to search through archive log files to find something like truncate table some_table;


How do I find out *why* a user has certain effective permissions? I know that I can query effective permissions by using sys.fn_my_permissions:

USE myDatabase;
SELECT * FROM fn_my_permissions('dbo.myTable', 'OBJECT')

 entity_name | subentity_name | permission_name
------------------------------------------------
 dbo.myTable |                | SELECT
 dbo.myTable |                | UPDATE
 ...

This tells me whether the current user has SELECT, INSERT, UPDATE, etc. permissions on myTable in database myDatabase.

Is it possible to easily find out why the user has these permissions? For example, I'd love to have a function fn_my_permissions_ex which outputs an additional reason column:

USE myDatabase;
SELECT * FROM fn_my_permissions_ex('dbo.myTable', 'OBJECT')

 entity_name | subentity_name | permission_name | reason
------------------------------------------------------------------------------------------------------------------------------------
 dbo.myTable |                | SELECT          | granted to database role public
 dbo.myTable |                | UPDATE          | member of group MYDOMAIN\Superusers, which belongs to database role db_datawriter
 ...

Unfortunately, I could not find such a function in the SQL Server documentation. Is there a tool or script that provides this functionality?

You can find some good information regarding security from the article below.

Reviewing SQL Server Permissions | TechRepublic http://tek.io/KfzEyp

Except:

  The following query uses the sys.database_permissions system view to
  indicate which users had specific permissions inside the current
  database.

SELECT
        dp.class_desc
       ,dp.permission_name
       ,dp.state_desc
       ,ObjectName = OBJECT_NAME(major_id)
       ,GranteeName = grantee.name
       ,GrantorName = grantor.name
    FROM
        sys.database_permissions dp
        JOIN sys.database_principals grantee
        ON dp.grantee_principal_id = grantee.principal_id
        JOIN sys.database_principals grantor
        ON dp.grantor_principal_id = grantor.principal_id


Database design for product purchase history Say I run a store that sells products of some sort. A product has typical attributes, most importantly price. When a customer makes a purchase, an invoice which references one or more products is generated.
Question: If the staff of the store updates the product, we may not be able to figure out how much a customer paid for a particular item because that product's price has been updated. How do I handle this use case? Three options I can think of:

Add a version column to the products table. When making a change, insert a new row and increment the version number, instead of updating. When I reference a product from an purchase, I reference a specific version of the product so I always know how much a product cost at the time of purchase.

The problem I see with this is that, ignoring performance and ever-increasing need for storage, whenever I need to update a price (or other product attribute), I need to update all foreign key references to that item. In my database I may have a dozen such tables, most of which do not need 'historical' information like past prices.

Create a separate product_history table, with the columns of the product table plus additional version and change_time columns with all previous versions. When updating the product table, I will save the previous version to the history table. Purchases, and any other table that needs historical information will reference rows in product_history, not product.

Add a version column to the products which is incremented every update. When there is a purchase, copy the product to a purchase_product table. The purchases table will reference purchase_product, not product. I would only keep one row per version of a product, so there is no worry of the table exploding in size when a lot of people buy the same item.

3 seems slightly better 2 because I only keep versions of products that I actually need (not for every single change of the product price). However, that means I won't be able to run analytics on when a product was NOT selling because (say) the price was too high...
Or, is there a more obvious solution?

The question you should be asking is whether product attribute changes in general are of interest or if you are particularly concerned with changes in price.
What you probably really want is to record the price actually paid in your sale transaction details.  This is something that you'll need for proper financial accounting.  There are lots of things that could impact the price paid. Maybe there was a sale on. Maybe the customer has a coupon or a loyalty discount card? Note how being on sale could be considered an attribute of the product, but a coupon or customer discount would never be.
Don't think of the price as being (only) an attribute of the product. Once it becomes the price actually paid, it's an attribute of the sale, not an attribute of the product.  Think of the price on your product table as being "the current normal price".  The price on your sale transaction detail is "the price actually paid on this sale".  You will need to have both because they are semantically different.  You just need to know where to look depending on which meaning is of interest for any specific purpose.


Comparing Query- redundancy select DISTINCT B.`Product#`,
                B.`Contact Email Address`,
CASE when A.`Product#` IS NULL then 'Yes' ELSE 'NO' END  as New customer
from `Table B` as B
LEFT JOIN `Table A` as A
ON A. `product#`= B.`product#`

What wrong in my query. I see lot of product # is repeated. Also it says "Yes" when Product # is there in Table B but none in table A..

Consider using this for the yes/no:

IF(EXISTS( SELECT 1 FROM A WHERE product_no = B.product_no ), 'Yes', 'No') AS NewCustomer


How to design a query that gives count of different parameters based on different condition from a single table I have a table books containing columns as bookid, issue_date,return_date.

Once a book is issued from library an issue_date corresponding to it is made into the table while return_date can have valid date or null value.

Now the situation is I want the number of books for the following cases:

1. no. of books issued and returned on the same day as SAME_DAY
2. no. of books that have not been returned until now (i.e. return_date is null) as NOT_RET
3. no. of books that were issued and returned on separate days as SEP_DAY

I want all these numbers as output of a single query as for example :

SAME_DAY    NOT_RET    SEP_DAY
______________________________
   12          13       10

select sum(case when issue_date = return_date then 1 else 0 end) SAME_DAY,
       sum(case when return_date is null then 1 else 0 end) NOT_RET,
       sum(case when issue_date <> return_date then 1 else 0 end) SEP_DAY
  from books;


Select statement on a table with constant inserts This is a dumb question. I was wondering how sql server works in this situation. Lets say I am selecting from a table ( select * from tab) and the table is having hundreds of rows getting inserted per second, how does sql server know when to stop reading the rows?

Edit: Just to add, I did a select on a 200Gb table and monitored the locks on another session with sp_lock and saw that sql server was grabbing page shared lock on one page at a time and reading data. So wondering how does it know when to stop? Does sql server take into consideration when the select started and select only the pages that are committed when the transaction starts?

It depends on transaction isolation level.

Your class of problem is called "phantom read", and will only be circumvented when your transaction isolation level is "serializable" or above. SQL Server has a default level of "read comitted", so you can read "too few" rows.

See https://en.wikipedia.org/wiki/Isolation_(database_systems)#Phantom_reads .


Displaying parent attributes alongside child attributes SSAS - Parent Child hierarchy - displaying other Parent attributes alongside Child Attributes

For example:

Employee Table

+-----+-------+------+---------------+
| KEY | NAME  | ROLE | MANAGERKEY    |
+-----+-------+------+---------------+
|   1 | John  | CAO  |             1 |
|   2 | Adam  | Emp  |             1 |
|   3 | Steve | CAO  |             3 |
|   4 | Colin | Con  |             3 |
|   5 | Tony  | Con  |             3 |
+-----+-------+------+---------------+

I've setup the Parent-Child relationship in the Dimension.

In Excel, when using Tabular view, I am able to see the following:

+-------------+-------+------+
| MANAGERNAME | NAME  | ROLE |
+-------------+-------+------+
| John        | John  | CAO  |
| Adam        |       | Emp  |
| Steve       | Steve | CAO  |
| Tony        |       | Con  |
| Colin       |       | Con  |
+-------------+-------+------+

I would like to see the Manager Role in the following, is this possible?

+-------------+-------+------+------------+
| MANAGERNAME | NAME  | ROLE | MANAGEROLE |
+-------------+-------+------+------------+
| John        | John  | CAO  | CAO        |
| Adam        | Emp   | CAO  |            |
| Steve       | Steve | CAO  | CAO        |
| Tony        |       | Con  | CAO        |
| Colin       |       | Con  | CAO        |
+-------------+-------+------+------------+

+-------+------+------------+
| NAME  | ROLE | MANAGEROLE |
+-------+------+------------+
| John  | CAO  | CAO        |
| Adam  | Emp  | CAO        |
| Steve | CAO  | CAO        |
| Tony  | Con  | CAO        |
| Colin | Con  | CAO        |
+-------+------+------------+

No, you can't.

As you currently have your Employee table, you can't have a Manager Role attribute for an employee.

To have a Manager Role attribute, you'd have to add a ManagerRole column to your Employee table:

Key  Name   Role  ManagerKey  ManagerRole
---  -----  ----  ----------  -----------
1    John   CAO   1           CAO
2    Adam   Emp   1           CAO
3    Steve  CAO   3           CAO
4    Colin  Con   3           CAO
5    Tony   Con   3           CAO

... so that you could define a Manager Role attribute on your Employee dimension.


How can I map a login to a database using T-SQL (not SSMS) I'm writing a program that requires me to assign all permissions and everything in code. I have gotten stuck in this part:

I just want to do the equivalent of clicking the little box under "Map" for the msdb database and assigning that user to SqlAgentUser role. I need a user to be able to add/edit SQL Server Agent jobs. I can get the settings right using SSMS but I can't for the life of me figure out how to do it in raw SQL.

I've looked into ALTER LOGIN but I'm not seeing anything that does what I need. I suspect I just don't know the correct terms to Google. I don't normally do this sort of thing.

Any help is much appreciated!

USE msdb;
GO
CREATE USER shims FROM LOGIN shims;
GO
ALTER ROLE SqlAgentUserRole ADD MEMBER shims;
GO

Also, for future reference, any time you know how to do something in the UI but not in a script, this is what the Script option on most dialogs is for - it will show you what script SSMS would have executed:


Assiging stored procedure results to SSIS variable I am trying to get the value from a stored procedure into an SSIS variable, and then testing to see if two SSIS tasks can work if I added an expression. So for an example, I am trying to use this stored procedure:

Maybe I am even setting up the SSIS variable properties entirely wrong, because I am also not sure if I am doing this the right way for the stored proc value to be imported into an SSIS variable. Please do tell me if you need any more screencaps of anything else.

Here is the task example:

And here is Precedence Constraint Editor screencap:

And here is the properties for the first task:

I want it to go forward (or fail) based on that condition. But when I test it, the process flows from first task to second regardless, and only shows me "100% complete" for the first task and nothing about whether it checked this expression to be true or not. How can I do such a thing and what is going wrong?
I do have a variable in SSIS called 'orderCount' to get the value from stored proc.

You have two choices to make this work. Either you can use a Single Result Set or you can use the OUTPUT parameter. You're currently using neither correctly.

OUTPUT parameter

Your stored procedure is defined as having a parameter of @OrderCount with a direction of OUTPUT

If you wanted to use the stored procedure within a tool, SSMS, .NET, whatever, it'd look something like

DECLARE @orderCount int = 0;
EXECUTE dbo.TestStoredProcSSVariable @orderCount OUTPUT;
SELECT @orderCount As OrderCountVariable;

It is valid to run the above without specifying OUTPUT but look at the value of @orderCount. It changes from 1435 to 0.

The same holds true when you're using the Execute SQL Task within SSIS. You must specify that the parameter is on OUTPUT and also specify it in the Parameter mappings tab.

Also specify the variable you want to map and use the OUTPUT direction there. Here I've mapped the result into an SSIS Variable of type Int32 called orderCount

Single Result Set

You have the first part of this correct - you've specified that the result set is Single Row.

You'll note that I am using EXECUTE dbo.TestStoredProcSSVariable ? as you must specify an input value or the proc call will break (at least as you've defined it). You could have hard coded a value in lieu of the ? like 0

Then, on the Result Set tab, here I'm mapping up the first column (zeroth ordinal) to a Variable called orderCountb

If you run the provided stored procedure, you will not get a value in orderCountb. Why? Because you aren't returning anything from the stored procedure call. I added a final statement inside the stored procedure of

SELECT @OrderCount AS OrderCount;

Do it yourself

You can explore either approach using the following biml. What is biml? The Business Intelligence Markup Language is the operating system for BI. Why you care about it is that it will allow you to transform some XML into an SSIS package. All you need to do is download and installed the free addon BIDS Helper

After installing BIDS Helper,

Right click on the project and select Add new Biml file
replace the contents of the file with the following XML
Fix the values in line 5. Update the Data Source to a real server and Provider to align with your SSIS version. Looking at your screenshot, this will likely be SQLNCLI10.1
Right click on BimlScript.biml and choose Generate SSIS Packages

Bimlscript.biml

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <Connection
            Name="tempdb"
            ConnectionString="Data Source=.\dev2014;Initial Catalog=tempdb;Provider=SQLNCLI11.1;Integrated Security=SSPI;"
            />
    </Connections>
    <Packages>
        <Package
            Name="dba_114775"
            ConstraintMode="Linear"
        >
            <Tasks>
                <ExecuteSQL
                    ConnectionName="tempdb"
                    Name="SQL Make procedure">
                    <DirectInput>
                        <![CDATA[IF EXISTS
(
    SELECT
        *
    FROM
        sys.procedures AS P
        INNER JOIN
            sys.schemas AS S
            ON S.schema_id = P.schema_id
    WHERE
        S.name = 'dbo'
        AND P.name = 'TestStoredProcSSVariable'
)
BEGIN
    DROP PROCEDURE dbo.TestStoredProcSSVariable
END
GO
CREATE PROCEDURE dbo.TestStoredProcSSVariable
(
    @OrderCount int OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON;

    SET @OrderCount = 1135;
    SELECT @OrderCount AS OrderCount;
END

GO

]]>
                    </DirectInput>

                </ExecuteSQL>
                <Container Name="SEQC Result set" ConstraintMode="Linear">
                    <PrecedenceConstraints>
                        <Inputs>
                            <Input OutputPathName="SQL Make procedure.Output"></Input>
                        </Inputs>
                    </PrecedenceConstraints>
                    <Tasks>
                        <ExecuteSQL
                              ConnectionName="tempdb"
                              ResultSet="SingleRow"
                              Name="SQL SingleRow">
                            <DirectInput>EXECUTE dbo.TestStoredProcSSVariable ?;</DirectInput>
                            <Results>
                                <Result VariableName="User.orderCountb" Name="0" />
                            </Results>
                            <Parameters>
                                <Parameter DataType="Int32" VariableName="User.orderCountb" Name="0" />
                            </Parameters>
                        </ExecuteSQL>
                        <ExecuteSQL ConnectionName="tempdb" Name="SQL Placeholder">
                            <DirectInput>SELECT 1;</DirectInput>
                        </ExecuteSQL>
                        <ExecuteSQL ConnectionName="tempdb" Name="Execute SQL Task 2">
                            <DirectInput>SELECT 1;</DirectInput>
                            <PrecedenceConstraints>
                                <Inputs>
                                    <Input
                                        OutputPathName="SQL Placeholder.Output"
                                        EvaluationOperation="ExpressionAndConstraint"
                                        EvaluationValue="Success"
                                        Expression="@[orderCount] &lt; 5" />
                                </Inputs>
                            </PrecedenceConstraints>
                        </ExecuteSQL>
                    </Tasks>
                </Container>
                <Container Name="SEQC Output Parameter" ConstraintMode="Linear">
                    <PrecedenceConstraints>
                        <Inputs>
                            <Input OutputPathName="SQL Make procedure.Output"></Input>
                        </Inputs>
                    </PrecedenceConstraints>
                    <Tasks>
                        <ExecuteSQL
                              ConnectionName="tempdb"
                              Name="SQL Output parameter">
                            <DirectInput>EXECUTE dbo.TestStoredProcSSVariable ? OUTPUT;</DirectInput>
                            <Parameters>
                                <Parameter
                                    DataType="Int32"
                                    VariableName="User.orderCount"
                                    Name="0"
                                    Direction="Output" />
                            </Parameters>
                        </ExecuteSQL>
                        <ExecuteSQL ConnectionName="tempdb" Name="SQL Placeholder">
                            <DirectInput>SELECT 1;</DirectInput>
                        </ExecuteSQL>
                        <ExecuteSQL ConnectionName="tempdb" Name="Execute SQL Task 2">
                            <DirectInput>SELECT 1;</DirectInput>
                            <PrecedenceConstraints>
                                <Inputs>
                                    <Input
                                        OutputPathName="SQL Placeholder.Output"
                                        EvaluationOperation="ExpressionAndConstraint"
                                        EvaluationValue="Success"
                                        Expression="@[orderCount] &lt; 5" />
                                </Inputs>
                            </PrecedenceConstraints>
                        </ExecuteSQL>

                    </Tasks>
                </Container>
                <ExecuteSQL ConnectionName="tempdb" Name="SQL Placeholder">
                    <DirectInput>SELECT 1;</DirectInput>
                    <PrecedenceConstraints>
                        <Inputs>
                            <Input OutputPathName="SEQC Result set.Output" />
                            <Input OutputPathName="SEQC Output Parameter.Output" />
                        </Inputs>
                    </PrecedenceConstraints>
                </ExecuteSQL>
            </Tasks>
            <Variables>
                <Variable DataType="Int32" Name="orderCount">-1</Variable>
                <Variable DataType="Int32" Name="orderCountb">-1</Variable>
            </Variables>
        </Package>
    </Packages>
</Biml>

Enjoy the following SSIS package


How does SQL Server reuse reserved pages after deleting a table I just want to know:

How SQL Server reuses reserved space after dropping a 1GB table? (call the table T1)
How SQL Server reuses reserved space after truncating a table? (call this table T2)

Both are clustered tables (not heaps).

Quite simply a DROP or TRUNCATE statement marks any allocated pages that were associated with the table (regardless if it was a heap or a clustered table) as no longer being allocated and the space is reused by the next operation that requires additional space (e.g. new records, a page split, etc.).

From sys.allocation_units (Transact-SQL):
ⓘ Note
When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Deferred drop operations do not release allocated space immediately. Therefore, the values returned by sys.allocation_units immediately after dropping or truncating a large object may not reflect the actual disk space available.

A TRUNCATE TABLE operation is effectively the same thing as a DROP TABLE statement, but it comes with the bonus of recreating the table immediately after it has been dropped.  This is why a TRUNCATE TABLE statement is a Data Definition Language (DDL) statement and not a Data Manipulation Language statement such as a DELETE.
If you want to look at how the engine deallocates and reuses space, you can dig into the DBCC PAGE undocumented function.  Paul Randal has a few articles (e.g. ref1, ref2, ref3, ref4) about its use that you can read through if you're so inclined.
If you've got a sandbox environment, you can also run the following series of scripts which will show the pages being allocated and deallocated in the same manner, regardless if a TRUNCATE TABLE or DROP TABLE operation is performed:
-- Create Test Database
USE [master]
GO

EXECUTE AS LOGIN = 'sa'

CREATE DATABASE [TestDB]

REVERT
GO

USE [TestDB]
GO

-- Look at GAM
DBCC PAGE(0, 1, 2, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
--Only 1016 pages are reserved.  This is because that's the initial size needed by data populating a "blank" database

ParentObject   Object                   Field                     VALUE
-------------- ------------------------ ------------------------- --------------
GAM: Header    GAM: Extent Alloc Status (1:0)        - (1:312)        ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:320)      - (1:1016)   NOT ALLOCATED
*/

-- Look at SGAM
DBCC PAGE(0, 1, 3, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
The SGAM also shows only 1016 pages are reserved, this view will remain relatively unchanged throughout the demo

ParentObject  Object                     Field                    VALUE
------------- -------------------------- ------------------------ --------------
SGAM: Header  SGAM: Extent Alloc Status  (1:0)        - (1:304)   NOT ALLOCATED
SGAM: Header  SGAM: Extent Alloc Status  (1:312)      -               ALLOCATED
SGAM: Header  SGAM: Extent Alloc Status  (1:320)      - (1:1016)  NOT ALLOCATED
*/

-- Create our Test Table
CREATE TABLE T1
(
    ID  INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    Value   CHAR(8000)
)
GO

-- Populate it with exactly 1GB of data
INSERT INTO T1 (Value)
SELECT TOP 131072 ''
FROM sys.configurations t1
    CROSS JOIN sys.configurations t2
    CROSS JOIN sys.configurations t3
    CROSS JOIN sys.configurations t4

-- Table is 1GB in Size, as expected
EXEC sp_spaceused 'T1'

/*
name  rows    reserved    data        index_size  unused
----- ------- ----------- ----------- ----------- -------
T1    131072  1052616 KB  1048576 KB  3904 KB     136 KB
*/

-- Recheck GAM and SGAM
-- Look at GAM
DBCC PAGE(0, 1, 2, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
This allocates 131072 8kb data-pages for the data and 488 8kb data-pages for the index
This view shows the location of the allocated and unallocated pages within the datafile

ParentObject   Object                   Field                     VALUE
-------------- ------------------------ ------------------------- --------------
GAM: Header    GAM: Extent Alloc Status (1:0)        - (1:8080)       ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:8088)     -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:8096)     - (1:16168)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:16176)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:16184)    - (1:24256)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:24264)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:24272)    - (1:32344)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:32352)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:32360)    - (1:40432)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:40440)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:40448)    - (1:48520)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:48528)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:48536)    - (1:56608)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:56616)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:56624)    - (1:64696)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:64704)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:64712)    - (1:72784)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:72792)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:72800)    - (1:80872)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:80880)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:80888)    - (1:88960)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:88968)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:88976)    - (1:97048)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:97056)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:97064)    - (1:105136)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:105144)   -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:105152)   - (1:113224)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:113232)   -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:113240)   - (1:121312)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:121320)   -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:121328)   - (1:129400)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:129408)   -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:129416)   - (1:130480)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:130488)   -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:130496)   - (1:132024)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:132032)   - (1:132088) NOT ALLOCATED
*/

-- Look at SGAM
DBCC PAGE(0, 1, 3, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
This view only changed to show no additional Shared Extents are allocated, though you can see
the number of 8kb data-pages reserved in the db grew to 132,088

ParentObject  Object                     Field                      VALUE
------------- -------------------------- -------------------------- --------------
SGAM: Header  SGAM: Extent Alloc Status  (1:0)        - (1:304)     NOT ALLOCATED
SGAM: Header  SGAM: Extent Alloc Status  (1:312)      -                 ALLOCATED
SGAM: Header  SGAM: Extent Alloc Status  (1:320)      - (1:132088)  NOT ALLOCATED
*/

TRUNCATE TABLE T1

-- Let the background process mark the extents as NOT ALLOCATED, so wait 10 seconds before running this
-- Recheck GAM and SGAM
-- Look at GAM
DBCC PAGE(0, 1, 2, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
This shows that all pages that were previous used by T1 have been deallocated

ParentObject   Object                   Field                     VALUE
-------------- ------------------------ ------------------------- --------------
GAM: Header    GAM: Extent Alloc Status (1:0)        - (1:312)        ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:320)      - (1:132088) NOT ALLOCATED
*/

-- Look at SGAM
DBCC PAGE(0, 1, 3, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
No changes here, as the db didn't grow and we haven't done anything regarding mixed extents

ParentObject  Object                     Field                      VALUE
------------- -------------------------- -------------------------- --------------
SGAM: Header  SGAM: Extent Alloc Status  (1:0)        - (1:304)     NOT ALLOCATED
SGAM: Header  SGAM: Extent Alloc Status  (1:312)      -                 ALLOCATED
SGAM: Header  SGAM: Extent Alloc Status  (1:320)      - (1:132088)  NOT ALLOCATED
*/

-- Repopulate T1
INSERT INTO T1 (Value)
SELECT TOP 131072 ''
FROM sys.configurations t1
    CROSS JOIN sys.configurations t2
    CROSS JOIN sys.configurations t3
    CROSS JOIN sys.configurations t4

-- Table is 1GB in Size
EXEC sp_spaceused 'T1'

/*
name  rows    reserved    data        index_size  unused
----- ------- ----------- ----------- ----------- -------
T1    131072  1052616 KB  1048576 KB  3904 KB     136 KB
*/

-- Recheck GAM and SGAM
-- Look at GAM
DBCC PAGE(0, 1, 2, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
Again, this allocates 131072 8kb data-pages for the data and 488 8kb data-pages for the index
This view shows the location of the allocated and unallocated pages within the datafile and they've
likely changed slightly from when they were first allocated, though the counts remain consistent

ParentObject   Object                   Field                     VALUE
-------------- ------------------------ ------------------------- --------------
GAM: Header    GAM: Extent Alloc Status (1:0)        - (1:8080)       ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:8088)     -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:8096)     - (1:16168)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:16176)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:16184)    - (1:24256)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:24264)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:24272)    - (1:32344)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:32352)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:32360)    - (1:40432)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:40440)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:40448)    - (1:48520)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:48528)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:48536)    - (1:56608)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:56616)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:56624)    - (1:64696)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:64704)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:64712)    - (1:72784)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:72792)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:72800)    - (1:80872)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:80880)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:80888)    - (1:88960)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:88968)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:88976)    - (1:97048)      ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:97056)    -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:97064)    - (1:105136)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:105144)   -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:105152)   - (1:113224)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:113232)   -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:113240)   - (1:121312)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:121320)   -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:121328)   - (1:129400)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:129408)   -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:129416)   - (1:130416)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:130424)   -            NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:130432)   - (1:131960)     ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:131968)   - (1:132024) NOT ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:132032)   - (1:132088)     ALLOCATED
*/

-- Look at SGAM
DBCC PAGE(0, 1, 3, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
Still no changes here, as the db didn't grow and we haven't done anything regarding mixed extents

ParentObject  Object                     Field                      VALUE
------------- -------------------------- -------------------------- --------------
SGAM: Header  SGAM: Extent Alloc Status  (1:0)        - (1:304)     NOT ALLOCATED
SGAM: Header  SGAM: Extent Alloc Status  (1:312)      -                 ALLOCATED
SGAM: Header  SGAM: Extent Alloc Status  (1:320)      - (1:132088)  NOT ALLOCATED
*/

DROP TABLE T1

-- Let the background process mark the extents as NOT ALLOCATED, so wait 10 seconds before running this
-- Recheck GAM and SGAM
-- Look at GAM
DBCC PAGE(0, 1, 2, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
Again, this shows that all pages that were previous used by T1 have been deallocated.
This is the same behavior we saw with a TRUNCATE, even though we just executed a DROP TABLE command.

ParentObject   Object                   Field                     VALUE
-------------- ------------------------ ------------------------- --------------
GAM: Header    GAM: Extent Alloc Status (1:0)        - (1:312)        ALLOCATED
GAM: Header    GAM: Extent Alloc Status (1:320)      - (1:132088) NOT ALLOCATED
*/

-- Look at SGAM
DBCC PAGE(0, 1, 3, 3) WITH TABLERESULTS, NO_INFOMSGS

/*
Still no changes here, as the db didn't grow and we haven't done anything regarding mixed extents

ParentObject  Object                     Field                      VALUE
------------- -------------------------- -------------------------- --------------
SGAM: Header  SGAM: Extent Alloc Status  (1:0)        - (1:304)     NOT ALLOCATED
SGAM: Header  SGAM: Extent Alloc Status  (1:312)      -                 ALLOCATED
SGAM: Header  SGAM: Extent Alloc Status  (1:320)      - (1:132088)  NOT ALLOCATED
*/

-- Cleanup
USE [master]
GO

DROP DATABASE [TestDB]
GO

For more information on the GAM, SGAM, extents, and the basics to the storage engine, start with the Pages and Extents Architecture Guide by Microsoft.  Hopefully this answers your question, though I feel you'll have more questions to follow-up with.


Create a plan guide to cache (lazy spool) CTE result I normally create plan guides by first constructing a query that uses the correct plan, and copying it across to the similar query that doesn't.  However, that is sometimes tricky, especially if the query is not exactly the same.  What is the right way of creating plan guides from scratch?

SQLKiwi has mentioned drawing up plans in SSIS, is there a way or useful tool to assist in laying out a good plan for SQL Server?

The specific instance in question is this CTE: SQLFiddle

with cte(guid,other) as (
  select newid(),1 union all
  select newid(),2 union all
  select newid(),3)
select a.guid, a.other, b.guid guidb, b.other otherb
from cte a
cross join cte b
order by a.other, b.other;

Is there ANY way to make the result come up with exactly 3 distinct guids and no more? I'm hoping to be able to better answer questions in future by including plan guides with CTE-type queries that are referenced multiple times to overcome some SQL Server CTE quirks.

Is there ANY way to make the result come up with exactly 3 distinct guids and no more? I'm hoping to be able to better answer questions in
  future by including plan guides with CTE-type queries that are
  referenced multiple times to overcome some SQL Server CTE quirks.

Not today.  Non-recursive common table expressions (CTEs) are treated as in-line view definitions and expanded into the logical query tree at each place they are referenced (just like regular view definitions are) before optimization.  The logical tree for your query is:

LogOp_OrderByCOL: Union1007 ASC COL: Union1015 ASC
    LogOp_Project COL: Union1006 COL: Union1007 COL: Union1014 COL: Union1015
        LogOp_Join
            LogOp_ViewAnchor
                LogOp_UnionAll
                    LogOp_Project ScaOp_Intrinsic newid, ScaOp_Const
                    LogOp_Project ScaOp_Intrinsic newid, ScaOp_Const
                    LogOp_Project ScaOp_Intrinsic newid, ScaOp_Const

            LogOp_ViewAnchor
                LogOp_UnionAll
                    LogOp_Project ScaOp_Intrinsic newid, ScaOp_Const
                    LogOp_Project ScaOp_Intrinsic newid, ScaOp_Const
                    LogOp_Project ScaOp_Intrinsic newid, ScaOp_Const

Notice the two View Anchors and the six calls to the intrinsic function newid before optimization gets started.  Nevertheless, many people consider that the optimizer ought to be able to identify that the expanded sub-trees were originally a single referenced object and simplify accordingly. There have also been several Connect requests to allow explicit materialization of a CTE or derived table.

A more general implementation would have the optimizer consider materializing arbitrary common expressions to improve performance (CASE with a subquery is another example where problems can occur today).  Microsoft Research published a paper (PDF) on that back in 2007, though it remains unimplemented to date.  For the time being, we are limited to explicit materialization using things like table variables and temporary tables.

  SQLKiwi has mentioned drawing up plans in SSIS, is there a way or useful tool to assist in laying out a good plan for SQL Server?

This was just wishful thinking on my part, and went well beyond the idea of modifying plan guides.  It is possible, in principle, to write a tool to manipulate show plan XML directly, but without specific optimizer instrumentation using the tool would likely be a frustrating experience for the user (and the developer come to think of it).

In the particular context of this question, such a tool would still be unable to materialize the CTE contents in a way that could used by multiple consumers (to feed both inputs to the cross join in this case).  The optimizer and execution engine do support multi-consumer spools, but only for specific purposes - none of which could be made to apply to this particular example.

  While I'm not certain, I have a fairly strong hunch that the RelOps can be followed (Nested Loop, Lazy Spool) even if the query is not exactly the same as the plan  - for instance if you added 4 and 5 to the CTE, it still continues to use the same plan (seemingly - tested on SQL Server 2012 RTM Express).

There is a reasonable amount of flexibility here.  The broad shape of the XML plan is used to guide the search for a final plan (though many attributes are ignored completely e.g. partitioning type on exchanges) and the normal search rules are considerably relaxed as well.  For example, early pruning of alternatives based on cost considerations is disabled, the explicit introduction of cross joins is allowed, and scalar operations are ignored.

There are too many details to go into in depth, but the placement of Filters and Compute Scalars cannot be forced, and predicates of the form column = value are generalized so a plan containing X = 1 or X = @X can be applied to a query containing X = 502 or X = @Y.  This particular flexibility can help greatly in finding a natural plan to force.

In the specific example, constant Union All can always be implemented as a Constant Scan; the number of inputs to the Union All does not matter.


Postgresql update table within trigger: duplicate key value violates unique constraint For a given table, I want to have a copy of all previous records in my database. I only insert data in the table.

I have an additional column seq to know which is the active record.

So, I use seq = 0 for the current record. Every record with seq < 0 is an old one.

This is the table (I ommit some columns to make it simpler)

CREATE TABLE public.machine (
    id character varying(25) NOT NULL,
    seq integer NOT NULL,
    vendor character varying(50),
    model character varying(50),
    CONSTRAINT pk_machine PRIMARY KEY (id, seq) );

Let me show some records:

"cat-1" 0   "Caterpillar"   "D3K2 TIER 4 FINAL"
"cat-1" -1  "Caterpillar"   "D3K2 TIER 4 FINAL"
"cat-1" -2  "Caterpillar"   "D3K2 TIER 4 FINAL"
"cat-1" -3  "Caterpillar"   "D3K2 TIER 4 FINAL"
"cat-1" -4  "Caterpillar"   "D3K2 TIER 4 FINAL"
"cat-1" -5  "Caterpillar"   "D3K2 TIER 4 FINAL"

If I need to insert a new record, I do:

UPDATE machine SET seq = seq-1 where id = 'cat-1';

and then:

insert into machine (id, seq, vendor, model) values ( 'cat-1', 0, 'Caterpillar', 'D3K2 TIER 4 FINAL');

But his does not work. Some times, the update fails with the error:

ERROR:  duplicate key value violates unique constraint "pk_machine"
DETAIL:  Key (id, seq)=(cat-2, 0) already exists.

If the update changes row by row, each of a time, then it might break. If the update is made in the right order (starting from the minimum number), the update would work.

How do I do the update order by seq descending?

I wrote a trigger to to this, but it fails some times.

CREATE TRIGGER make_history
    BEFORE INSERT
    ON public.machine
    FOR EACH ROW
    EXECUTE PROCEDURE public.history();

CREATE OR REPLACE FUNCTION history() RETURNS TRIGGER AS $$
BEGIN
    UPDATE machine SET seq = seq-1 where id = NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Thanks @a_horse_with_no_name. The DEFERRED was the trick.

I was able to solve this issue changing the trigger function to:

CREATE OR REPLACE FUNCTION history() RETURNS TRIGGER AS $$
BEGIN
    SET CONSTRAINTS ALL DEFERRED;
    UPDATE machine SET seq = seq-1 where id = NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


Optional Parent-Child relationship I have a problem modeling a relationship whereby the child may or may not be related to a parent. For example. You have a Band database with a Tour(parent) table, and a Gig/Concert(child) table. Now, a Tour is a compilation of 1 or more Gigs, but a Gig may also be held off-tour. How do i model this without violating referential integrity?

E.G Tables:

    create table Tour(
    tourID int primary key,
    ...//
    );

    create table Gig(
    gigID int primary key,
    tourID int,
    ...//
    foreign key(tourID) references Tour.tourID
    );

PS: Newbie here

Just make the Tour optional?   The below code is for SQL Server, but you should be able to port this to other flavors of SQL.  Essentially, the TourID column can be nullable, indicating the Gig is not part of a Tour.

CREATE TABLE dbo.Bands
(
    BandID INT NOT NULL
        PRIMARY KEY CLUSTERED
    , BandName VARCHAR(100) NOT NULL
);

CREATE TABLE dbo.Tours
(
    TourID INT NOT NULL
        PRIMARY KEY CLUSTERED
    , TourName VARCHAR(100) NOT NULL
)

CREATE TABLE dbo.Gigs
(
    GigID INT NOT NULL
    , GigName VARCHAR(100) NOT NULL
    , GiGDate DATETIME NOT NULL
    , BandID INT NOT NULL
        FOREIGN KEY
        REFERENCES dbo.Bands(BandID)
    , TourID INT NULL
        FOREIGN KEY
        REFERENCES dbo.Tours(TourID)
);

This design will also allow multiple bands to take part in any given tour, thereby providing a mechanism for headliners and opening acts, or multi-stage events, etc.

Filling out the example from above with some data:

INSERT INTO dbo.Bands (BandID, BandName)
VALUES (1, 'Past the Perimeter');

INSERT INTO dbo.Tours (TourID, TourName)
VALUES (1, 'Taking it to the City');

INSERT INTO dbo.Gigs (GigID, GigName, GiGDate, BandID, TourID)
VALUES (1, 'Tour Date 1', '2016-05-27T21:30:00', 1, 1);

INSERT INTO dbo.Gigs (GigID, GigName, GiGDate, BandID, TourID)
VALUES (2, 'Standalone Gig', '2016-05-28T21:30:00', 1, NULL);

SELECT b.BandName
    , g.GigName
    , g.GiGDate
    , t.TourName
FROM dbo.Bands b
    INNER JOIN dbo.Gigs g ON b.BandID = g.BandID
    LEFT JOIN dbo.Tours t ON g.TourID = t.TourID
ORDER BY g.GiGDate;


Oracle: How do I query a Hierarchical table? Background

This is for the construction of some views we'll be using for reporting.

I have an table of locations, the key fields being "location" and "parent".

The structure that these two fields create, level-wise, are along the lines of Company Name --> Campus Name --> Building Name --> Floor Name --> Room Name. Company name remains the same and Campus name remains the same in this case.

The structure of locations generally looks like this:

                                 +-----------+
                                 | Org. Name |
                                 +-----+-----+
                                       |
                                 +-----v-----+
           +--------------------+|Campus Name|+---+--+-------------+
           |                     +--+--------+    |                |
           |                        |             |                |
           |                        |             |                |
        +--+-----+           +------+-+        +--+----+       +---+---+
    +--+| BLDG-01|+--+       | BLDG-02|        |BLDG-03|       |Grounds|
    |   +--------+   |       +--------+        +-------+       +-------+
  +-+------+   +-----+--+
  |Floor-01|   |Basement+-------+
  +-+------+   +--------+       |
    |                           |
    |                           |
    | +----------+      +-------+--+
    +-+Room 1-001|      |Room B-002|
      +----------+      +----------+

Every location links back to its parent location, which is ultimately the organization name. Currently, there is only one organization and one campus.

Goals

I would like to be able to query all locations beneath any given location at the "Building" level. This is so I can return things like how many workorders have been performed for any location within a given building.
I would like to be able to determine which sub-location belongs to which building. Essentially the reverse; I would like to go from any level beneath the building level and trace back up to what the building is.
I would like this to be in a view. That means, I would like to have a table that for every item at the "building" level, lists the building in the left-hand column and all possible locations UNDER that building in the right-hand column. This way I'd have a list that I could query at any time to find which locations are part of which building.

Attempts and Doing it Right

I've attempted to do this through horribly constructed views, UNION queries, etc. -- which all have seemed like a bad idea. I know Oracle possesses a mechanism for this through "CONNECT BY"; I'm just not sure how to make use of it.

CONNECT BY is the correct way to handle data that is naturally recursive.

I don't know what your table looks like but maybe something like:

SELECT *
FROM some_table st
START WITH st.location = 'BLDG-01'
CONNECT BY PRIOR st.location = st.parent;

This should get nodes under "BLDG-01".

The START WITH clause is your base case.

Another explanation (aside from the Oracle one which I assume you've already read and had trouble with, it's probably very terse):

http://www.adp-gmbh.ch/ora/sql/connect_by.html

Also:

http://psoug.org/reference/connectby.html

And:

http://www.oradev.com/connect_by.jsp


How can sp_blitz help investigate what caused TLog to fill up Non-uncommon scenario:  Something has ran wild and caused the TLog for a patifular Db to fill and everything has stopped.  How can sp_blitz show us some info on what led up to this point? What about sp_whoisactive?

No, sp_Blitz doesn't track that kind of thing. A better approach would be querying LOG_REUSE_WAIT_DESC.


How to use JSON path wildcards in JSON_QUERY? For a minimal working example say I have a column storing JSON with a structure similar to the following.

DECLARE @json NVARCHAR(4000) =
    '{"Dims":
     [
      {"Name":"Apple", "Baking": ["Pie","Tart"], "Plant":"Tree"},
      {"Name":"Tomato", "Cooking":["Stew","Sauce"], "Plant":"Vine"},
      {"Name":"Banana","Baking":["Bread"], "Cooking":["Fried"], "Plant":"Arborescent"}
     ]}
'

This is valid JSON and scalars can be returned.

SELECT ISJSON(@json);

1

SELECT JSON_VALUE(@json,'$.Dims[0].Name');

Apple

However, when I use JSON_QUERY to return the names of all of the "Dims" SQL Server 2017 errors.

SELECT JSON_QUERY(@json,'$.Dims[*].Name');

Msg 13607, Level 16, State 4, Line 16
JSON path is not properly formatted. Unexpected character '*' is found at position 7.

This should return

["Apple", "Tomato", "Banana"]

This is a valid standard JSON path. Any ideas why this is not working and how to get this to work on SQL Server 2017.

Looks like that is not currently supported, and would make a good feedback item.

You can get the names into a resultset with a query like:

select json_value(v.value,'$.Name') Name
from openjson(@json,'$.Dims') v

And from there you can construct a new JSON doc, but if you want an array of primitives, you need to do something like this.

select concat('[',string_agg( quotename(json_value(v.value,'$.Name'), '"'), ', ')  WITHIN GROUP ( ORDER BY [key] )   ,']')
from openjson(@json,'$.Dims') v

I don't think there is a way to get an array of primitive values (ie not objects) with FOR JSON queries.


Can't restart Postgres on Windows 10 with external drive I've been working on a postgres database, with my data directory on my 8TB external USB drive. Occasionally, I need to restart the postgres instance to change configuration settings, but for some reason I can never get the service to restart.

Everything works fine in Windows 7
Can't restart postgres in Windows 10
Data directory in D:\postgresdata\
Database is empty at the moment, just the default postgres instance
When windows starts, the database engine starts automatically, and functions correctly
Running "show data_directory" returns D:/postgresdata/
Opening services.msc and restarting my postgres service starts and stops, never successfully starting.
Running pg_ctl.exe start -D D:\postgresdata fails to start with these errors:

FATAL:  could not access status of transaction 0
DETAIL:  Could not open file "pg_notify/0000": Invalid argument.

Things I've tried:

I've tested the same scenario on my C: drive, and it always starts and restarts correctly.
I've run chkdsk on the D: drive, nothing is wrong with it.
I've played around with permissions on D: and nothing makes any difference.
I've made a second partition from my C: drive and verified that a data directory on it always starts and restarts correctly.
Initializing a new database with pg_ctl init -D D:\someotherpath\ on the D: drive does not work either, same error as restarting in general.

I'm at a loss for what else to try. The weirdest thing is how the system always starts up and works at system boot time, but not in any other circumstance.

Update:
I attempted to run postgres against a USB thumb drive and the error is the same.

What type of partitioning scheme and USB chipset (USB-sata bridge) does the drive use? There are all sorts of unexpected limits you can still hit on a modern USB drive with certain partition types/sizes and chipsets. I've run into issues with 2TB limits imposed by some USB drive controllers. You can format a partition larger than that but when you try to read/write to a higher block number you get errors.

You can also hit limits imposed by the older MSDOS partition scheme which can be overcome by reformatting as GPT (obviously move/backup your data first).

There's a more indepth explanation here: here and here

So your solution might simply be to buy a more modern/high-end external enclosure.

Another possibility is you're hitting some sort of bug related to writeback caching. You could try adjusting the drive cache settings as described in this article

The fact it works in win7 but not win10 could also indicate a bad driver but not sure if there's anything you can do about that other than check the manufacturers website.

You can test all of the above theories by trying to write some really big files to the drive and then testing the files for corruption, one way to do that is to record a giant MPEG with your webcam or VLC screen capture. To be a useful test the files need to be really big (like 2TB+)


How to alter an existing partitioned table to add sub-partitions to it? This forum has helped me earlier on many occasions and now I need someone to help me with this.

I am looking to convert a pre-existing partitioned (range) table - to sub-partitioned (composite range-list).

1) I Range Partitioned an existing table using the query below:

alter table PART_TEST
modify PARTITION BY RANGE (CREATEDATE)
(
PARTITION p1 VALUES LESS THAN (TO_DATE('15-MAY-2019', 'DD-MON-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('16-MAY-2019', 'DD-MON-YYYY')),
PARTITION p3 VALUES LESS THAN (maxvalue)) online;

2) Then I tried to alter the table to add sub-partitions (list) using the query below:

ALTER TABLE PART_TEST
MODIFY PARTITION P1
ADD SUBPARTITION SP1
VALUES ('1') tablespace TEST_PART;

But I see this error:

  Error report - ORA-14253: table is not partitioned by composite
  partition method
  14253. 00000 -  "table is not partitioned by Composite Range method"
  *Cause:    The table in a subpartition maintenance operation (ALTER TABLE
             EXCHANGE/MODIFY/MOVE/TRUNCATE SUBPARTITION, or ALTER TABLE MODIFY
             PARTITION ADD/COALESCE SUBPARTITION command must be partitioned
             by Composite Range method
  *Action:   Ensure that the table is partitioned by Composite Range method

Below is what I am tasked with,

Scenario - 1:
==> I want to have my existing partitions(range) subpartitioned (list) so that my old data is usable. The data in each of my partitions have to be moved to a sub-partition,

Scenario - 2: Then revert back to partition level (reverse migrate),
==> I mean the data from all of the sub-partitions within each partition is moved to be at the partition level and no sub-partitions are left in the database.

Is it possible and is there a way to do so?
If so how to implement the above scenarios - convert existing partitions(range) to sub-partitioned (Range-list) - and how to move back the data from sub-partitions to each of its partition back and no-sub partitions are left.

Scenario 1 - redefine the table

Your SQL is adding a sub-partition, not redefining the table.  I suspect you need to redefine the table to have sub-partitions before you can add a sub-partition.

12.2c+

The ability to change a non-partitioned table into a partitioned table via ALTER TABLE was introduced in 12.2c.

Looking at the documentation, it appears that you can change a RANGE partitioned table into a RANGE-LIST partition in the same way.  I haven't tried; be cautious; use DEV for practice.

pre-12.2c

DBMS_REDEFINITION is the de facto way of changing a table between non-partitioned and partition designs.

Google is your friend.  But, here is my recommended link:

Scenario 2 - convert back to non-partitioned table.

This requirement perplexes me.  Doing such a massive change, multiple times, is probably a Tell-Tale sign that you have a more serious problem that you should be solving.

Any way, I suspect that the only way to do this is with DBMS_REDEFINITION.


MariaDB Concurrent Connections with Update Statement I have two high-performance servers (Xeon 2.1 Ghz, 64GB RAM, SSD 120GB) with private IPs (i.e 10.0.0*) and 100Mb/s bandwidth:

SERVER1: Nginx + PHP7.0-FPM (latest versions)
SERVER2: MariaDB (latest version stable)

The database has only 1 row and this is the table structure:

CREATE TABLE users (
       `id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
       `added` int(11) DEFAULT 0,
       `email` VARCHAR(150) NOT NULL,
       `files` BIGINT DEFAULT 1,
       UNIQUE KEY `email` (`email`),
       INDEX email_index (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

This is the PHP script that makes just one select and one update to the first (and only) item with ID 1:

$mysqli = new mysqli("p:10.0.XX.XX", "username", "password", "database");

if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

echo "Connected to the database!"."\n";

$result = $mysqli->query("select files from users where id = 1");

if($result)
{
    $row = $result->fetch_array(MYSQLI_ASSOC);
}
else
{
    echo "
Failed to select user fields
";
    exit();
}

$mysqli->query("update users set files='".intval(intval($row['files'])+1)."' where id = 1");

$mysqli->close();

Here is the changes I made to the default MariaDB config file:

[mysqld]
skip-name-resolve
bind-address            = 10.0.XX.XX
max_connections         = 25000
max_allowed_packet      = 1G
innodb_buffer_pool_size = 55G

** Bin logs is disabled, flush trx is set to 0, general logging is disabled, only slow queries are logged **

** I am not interested in ACID or other redundancy options **

Everything works perfectly without the "update" query, and can handle up to 10K concurrent connections according to https://loader.io/ tests. The problem is when the PHP script has to do the update statement, looks like all "update" queries are queued in the mariaDB server and it takes a lot of time to finish, more than 30 minutes (I then rebooted the mariaDB server because didn't want to wait more). So my questions are:

1) Is there a way to make "update" statement to not be queued and to be processed directly?

2) How many concurrent connections can the 64GB RAM + SSD mariaDB server handle considering the "update" statement?

You cannot have "concurrent" updates of a single row.  The best you can do is to make them fast.

If you don't need the value, do it in a single statement:

UPDATE user SET files = files + 1 WHERE id = 1

Also, be sure that autocommit=ON is configured.

This will easily handle 100 increments per second on a spinning drive.  If you need even more speed, then set

innodb_flush_log_at_trx_commit = 2

This will cut back significantly on disk hits, at the potential loss of data for up to a second (in the case of a power failure).

max_connections = 25000 is grossly unreasonable; if you get more than a few dozen connections stumbling over each other, the system will appear to 'hang'.  At which point, more and more connections will be started, while the running connections run slower and slower.  This will because the OS is sharing the CPU, etc, among too many threads to actually get anything completed.

If you need thousands/second, I'll provide you with some other ideas.  (Note:  I say "per second", not "concurrently".)


How does Berkeley DB manage its files? I'm using Berkeley DB (BDB) as a persistent store for a JMS queue.  When I consume an entry from the queue the underlying BDB files do not immediately shrink, but do eventually.  I'm running into issues with the BDB files taking up lots of space on the file system while retrieval performance degrades.

My entry size varies considerably but it is not uncommon to have 400,000 messages of around 32kb each in the persistent queue.

I'd like to understand how BDB manages the files so that I can throttle the number of entries for file size/retrieval performance.  Or so I can rule out BDB as my persistent store mechanism.

I am probably searching for the wrong terms but have not found what I'm looking for in the Oracle documentation or The Berkeley DB Book.  I would not be surprised if BDB doesn't want me messing with its internals but I would be surprised if (at least) an overview of how it does handle its internals is not available.

Basically the philosophy seems to be that its not worth putting much effort on compacting the DB if it will grow again. The way the BDB engine works makes it hard to really reclaim any freed space on workloads that have much insert/update activity, and I think JMS persistence quite possibly is such a workload. The gain from this philosophy, of course, is that on a burst of new messages the DB doesn't need to allocate more pages but can directly write the data on existing pages in the most efficient way. But if the hit on retrieval performance is significant it might be that BDB is indeed not right choice for you workload.

I wonder if the answers provided in these posts in the Oracle forums provide any light to the mystery (the quote comes from the second link).

Understanding compact and database file size

Berkeley DB database file size keeps growing

  There is no specific maximum size that the database must reach before
  page reuse starts. Also, you don't need to close and reopen the
  database handles in order to influence page reusing. BDB reuses pages
  when they are emptied and it does not perform any kind of automatic
  key/page balancing (as it will lead to deadlocks). The factors that
  you should be looking into are the insert, update and delete rates, if
  transactions are long-lived, the deadlock detection policy, the size
  of the database page size and the page fill factor etc.

  One example that might explain the behavior you're seeing is one where
  the delete process/thread(s) gets "late" to a page when trying to
  remove on key found on that page. By the time it acquires the write
  lock on that page, there are no more consecutive keys there, and at
  best the delete process/thread only manages to remove just part of
  those keys (the other keys are ones with higher values, as the insert
  and update processes/threads are far ahead), thus the page will not be
  emptied so that it can be placed onto the free list for reuse. If the
  insert and update processes are very active, this may result in rapid
  page splits (or new page allocations), hence sets of keys are moved
  over onto new pages. In addition, the new keys inserted may land on
  already populated pages, thus making things more difficult for the
  delete process. If the new keys being inserted can be distributed
  based on their values to already populated pages where there is room
  for them, then the pages on the free list, if any, will not be reused.


Azure SQL Server Trigger Not Firing Using Azure SQL Server, on a managed instance (i.e. a single server running multiple databases, where the db in question is one of them).

I've created a trigger on INSERT to table A, that performs an insert of the same data (with some massaging) to table B.

When checking the sys.triggers table, it seems that the trigger is there and is not disabled. When performing a single manual insert into the table for testing purposes, that trigger works. When looking at the audit logs for that database, that insert is indeed there (i.e. the insert created by the trigger).

When an application is doing the insert, however, the trigger does not fire. I.E. no rows are inserted the table B, and the audit logs are not showing inserts created by the trigger (i.e. the inserts to table B), but are showing the inserts to table A.

Not sure how to debug this further - suggestions for further explorations / solutions are welcome.

One of the more likely reasons for this is (as you confirmed) Bulk Insert.  By default, Bulk Insert does not fire triggers.  You can change this behavior in the options for the bulk insert when your application/code is setting up the operation.

How to do that can vary by the specifics of your code, Powershell, .Net, etc.  But this other answer may be able to help.  https://stackoverflow.com/questions/12366510/trigger-not-firing-for-multirow-bulk-insert


Installing Oracle 12c on Red Hat Enterprise Linux 6 When I try to ./runInstaller I get this error message:

  Source location is incomplete. Make sure you have downloaded and extracted all the relevant archives.

I have two database unzip folders, database and database(0).

I copied database(0)/install/.oui to database/install, but the error message does not disappear.

This is the part of runInstaller:

# Abort the install, if install/.oui file is not found in the stage area
#
if [ ! -f $CMDDIR/install/.oui ]
then
        echo "Source location is incomplete. Make sure you have downloaded and extracted
all the relevant archives."
        exit 1
fi

#

This is the list of my database directory:

Certainly I have the .oui in database/install.

What can I do to resolve this error?

Extracting the Installation Files

  Oracle RDBMS software is available as two archive files. Ensure that
  you extract both the archive files to the same directory.

Not just install/.oui, everything.


LIKE character length limitation The background to this question can be found here.

The following part of my stored procedure does not work. Is there a character length limitation for the LIKE clause? t1.ProductList sometimes can be up to 1000 characters in length:

DELETE t1 FROM #tmptable t1
WHERE EXISTS (SELECT 1 FROM #tmptable2_ t2
              WHERE t1.DealerId != t2.DealerId
              AND t2.ProductList LIKE '%' + t1.ProductList + '%'
              AND t2.numberOfVibs > t1.numberOfVibs);

Sample data for a single row of the ProductList column:

B1ZMA25706,B1ZMI09502,B1ZMI12910,B1ZMI18602,BB001TBL26,BBHMOVE4,BE09501FBL,BGS52200,BKS3003,BM2,BO11001EBO,BPGTB1200,BPGTC172HP,BPGTX663,BPIVMS6502,BPIVOD1022,BPVA475,BPVB1000,BPVB800,BPVC652,BPVPW1500,BPVW1000,BPVX652,BPVX662,BREMEN78,BSA2602,BSD2880,BSG62082,BSG71800,BSG81623,BSG82422,BSG82480,BSGL32015,CAB150,CAB21,CD1401B,CD21001WAL,CD21004WAL,CD2105WHI,CD2108WAL,CD5601S,CDBSE7300A,CEPM8CAPPU

Is there any character length limitation for LIKE clause?

Yes. From the documentation for LIKE:

  pattern

    Is the specific string of characters to search for in match_expression, and can include the following valid wildcard characters. pattern can be a maximum of 8,000 bytes.

In your case the limit is 4,000 characters because the FOR XML PATH expression returns a Unicode string (two bytes per character). If you check the ProductList column of your temporary table, you will see the data type is nvarchar(max):

EXECUTE tempdb.sys.sp_help
    @objname = N'#tmptable';

Depending on your data, you may be able to use single-byte ANSI characters instead, giving you up to 7,998 characters for the concatenated string of ProductCodes:

CREATE TABLE #SellerProducts
(
    SellerID        integer PRIMARY KEY,
    ProductList     varchar(7998) NOT NULL,
    ProductCount    bigint NOT NULL
);

INSERT #SellerProducts
(
    SellerID,
    ProductList,
    ProductCount
)
SELECT
    s.SellerId,
    STUFF
    (
        (
        SELECT
            ',' + ProductCode
        FROM Stocks
        WHERE
            s.SellerId = Stocks.SellerId
        ORDER BY
            ProductCode
        FOR XML
            PATH('')
        )
        , 1, 1, ''
    ),
    COUNT_BIG(*)
FROM dbo.Stocks AS s
WHERE
    s.ProductCode IN ('30A','20A','42B')
    AND s.StockData > 0
GROUP BY
    s.SellerId;

The code above is deliberately designed to throw the following error if ProductList contains more than 7,998 characters:

You do not need to create a second copy of the temporary table to do the DELETE:

DELETE t1
FROM #SellerProducts AS t1
WHERE EXISTS
(
    SELECT 1
    FROM #SellerProducts AS t2
    WHERE
        t2.SellerId <> t1.SellerId
        AND t2.ProductList LIKE '%' + t1.ProductList + '%'
        AND t2.ProductCount > t1.ProductCount
);

The character limit for the ProductList column is 7,998 characters to allow two for the % characters added before the LIKE is performed - giving a total of 8,000 characters, the maximum allowed for the LIKE pattern string.


Any general reasonable performance tests for DBs on application server and DBs on separate server? My company are planning to move the DBs (SQL Server 2008 R2) from the application server to another server (random distance). All servers are Windows 2003 x86.

Are there any general performance tests in SQL Server I can run to find out if under three different circumstances (no load, normal load, full load) this configuration is performing better or worse than the original configuration where application and DBs were located on the same server?

(I do not have more details yet.)

If you can capture a typical workload with Profiler, you could replay it against a point-in-time restored copy of the database via the RML tools. Summary of the RML tools can be found in Testing Stored Procedure Scalability.

What you decide to measure as part of these tests in dependent on the application. Measuring response time is simple for a web application for example, not so easy with a desktop client.

It would be rare to find that performance degrades when you move the database to a dedicated server. Typically this only occurs when the application is overly "chatty" i.e. executing lots of iterative queries (incorrect use of an ORM a common cause), rather than set operations. In this situation the additional overhead of marshalling data over the wire via TCP or Named Pipes vs Shared Memory Protocol outweighs the performance gained from dedicating a server to SQL.


How to send secure emails using sp_send_dbmail When using sp_send_dbmail to send an email on SQL Server, how do you specify that the email be sent in an encrypted format?

I found a thread discussing including the string "[SECURE]" in the subject line, but these comments are 8 years old and not backed up by any source.

I don't think SQL Server has any built-in functionality to achieve what you're after.

One possible solution is to use a local SMTP server as a relay, so that the email is not encrypted on the initial hop (between SQL Server and the local SMTP server), but it gets encrypted leveraging the SMTP server's functionalities on the second hop (to the outbound SMTP server).


Is it possible to READ LOCK all databases and then UNLOCK one by one? I need to take backup of databases from a live server for replication. There could be 2-5 databases each having size of 1-5 GB. Master is a live server so I can not stop mysql service. Since size of data is big, baking up in Read-Only mode could be dangerous too. I'm wondering is it possible to

1. READ LOCK all databases and stop bin-log writing
2. Take backup of a database and UNLOCK only that one.

MySQL Version   : 5.1.61
Storage Engine  : MyISAM

Thanks in advance.

If you already have a Slave hanging off the Master, STOP SLAVE and backup the Slave.

If you are trying to set up replication to begin with, you are out of luck because you are still on MyISAM.


Processor affinity for SQL Server on vmware A while ago a consultant said that I should set processor affinity when I'm running SQL Server on VMWare. The advice was to disable CPU0 so it was free for the OS.

When I read the "Architecting Microsoft SQL Server on VMware vSphere"-PDF on the site of VMWare I find following:

  VMware recommends not using CPU affinity in production because it
  limits the hypervisor’s ability to efficiently schedule vCPUs on the
  physical server.

To me it looks like overkill to set processor affinity so a core would be free for Windows.
What is your advice about this?

The Microsoft documentation states not to change processor affinity unless it is necessary.

affinity mask Server Configuration Option

Because setting SQL Server processor affinity is a specialized operation, it is recommended that it be used only when necessary.

It also states that the feature will be removed in a future version.

This, combined with the VMware documentation stating that it should not be changed, should be quite convincing.  Additionally, recommendations from the SQL Server community online to change it by default seem to be lacking.

Lastly, Window kernel processes run at a higher priority than other processes on the system (unless they are unwisely set to real-time priority), so all critical processes in Windows will get the CPU they need regardless of how much CPU SQL Server wants at any given time.  Ref. Thread Priorities


Are cut and paste of records from a table to another a correct pratice? I am working on an app that does task dispatching.  A user inputs a task with a start date and end date.  The app gives the user a list of workers available and chooses to offer the task to some of them.  When a worker logs in the app and accepts the task offered, he is booked to do the task.

I plan on using the following strategy :
There would be a taskPossibilities table and a bookedTasks table
a SELECT in the bookedTasks would retain the workers who are not already booked for the chosen period.  All the workers chosen by the user would be put in the taskPossibilies table.  When a worker accepts a task, the record of this worker is copied to bookedTasks, and then all corresponding records in the taskPossibilities are deleted.

I am not asking how to do it : I have done it already, but tested on a local machine only.  I am wondering if this way of doing things represents a risk on data integrity, and wheter there is a better practice for such a situation.

As a general rule I would recommend updating/creating relationships instead of copying data around to different tables.  This is more in line with the relational model, and prevents tables-as-business logic which is what you seem to be doing.

Instead of copying data from one table to another I would think about updating a status flag.  This could be a simple boolean value or a FK relationship to a status table when there are more than two possible states.

Depending on your business logic a better solution would probably be to associate a user with their selected tasks.  This could be via a join table or with a nullable FK on the BookedTasks table.

To give a better or more complete answer I would need you to update your question to include your proposed table structures and some sample data/workflows.


Is it possible to PIVOT on a LIKE statement Is it possible to group by elements (as in COLUMN LIKE='Value%') in a PIVOT table? I have a table [DBT].[Status] which contains various statuses (of databases, instances, etc.) and don't want to pivot/query all the PROD and TEST values as single values, but group them.

E.g. Instead of having columns for the statuses Prod, Prod ACC, Prod APP, .. etc. I would have only one column containing the values for Name LIKE 'Prod%' and Name LIKE 'Test%'.

What I have so far:

Table Definition

CREATE TABLE [DBT].[Status](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Status] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY],
 CONSTRAINT [IX_Status] UNIQUE NONCLUSTERED
(
    [Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

Table Values

INSERT INTO [DBT].[Status]
(
    -- ID -- this column value is auto-generated
    Name
)
VALUES
('Test ACC'),
('Test APP'),
('Test DBA'),
('Prod ACC'),
('Prod APP'),
('Prod DBA'),
('Prod'),
('Test'),
('Migrated'),
('Offline'),
('Reserved')

The Pivoted Status Table

SELECT 'Database Status' AS [DB Status],
[1] AS [Test ACC], [2] AS [Test APP], [3] AS [Test DBA], [4] AS [Prod ACC], [5] AS [Prod APP], [6] AS [Prod DBA], [7] AS [Prod], [8] AS [Test], [9] AS [Migrated], [10] AS [Offline], [11] AS [Reserved]
FROM
(
    SELECT ID, Name  FROM [DBT].[Status]
) AS Source
PIVOT
(
    COUNT(Name) FOR ID IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11])
) AS PivotTable

Output So Far

DB Status       Test ACC    Test APP    Test DBA    Prod ACC    Prod APP    Prod DBA    Prod        Test        Migrated    Offline     Reserved
--------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
Database Status 1           1           1           1           1           1           1           1           1           1           1

db<>fiddle

The dbfiddle so far.

Question

Instead of having multiple rows for the various Test...  and Prod.... values, I would prefer to have them grouped, similar to the following:

DB Status       | Test | Prod | Migrated | Offline | Reserved
--------------- | ---- | ---- | -------- | ------- | --------
Database Status |    4 |    4 |        1 |       1 |        1

I don't have any clue how to go about solving my question. (To be honest I only just grasped PIVOT yesterday after extensive trial and errors).

This question is loosely related to the question How to create sums/counts of grouped items over multiple tables I have already asked. The tables [DBT].[Instance] and [DBT].[Database] contain a column with the [StatusID] which corresponds to the table we are looking at now.

SUM(CASE

For a limited number of Names you can use a SUM(CASE solution in this way:

SELECT
    'Database status' as [DB Status],
    SUM(CASE WHEN Name LIKE 'Test%' THEN 1 ELSE 0 END) As Test,
    SUM(CASE WHEN Name LIKE 'Prod%' THEN 1 ELSE 0 END) AS Prod,
    SUM(CASE WHEN Name = 'Migrated' THEN 1 ELSE 0 END) AS Migrated,
    SUM(CASE WHEN Name = 'Offline' THEN 1 ELSE 0 END) AS Offline,
    SUM(CASE WHEN Name = 'Reserved' THEN 1 ELSE 0 END) AS Reserved
FROM
    [Status];

PIVOT

If there is an extensive list of Names but only few of them must be rewritten you can maintain the PIVOT solution:

SELECT 'Database Status' AS [DB Status],
[Test], [Prod], [Migrated], [Offline], [Reserved]
FROM
(
    SELECT
        ID,
        CASE
            WHEN Name LIKE 'Test%' THEN 'Test'
            WHEN Name LIKE 'Prod%' THEN 'Prod'
            ELSE Name
        END AS Name
    FROM
        [Status]
) AS Source
PIVOT
(
    COUNT(ID) FOR Name IN ([Test], [Prod], [Migrated], [Offline], [Reserved])
) AS PivotTable;

db<>fiddle here

DYNAMIC QUERY

If you feel a bit lazy and don't want to write all column names, you can use a dynamic query:

DECLARE @cols nvarchar(max);

SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(CASE WHEN Name LIKE 'Test%' THEN 'Test'
                                                    WHEN Name LIKE 'Prod%' THEN 'Prod'
                                                    ELSE Name END)
                   FROM [Status]
                   FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

DECLARE @cmd nvarchar(max);

SET @cmd =
'SELECT ''Database Status'' AS [DB Status],' + @cols + ' FROM
    (SELECT
        ID,
        CASE
            WHEN Name LIKE ''Test%'' THEN ''Test''
            WHEN Name LIKE ''Prod%'' THEN ''Prod''
            ELSE Name
        END AS Name
    FROM
        [Status]
) AS Source
PIVOT
(
    COUNT(ID) FOR Name IN (' + @cols + ')
) PVT'

EXEC(@cmd);

db<>fiddle here


Shared availability rules schema design I'm designing a schema which includes the concept of availability rules. The rules are periods of a day when a linked entity is available. This can be the opening hours of a business, the working hours of a staff member, or the availability hours of a specific service. This schema needs to allow for multiple periods on the same day. The rules aren't shared across entities, a business has one group of rules, and each of the other entities are the same.
My first run through this, gave me this schema, but the fact that availabilityGroup doesn't actually contain any data makes me feel like this could be done differently.
CREATE TABLE `business` (
  `id` integer PRIMARY KEY,
  `name` varchar(255),
  `availabilityId` integer,
  CONSTRAINT `FK_business_to_availability_group` FOREIGN KEY (`availabilityId`) REFERENCES `availabilityGroup` (`id`)
);

CREATE TABLE `staff` (
  `id` integer PRIMARY KEY,
  `availabilityId` integer,
  `name` varchar(255),
  CONSTRAINT `FK_staff_to_availability_group` FOREIGN KEY (`availabilityId`) REFERENCES `availabilityGroup` (`id`)
);

CREATE TABLE `service` (
  `id` integer PRIMARY KEY,
  `availabilityId` integer,
  `name` varchar(255),
  CONSTRAINT `FK_service_to_availability_group` FOREIGN KEY (`availabilityId`) REFERENCES `availabilityGroup` (`id`)
);

CREATE TABLE `availabilityGroup` (
  `id` integer PRIMARY KEY
);

CREATE TABLE `availabilityRule` (
  `id` integer PRIMARY KEY,
  `availabilityGroupId` integer,
  `day` string,
  `startTime` time,
  `endTime` time,
    CONSTRAINT `FK_availability_rule_to_availability_group` FOREIGN KEY (`availabilityGroupId`) REFERENCES `availabilityGroup` (`id`)
);

Another possible solution could be to make use of single table inheritance with something like this, which is supported by the ORM we're using too https://www.doctrine-project.org/projects/doctrine-orm/en/2.7/reference/inheritance-mapping.html#single-table-inheritance
CREATE TABLE `singleAvailabilityRulesTable` (
  `id` integer PRIMARY KEY,
  `discriminator` enum('business','staff','service'),
  `entityId` integer,
  `day` string,
  `startTime` time,
  `endTime` time
);

Any thoughts?

It's not worth trying to cram everything into one generic table.  Better to just build off each entity like so:
CREATE TABLE business
(
  business_id  INT           NOT NULL
 ,name         VARCHAR(255)  NOT NULL
 ,CONSTRAINT PK_business PRIMARY KEY (business_id)
 ,CONSTRAINT AK_business UNIQUE (name)
)
;

CREATE TABLE business_hours_of_operation
(
  business_id  INT              NOT NULL
 ,day_of_week  TINYINT          NOT NULL
 ,start_hour   DECIMAL(4,2)     NOT NULL /* Expressed as fractional - i.e. 12:30 = 12.5 */
 ,hours_open   DECIMAL(5,2)     NOT NULL
 ,CONSTRAINT FK_Hours_Of_Operation_For_Business FOREIGN KEY (business_id) REFERENCES business (business_id)
 ,CONSTRAINT PK_Business_Hours_Of_Operation PRIMARY KEY (business_id, day_of_week, start_hour)
 ,CONSTRAINT CK_Business_Hours_Of_Operation_Day_Of_Week_Is_Valid CHECK(day_of_week >= 1 AND day_of_week <= 7)
 ,CONSTRAINT CK_Business_Hours_Of_Operation_Start_Hour_Is_Valid CHECK(start_hour >= 0.00 AND start_hour < 24.00)
 ,CONSTRAINT CK_Business_Hours_Of_Operation_Hours_Open_Is_Valid CHECK(hours_open > 0.00 AND hours_open <= 168.00)
)
;

To display everything in intervals:
SELECT
  business_id
 ,day_of_week AS start_day_of_week
 ,hour AS start_hour
 ,1 + MOD(day_of_week - 1 + FLOOR((hour + hours_open)/24),7) AS end_day_of_week
 ,MOD(hour + hours_open,24) AS end_hour
FROM
  business_hours_of_operation
;

You'll need to lock things down with stored procedures and/or triggers to ensure that the intervals don't overlap and any other business rules.
See Fiddle here.


MySQL upgrade version output seems odd I just upgraded my old MySQL instance to 5.7.31.
When I do a
select version();

I get
5.7.31-log

I am used to seeing something like (on my older version)
5.5.2

What does the -log stand for in 5.7.31-log and it is an issue of concern or just a change in how the version is outputted?

Short answer:  Not to worry.
Long answer:
I did a survey of over 200 MySQL and MariaDB version names -- 40% ended with -log.  They went back to version 4.1, perhaps farther.
(Interestingly, none of the 8.0 versions ended in -log.)
(I've been using MySQL for 20 years, and have never heard why some have "-log".  And I have not noticed a problem.)
For more trivia:
SHOW VARIABLES LIKE 'version%';


How to grant access permision for multiple database and multiple tables to a user in MySQL? I have two databases WORLD,TEST each one contain 3 Tables.I also created a new User test_grant now i want to give SELECT,DELETE,UPDATE permission to test_grant user.But here i want to give two tables permission of TEST database rather than all tables permission in single SQL statement.

GRANT ... ON dbname.tablename TO user@... IDENTIFIED BY ...;

You can grant ON *.*, ON dbname.*, ON db.tbl, or even on column(s).

You will need multiple GRANTs for multiple tables.


Query between tables to delete similar records from another table I am writing an SQL query on two tables products01 and products02 in a PostgreSQL database.

For the entire set of records in products02 take the substring (first 10 characters of volumeId and use those substrings to find all the corresponding records in products01. There can be more than one hit in products01, for instance finding older legacy record of the same item, just a different edition. The eleventh character of volumeId makes it unique.

For instance, in products01 I can have volumeId's like:

record45671
record45672
record45673

And in products02 I would be using substring(volumeId for 10) or 'record456' to find those similar records in products01.

Also I'd like to display the results so I can verify my query is accurate.

Here is my initial attempt:

    SELECT substring(volumeId, for 10) FROM temporary.products01 t01
    WHERE EXISTS (
        SELECT volumeId FROM temporary.products02 t02
        WHERE t01.volumeId LIKE t02.volumeId);

To find all rows from table products01 where the 10 leading characters of volumeId match any products02.volumeId:

SELECT t1.*
FROM   products01 t1
JOIN   products02 t2 ON left(t1.volumeId, 10) = t2.volumeId;

To delete:

DELETE FROM products01 t1
USING  products02 t2
WHERE  left(t1.volumeId, 10) = t2.volumeId;

You can use this equivalent syntax to get both commands more in line:

SELECT DISTINCT t1.* FROM products01 t1, -- see below
-- DELETE FROM products01 t1 USING
products02 t2
WHERE  left(t1.volumeId, 10) = t2.volumeId;

Only add DISTINCT if t2.volumeId is not fully deterministic (t1.volumeId can match multiple t2.volumeId).

Now you can either select the whole statement to select or starting with DELETE to delete. See:

What does [FROM x, y] mean in Postgres?
The manual on DELETE


What are commonly used database types? There are a lot of database types. Can you list a list of the top classification of databases commonly found in schools and offices? Any help is appreciated. Hope to see compressed and summarized type as possible.

Take a look at this list of mine :

Employee/ Student Information
Account Receivable/Payable
Attendance Record
Inventory
Class/Task Records

Hope this list helps. For further reference, you can try to look at this : http://www.techyv.com/article/most-used-database


Do I need to explicitly grant permissions for a login under the sysadmin role for it to access a custom schema? So I have a login that has sysadmin rights on the db server.  However, it's not able to access database objects under a custom schema that was created (eg. it cannot select from tables with [custom_schema].[table_name]).  Do I need to explicitly grant permissions for this login to the custom schema even though it is part of the sysadmin server role?  If that is the case, do I simply run the following if I want it to have db_owner rights to my custom schema?

GRANT TAKE CONTROL ON SCHEMA :: [custom_schema] TO [login];

Thank you in advance!

No, members of the Sysadmin role or the database owner role bypass the permission checking algorithm. So no matter what permission are granted or denied , they are able to always access the objects.