Are null values allowed in any column of a table in SQL? I have doubts over null values.Therefore can you please explain me briefly what is a null value in sql and can we use null values at any place in sql tables?

Allowing Null Values

  Columns defined with a PRIMARY KEY constraint or IDENTITY property
  cannot allow null values.

  The nullability of a column determines whether the rows in the table
  can contain a null value for that column. A null value, or NULL, is
  different from zero (0), blank, or a zero-length character string such
  as "". NULL means that no entry has been made. The presence of NULL
  typically implies that the value is either unknown or undefined.

Microsoft SQL Server's definition of NULL:

  A value of NULL indicates that the value is unknown. A value of NULL
  is different from an empty or zero value. No two null values are
  equal. Comparisons between two null values, or between a NULL and any
  other value, return unknown because the value of each NULL is unknown.

  Null values generally indicate data that is unknown, not applicable,
  or that the data will be added later. For example, a customer's middle
  initial may not be known at the time the customer places an order.

SET ANSI_NULLS setting can change the behavior of NULL.  In future versions this setting will be ON by default and you will not be able to turn it off.
Details here:
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql

This is an article which go in depth about NULL.
https://www.simple-talk.com/sql/t-sql-programming/how-to-get-nulls-horribly-wrong-in-sql-server/

Few example of NULL in TSQL here.


In MongoDB, is having lots of small collections better than having one huge collection? Huge collections have huge indexes that may not fit into RAM. Is that problem entirely solved with sharding ?

If I have lots of small collections with small indexes, what are the options for storing them ? Since only very few of them would be bigger than 200mb they wouldn't be eligible for sharding. What MongoDB database architecture can hold a huge number of fairly small collections ? Since all the queries would be to a specific collection, would this be as fast as a targeted query in a sharding environment ? Faster ?

The smaller the collection the better. Yes if the index does not fit into ram it will be come slower. the way most index searching works is based on binary searching. Its a great method. I build a little test last week in PHP and you can have a list of 1000 numbers and you will be able to find each number in about 12.3 comperasons. So think of it that way. If you a table of 1000 items only having to check 12 times to find the correct result is pretty good in my book. The more documents you add in mongo the more operations that have to take place.But its imporant not to make them to small that your server has to keep switching for simple things.

Also make sure to remember that each document has a max size of about 18mb I think. That is a lot of data. Look up how many words are in the book "war and peace" and that can fit in about 3mb of data. But dont let it control you over stuffing things or under stuffing them.


Timestamp on SSIS update on tsql I have a couple of SSIS packages that copy the content of several tables to another db. One of the columns updated in the process is a timestamp datatype. From what I read this datatype can't be updated, it gets updated automatically with each UPDATE or INSERT. But digging on the net I've found this and this about issues on SSIS when updating this type of column and using DT_BYTES and DT_WSTR types. If I got it right, using SSIS packages those columns can be modified.

My issue is that now I want to replace those SSIS packages with stored procedures that will be called by the SQL Server Agent. I'm reusing the already existing code on the packages but of course I'm getting the errors "Cannot insert an explicit value into a timestamp column." or "Cannot update a timestamp column.".

Is there any way I can replicate the timestamp values from one database to the other? Without changing the table structure or column type as the whole process is already configured and running on several databases.

I know of two ways to copy timestamp data:

A SELECT..INTO will insert explicit values into a timestamp column, but it also requires that the destination table does not exist. That will not help in your situation.
Convert it to a VARBINARY or VARCHAR value. This is the equivalent of the SSIS conversions to DT_BYTES/DT_WSTR you mentioned.  In both cases, you can copy the values, you can manipulate the values, but you cannot reinsert them into a TIMESTAMP column.

In short, no, I don't know of a way to copy the exact same timestamp values between databases. If you need the exact value, you'd need to change the column type on the destination.

In most cases I've seen, though, the value is much less important than the sequence provided by the timestamp column.  If you insert the values into the destination table in the order of the timestamp values in the source table, you should end up with the same sequence, even if the values are different.


Suddenly SQL server restarted with pending task count increased We are trying to understand the issue where SQL server was suddenly restarted on its own-
Its a SQL server 2012 SP4 -GDR applied with 40 CPu's HT enabled , thus total of 80 logical processors-

MAXDOP=8 , CTOP 5

From error logs we found

/**********************/
BEGIN stack Dump
Non- Yielding Scheduler /**********************/

Error messages just at the time of restart of SQL. Also there was no mini dump created as checked.
Yes we have quite some queries running at that time. Top 3 waits seen were:-
1.TranLogIO
2.CXPACKET
3.PAGELATCH SH

However we also noticed wait called SOS WORKER as collected from system health XE which i believe is nothing else but THREADPOOL. Therefore i went further and analyse the query processing details from that system health and found at the time of non yielding scheduler errors below was logged

At 19:46 maxworkers- 2944 workers created 789 oldest pending taskwait time 0 pending task 4
At 19:51 maxworkers- 2944 workers created 982 oldest pending taskwait time 256987 pending task 165
At 19:51 we started seeing restart happened.

Question is why there would be a threadpool wait if almost 2000 workers are still available from above calculation. Also why those pending task count 165 when queries have so many schedulers available to run and complete the request for bunch of queries waiting on CXPACKETS?
Edit: Updating my question for couple of waits also seen from XE
SOS_MEMORY_TOPLEVELBLOCKALLOCATOR

As i am reading info on above wait here https://www.sqlskills.com/help/waits/sos_memory_toplevelblockallocator/
Based on the blog there is a fix in SP3 CU but with TF T8075 : I am currently on SP4-GDR latest patch, do i still need to apply the TF, though i do not see any messages like Failed allocate pages: FAIL_PAGE_ALLOCATION 513 in error log
Not sure if it helps- i see total server memory dropping a gig now and then and then matching upto target server memory which is = max server memory (750GB)
Most of the times total=target=max server memory. The drops are just by a gig and not much
Memory info= total ram: 880 GB Max memory : 750GB  Min server memory: 130 GB
Its a 2 node windows cluster and no other SQL instance is shared. Resource governor is not enabled
Thanks

I would start by saying that best person to analyze dump in MS guy or person who knows about it. I would just try to point out some basics from the log you posted

At 19:46 maxworkers- 2944 workers created 789 oldest pending taskwait time 0 pending task 4
At 19:51 maxworkers- 2944 workers created 982 oldest pending taskwait time 256987 pending task 165

Please see that taskwait time 256987 and pending task 165. This means the scheduler was hung and 165 tasks were waiting on it to get a scheduler and run. In this case MS SQL Server was not able to get out of this hung scheduler scenario and waited for a while but again it decided that it would be best to restart itself to come out of this hung scheduler scenario and hence it restarted. Now for reason why it hang is beyond my ability to tell with the information you have posted.
Also note a thread is assigned a scheduler and it should run on that scheduler and that is why so many threads were waiting on this hung scheduler.


How to add an article to existing replication This is the first time I am going to add tables to replication.

I have outlined the below steps to add a new table to existing replication. Please correct me if I am wrong. The table is just created - no data.

exec sp_addarticle @publication = N'pub1', @article = N'tab1', @source_owner = N'dbo', @source_object = N'tab1'

 EXEC sp_addsubscription
  @publication = 'pub1',
  @subscriber = 'sub1',
  @destination_db = 'MLT-Rep'
GO
 EXEC sp_addsubscription
  @publication = 'pub1',
  @subscriber = 'sub2',
  @destination_db = 'MLT-Rep'

After this, I am supposed to run Snapshot right? How do I go about doing that? Just navigate to the "Local Publication" -> "View Snapshot Agent Status" -> Start

Are the above steps right?

Adding an a new article involves adding the article to the publication, creating a new snapshot, and synchronizing subscriptions to apply the schema and data for the newly added articles.  Reinitialization is not required, but a new snapshot is.

Your steps are correct if the publication only has existing push subscriptions.  If the publication has existing pull subscriptions, you will also need to execute sp_refreshsubscriptions.  And you are correct, to generate a new snapshot you can use the View Snapshot Agent dialog, or you can execute sp_startpublication_snapshot.

To avoid generating a full snapshot when adding a new article, publication properties @immediate_sync and @allow_anonymous must be set to 0. Execute sp_addarticle, then execute sp_addsubscription. If the publication has existing pull subscriptions, you must also call sp_refreshsubscriptions. Then generate a snapshot and only a snapshot for the newly added article will be generated.  Note that this approach is not necessary but is typically used when Administrators want to avoid generating a full snapshot when adding a new article.

Please see Add Articles to and Drop Articles from Existing Publications for more information.


Percona Xtrabackup on Suse EL 10 -- Failing I have evaluated Percona Xtrabackup successfully and it works like a charm on my several CentOS / RHEL servers! Hats off to the team!

But my problem starts when I tried to install this on one of my client's Suse EL 10 server. I believe Xtrabackup is not natively supported on Suse. But being an RPM based platform, I tried installing RHEL5 rpms from percona site. But they don't work. They install (a fairly older version 1.5.x) but when run they throw the error like

Command I am running : /usr/bin/innobackupex --ibbackup=xtrabackup --defaults-file=/etc/my.cnf --user=some_user --password=password /root/backups

Error shown is : innobackupex: fatal error: no 'mysqld' group in MySQL options

I have made sure that my.cnf exist, it contains all necessary parametes like datadir / log files related parameters.

See if you can help me with this and let me know if you need any more help / logs that I can provide.

I guess you are under a wrong impression that it is a OS related issue.
Check your server bit if it is 32 bit and if you try to install 64bit package percona's xtrabackup . It would end up throwing such error messages.


Schema design for metered billing depending on account usage Sorry if this has been asked somewhere else, but so far I haven't found any questions like this, or can't find the words to search for.

TL;DR: What's the best way to implement counters in the database to track usage for billing purposes? Specifically when those counters will reach into the 10's or 100's of thousands?

I have a LAMP application that allows users to send out emails (Laravel, MySql).  I want to start charging them for "email usage" and imposing monthly sending limits, which if they exceed, I will charge them extra, rounding to the next thousand.

So from a database standpoint, how can I make this happen in a way that is stable and maintains integrity, while not burning up all my CPU with updating counters?  I also would like to record usage month by month for good record keeping.  Here is what I'm considering:

accounts:
    account_id
    company_name
    email_limit (counter integer, example might be 20,000)
    email_usage (counter integer, example might be 13,425)
    ...

users:
    user_id
    account_id
    email
    ...

email_history
    account_id
    period (date field or something to know which month it was)
    email_usage (the historical usage they used that month)
    email_limit (the historical limit they had that month)

