Today we have a guest editorial as Steve is away at SQL Bits.
Have you ever had a discussion with your manager and he/she threw around phrases like “out-of-the-box” and “one-size-fits-all?” If you have, then hopefully you were able to maintain a poker face and not cringe too much.
These dangerous words were presented to me during a conversation about SQL Server and multi-site fail-over / replication. My boss wanted a single strategy which would perform optimally for all of our systems, be fully supported by Microsoft, and have zero impact to any of our development practices and database structure. A single, company-wide strategy might be possible for some companies that only have a single type of system, but my company deals with many different data processes. We have customer facing transactional systems, internal transactional systems, back-end high-volume processing systems, BI data warehouses / cubes, and every variation in-between.
With each of our types of data processing, I would normally recommend different disaster recovery or active-active strategies with varying data loss SLAs and RTOs. In addition, seamless implementation without impacting the development teams is unreasonable. Each strategy has the potential to induce constraints, or at least, change the existing procedures for dealing with failures.
With that said, there is no 100% out-of-the-box fail-over or active-active solution that comes in the form of a one-size-fits-all black box. For that matter, SQL Server doesn't have a good replication feature to support multiple masters at all. Attempting to implement a less than optimal method for the type of processing will produce nothing more than a less than optimally functioning system. Multi-site configurations need to be a part of the design of a system rather than an afterthought.
Discover the different ways you can make writing, exploring, and refactoring SQL code refreshingly effortless with SQL Prompt 6. Download a free trial.
Free eBook: SQL Server Backup and Restore
With the tools, scripts, and techniques in this free eBook, you will be prepared to respond quickly and efficiently to disaster, whether it's disk failure, database corruption, or accidental data deletion. Download the free eBook.
New! SQL Doc 3
Rapidly documenting your entire database schema just became even easier with SQL Doc 3. Includes a new UI, documentation of database- and server-level properties, and greater customization control. Download a free trial now.
What is a SQL Server principal? And what does it get a permission on? In this stairway level, you’ll learn about the various principals that can be authorized through permissions to perform actions and access securable objects in the SQL Server instance. More »
When a SQL Server database is operating smoothly and performing well, there is no need to be particularly aware of the transaction log, beyond ensuring that every database has an appropriate backup regime and restore plan in place. When things go wrong, however, a DBA's reputation depends on a deeper understanding of the transaction log, both what it does, and how it works. More »
SQL Saturday is coming to Albany, NY on July 26, 2014. This is a free full day of training and networking for SQL Server professionals. This event also features a paid-for precon session with Grant Fritchey on query performance tuning. The event is soon so register while space is available. More »
Question of the Day
Today's Question (by Phil Factor):
Is a Primary Key an index?
Think you know the answer? Click here, and find out if you are right.
We keep track of your score to give you bragging rights against your peers.
This question is worth
1 point in this category: Database Design.
We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the
Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks. Get your copy from Amazon today.
Yesterday's Question of the Day
(by Jason Whitish):
What will the following query return?
select cast(cast(cast(-2 as varbinary) as tinyint) as int)
This has to do with the size of your datatype. Tinyint is only 2 bytes, so when it looks at the varbinary value, it only sees part of the value.
You can see this more clearly if you use just the value:
-- -2, -2, and 254
select cast(0xFFFFFFFFFFFFFFFE as int)
select cast(0xFFFFFFFFFFFFFFFE as bigint)
select cast(0xFFFFFFFFFFFFFFFE as tinyint)
-- -2, 4294967294, 254
select cast(0x00000000FFFFFFFE as int)
select cast(0x00000000FFFFFFFE as bigint)
select cast(0x00000000FFFFFFFE as tinyint)
-- 254, 254, 254
select cast(0x00000000000000FE as bigint)
select cast(0x00000000000000FE as int)
select cast(0x00000000000000FE as tinyint)
Data Type Conversion (http://technet.microsoft.com/en-us/library/ms191530(v=sql.105).aspx):
"You can convert int, smallint, and tinyint to binary or varbinary, but if you convert the binary value back to an integer value, this value will be different from the original integer value if truncation has occurred. For example, the following SELECT statement shows that the integer value 123456 is usually stored as a binary 0x0001e240:"
Let’s say you want to quickly provide a script to someone that will populate a particular table with data from your database. What if you have 200 tables? This could take some time unless you're using Red Gate's SQL Data Compare, but what if you have no access to the target database? You could certainly use the MERGE statement, but that’s a lot of code to write. What if you could run a script that created a SQL file containing all of the MERGE statements in one file and ready to run, after review of course.
Well, here's that alternative and hopefully not just something that will help you, but get you thinking about other possibilities with script automation and SQLCMD mode.
SQLCMD mode can be enabled via the SSMS Menu Query > SQLCMDMode or Tools > Options > Query Execution and checking the "By default, open new queries in SQLCMD Mode.
Besides, what self-respecting DBA doesn’t want to know how to do it via SQL?
What data is our best candidate? Well, just about any type, lookup, setup, or static data, etc...
We use an extended property called "SupportingData" to identify these tables. This script assumes that you have an EP on all of your supporting data tables, but this can be modified to fit your specific selection criterion.
This script does NOT handle all situations and I'm not advertising it as a complete script. i.e. data types like Text, NTEXT, XML, Image along with those pesky FK dependencies are not addressed. I’m sure there’s a way, it’s just not in this version.
There are many possible applications here, including:
As this was originally designed to be used with SSDT as a method to get our supporting data into source control. Utilizing a small home built VB app to parse the output file into individual files then including these files in your solution and setting the appropriate build action (Pre/Post deploy). POOF! Your data is now in Source Control !
You can put this into a stored procedure or function to call by a single table name, but you'll have to remove the SQLCMD parts. This would be beneficial if you need MERGES throughout the day during development.
*** This is provided as-is and with any code from the internet TEST, TEST, TEST! The goal in providing this script is to get the majority of the work done for you and for you to tweak as needed.
If you copy he output to a new query window the formatting should be retained.
Comments, suggestions and improvements are always welcome.
Identify Application Name
- Hi guys,
Is there any SQL script that I can use to know what application connected to each databases?
Need help in retrive the data from openxml.
- Hi All,
Please read the following query.
DECLARE @MyXML XML
SET @MyXML = '<Item>
<Item accountnumber="900044010163" versionnumber="1" repaymentdate="2013-09-05" />
DECLARE @i INT
EXEC sp_xml_preparedocument @i...
PIVOT more than two columns
- I have data as below in a table
Region Transactions Production Value
EAST Sales LUX 1000
EAST Sales Cinthol 1500
EAST Purchases LUX 1000
EAST Purchases Cinthol 1500
NORTH Sales LUX 3000
NORTH Sales Cinthol 3500
NORTH Purchases LUX 30
Distinct Statement Mystery
- I have a db of Contacts - it's real simple, NAME, ADDRESS, CITY, ST, ZIP, etc...
I CAN DO
How would I tune this query?
- I have this query that I am trying to tune(Just to practice)
[quote]SELECT TOP 7401 T270.C1,T270.C18133,T270.C18005,convert(nvarchar(15),B270.CO18004)+N';'+convert(nvarchar(15),B270.CC18004)+N';'+B270.C18004 B270C18004 FROM T270 LEFT OUTER...
Remote Stop / Start - SQL Services
In joined new environmet, DBA's are responsible of SQL Server Services (e.g. MSSQLSERVER and SQLSERVERAGENT etc.). When there is restart...
adding new data to a table
i have two tables.
table 1 is
PK_GL Section SectionNumber
1 Assets 1
2 Liabilities 2
3 Revenue 3
4 Expense 4
5 Capital 5
and my second table
PK_GL Section Group Financial Account Description Opening Balance Debits Credits Closing Balance Fund Legal Entity FundStructure Code Period End Date AccountName
3487 Test Test Test
Correct Age calculation
- We are using the following to calculate age:
It doesn't seem correct ?
Any ideas would be great?
INSERT INTO @TEMPTABLE(TAG_NAME,TAG_DATA)
SELECT TAG_NAME = 'CUST_AGE',...
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.