SQL Interview Q & A
In this very first blog post – various aspect of the interview questions and answers are discussed. Some people like the subject for their helpful hints and thought provoking subject, and others dislike these posts because they feel it is nothing more than cheating. I’d like to discuss the pros and cons of a Question and Answer format here.
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 2 of 31
- What is RDBMS?
- What are the Properties of the Relational Tables?
- What is Normalization?
- What is De-normalization?
- How is ACID property related to Database?
- What are the Different Normalization Forms?
SQL SERVER – Performance: Do-it-Yourself Caching with Memcached vs. Automated Caching with SafePeak
Caching is one of the most difficult tasks to get right!
Custom CLRType to SqlDbType Mapping
Right now I’m working on implementing our own ‘LocalDate’ object because the built in ‘DateTime’ is causing several issues for us – it has a time component, Mongo DB automatically converts it to Utc when you save. All of that prompted us to come up with a custom object ‘LocalDate’.
The first requirement is that it’s recognised and saved correctly by Mongo DB, which was a relatively painless process. Then I had to make it behave similarly to the ‘DateTime’ object – create some explicit cast functions, implement various operators etc. Then followed binary and xml serialisation – so far, easy.
The problem arose when I tried to write a unit test to save it to SQL Db and I immediately got the following exception:
“No mapping exists from object type LocalDate to a known managed provider native type.”
I thought, fair enough, ADO.NET doesn’t know what to do with the type, so I must somehow specify how to convert it to a SqlDbType.
The problem is that the mapping is hardcoded and cannot be changed, unless I come up with some hacky solution.
So, for the time being the users have to call ‘ToSqlDbType()’ before saving it to SQL.
The code below is responsible for the problem. As you can see there is no easy solution to this 😦
static private MetaType GetMetaTypeFromValue(Type dataType, object value, bool inferLen, bool streamAllowed) {
switch (Type.GetTypeCode(dataType)) {
case TypeCode.Empty: throw ADP.InvalidDataType(TypeCode.Empty);
case TypeCode.Object:
if (dataType == typeof(System.Byte[])) {
// mdac 90455 must not default to image if inferLen is false ...
//
if (!inferLen || ((byte[]) value).Length <= TdsEnums.TYPE_SIZE_LIMIT) {
return MetaVarBinary;
}
else {
return MetaImage;
}
}
else if (dataType == typeof(System.Guid)) {
return MetaUniqueId;
}
else if (dataType == typeof(System.Object)) {
return MetaVariant;
} // check sql types now
else if (dataType == typeof(SqlBinary))
return MetaVarBinary;
else if (dataType == typeof(SqlBoolean))
return MetaBit;
else if (dataType == typeof(SqlByte))
return MetaTinyInt;
else if (dataType == typeof(SqlBytes))
return MetaVarBinary;
else if (dataType == typeof(SqlChars))
return MetaNVarChar; // MDAC 87587
else if (dataType == typeof(SqlDateTime))
return MetaDateTime;
else if (dataType == typeof(SqlDouble))
return MetaFloat;
else if (dataType == typeof(SqlGuid))
return MetaUniqueId;
else if (dataType == typeof(SqlInt16))
return MetaSmallInt;
else if (dataType == typeof(SqlInt32))
return MetaInt;
else if (dataType == typeof(SqlInt64))
return MetaBigInt;
else if (dataType == typeof(SqlMoney))
return MetaMoney;
else if (dataType == typeof(SqlDecimal))
return MetaDecimal;
else if (dataType == typeof(SqlSingle))
return MetaReal;
else if (dataType == typeof(SqlXml))
return MetaXml;
else if (dataType == typeof(SqlString)) {
return ((inferLen && !((SqlString)value).IsNull) ? PromoteStringType(((SqlString)value).Value) : MetaNVarChar); // MDAC 87587
}
else if (dataType == typeof(IEnumerable<DbDataRecord>) || dataType == typeof(DataTable)) {
return MetaTable;
} else if (dataType == typeof(TimeSpan)) {
return MetaTime;
}
else if (dataType == typeof(DateTimeOffset)) {
return MetaDateTimeOffset;
}
else {
// UDT ?
SqlUdtInfo attribs = SqlUdtInfo.TryGetFromType(dataType);
if (attribs != null) {
return MetaUdt;
}
if (streamAllowed) {
// Derived from Stream ?
if (typeof(Stream).IsAssignableFrom(dataType)) {
return MetaVarBinary;
}
// Derived from TextReader ?
if (typeof(TextReader).IsAssignableFrom(dataType)) {
return MetaNVarChar;
}
// Derived from XmlReader ?
if (typeof(System.Xml.XmlReader).IsAssignableFrom(dataType)) {
return MetaXml;
}
}
}
throw ADP.UnknownDataType(dataType);
case TypeCode.DBNull: throw ADP.InvalidDataType(TypeCode.DBNull);
case TypeCode.Boolean: return MetaBit;
case TypeCode.Char: throw ADP.InvalidDataType(TypeCode.Char);
case TypeCode.SByte: throw ADP.InvalidDataType(TypeCode.SByte);
case TypeCode.Byte: return MetaTinyInt;
case TypeCode.Int16: return MetaSmallInt;
case TypeCode.UInt16: throw ADP.InvalidDataType(TypeCode.UInt16);
case TypeCode.Int32: return MetaInt;
case TypeCode.UInt32: throw ADP.InvalidDataType(TypeCode.UInt32);
case TypeCode.Int64: return MetaBigInt;
case TypeCode.UInt64: throw ADP.InvalidDataType(TypeCode.UInt64);
case TypeCode.Single: return MetaReal;
case TypeCode.Double: return MetaFloat;
case TypeCode.Decimal: return MetaDecimal;
case TypeCode.DateTime: return MetaDateTime;
case TypeCode.String: return (inferLen ? PromoteStringType((string)value) : MetaNVarChar);
default: throw ADP.UnknownDataTypeCode(dataType, Type.GetTypeCode(dataType));
}
}
Elliot Balynn's Blog 