In this scenario, my system would process, let's say 5000 emails at once, and increment the email_usage counter 5000 times.  On the other hand, I could just use an internal counter ($i) and when the process is complete, just add $i to the usage counter.  But what if there's an error during processing?  I could lose my counter half way through.

Another way I could think to do this would be to save batch operations, so when we process 5000 emails at once, we just record that:

accounts:
    account_id
    company_name
    ...

users:
    user_id
    account_id
    email
    ...

email_batches
    account_id
    date (date batch was processed)
    emails_sent (integer, total emails in batch)

That way, when it comes time to process billing each month, we just SUM(email_batches.emails_sent) WHERE MONTH(date) = [this_month] and process that.

We're going to be billing people based on this mythical usage tracking, so any advice from someone who's done it before would be appreciated.

UPDATE: My project's been put on hold for a short bit, but I will definitely mark an answer when I start working on it again (this is a high priority, so it won't be long).

I regularly handle tables with 250GB of data that needs "counting" (Example: https://phabricator.wikimedia.org/T124676).

Let me start by saying that MySQL is not a good place for these kind of queries- its focus is OLTP (in a nutshell, short reads and writes in parallel), not OLAP. There are multiple storage/computing models (such as column-based data stores, or datawarehouse solutions that allows map/reduce jobs in parallel) that would be better suiting for your task, so if this is a business-critical/very frequent part of your needs, I would suggest setting up a complementary service that allows easier/faster handling of these kind of tasks. The reason is that counting things on MySQL usually require "snapshoting" and reading all rows one by one, and that can only be done on a single thread.

Having said that, your needs may not merit a full-blown solution, and only make that specific need faster. You can implement analytics by implementing such functionality on regular MySQL objects. The implementation will depend on your requirements, and that is mainly: "freshness" of your data, its dynamic nature, concurrency and if it is append-only.

If your counter does not need to be 100% accurate, you can run counting jobs every X time. Make sure no more than 1 job is running at each time by locking on a particular resource. We do such things with special pages such as: https://en.wikipedia.org/wiki/Special:WantedPages
If data is not always modified, but it needs to be more or less up to date with the rows, you can start a background thread that gets notified when data changes. If it gets notified several times before it runs, it de-duplicates the job by updating it only once. We do such a thing for Category updates: https://en.wikipedia.org/wiki/Category:Contents
Both of the "counters" from the previous tables are stored on auxiliary tables.
If you need to have perfect counting, you need to cache it with a trigger-like execution. It doesn't need to be a on server side, but be part of the transaction that updates the row. This will be more costly, as it will increase the latency of each individual DML, but it may be interesting in some cases. In our model, user have (or at least, used to have) an edit counter that it is transactionally updated every time an edit is created or deleted.
If your data is append-only, you can avoid counting all data every time, and only count the most recent records. Store the last counted record and then the total/per month/yearly/etc. count on another column, per item. We use such a model for counting things like number of visits per article.

In general, with MySQL you are forced to count- whether you cache or precache that value is a question of handling caches, which is a very common problem (although as you may know, not a simple one).

One common mistake is to store a single counter field when there is a lot of concurrency- e.g. visits for a webpage. For such cases you can distribute your counters according to your structure (e.g. one per fronted) or just use some kind of hashing table so you do not have to serialize every single update. On actual counting, you can sum the much fewer counters, which will be easier than counting the individual values.

If you need to speed up counting, there are even frameworks such as Shard-query that could simulate the panellization of queries for MySQL to some extent.

The summary is that you have 3 options: make things consistent, make them fast or make them cheap, choose 2 :-)


Database space doesn't match ibdata1 size I'm using InnoDB database with a single file configuration (in /var), so no innodb_file_per_table.

In the MySql workbench, when I query for the databases used space, with this query

SELECT table_schema "Database", sum( data_length + index_length ) / 1024 / 1024 "Data     Base Size in MB"
FROM information_schema.TABLES GROUP BY table_schema;

I get 47 GB.
However, when I check the space on the /var directory with df -h, it returns 129 GB of used space. Last week, this was at 127 GB, so I know that the whole 129 GB is used because new space is allocated by MySql.

I was under the impression that when deleting rows from a table, the space was freed, but the ibdata1 does not skrink and re-use the space latter.

So my question is, why does ibdata1 keep growing to 129 GB and beyond when, supposedly, my databases only uses 47 GB of space?

This sounds a lot like another post I wrote back on August 27, 2012 : How To Optimize and Repair InnoDB tables? ALTER and OPTIMIZE table failed

With innodb_file_per_table disabled, the system tablespace ibdata1 is the home of seven classes of information:

Data Pages for InnoDB Tables
Index Pages for InnoDB Tables
Data Dictionary
Double Write Buffer

Safety Net to Prevent Data Corruption
Helps Bypass OS for Caching

Insert Buffer (Streamlines Changes to Secondary Indexes)
Rollback Segments
Undo Logs
Click Here to see a Pictorial Representation of ibdata1

With innodb_file_per_table disabled, running OPTIMIZE TABLE against any InnoDB table is really asking for trouble. Why? Because all OPTIMIZE TABLE does is write all data pages and index pages for a given table contiguously in ibdata1. That makes ibdata1 grow. In light of this, shrinkage of ibdata1 is totally impossible.

Please note that even if innodb_file_per_table were enabled and you ran OPTIMIZE TABLE, that will extract the table into an external file. Yet, the space left behind is unrecoverable.

I wrote a nice one-time Cleanup Procedure of ibdata1 in StackOverflow back on Oct 29, 2010.

Here are my other posts on this subject of InnoDB and its effects on ibdata1

Apr 01, 2012 : Is innodb_file_per_table advisable?
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Feb 03, 2012 : Scheduled optimization of tables in MySQL InnoDB
Nov 26, 2011 : ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full

SUMMARY

Even if you implement the InnoDB Cleanup (which separates all data and index pages from ibdata1), ibdata1 can still grow in a heavy-write, heavy transaction environment due to the 5 other classes of information (Data Dictionary, Double Write Buffer, Insert Buffer, Rollback Segments, Undo Logs).

If you want to know how much space is used by data and index pages in ibdata1, run this:

SELECT InnoDB_Bytes,InnoDB_Bytes/POWER(1024,3) InnoDB_GB
FROM (SELECT SUM(data_length+index_length) InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

Now as for ibdata1, it still the Data Dictionary, Double Write Buffer, Insert Buffer, Rollback Segments, Undo Logs, and unused pages due to fragmentation. There is no really way to know:

the exact amount of fragmentation
how much space is used by the following (since they change rapidly):

Data Dictionary
Double Write Buffer
Insert Buffer
Rollback Segments
Undo Logs

For the sake of simplicity, I will say this: Just subtract InnoDB_Bytes from the filesize of ibdata1.

After doing the InnoDB Cleanup, you should schedule running OPTIMIZE TABLE on every InnoDB table that is transaction-heavy. That will actually shrink the .ibd file for every InnoDB tables. The ibdata1 file will grow much slower thereafter. Yet, you will still have to live with some transactional growth.

If you implement InnoDB Cleanup but leave innodb_file_per_table disabled, that will shrink shrink ibdata1, but it will just climb back to 99G and beyond through normal application usage.

If you do not implement InnoDB Cleanup, no OPTIMIZE TABLE will ever shrink ibdata1.

RECOMMENDATION

Please implement InnoDB Cleanup with innodb_file_per_table enabled. Going forward, you should create a cronjob to run OPTIMIZE TABLE on all tables what experience mass INSERTs, mass UPDATEs, and mass DELETEs.


How long will a truncate table take on 252M Rows (170GB data)? I understand that there are many variables in this and am not looking for an exact number of min:sec :) I've read many resources that state that a TRUNCATE uses far less resources than a DELETE and hence will perform faster. But there are no order of magnitudes mentioned. As a TRUNCATE is a DDL (Data Definition Language) operation and not a DML (Data Manipulation Language) operation and only the system tables are part of the transaction, I'm presuming that it should not take very long.

Anyone with experience on doing this with a table anywhere near this size that can lend some insight? We talking up to a minute, up to an hour, days, weeks?

A millisecond or so.

Truncate is O(1) - a pure metadata operation.

This is assuming there is no concurrent activity on the table.

Truncate will potentially need to wait until existing reads are done to acquire a Sch-M (schema modification) lock.

For large tables, the actual deallocation is done asynchronously on a background thread (SQL Server 2000 SP3 onward).

For some other myths surrounding truncate, see A SQL Server DBA myth a day: (19/30) TRUNCATE TABLE is non-logged by Paul S. Randal.


Will VSS backups break logchain? Our "hardware" dept has started some ASR replication on one of the servers where we run SQL Server. It seems that the method they use for this uses VSS, and I can see a lot of messages in the SQL Log about databases being backed up, and also the I/O frozen/resumed. It seems to do this about every hour.

Will these backups taken by VSS break the log chain, or are they copy backups? Is there any way to see if they are copy backups? Is it possible to instruct the VSS to use copy backups?

Any feedback appreciated.

You could verify the backup history with the following script:

SELECT
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SRVNAME,
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_start_date,
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.expiration_date,

   CASE msdb..backupset.type
       WHEN 'D' THEN 'Full'
       WHEN 'I' THEN 'Diff'
       WHEN 'L' THEN 'Log'
   END AS backup_type,
   msdb.dbo.backupset.backup_size / 1024 / 1024 as [backup_size MB],
   msdb.dbo.backupmediafamily.logical_device_name,
   msdb.dbo.backupmediafamily.physical_device_name,
   msdb.dbo.backupset.name AS backupset_name,
   msdb.dbo.backupset.description,
   msdb.dbo.backupset.is_copy_only

FROM   msdb.dbo.backupmediafamily
   INNER JOIN msdb.dbo.backupset
   ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

WHERE 1 = 1
ORDER BY   2,3 desc

The script will give you an overview of the backup media and files and if you look at the last column is_copy_only you will see if the backup is COPY_ONLY (1) or not (0).

If they are COPY_ONLY then they will not break the backup chain. Otherwise you might face issues as explained in the following article:

COPY_ONLY Backups with SQL Server

  Starting with SQL Server 2005, a new backup option has been added that allows you to take full and transaction log backups in between your regularly scheduled backups without affecting the LSNs and therefore the sequence of files that would need to be restored.  Since Differential backups backup all data pages since the last full backup, these types of backups do not affect the LSNs and there is no difference when using the COPY_ONLY feature.

You might want to check out my answer here which was posted in response to the question How can I backup an SQL Server database using Windows Server Backup?.

It is related to Windows Server Backup and VSS, but touches on some points you might encounter with the VSS backup strategy that has been implemented at your shop.


How do you mysqldump specific table(s)? How can I dump a specific table or set of tables without including the rest of the db tables?

If you are dumping tables t1, t2, and t3 from mydb
mysqldump -u... -p... mydb t1 t2 t3 > mydb_tables.sql

If you have a ton of tables in mydb  and you want to dump everything except t1, t2, and t3, do this:
DBTODUMP=mydb
SQL="SET group_concat_max_len = 10240;"
SQL="${SQL} SELECT GROUP_CONCAT(table_name separator ' ')"
SQL="${SQL} FROM information_schema.tables WHERE table_schema='${DBTODUMP}'"
SQL="${SQL} AND table_name NOT IN ('t1','t2','t3')"
TBLIST=`mysql -u... -p... -AN -e"${SQL}"`
mysqldump -u... -p... ${DBTODUMP} ${TBLIST} > mydb_tables.sql

Give it a Try !!!
UPDATE 2014-03-06 10:15 EST
@RoryDonohue pointed out to me that the GROUP_CONCAT function needs to have its max length extended. I added the session variable group_concat_max_len to my answer with a length max of 10K. Thanks, @RoryDonohue.


Powershell Output Help I have wriiten the following SQL script to execute using powershell.

    cls
foreach ($svr in get-content "demo.txt")
{
  $con = "server=MC1-PQ10X.RF.LILLY.COM\SQL01;database=mylilly_WSS_Content_INCTSK0014840;Integrated Security=sspi"

  $cmd = "SELECT
Docs.DirName + '/' + Docs.LeafName AS 'Item Name',
DocVersions.UIVersion,
(DocVersions.UIVersion/512) as Version_Label, DocVersions.Level, DocVersions.TimeCreated
FROM DocVersions FULL OUTER JOIN Docs ON Docs.Id = DocVersions.Id
--   INNER JOIN Webs On Docs.WebId = Webs.Id
--INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE (DirName LIKE '%globalcontentrepository%')
AND (IsCurrentVersion = '0')
AND (DocVersions.Id IN ('$svr'))"

  $da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)

  $dt = new-object System.Data.DataTable

  $da.fill($dt) |out-null

  $dt | Export-Csv music.csv -Encoding ascii -NoTypeInformation

 }

The problem I'm facing with the above code is regarding the output. For every $svr, this code is creating a new csv file. The input file is containing around 1000 inputs.
My requirement is that all the output should get stored in the file rather than creating new file.

Please look into this. Kindly give your suggestions.
Thanks in advance

In order to have it go to one file you have two options, and I think the first will get a little better performance than the second.

First you can simply add a parameter to the Export-Csv to append to the file. As long as the columns are consistent (the same) it should work with out issue.

$dt | Export-Csv music.csv -Encoding ascii -NoTypeInformation -Append

Second, pull the contents of the foreach loop into a variable and then output it all at one time to the Export-Csv. This will will mean it will pull all of the data into memory and then output it all to the file. It depends on how much data you are pulling out but this may add on some time for the execution of the script.

$results =
foreach ($svr in get-content "demo.txt")
{
  $con = "server=MC1-PQ10X.RF.LILLY.COM\SQL01;database=mylilly_WSS_Content_INCTSK0014840;Integrated Security=sspi"

  $cmd = "SELECT
Docs.DirName + '/' + Docs.LeafName AS 'Item Name',
DocVersions.UIVersion,
(DocVersions.UIVersion/512) as Version_Label, DocVersions.Level, DocVersions.TimeCreated
FROM DocVersions FULL OUTER JOIN Docs ON Docs.Id = DocVersions.Id
--   INNER JOIN Webs On Docs.WebId = Webs.Id
--INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE (DirName LIKE '%globalcontentrepository%')
AND (IsCurrentVersion = '0')
AND (DocVersions.Id IN ('$svr'))"

  $da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)

  $dt = new-object System.Data.DataTable

  $da.fill($dt) |out-null

  $dt

 }

$results | Export-Csv music.csv -Encoding ascii -NoTypeInformation


"Undo" some transactions from backups Let's suppose that we are planning to execute a "risky action" on a database. Naturally we've tested on non-production databases and everything seems to be fine. We backup the database. Then we execute the risky action. After some number of additional transactions, we realize a horrible mistake. Now we'd like to "undo" the risky action but maintain the subsequent transactions.

I am aware that crisis tools exist that will help undo transactions based on transaction logs. Since I don't actually have a crisis, I am curious about whether this can be accomplished without those tools in the right situation.

I have in mind that something like the following could be accomplished, but I don't know whether it's actually possible on SQL Server.

Timeline of our invented crisis

Make full backup of database (B)
Execute risky action (T1)
Transaction 2 occurs (T2)
...
Transaction n occurs (Tn)
Realize that we need to undo T1.

"Undo"

Restore B into a new database (B')
Somehow apply T2 through Tn to B'

Is this something that can be done with built-in commands? I've tagged SQL Server 2008 since that is the package I most frequently use. However, if a solution also works on SQL Server 2005 (or only on 2008 R2), I'd like to hear about that too.

Not possible exactly as you intend, but here are some tools:

For your point in time, try a DB Snapshot:

http://msdn.microsoft.com/en-us/library/ms175876.aspx

To revert from DB Snapshot:

http://msdn.microsoft.com/en-us/library/ms189281.aspx

Now that would revert in total, not leaving the intended T2, but would keep you from having to deal with restoring backups.

What you could do is with the DB still live and with a DB Snapshot available, then use a tool like Redgate for DML compare (compare live DB to DB Snapshot):

http://www.red-gate.com/products/sql-development/sql-data-compare/

You'd have to then pick and choose what DML you wanted to apply, and use Redgate to generate the script. Then you could revert with the snapshot, and apply the Redgate script to include only the DML you wanted to retain.

That's as close to meeting your requirement as comes to mind. Otherwise there's no way to say "roll this one back and not that one".


How to do a case-insensitive LIKE in a case-sensitive database? My vendor requires the data warehouse database to be case sensitive, but I need to do case-insensitive queries against it.

In a case-sensitive database, how would you write this to be case-insensitive?

    Where Name like '%hospitalist%'

You can append a new collation to your select query to find case sensitive or insensitive.

-- Case sensitive example
SELECT *
FROM TABLE
WHERE Name collate SQL_Latin1_General_CP1_CS_AS like '%hospitalist%'

-- Case insensitive example
SELECT *
FROM TABLE
WHERE Name collate SQL_Latin1_General_CP1_CI_AS like '%hospitalist%'

Just be aware of the performance problems this could present. You will need to scan the clustered index to adjust / find the values when you perform the collation. The way you are writing the LIKE piece also makes the query non-sargable.

I picked up the collation trick from Kendra Little's SELECT Seminar classes. You can find additional collation information though from Ben Snaidero   from MS SQL Tips.

MSDN on Collate.


SQL database restore failed Possible Duplicate:
  SQL database restore failed

I have a c# application that is connected to my sql server 2005 database, i am working on the application to insert some entries in the database and due to power failure system got down and from that time onwards i am getting this error. I refresh the database, make a copy of it, delete it & try to attach it again and thus the error occurs.

  The log cannot be rebuilt because there were open transactions/users
  when the database
      was shutdown, no checkpoint occurred to the database, or the database was read-only. This  error could occur if the transaction log
  file was manually deleted or lost due to a hardware  or environment
  failure.

If i use the log file while attaching the db, i got this error

  The log scan number (1224:41:1) passed to log scan in database 'test' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

Are you copying the mdf/ldf files with SQL Server running? That will almost never give you a usable copy, unless there are no writes to either file during the entire copy process. Either stop SQL Server or detatch the database before copying the files.


Assigning new auto increment value I have a table that is created as below.

CREATE TABLE [dbo].[Table1](
    [ID] [INT] IDENTITY(1,1) NOT NULL,
    [Title] [VARCHAR](255) NULL,
    ...

Lets say that the latest ID is 100 ( I have 100 records)

Is there a way to ALTER this table's IDENTITY column and change the auto increment value to now be 500?

So at the end i would have something like this
ID 98, 99, 100, 500, 501, ....

I can not seem to find a solution for this in T-SQL

You can use DBCC CHECKIDENT to reseed the IDENTITY column.

Here is a sample you can run:

SET NOCOUNT ON;
USE tempdb;
GO

CREATE TABLE dbo.foo(ID INT IDENTITY(1,1));
GO

INSERT dbo.foo DEFAULT VALUES;
GO 100

-- note: set it to ([the next value you want] - 1)
DBCC CHECKIDENT(N'dbo.foo', RESEED, 499);
GO

INSERT dbo.foo DEFAULT VALUES;
GO 3

SELECT ID FROM dbo.foo WHERE ID >= 98;

Results:

98
99
100
500
501
502


All my database on the fail over server showing in "Synchronizing" state Current Configuration: I have two SQL Servers, which are connected by the failover mechanism, meaning one is primary and one is secondary.
I restarted both services on both servers sequentially.
On the secondary server, all the databases are in the "Synchronizing" state.
AG replication mode is synchronous. It has been 12hr it's still showing "Synchronizing" state.
What I did so far to resolve
Drop the database from the secondary server and remove the database from AG group on the primary server and add it back.
Any insight on this, I am using SQL Server 2017.

Firstly, if you could drop the database from any one of replica, it means the database is not yet part of any of availability groups, before dropping the database it must be removed from availability group first.

Assuming that your current scenario has 2 nodes in 1 cluster, I would recommend first to add witness (preferred File-share from different server other than 2 nodes) in WSFC to ensure the quorum healthy, so that while you restarting 1 server there would be 2 votes available (1 Node, 1 File-share witness), and the WSFC could smoothly fail-over the roles (Availability groups) from one to another node.

For the synchronizing issue, please do the following

Make sure the network connection is stable between 2 nodes especially with endpoint port (Default: 5022) which you can identity from any availability group properties right click on AG Name -> Properties -> look at availability Replicas section (bottom) example: TCP://<IP ADDRESS>:5022. Ensure the failover mode is Automatic

Try Suspend data movement and Resume, you can do this for any one of the database that is already part of availability group
If step 2 completed without any error, verify the hardening of LOG by REDO worker is normal which you can identity with following query, result can describe with the delay between LAST SENT (Primary) - LAST RECEIVED  (Secondary)- LAST HARDENED (Secondary) which is not available in AG Dashboard

select  db.name,
        db.database_id,
        ag.name as GroupName,
        state_desc,
        recovery_model_desc,
        log_reuse_wait_desc,
        AGDB.truncation_lsn,
        Rep.replica_server_name,
        rep.endpoint_url,
        DBRepStats.is_primary_replica,
        DBRepStats.synchronization_health_desc,
        DBRepStats.database_state_desc,
        (redo_queue_size / 1024.0) as redo_queue_size_MB,
        last_redone_time,
        last_redone_lsn,
        DBRepStats.end_of_log_lsn,
        DBRepStats.last_sent_lsn,
        DBRepStats.last_sent_time,
        DBRepStats.last_received_lsn,
        DBRepStats.last_received_time,
        DBRepStats.last_hardened_lsn,
        DBRepStats.last_hardened_time
from sys.databases as db
    left outer join sys.availability_databases_cluster as AGDB on db.group_database_id = AGDB.group_database_id
    left outer join sys.dm_hadr_database_replica_states as DBRepStats on db.group_database_id = DBRepStats.group_database_id
    left outer join sys.availability_replicas as Rep on DBRepStats.group_id = Rep.group_id and DBRepStats.replica_id = Rep.replica_id
    left outer join sys.availability_groups as AG on DBRepStats.group_id = AG.group_id
where db.database_id > 4
go

If no luck with above steps, have a look at following sections

  "AlwaysOn_health" from Management -> Extended Events -> Sessions (via SSMS)
  SQL Error Log
  WSFC Log


Executing Powershell SMO against a cluster with elevated privileges to update service accounts I'm working on a Powershell script to update service accounts on named SQL instances in a failover cluster.  I'm trying to use the SMO and WMI to update this so that I can update the service account on the active node and have it populated to the other passive nodes (as would happen if I updated manually in the configuration tools).  I'm getting hung up on trying to execute the script on my local machine but updating a remote server. The error I see appears to be security related based on my Google searches and is successful if I run the script locally on the remote machine with elevated permissions (as administrator).

The code:

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
$mc=New-Object -TypeName Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer "SQLHOST01"

$srvcs= $mc.Services | Where {$_.name -like "*INSTANCE01*"} | Where {$_.Type -like "Sql*"}

$srvcs | ft name,servicestate,serviceaccount

foreach ($s in $srvcs){
    $s.SetServiceAccount("FOO\Bar","strongpass")
}

Error:

Exception calling "SetServiceAccount" with "2" argument(s): "Set service account failed. "
At line:11 char:5
+     $s.SetServiceAccount("FOO\Bar","strongpass")
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : FailedOperationException

Using:
Windows 2008 R2 (remote)
SQL 2008 R2 (remote)
Windows 7 with SQL 2012 sqlps module (local)

Update:  The account I am executing the script under has administrator rights on the remote machine.

The primary question here is how can I make my call from the client machine act as if it were running under elevated permissions on the remote machine?

I ran into the same problem, looks like its an SMO bug (1st link) (however I have not tried the CredSSP stuff from the second link below)

http://social.technet.microsoft.com/Forums/en-US/winserverpowershell/thread/265ee700-182d-4a99-96f8-cada117fd3d3

http://www.ravichaganti.com/blog/?p=1230

Please let me know if that works..


How can I use with differential backup of SQL Server It's said differential backup could be restored only after it's base. It sounds like I just divide the one step full restore into two steps.

And I can't see the benefit here.

How can i use with differential backup of SQL Server?

BTW, I'm looking for a solution to restore my data from the primary database to the standby one only with the differential data everyday. It's quite appreciated for any suggestion.

Differentials are useful only in the presence of log backups. A restore starts from the last full, and then all log backups are applied. In a typical scenario of backing up the log, say, every 15 minutes, the number of log backups to be applied can quickly escalate. Having differentials allow the sequence to be shortened to last full -> last differential -> all log backups after that.

  I'm looking for a solution to restore my data from the primary database
  to the standby one

That is called Log Shipping and is built into the product. You must change the recovery model to full (or at least bulk_logged) and start taking log backups. There is just no way to avoid this.


Installation of PostgreSQL-11: Depends: libllvm6.0 (>= 1:6.0~svn298832-1~) but it is not installable I've added the official repository to my sources.list,

deb http://apt.postgresql.org/pub/repos/apt/ cosmic-pgdg main 11

When I try to install PostgreSQL-11 on Ubuntu Cosmic (18.10), I get the following error

$ apt-get install postgresql-11
Reading package lists... Done
Building dependency tree
Reading state information... Done
Some packages could not be installed. This may mean that you have
requested an impossible situation or if you are using the unstable
distribution that some required packages have not yet been created
or been moved out of Incoming.
The following information may help to resolve the situation:

The following packages have unmet dependencies:
 postgresql-11 : Depends: libllvm6.0 (>= 1:6.0~svn298832-1~) but it is not installable
E: Unable to correct problems, you have held broken packages.

How do I go about resolving this error?

This error is because the Official PostgreSQL PPA for Cosmic is currently building against libllvm6.0. That's an import from Debian Sid, currently Ubuntu is building against the newer libllvm7.0. To get access to this package you'll have to add the universe to your sources.list.

sudo add-apt-repository "deb http://archive.ubuntu.com/ubuntu $(lsb_release -sc) universe"


SQL Server 2008 - Replacing csv substrings in view-row using data from other table Using SQL server 2008, is there a way to perform SELECT query in a view replaces a row containing comma-separated values with their corresponding text value from another table? STRING_SPLIT and STRING_AGG is not available in 2008 version.
EDIT:
Added create and insert script
CREATE TABLE Data(
Id int,
Value1 varchar(50) NULL,
Value2 int NULL,
Value3 datetime
)
GO

CREATE TABLE CodeValue(
    Id int,
    Code varchar(50) NULL
)
GO

INSERT [dbo].[Data] ([Id], [Value1], [Value2], [Value3]) VALUES (1, N'0;1;2', 43, CAST(N'2020-07-09T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Data] ([Id], [Value1], [Value2], [Value3]) VALUES (2, N'0;2;3', 652, CAST(N'2020-07-03T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Data] ([Id], [Value1], [Value2], [Value3]) VALUES (3, N'2', 1234, CAST(N'2020-07-02T00:00:00.000' AS DateTime))
GO

INSERT [dbo].[CodeValue] ([Id], [Code]) VALUES (0, N'Apple')
GO
INSERT [dbo].[CodeValue] ([Id], [Code]) VALUES (1, N'Orange')
GO
INSERT [dbo].[CodeValue] ([Id], [Code]) VALUES (2, N'Banana')
GO
INSERT [dbo].[CodeValue] ([Id], [Code]) VALUES (3, N'Dogmeat')
GO

Consider my view contains data from two tables; Data and CodeValue, that would look like this:
Data
Id | Value | Value2 | Value 3
==============================
  1| 0;1;2| some other data
  2| 0;2;3|
  3| 2    |

CodeValue
Id | Code
=============
  0| Apple
  1| Orange
  2| Banana
  3| Dogmeat

So the actual output from the SELECT query in my view would be:
View
Id | Value
============
  1| Apple, Orange, Banana
  2| Apple, Banana, Dogmeat
  3| Banana

I've messed around with stored procedures and functions, but can't wrap my head around those and how to actually implement this.
EDIT 2:
Tried using STUFF() using the following template:
WITH CTE_TableName AS (
       SELECT FieldA, FieldB
         FROM TableName)
SELECT t0.FieldA
     , STUFF((
       SELECT ',' + t1.FieldB
         FROM CTE_TableName t1
        WHERE t1.FieldA = t0.FieldA
        ORDER BY t1.FieldB
          FOR XML PATH('')), 1, LEN(','), '') AS FieldBs
  FROM CTE_TableName t0
 GROUP BY t0.FieldA
 ORDER BY FieldA;

However I can't seem to join codeValues on split values using homebrew split_string function:
CREATE FUNCTION dbo.tvf_SplitString (@stringToSplit VARCHAR(100))
RETURNS @returnList TABLE(Id VARCHAR(5))
AS
BEGIN
    DECLARE @splitValue VARCHAR(5)
    DECLARE @post INT

    WHILE CHARINDEX(';', @stringToSplit) > 0
    BEGIN
        SELECT @pos = CHARINDEX(';', @stringToSplit)
        SELECT @splitValue = SUBSTRING(@stringToSplit, 1, @pos-1)
        INSERT INTO @returnList
        SELECT @splitValue
        SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit -@pos)
    END
    INSERT INTO @returnList
    SELECT @stringToSplit
    RETURN
END

As a joke (but it works nevertheless):
WITH
cte1 AS ( SELECT id, ';'+value1+';' value1, value2, value3
          FROM data ),
cte2 AS ( SELECT id, ';'+CAST(id AS VARCHAR)+';' sid, ';'+code+';' code
          FROM codevalue ),
cte3 AS ( SELECT cte1.id, REPLACE(cte1.value1, cte2.sid, cte2.code) value1, cte1.value2, cte1.value3, cte2.id cid
          FROM cte1
          JOIN cte2 ON cte2.id = 0
        UNION ALL
          SELECT cte3.id, REPLACE(cte3.value1, cte2.sid, cte2.code) value1, cte3.value2, cte3.value3, cte2.id
          FROM cte3
          JOIN cte2 ON cte2.id = cte3.cid + 1 )
SELECT id, SUBSTRING(value1, 2, LEN(value1) - 2) value1, value2, value3
FROM cte3
WHERE cid = ( SELECT MAX(id)
              FROM codevalue )
ORDER BY id

fiddle
Needs CodeValue.id have no gaps. If not then add ROW_NUMBER() column to cte2 and use it for next codevalue row selection (do not forget to alter starting value in static part from 0 to 1).


Manually VACUUM bloated tables once `hot_standby_feedback` is on on postgresql? As seen in this answer by setting postgresql's hot_standby_feedback on it may cause having bloated table on long running queries. Therefore, in case that size goes out of control (my database consumes way too much space because of dead rows) to be able to handle it.

Therefore, I thought to enforce a VACCUMM action in this situation to delete stale and unused rows in case that storage goes out of controll as immediate action.

But how I can VACUUM all the tables of my database manually? Also, how I can make a dedicated user that is able only to VACUUM in order to create custom scripts that I can run in these situations?

If you have hot_standby_feedback = on, a manual VACUUM won't delete the dead row versions if there is a long running query on the standby.

The only way to do that would be to set old_snapshot_threshold to a value different from the default value. Then VACUUM (and autovacuum) will remove dead tuples even if an old transaction might still need them. Note that that would defy the purpose of hot_standby_feedback, as then the long running query might receive a snapshot too old error.


Storing a Billing Address Best Practice in Orders Table Can someone help me understand this user's answer for a CustomerLocation table. I really want a good method for storing addresses in the orders table.

What I'm looking for is how I can set up my addresses so when I edit
them, the order is not effected by the fact that a customer updates
his address or relocates.

As it stands my schema looks similar to:

 Person           |EntityID|
 EntityAddress    |EntityID|AddressID|
 Address          |AddressID|AddressType|AddressLine1|AddressLine2|
 Order            |OrderID|BillingAddressID|

Conceptually speaking, although in your business environment Order and Address are ideas that are closely associated, they are in effect two separate entity types, each with its own set of applicable properties (or attributes) and constraints.
Therefore, as previously stated in comments, I agree with @Erik, and you should organize the logical layout of your database declaring among other elements:

one discrete table to keep Address pieces of information;
one table to retain Customer-specific details;
one table to enclose Order data points; and
one table to contain facts about the associations between Customer(s) and Address(es);

as I will exemplify below.
Expository IDEF1X diagram
A picture is worth a thousand words, so I created the IDEF1X diagram shown in Figure 1 to illustrate some of the possibilities opened by my suggestion:

Customer, Address and their associations
As demonstrated, I portrayed an association with a many-to-many (M:N) cardinality ratio between the entity types Customera and Address; this approach would provide future flexibility because, as you know, a Customer can keep multiple Addresses over time, or even simultaneously, and the same Address can be shared by multiple Customers.
A particular Address can be used in several ways by one-to-many (1:M) Customers; e.g., it can be defined as Physical, and/or it can be set for Shipping, and/or for Billing. Perhaps, the same Address instance can serve each of the aforementioned purposes at the same time, or it may be covering two uses while a different Address occurrence covers the remaining one.

a In some business environments, a Customer can be either a Person or an Organization (situation that would imply a slightly distinct arrangement, as detailed in this answer about a supertype-subtype structure) but with the objective of providing a simplified example, I decided not to include that possibility here. In case you need to cover that situation in your database, the previously linked post shows the method to solve said requirement.

Order, Address, CustomerAddress and Address Roles
Commonly, an Order requires only two kinds of Addresses, one for Shipping and one for Billing . In this way, the same Address instance could fill both Roles for an individual Order, but each Role is pictured by the respective property, i.e., ShippingAddressId or BillingAddressId.
Order is connected with Address via the CustomerAddress associative entity type with the aid of two multi-property FOREIGN KEYs, i.e.,

(CustomerNumber, ShippingAddressId), and (CustomerNumber, BillingAddressId),

both pointing to the CustomerAddress multi-property PRIMARY KEY shown as

(CustomerNumber, AddressId)

… which helps to represent a business rule that stipulates that (a) an Order instance must be linked exclusively with (b) Address occurrences previously associated with the specific Customer who made that Order, and never with (c) a random non-Customer-related Address.
History for (1) Address and for (2) the CustomerAddress association
If you want to supply the possibility of modifying Address pieces of information, then you have to keep track of all the data changes. In this manner I depicted Address as an “auditable” entity type that maintains its own AddressHistory.
Since the nature of a connection between a Customer and an Address can also suffer one or more modifications, I have as well depicted the possiblity of handling such an association as an “auditable” one by virtue of the CustomerAddressHistory entity type.
In this respect, various factors dealt with in Q & A no. 1 and Q & A no. 2, —both about enabling temporal capabilities in a database— are really relevant.
Illustrative SQL-DDL logical layout
Consequently, in terms of the diagram displayed and explained above, I declared the following logical-level arrangement (which you can adapt to meet your needs with exactitude):
-- You should determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on your business context characteristics.

-- Also, you should make accurate tests to define the
-- most convenient INDEX strategies based on the exact
-- data manipulation tendencies of your business domain.

-- As one would expect, you are free to utilize
-- your preferred (or required) naming conventions.

CREATE TABLE Customer (
    CustomerNumber      INT      NOT NULL,
    SpecificAttribute   CHAR(30) NOT NULL,
    ParticularAttribute CHAR(30) NOT NULL,
    CreatedDateTime     DATETIME NOT NULL,
    --
    CONSTRAINT Customer_PK PRIMARY KEY (CustomerNumber)
);

CREATE TABLE Address (
    AddressId           INT      NOT NULL,
    SpecificAttribute   CHAR(30) NOT NULL,
    ParticularAttribute CHAR(30) NOT NULL,
    CreatedDateTime     DATETIME NOT NULL,
    --
    CONSTRAINT Address_PK PRIMARY KEY (AddressId)
);

CREATE TABLE CustomerAddress (
    CustomerNumber  INT      NOT NULL,
    AddressId       INT      NOT NULL,
    IsPhysical      BIT      NOT NULL,
    IsShipping      BIT      NOT NULL,
    IsBilling       BIT      NOT NULL,
    IsActive        BIT      NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT CustomerAddress_PK           PRIMARY KEY (CustomerNumber, AddressId),
    CONSTRAINT CustomerAddressToCustomer_FK FOREIGN KEY (CustomerNumber)
        REFERENCES Customer (CustomerNumber),
    CONSTRAINT CustomerAddressToAddress_FK  FOREIGN KEY (AddressId)
        REFERENCES Address  (AddressId)
);

CREATE TABLE MyOrder (
    CustomerNumber      INT      NOT NULL,
    OrderNumber         INT      NOT NULL,
    ShippingAddressId   INT      NOT NULL,
    BillingAddressId    INT      NOT NULL,
    SpecificAttribute   CHAR(30) NOT NULL,
    ParticularAttribute CHAR(30) NOT NULL,
    OrderDate           DATE     NOT NULL,
    CreatedDateTime     DATETIME NOT NULL,
    --
    CONSTRAINT Order_PK                  PRIMARY KEY (CustomerNumber, OrderNumber),
    CONSTRAINT OrderToCustomer_FK        FOREIGN KEY (CustomerNumber)
        REFERENCES Customer        (CustomerNumber),
    CONSTRAINT OrderToShippingAddress_FK FOREIGN KEY (CustomerNumber, ShippingAddressId)
        REFERENCES CustomerAddress (CustomerNumber, AddressId),
    CONSTRAINT OrderToBillingAddress_FK  FOREIGN KEY (CustomerNumber, BillingAddressId)
        REFERENCES CustomerAddress (CustomerNumber, AddressId)
);

CREATE TABLE AddressHistory (
    AddressId           INT      NOT NULL,
    AuditedDateTime     DATETIME NOT NULL,
    SpecificAttribute   CHAR(30) NOT NULL,
    ParticularAttribute CHAR(30) NOT NULL,
    CreatedDateTime     DATETIME NOT NULL,
    --
    CONSTRAINT AddressHistory_PK          PRIMARY KEY (AddressId, AuditedDateTime),
    CONSTRAINT AddressHistoryToAddress_FK FOREIGN KEY (AddressId)
        REFERENCES Address  (AddressId)
);

CREATE TABLE CustomerAddressHistory (
    CustomerNumber  INT      NOT NULL,
    AddressId       INT      NOT NULL,
    AuditedDateTime DATETIME NOT NULL,
    IsPhysical      BIT      NOT NULL,
    IsShipping      BIT      NOT NULL,
    IsBilling       BIT      NOT NULL,
    IsActive        BIT      NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT CustomerAddressHistory_PK                  PRIMARY KEY (CustomerNumber, AddressId, AuditedDateTime),
    CONSTRAINT CustomerAddressHistoryToCustomerAddress_FK FOREIGN KEY (CustomerNumber, AddressId)
        REFERENCES CustomerAddress (CustomerNumber, AddressId)
);

If you want to have a look, I tested it in this db<>fiddle that runs on SQL Server 2017.
The History tables
The following excerpt from your question is very important:

What I'm looking for is how I can set up my addresses so when I edit them, the order is not affected by the fact that a customer updates his address or relocates.

The AddressHistory and CustomerAddressHistory tables aid in ensuring that an Order is not affected by Address changes, as all the “previous” rows should be retained in the respective History table and may be involved in SELECT queries whenever necessary.
The Interval encompassed between the values enclosed in AddressHistory.CreatedDateTime and AddressHistory.AuditedDateTime stands for the entire Period during which a certain “past” Address row was  deemed “present”, “current” or “effective”. Similar considerations apply to the CustomerAddressHistory rows.
In turn, the CustomerAddress.IsActive BIT (boolean) column is meant to point out whether a certain Address row is “usable” by a Customer row or not; e.g., if it is set to ‘false’ it would convey the situation in which a Customer is not using that Address anymore and hence it cannot be used for new Orders.
Data manipulation factors
Modifiying and erasing
If a Customer wants to change one or more pieces of information about a given “current” Address, one must ensure that (a) the corresponding Address row that was “present” until the modification took place is INSERTed into the AddressHistory table, and also that (b) the Address row in question is UPDATEd with the new value(s). I suggest that you carry out this process as a single unit of work inside an individual ACID TRANSACTION.
UPDATE operations on the two History tables should be forbidden (trying to “change” history can even have negative legal implications), since each row retained in that “kind” of tables represents a fact that happened in the past, thus it cannot be modified and a row about it should not be altered.
As for DELETEs on these tables, there are very specific cases where they must be enabled in order to comply with, say, confidentiality laws/policies —but that, as usual, depends entirely on the exact requirements of the business environment of significance—.
Retrieval and physical-level tuning
The “present”, “current” or “effective” version of an Address occurrence must be contained as a row in the Address table, but SELECTing the previous “states” of an Address FROM the AddressHistory (or from CustomerAddressHistory) table is easy, and it may be an interesting exercise to enhance your SQL coding skills.
With respect to one of the situations that you mentioned in comments, if you want to retrieve the “second to last version” of an individual Address row FROM its AddressHistory, you have to take into account the MAX(AddressHistory.AuditedDateTime) and the AddressHistory.AddressId that matches the particular Address.AddressId value at hand.
In this regard —at least when building a relational database—, it is quite convenient to first define the corresponding conceptual schema (based on the applicable business rules) and after that declare its subsequent logical DDL arrangement. Once you obtain stable and reliable versions of these fundamental elements (which, of course, can evolve over time), it is time to analyze and determine the best ways to manipulate (via INSERT, UPDATE, DELETE and SELECT operations or combinations thereof) the concerning data, taking care of the optimization of the respective physical level processing (by dint of, e.g., index tunning, hardware upgrades, proper software settings, etc.).
End-users perception, views and application program(s) assistance
Evidently, at the external level of abstraction, Address information is perceived (by end-users) as being part of an Order —and there is nothing wrong with it—, but that does not necessarily mean that the modelers have to design the significant parts of the database in question in correspondance with said perception. On this point, if there is the need to, e.g., print a “full” Order (very feasible), you can “reproduce” it on-demand with the help of a few JOIN operators and WHERE clauses (considering the concerning validity period, etc.) maybe fixed in views for future consumption, sending the pertinent data set to the related application program(s) that, in turn, can enhance its formatting as necessary.
Of course, the application program(s) will be very helpful too when an Order is being effectuated; e.g., a desktop/mobile app window or a web page can:

display only the Address(es) that the involved Customer has established as “usable” (via CustomerAddress.IsActive);
list together all the Addresses that the Customer has enabled for billing service (via CustomerAddress.IsBilling); and
group all the Addresses that the Customer has defined for shipping service (via CustomerAddress.IsShipping);

facilitating in this manner all the involved processes at the GUI (which, naturally, is part of the external level of abstraction of a computerized system).

Alternative
On the other hand, I have seen some systems in which every time that a new Order is effectuated the relevant Address information has to be entered (sometimes repeatedly), and the Address(es) used for past Orders are never erased, hence the Orders are not affected by Address changes.
This course of action can decidedly involve substantial volumes of data duplication, but it is a possibility that —depending on the exact informational requirements of your business domain— could work, so you might like to evaluate its pros and cons as well.

Comment interactions

Could you explain me the reason we need an Address entity? wouldn't be enough just have CustomerAddress? – @Cristiano, on Mar 11 at 19:38:05Z

In this specific case, the original poster —with a now deleted account— presented a scenario involving an Address entity type that holds four discrete properties of interest, i.e., AddressID, AddressType, AddressLine1 and AddressLine2 —as shown in the question itself—, each of which (a) has a particular set of valid values, (b) involves a potential exclusive set of constraints and (c) implies values that have to be manipulated separately. In addition, (d) any given Address instance —i.e., the proper Address entities— may be associated with n occurrences of entities of other types; therefore, (e) Address is an entity type in its own right.
There very well may be other situations where, depending on the exact business requirements, a Customer.Address should be treated as a single property, in which case its values have to be handled atomically at the logical level of abstraction, i.e., you always manipulate every Address value as a whole, never perform operations (INSERTs, UPDATEs, DELETEs, SELECTs) on certain value part(s) only. Of course, if required, one should first set up constraints for the corresponding atomic Customer.Address column, since these would not be necessary for column “fractions”. One of these situations could be the scenario discussed in the prior section that is entitled “Alternative”.
Suggested reading
You requested (in now removed comments) some pointers about sound database literature; therefore, as for theoretical material, I highly advise that you read all the work written by Dr. E. F. Codd, a Turing Award recipient and, of course, the sole originator of the relational model of data (maybe now more relevant than ever). This list includes some of his tremendously influential articles and papers.
Two important works that are not comprised in the aforesaid list are, precisely, his ACM Turing Award Lecture entitled Relational Database: A Practical Foundation for Productivity, from 1981, and his book denominated The Relational Model for Database Management: Version 2, which was published in 1990.
On the conceptual design front, Integrated Definition for Information Modeling (IDEF1X) is a seriously recommendable technique that was defined as a standard in December 1993 by the U.S. National Institute of Standards and Technology (NIST).


Find top by values by percents using range in SQL Server What will be easier way to group records by range of percents.

Here what I try to accomplish

Select top percents between 50 and 75 * from myTable order by PKI

Here is way I did this but I have felling that it cloud be much much easier

select
    top(25) percent
     *
from myTable
where id not in (
            select
                top(25) percent
                 ID
            from myTable
            order by PKI
            )
order by PKI

This should be top 50 - 75 percents of my table ?

Is there better (nicer) way to do this.

Another way of doing it would be

WITH CTE
     AS (SELECT ID,
                PKI,
                ROW_NUMBER() OVER ( ORDER BY PKI) AS RN,
                COUNT(*) OVER()                   AS Cnt
         FROM   myTable)
SELECT ID,
       PKI
FROM   CTE
WHERE  RN BETWEEN 0.5 * Cnt AND 0.75 * Cnt


Backup MS SQL AG on a non-production environment We've created a non-production environment for SQL Server.
This environment uses the SQL Server Always-On Availability Groups as a HA-solution.

To work with SQL Server AG's you have to set the recovery model to full so the log can be synced with the other replicas, but setting the recovery model to full also implies that the log keeps on growing if you don't take backups.

Since this is a non-production environment we're not that interested in the backups of these databases.
What do you guys think is the best way to go in this environment?

Create backups to disk and delete them
Do take backups of this environment
Other suggestions...

So as a preface for this answer, please consider the following points:

If your system is meant to be HIGHLY AVAILABLE, what are your plans for inevitable down-time and data loss? Will the time taken to recover this environment mean it will simply be faster to keep backups?
If your system is simply a copy of production, why are you not mirroring your backup jobs on that system?

The above may highlight why you do in fact want to set up proper backup jobs. This said, here's a really bad idea™.

BACKUP LOG [DBName] TO DISK = N'nul';
BACKUP DATABASE [DBName] TO DISK = N'nul';

This uses the nul device to stream a backup into nothingness. It should take a similar time to how long the read phase of the backup takes.

N.B. The above should not go anywhere near any production systems - you could end up losing data, and your job. The backups taken using this code (and therefore your backup chain) are not recoverable in any way.


Table access for all users and logins I have a server in which many users and logins already have access to
I was wondering if it is possible to allow all of these users and logins SELECT privilege to a specific table within a database?

The easiest way would be to create a role, assign that role select access to the table in question, then assign the users and logins to that role.
https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver15


Load file based on line number in sqlldr I am trying to load header and detail records from a file. The only way I know the difference between the header and the detail records is the line number. The first line is always the header and the other lines are detail records.

How can I specify the line number of a file when doing a conditional load? I would like to do something like:

LOAD DATA
APPEND INTO TABLE x
WHEN [line_number] = 1
([mapping for header line])
WHEN [line_number] > 1
([mapping for detail lines])

The only reason I ask is b/c I take the primary key from the header record in the database and insert it into each detail record. That is a sequence that doesn't get populated until the insert into the header table. I have to load the header/detail in the same transaction because I can't call currval on the sequence until I call nextval - which is only called in the header insert.

Is there a way for me to do this?

skip=N is what you're looking for. Skips the first N rows, so in your case you just want to skip the first.

OPTIONS(skip=1)
LOAD DATA
... rest of normal control file


You can use LAG() function in a query with GROUP BY just like any other query. The only difference is that the columns allowed in the window (OVER) and in the LAG are the ones allowed in SELECT after a GROUP BY:

select
    date,
    session_id,
    sum(upload_usage) as upload,
    sum(download_usage) as download,
    sum(total_usage_on_a_day) as total_as_cumm,
    sum(total_usage_on_a_day)
    - coalesce(lag(sum(total_usage_on_a_day)) over (partition by session_id order by date), 0)
        as expected_difference
 from jiodba.s_crc_zda_mon_conn_usage
 where gpart = '1100043958'
   and zzaccess_ntwk_id = 'FTTH'
 group by session_id, date
 order by session_id, date
 limit 100 ;

I'm not sure why you used max(usage) while you name the column total_usage. I changed it to use sum() instead. If you need to use max(usage) there, pick a more appropriate name for that column.


Trigger to delete inserted record if it doesn't exist in another table I have a question.  I have two tables:

zip_code (zip_id, zip_code, state) - this is a static table that
stores areas I service
tech_zips (tz_id, tech_id, zip) - this is where I store what zip codes a
specific tech services

So, on my form, I have a select list that chooses a tech and a textarea to paste in zip codes.  This works fine, except it doesn't take into consideration whether or not I service a zip code.

Instead of coming up with crazy arrays and whatnot in PHP - I figured a trigger would be easier, except I know nothing about them.  :(

How would I write a trigger that does this: When a new zip code is inserted into tech_zips, the trigger sees whether or not that zip  exists in the zip_code table.  If it doesn't, then it deletes it.

Thanks for any help!  Oh, I have phpmyadmin.

Well, it's a crazy and novel idea, but you might consider that this is what foreign key constraints are actually for... you can't insert a row in table "x" if a column there has a foreign key constraint against a column in table "y" but the value you're inserting in "x" doesn't exist in "y"...

mysql> create table t1 (
         id int not null auto_increment primary key
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.40 sec)

mysql> create table t2 (
         id int not null auto_increment primary key,
         t1_id INT NOT NULL,
         FOREIGN KEY(t1_id) REFERENCES t1(id)
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.49 sec)

mysql> insert into t2 (t1_id) VALUES (1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`))

mysql>


Best practice - use of derived table or use the original table in join? Imagine I have the following tables in Teradata :

TableA :  Field1|Field2|Field3
TableB :  Field1|Field4|Field5

I am trying to join these two tables

Method 1 :

SEL TableA.Field3, TableB.Field4
FROM TableA
LEFT JOIN TableB
 ON TableA.Field1 = TableB.Field1

WHERE TableB.Field5<>0

Method 2 :

SEL TableA.Field3, TableB.Field4
FROM TableA
LEFT JOIN TableB
   ON TableA.Field1 = TableB.Field1
  AND TableB.Field5<>0

Method 3 :

   SEL TableA.Field3, TableB.Field4
    FROM TableA
    LEFT JOIN (
                SEL Field1 ,Field4
                FROM TableB
                WHERE Field5<>0
              ) DTable
    ON TableA.Field1 = DTable.Field1

Question :
I always go for Method 3. But recently my colleague advised it is not the best coding practice as it is using the subquery in join.
Is there anything like a coding standard for joins ?

Your colleague is right.  Method 3's subquery can waste compute.  Therefore use Method 2.

Method 1 and Method 3 do not yield the same results.  The WHERE clause in Method 1 performs the filtering after joining the tables.  In effect, it turns the LEFT JOIN into an INNER JOIN.

Method 2 is equivalent to Method 3 because the AND clause performs the filter on TableB before the join (which is what the derived query does).

I created a SQLFiddle which illustrates the case.  Full code follows:

CREATE TABLE TableA(
    Field1 int
  , Field2 varchar(10)
  , Field3 varchar(10)
  );

CREATE TABLE TableB(
    Field1 int
  , Field4 varchar(10)
  , Field5 int);

INSERT INTO TableA
SELECT 0, 'Zero', 'Inner'
UNION ALL
SELECT 1, 'One', 'Inner'
UNION ALL
SELECT 2, 'Two', 'Left';

INSERT INTO TableB
SELECT 0, 'Zero', 0
UNION ALL
SELECT 1, 'One', 1
UNION ALL
SELECT 5, 'Five', 5;

SELECT TableA.Field1
  , TableA.Field2
  , TableA.Field3
  , TableB.Field4
  , TableB.Field5
INTO Method1
FROM TableA
  LEFT JOIN TableB on TableA.Field1 = TableB.Field1
WHERE TableB.Field5 <> 0;

SELECT TableA.Field1
  , TableA.Field2
  , TableA.Field3
  , TableB.Field4
  , TableB.Field5
INTO Method2
FROM TableA
  LEFT JOIN TableB on TableA.Field1 = TableB.Field1
    AND TableB.Field5 <> 0;

SELECT TableA.Field1
  , TableA.Field2
  , TableA.Field3
  , DTable.Field4
  , DTable.Field5
INTO Method3
FROM TableA
  LEFT JOIN (SELECT *
             FROM TableB
             WHERE TableB.Field5 <> 0) DTable
  on TableA.Field1 = DTable.Field1;

SELECT *
FROM Method1;

SELECT *
FROM Method2;

SELECT *
FROM Method3;


Recover SA Password - PSTools - Not working I'm on a new company, and the first thing I saw is that they don't even know how many instances they have. they don't have passwords and the old DBA had no Idea what he was doing.

long story short, I could manage to create my own sysadmin account in almost every server ( I will move all databases to only 1 or 2 instances ) but there are some that I just can't.

In this Link by mssqltips there is a tool called PsExec. Following the tutorial I could run the program and connect to SQL Server with NT AUTHORITY\SYSTEM. But it's not working. NT AUTHORITY\SYSTEM is Public and I can't add my account with sysadmin. Does this program runs only with SQL 2008?

There's no way to stop servers because our databases aew running for all over Brazil.

Countless posts here, but almost everyone needs to restart SQL Server.

How do you reset the sa password?

How to add sysadmin to user in SQL Server 2008 when no sysadmin accounts exist ( This one says about the program, but as I said, it's not working for me (he is using 2008 )).

In this video  he's using the -M method. Does someone already tested it on SQL Server 2014? I would like to know and, I will try.

I can connect with the NT AUTHORITY\SYSTEM user  but when I try to create a sysadmin login or change sa password, it says I have no rights to do that. And then, looking at the nt rights, it's public. I could change the sa login with the -m trick on my machine, but I would like something that does not require to shutdown the SQL.

Here is a technet article that has the detailed steps.

In a nutshell you need to start SQL server in single user mode and connect using sqlcmd to add your account. This should get you what you need as long as you have admin rights at the OS level.

One note is that if the sqlagent is configured to start up automatically you should disable that before you start SQL server in single user mode so the connection isn't taken by the sqlagent.


Oracle 12c (RDS) Questions from a SQL Server DBA I am transitioning into Oracle on RDS (should make it easier than Linux based!) from a SQL Server background and have a few queries;

Index Maintenance. In SQL Server dependant on maintenance window I reorganise anything over x amount of rows with fragmentation > 10% but < 30%. Anything > 30% gets a rebuild. I can't seem to find any advice on this sort of thing in Oracle
Integrity check. In SQL Server I do these often as possible, usually every 24 hours. Again struggling to find best practice for Oracle here. I've seen DBVERIFY mentioned but that's about it
Statistics - I wont do routine maintenance on this (unless done via index rebuild) unless I find an issue. In SQL Server stats are automatically taken care of when a table grows by 500 rows + 20%, is there an Oracle equivalent?
TABLESPACES - not so much of a maintenance issue, however I believe these are equivalent to databases files in SQL SErver which can grow in a similar fashion, I believe by "AutoExtend". However how do I set what percent or size a TABLESPACE should grow by? And also is it not better practice to presize these to avoid any growth during production hours?
What are the best "DBA" and performance views to use? i.e. in SQL Server I will use sys.dm_exec_Requests or sp_who2 to see what is hitting the server, sys.dm_os_performance_counters to see performance counters and will log into the box to use perfmon for a graphic interface, what are the rough equivalents of this?
On the above point, are the key metrics in Oracle the same as SQL Server, roughly? I.e. PLE, buffer hit cache, disk latency etc

Any other links or advice which will give me enough knowledge to "Keep the lights on" and know where to look if an issue arises would be greatly appreciated.

Index Maintenance: Generally speaking you don't need to rebuilt b-tree indexes because they are self-managed.

When to rebuild Indexes?

Index Internals- Rebuilding The Truth

Integrity Check: Oracle has DBVERIFY(dbv) command-line utility for physical data structure integrity check. But you can't use this tool on Amazon RDS as it doesn't provide shell access to database server.

 DBVERIFY: Offline Database Verification Utility

Statistics: The preferred method for gathering statistics in Oracle is to use the supplied automatic statistics gathering
job. Or you can gather it manually using DBMS_STATS package. Details are given in the following white paper.

Best Practices for Gathering Optimizer Statistics

  In SQL Server stats are automatically taken care of when a table grows by 500 rows + 20%, is there an Oracle equivalent?

How often we should gather statistics is depend upon how much changes are being made in the tables which affect the query plan. In other words when statistics become stale we need to gather statistics. You can set stale percentage threshold for a table using STALE_PERCENT(default is 10%) parameter of SET_TABLE_PREFS procedure under DBMS_STATS package.
For details:

DBMS_STATS

Tablespaces: You can define the increment size using AUTOEXTEND ON NEXT clause either in CREATE TABLESPACE or ALTER TABLESPACE statements.

  And also is it not better practice to presize these to avoid any growth during production hours?

Auto extending doesn't affect the performance noticeably as it doesn't happen frequently unless we specify very small size on NEXT clause.

CREATE TABLESPACE

 Important DBA and Performance Views: Some the of the important dynamic performance views are given below(v$ views).

V$SESSION: Currently active sessions.

V$SQL: SQL command recently executed and its related information.

V$SGA: Statistics related to Oracle's shared memory called SGA.

Dynamic Performance View Description

 Key Metrics

  On the above point, are the key metrics in Oracle the same as SQL Server, roughly? I.e. PLE, buffer hit cache, disk latency etc.

Generally speaking, these are same just Oracle has different terminologies to address them. For example, Page is equivalent to Data Block in Oracle and life expectancy of a block is determined by touch count algorithm similar to LRU(Least Frequently Used) algorithm.


How to execute alter database commands from DbUp scripts? I am using DbUp to maintain and upgrade my SQL Server 2016 databases. Typical SQL scripts that I have executed so far included tasks like creating tables, creating or altering stored procedures, etc.

I am going to start using Memory Optimised Tables, and need to run ALTER DATABASE commands to create the necessary filegroups in the existing databases. I tried running a dynamic SQL script that builds and runs ALTER DATABASE commands containing the database name that the script is currently running for, but since DbUp uses transactional execution, it seems that ALTER statements are not allowed and I am getting this error:

Script block number: 2; Block line 1; Message:
System.Data.SqlClient.SqlException (0x80131904): ALTER DATABASE statement not allowed within multi-statement transaction.
ALTER DATABASE statement not allowed within multi-statement transaction.
ALTER DATABASE statement not allowed within multi-statement transaction
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

Any ideas how I can run ALTER DATABASE commands via DbUp, or is there an alternative way how to handle this case?

Thanks in advance.

This is DbUp's default behavior; nothing you can do on the SQL Server side. There's a similar discussion on DbUp's github issues section with workaround that may work for you so do check it out. I don't think there's a DbUp tag on SE so if those workarounds don't work for you, try posting on that page or their discussion group.


Need to use one and one only of multiple parameters in a stored procedure The issue is that I have 4 parameters for the user to select, and he/she can only choose one of the four.  In my current WHERE clause I have this logic...

WHERE (PARAMETER1 = DBFIELD1
    OR PARAMETER2 = DBFIELD2
    OR PARAMETER3 = DBFIELD3
    OR PARAMETER4 = DBFIELD4)

But this brings back all the rows in the db.  I need to somehow NULL out the parameters that are not being used.  Is that possible within the WHERE clause?

I think something like the following pattern might work:

WHERE
  (PARAMETER1 = DBFIELD1 AND PARAMETER2 IS NULL AND PARAMETER3 IS NULL AND PARAMETER4 IS NULL)
  OR (PARAMETER2 = DBFIELD2 AND PARAMETER1 IS NULL AND PARAMETER3 IS NULL AND PARAMETER4 IS NULL)
  OR ...

That pattern is pretty ugly but it will only give you rows if one parameter is set and the others are null. In order to give you something more I'd need you to edit your question to better explain what you want.


Oracle SQL Throwing "ORA-00934: group function is not allowed here" error for simple Select w/AVG I am having a hard time with what should be a simple query. I have studied the documentation on the AVG function and understand that I may need to execute this  with a subquery. This is the gist of what I want to accomplish - finding values greater than 3 * the average of the non-zero values of a column. Here is what I thought would work. Any help appreciated.

SELECT COL1, COL2
FROM TABLE1
HAVING COL3 > 0 AND COL3 > AVG(COL3*3)
ORDER BY COL1;

For that version of AVG(), you need to GROUP BY on the columns, but that way your average value would be incorrect. Sure you can use a subquery (where you do not reference any other column, and because of that, you do not need a GROUP BY inside), for example:

SELECT COL1, COL2
FROM TABLE1
WHERE COL3 > (SELECT AVG(COL3) * 3 FROM TABLE1)
ORDER BY COL1;

But this will perform 2 index/table scans, that work can be reduced by using the analytic version of AVG():

SELECT * FROM
(
  SELECT COL1, COL2, COL3, AVG(COL3) OVER () * 3 AS COL3_AVG3
  FROM TABLE1
)
WHERE COL3 > COL3_AVG3;

Edit: forgot about the greater than zero condition, including that:

SELECT COL1, COL2
FROM TABLE1
WHERE COL3 > (SELECT AVG(COL3) * 3 FROM TABLE1 WHERE COL3 > 0)
ORDER BY COL1;

And:

SELECT * FROM
(
  SELECT COL1, COL2, COL3, SUM(CASE WHEN COL3 > 0 THEN COL3 ELSE 0 END) OVER () / SUM(CASE WHEN COL3 > 0 THEN 1 ELSE 0 END) OVER () * 3 AS COL3_AVG3
  FROM TABLE1
)
WHERE COL3 > COL3_AVG3;


PostgreSQL prevents HDD from spin-down I am using my router with OpenWRT as a small server. To experiment a little with more advanced database administration in my programming projects I set up psql on it. The.server uses my HDD, which is attached via USB to the router. This is no high-availability.system,. primarily as a DB connection point inside my private network.

Everything is going alright so far. I just experienced that my hd-idle setup is not working anymore. I highly suspect PostgreSQL to be the reason for that. Since the drive is not really that quiet and it's standing in my living room, I was Keen to spin the HDD down if I didn't use it for an hour. I now register quite some regular I/O on the device and I guess it's logging or similar processes.
Is it possible to reduce the frequency of these helper processes down to maybe once a day?

OpenWRT doesn't provide anything that I know of anyway to spindown hard drives. From the documentation,

  Unfortunately there is no solution that works always for everybody. The first option to try is provided by the package hd-idle, which will spin down any disk accessible via the SCSI layer. Install it and test if it can spindown the hard disk (let us assume that your disk is /dev/sda):

The other solution is hdparm -y. So what you're doing is using something to tell your drives when to spin down. The idea is you now either

Script a daemon to monitor for when use stops (such as in the case of unmount),
Periodically tell the hard drive to spin itself down and hope it doesn't wake.

If PostgreSQL wakes the drive up, that's absolutely to be expected. Like any decent database, there is always maintenance to be done, and even when you're not accessing that database it will autovacuum. You may consider

Disabling autovacuum (which will help). But, then you'll have to manually VACUUM. Even disabling autovacuum, I have no idea if the PostgreSQL daemon will wake back up the drive.
Shutting down PostgreSQL before you set the hard drives to spindown.


Unique index by VARCHAR(1023) vs by BINARY(32) I have a table of unique urls (~70M records, an average urls length is 51 chars):

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uri` varchar(1023) NOT NULL,
  `uri_hash` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `i_1` (`short_url_code`),
  UNIQUE KEY `i_2` (`uri`(255))
) ENGINE=InnoDB CHARSET=utf8;

uri_hash = hash(uri)

It was created when MySQL did not support long unique indexes. Now we have MySQL 5.7 and I think about optimisation, but I'm not sure wich way is better:

remove uri_hash column and make index i_2 longer (1023), and update uri column to support different cases (upper/lower) CHARACTER SET utf8 COLLATE utf8_bin
remove index i_2 that we don't use and search by uri_hash

a. leave uri_hash varchar(64)

b. convert to uri_hash binary(32)
leave as is

I have done some benchmarks, but I have not found significant difference between these variants: the same 60 reads + 60 writes actions per second in one thread

This table will grow up to ~200M records.

Can you suggest what variant is better?

First, I must point out why this is bad:

UNIQUE KEY `i_2` (`uri`(255))

It constrains only the first 255 characters of uri to be unique.  That is, you cannot have two rows that agree in the first 255 characters, but differ after that.

Where is short_url_code?

Back to the question... Answer: "none of the above".  But here are some tips on improving performance:

If you need a uniqueness constraint on a too-long column, here is one approach:

Use UNHEX(MD5(uri)) in a BINARY(16).  Then make that the PRIMARY KEY and get rid of id.

For those who complain that MD5 might have dups, I point out that "If you have 9 trillion rows, there is only one chance in 9 trillion that the next row you insert will be a false dup."

So, I would have only these two indexes:

PRIMARY KEY(md5_uri),
INDEX(uri(44))

The secondary index assumes you need to look up uri prefixes when you don't know the full string.  The 44 was somewhat arbitrarily picked -- smaller to save space; larger to have fewer dups.  More important than the average of 51 you mentioned is "what is the shortest length that leads to a 'reasonably small' number of dups for each prefix."

The problem with any type of hash is the inability to cache the data (and/or index) after the data (or index) becomes bigger than can fit in cache (innodb_buffer_pool_size).

My suggested changes will

shrink amount of cache needed by getting rid of an index and a column, and
have only 1 uniqueness constraint (instead of 3) to check during INSERT.

But you still need more ram than the dataset size, so that innodb_buffer_pool_size has space for caching all the data.  Else, you will stuck with being as slow as your disk drive (which should like a slow spinning drive at 60/sec.)  Are there more columns in the table than what you listed?  If so, we might consider vertical partitioning.

That leaves another problem that you alluded to -- case folding.  No hash is impervious to case without help.  You might do UNHEX(MD5(LOWER(uri))).  But that will also strip accents in utf8.  Not knowing your full set of requirements here, I cannot discuss further.

Another thing to do is to avoid the at-least-one I/O that occurs with every transaction.  Where practical, batch inserts, group things between BEGIN..COMMIT, and/or set innodb_flush_log_at_trx_commit=2 (instead of the default of 1).  SSDs would also help.


Is it possible to define package procedure/function outside of package body block (for Oracle)? Is it possible to define package procedure/function outside of package body block (for Oracle)?

Simplified syntax for defining package body is:

create or replace package body <package_name> IS

  function <function_name>
   <function_body>

  procedure <procedure_name>
   <procedure_body>

end <package_name>;
/

Is it possible to define procedure within package like:

procedure <package_name>.<procedure_name>
 <procedure_body>

I ask this question as I am newbie and don't understand why I need to compile  whole package when I need only to apply changes form a single procedure...

RELATED

What are the benefits of packages over standalone procedures and functions?
https://stackoverflow.com/questions/1812049/performance-standalone-procedure-vs-packaged-procedure-in-oracle

Tom says:

Always use a package.
Never use a standalone procedure except for demos, tests and standalone utilities (that call nothing and are called by nothing)

You always need to compile the entire package body -- it is one item of code. You can create functions and procedures inside other procedures, but that does not change the need to recompile the package body.

What problem are you trying to solve here?


Insert the same time, it is possible to happen? Good evening.

I have a doubt about inclusion in the database. A common example is a user account where the user names must be unique.

In the following logic:

User enters data into a form.
Data are collected via $ _POST (or other method)
Before saving the data is checked if the user name is already registered
If not, save the record, if it is, it informs the user that the username that already exists.

Example:

<?php
$username = $_POST['username'];
$query = "SELECT * FROM `user_tbl` WHERE `username` = '{$username}'";
$result = mysql_query($query);
if ( mysql_num_rows ( $result ) > 1 ) {
    /* Username already exists */
    echo 'Username already exists';
} else {
    /* Username doesn't exist */
    /* a certain time is elapsed after checking */
    /* .. insert query */
}

My question is, in a system with high volume of requests, it is possible that between the time to check whether the user name already exists and the inclusion (if the user name does not exist), another user can do the same action in same time?

Theoretically you could end up with duplicate usernames.
In a scenario of high incoming requests, a race condition could occur.

If you want to avoid this, just put an UNIQUE index on your column.
You could create a UNIQUE index on username by using this query:

CREATE UNIQUE INDEX IDX_USERNAME ON `user_tbl` (`username`);

Prior to creating this unique index, please verify that there are no duplicate entries in your table, as the index creation would fail.

When you have an unique index on this field, it will be impossible to end up with duplicate usernames.
When a username is inserted, the table is locked during the insertion, this guarantees that a race condition would not occur.


Create table shows ERROR 1005(HY000) errno 150 i tried to create tables as per given in my textbook in MySQL server as follows:

create table department
(dept_name      varchar(20),
 building       varchar(15),
 budget             numeric(12,2),
 primary key (dept_name)
);

create table course
(course_id      varchar(8),
 title          varchar(50),
 dept_name      varchar(20),
 credits        numeric(2,0),
 primary key (course_id),
 foreign key (dept_name) references department
);

the department table gets created, but an error

ERROR 1005 (HY000): Can't create table 'test.course' (errno: 150)

is displayed.

The queries seem very simple. And i am unable to find any mistake.
What can be the possible error that is preventing me to create the table?

You are missing column name of parent table to which your child refer, while creating child table, do like below

create table department
(dept_name      varchar(20),
 building       varchar(15),
 budget             numeric(12,2),
 primary key (dept_name)
);

create table course
(course_id      varchar(8),
 title          varchar(50),
 dept_name      varchar(20),
 credits        numeric(2,0),
 primary key (course_id),
 foreign key (dept_name) references department(dept_name)
);


tds: unable connect to SQL Server using servername specified in freetds.conf file I am using freetds to connect to SQL Server from ubuntu.

First, the SQL Server is running at port 1433 currently. I can telnet it. The following command also works. I can query table data using it,

sqsh -H 192.168.10.249 -p 1433 -H xx -P xxxxxx

however, this command does not work, and consequently making my ror app crash.

sqsh -S developer -U xxx -P xxxxxxxxx

this is error inside freetds.log

1:24:59.068648 5010 (log.c:190):Starting log file for FreeTDS 0.82 on 2012-06-04 11:24:59 with debug flags 0xffff.
11:24:59.069259 5010 (iconv.c:197):names for ISO-8859-1: ISO-8859-1
11:24:59.069298 5010 (iconv.c:197):names for UTF-8: UTF-8
11:24:59.069317 5010 (iconv.c:197):names for UCS-2LE: UCS-2LE
11:24:59.069335 5010 (iconv.c:197):names for UCS-2BE: UCS-2BE
11:24:59.069352 5010 (iconv.c:363):iconv to convert client-side data to the "UTF-8" character set
11:24:59.069394 5010 (iconv.c:516):tds_iconv_info_init: converting "UTF-8"->"UCS-2LE"
11:24:59.069461 5010 (iconv.c:516):tds_iconv_info_init: converting "ISO-8859-1"->"UCS-2LE"
11:24:59.069492 5010 (net.c:836):tds7_get_instance_port(192.168.10.249, MSSQLSERVER)
11:25:00.070784 5010 (net.c:905):tds7_get_instance_port: timed out on try 0 of 16
11:25:01.072121 5010 (net.c:905):tds7_get_instance_port: timed out on try 1 of 16
11:25:02.073441 5010 (net.c:905):tds7_get_instance_port: timed out on try 2 of 16
11:25:03.074756 5010 (net.c:905):tds7_get_instance_port: timed out on try 3 of 16
...
11:25:15.095377 5010 (net.c:905):tds7_get_instance_port: timed out on try 15 of 16
11:25:15.095480 5010 (net.c:974):instance port is 0
11:25:15.095506 5010 (login.c:418):invalid port number
11:25:15.095532 5010 (mem.c:563):tds_free_all_results()
11:25:15.095580 5010 (ct.c:630):leaving ct_connect() returning 0
11:25:15.095605 5010 (ct.c:334):ct_con_props() action = CS_GET property = 9143
11:25:15.095630 5010 (ct.c:1865):ct_con_drop()
11:25:15.095652 5010 (ct.c:242):ct_exit()

this is my freetds.conf file

[global]
;   tds version = 4.2

    # Whether to write a TDSDUMP file for diagnostic purposes
    # (setting this to /tmp is insecure on a multi-user system)
;   dump file = /tmp/freetds.log
;   debug flags = 0xffff

    # Command and connection timeouts
;   timeout = 10
;   connect timeout = 10

    text size = 64512
[egServer50]
    host = symachine.domain.com
    port = 5000
    tds version = 5.0

[developer]
    host = 192.168.10.249
     port = 1433
    #instance = MSSQLSERVER
    tds version = 8.0
    client charset = UTF-8
    dump file = /home/daisy/freetds.log
    dump file append = yes
    debug flags = 0xffff

From the log output, I can see the program does not read the SQL Server port correctly in this case, it reads 0 which is wrong.

It also outputs some information relating to encoding. so I guess it may relate to the translation issue.

tds7_get_instance_port means freedts is using udp 1434 to query the port of the instance (source: net.c). But this should only be called if the instance is specified. Please doublecheck your freetds.conf